- 在my.cnf配置文件增加下面内容:
以下内容部分参数需要根据你实际情况调整路径,例如basedir、datadir、socket、pid_file等,毕竟每个人电脑上路径可能都不一样。
#主从节点server_id不可重复 server_id = 1 basedir = /opt/mysql datadir = /usr/local/mysql/data port = 3306 socket = /usr/local/mysql/mysql.sock pid_file=/usr/local/mysql/mysql.pid log_error=/usr/local/mysql/data/mysql.err log-bin=mysql-bin sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES #gtid配置 gtid-mode=ON enforce_gtid_consistency=on log-slave-updates=true master-info-repository=TABLE relay-log-info-repository=TABLE relay_log = /usr/local/mysql/relay.log relay_log_index = /usr/local/mysql/relay_log.index report-port = 3306 sync-master-info=1 slave_parallel_workers=5 binlog-checksum=CRC32 master-verify-checksum=1 slave-sql-verify-checksum=1 binlog-rows-query-log_events=1 auto-increment-increment = 2 #auto-increment-offset 主1从2 auto-increment-offset = 2 slave-skip-errors = all #防止复制随着mysql启动而自动启动 skip-slave-start = 1 slave-parallel-type = LOGICAL_CLOCK |
- 创建复制用户并授权:
#mysq01节点:
grant replication slave on *.* to ‘mysql01’@’mysql02节点IP’ identified by ‘mysql01’;
flush privileges;
#mysq02节点:
grant replication slave on *.* to ‘mysql02’@’mysql01节点IP’ identified by ‘mysql02’;
flush privileges;
- 执行数据同步命令:
mysql02节点执行:
#初始化主从 stop slave; reset slave all; reset master; #建立主从 change master to master_host ='mysql01节点IP',master_user ='mysql02',master_password ='mysql02',master_auto_position =1; #启动同步 start slave; |
mysql01节点执行:
#初始化主从 stop slave; reset slave all; reset master; #建立主从 change master to master_host ='mysql02节点IP',master_user ='mysql02',master_password ='mysql02',master_auto_position =1; #启动同步 start slave; |
- 开启主从同步前要确保从库和主库基本一致,最好事先恢复一下主库的备份到从库。
备份:
/opt/mysql/bin/mysqldump -umysql01 -p -S /usr/local/mysql/mysql.sock -R --single-transaction --add-drop-database --master-data=2 --databases test01database > /data/backup/test01database.sql |
恢复:
#登录mysql恢复数据 mysql -umysql02 -p source /data/backup/xxx.sql |
- 分别查看主从同步状态
show slave status\G; 查看主从同步状态,即以下三个步骤
查看Slave_IO_Running状态是否为yes
查看Slave_SQL_Running状态是否为yes
查看Last_SQL_Error是否为空
以上检查无误的话代表双主同步运行正常
- 测试同步效果
可以在mysql01节点创建一张测试表,添加一些数据,然后在mysql02查看测试表是否同步过来,在mysql02节点上对该测试表增加数据,切回mysql01查看是否同步了mysql02新增的数据。