1、wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
2、yum localinstall mysql57-community-release-el7-11.noarch.rpm
3、yum install mysql-community-server
4、systemctl restart mysqld.service
5、systemctl enable mysqld.service
6、grep 'temporary password' /var/log/mysqld.log #找到root用户临时密码
7、mysql -uroot -p
8、alter user 'root'@'localhost' identified by '123';
9、使用新密码重新登录mysql
grant all privileges on *.* to 'root'@'%' identified by '123' with
grant option;
flush privileges;
创建普通用户：
grant all privileges on *.* to 'myuser'@'%' identified by '123';
创建只读用户：
CREATE USER test IDENTIFIED BY 'Test@123';
grant select on *.* to test@"%" identified by "Test@123";
修改密码：
update mysql.user set authentication_string=password('123')
where user='root' and Host ='localhost';
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload
主从复制配置：
1、主库上配置my.cnf
server-id=1
port=*3309
user=mysql
log_bin=master-bin
expire_logs_days=30#binlog-do-db=数据库的名字
#binlog-do-db=**_service
#binlog-do-db=**_api
#binlog-ignore-db=mysql
#binlog-ignore-db=information_schema
#binlog-ignore-db=performance_schema
#binlog-ignore-db=sys
重启主库mysql服务systemctl restart mysqld.service
测试log_bin是否成功开启
show variables like '%log_bin%';
2、主库上添加backup用户
grant replication slave on *.* to 'backup'@'%' identified by '1234';
flush privileges;
锁定一下表，保证同步前数据一致，注意锁定只在当前mysql会话有效
flush tables with read lock;
#unlock tables; #slave同步完成后再解锁
查看master状态，记录二进制文件名(master-bin.000002)和位置(591)
show master status;
+-------------------+----------+--------------+------------------+--------------
-----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+--------------
-----+
| master-bin.000002 | 591 | | |
|
+-------------------+----------+--------------+------------------+--------------
-----+
1 row in set (0.00 sec)
#flush logs; #切换到新的bin文件
3、从库上配置my.cnf
server-id=2replicate_wild_do_table=数据库的名字.%
replicate_wild_do_table=**_service.%
replicate_wild_do_table=**_api.%
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%
replicate_wild_ignore_table=sys.%
重启从库mysql服务systemctl restart mysqld.service
导出主库内容
mysqldump --default-character-set=utf8 --single-transaction --set-gtid-
purged=OFF -hIP -P*3309 -uroot -p*pw -R -E 数据库的名字>
/opt/数据库的名字.sql;
mysqldump --default-character-set=utf8 --single-transaction --set-gtid-
purged=OFF -hIP -P*3309 -uroot -p*pw -R -E **_service>
/opt/**_service.sql;
mysqldump --default-character-set=utf8 --single-transaction --set-gtid-
purged=OFF -hIP -P*3309 -uroot -p*pw -R -E **_api>
/opt/**_api.sql;
导入到从库
CREATE DATABASE `数据库的名字` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `**_service` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
CREATE DATABASE `**_api` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci';
mysql -h127.0.0.1 -P*3309 -uroot -p*pw 数据库的名字 <
/opt/数据库的名字.sql;
mysql -h127.0.0.1 -P*3309 -uroot -p*pw **_service <
/opt/**_service.sql;
mysql -h127.0.0.1 -P*3309 -uroot -p*pw **_api < /opt/**_api.sql;
将从库手动与主库同步之后，在/etc/my.cnf中增加以下内容并重启systemctl restart
mysqld.service
#加上以下参数可以避免更新不及时，SLAVE 重启后导致的主从复制出错
read_only=1 #可以防止普通权限的用户进行写操作，不能分配all, super权限给普通用户
master_info_repository=TABLE
relay_log_info_repository=TABLErelay_log_recovery=1 #从机禁止写
#super_read_only=1 #从机禁止写 （root也无法进行写操作）
从库上执行sql
CHANGE MASTER TO MASTER_HOST='*IP', MASTER_PORT=*3309,
MASTER_USER='backup', MASTER_PASSWORD='1234', MASTER_LOG_FILE='master-
bin.000001', MASTER_LOG_POS=2156;
启动slave同步线程
start slave;
查看slave状态
show slave status\G;
检查主从复制通信状态
Slave_IO_State: Waiting for master to send event #从站的当前状态
Slave_IO_Running： Yes #读取主程序二进制日志的I/O线程是否正在运行
Slave_SQL_Running： Yes #执行读取主服务器中二进制日志事件的SQL线程是否正在运行
Seconds_Behind_Master #是否为0，0就是已经同步了
设置从库只读
set global read_only=1;
