MySQL主从复制同步完整配置

两台虚拟机: 主库master:172.16.211.146 从库slave：172.16.211.147
1.设置server-id值并开启bin-log参数
Master: [ip:172.16.211.146 port:3306 server-id=1]

[root@master:/root]# vim /etc/my.cnf

在[mysqld]中添加：
		server-id=1
		log-bin=/usr/local/mysql/data/mysql-bin

○ 重启master中MySQL数据库

[root@master:/root]# /etc/init.d/mysqld stop
[root@master:/root]# /etc/init.d/mysqld start

○ 检查是否log-bin开启成功方法一，查看/usr/local/mysql/data 下是否有mysql-bin.000001和mysql-bin.index○ 检查是否log-bin开启成功方法二，登录mysql执行 show variables like ‘log_bin’;

[root@master:/root]# mysql -uroot -p'oracle2019' -e "show variables like 'log_bin';"

Slave: [ip:172.16.211.147 port:3306 server-id=2]

[root@slave:/root]# vim /etc/my.cnf

在[mysqld]中修改：
      server-id=2

○ 重启slave中MySQL数据库

[root@slave:/root]# /etc/init.d/mysqld restart

2.Master中建立用于slave库复制的账户rep

mysql> create user 'rep'@'172.16.211.146' identified by 'oracle2019';
mysql> update user set host='172.16.211.%' where user='rep';
mysql> grant replication slave  on *.* to 'rep'@'172.16.211.%';

注：MySQL8.0版本中，不能直接使用“grant replication slave on . to
‘rep’@‘172.16.211.%’ identified by
‘oracle2019’;”，需要先创建rep用户，再授权。否则会报"ERROR 1064 (42000): You have an
error in your SQL syntax; check the manual that corresponds to your
MySQL server version for the right syntax to use near ‘identified by
‘oracle2019’’ at line 1" 错误。

mysql> select host,user from user;
mysql> show processlist;

3.Master库做备份并传文件至slave备库
○先执行锁库命令 （此窗口不能关断）

mysql> flush table with read lock;

○查看matser上的备份点

mysql> show master status;
mysql> show master logs;

○执行备份命令（新开一个窗口）

[root@master:/opt]# mysqldump -uroot -p'oracle2019' -A -B --events |gzip > /opt/rep.sql.gz

○备份完毕解锁(此步骤可以呆从库同步后再解锁)

mysql> unlock tables;

○传备份文件到slave库

[root@master:/opt]# scp /opt/rep.sql.gz root@172.16.211.147:/opt/

4. Slave库导入备份文件

[root@slave:/opt]# gzip -d /opt/rep.sql.gz
[root@slave:/opt]# ls
[root@slave:/opt]# mysql -uroot -p'oracle2019' < /opt/rep.sql

[root@slave:/root]# mysql -uroot -p'oracle2019';

mysql> CHANGE MASTER TO
    -> MASTER_HOST='172.16.211.146',
    -> MASTER_PORT=3306,
    -> MASTER_USER='rep',
    -> MASTER_PASSWORD='oracle2019',
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=1666;

注：假如开启了–master-data=1，那么MASTER_LOG_FILE='mysql-bin.000001’和MASTER_LOG_POS=1666可以省略

mysql> start slave;

5.slave库检测是否成功

mysql> show slave status\G

6.新建一个database测试是否同步完成
○ Master中

mysql> create database mappletest;

○ Slave中(测试成功)

mysql> show databases;

7.注意点及已出错点

mysql> show variables like '%timeout%';