下面演示一下在MySQL 5.7下搭建多主一从的过程:
实验环境:
Master_1: 192.168.10.128
Master_2: 192.168.10.129
Slave_3: 192.168.10.130
一、分别在Master_1和Master_2上导出需要同步的数据库:
在Master_1:
[root@Master_1 mysql]# mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -S/data/mysql/data/mysql.sock -uroot -pPass! ucloud> uc.sql ( 导出整个库)
/usr/local/mysql/bin/mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 -S /data/mysqld/3307/mysql.sock -uroot -pPass! --databases backend uext ucloud > 0901.sql ( 导出多个数据库)
/usr/local/mysql-5.7.12/bin/mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob -S/data/mysqld/3307/mysql.sock -uroot -pPass! ucloud tbl_user >/data/tmp/0901.sql (导出部分表)
在Master_2:
[root@Master_2 mysql]# mysqldump -uroot -p123456 --master-data=2 --single-transaction --databases --add-drop-database xuanzhi_2 >xuanzhi_2.sql
把分别把备份scp到Slave上:
[root@Master_1 mysql]# scp -P22 xuanzhi.sql 192.168.10.130:/data/service/mysql/
[root@Master_2 mysql]# scp -P22 xuanzhi_2.sql 192.168.10.130:/data/service/mysql/
二、在Master_1和Master_2上创建复制账号,这个操作跟MySQL 5.7之前版本一样:
在Master_1:
create user 'repl'@'%' identified by '111111';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
在Master_2:
create user 'repl'@'%' identified by '111111';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
# 重启mysql实例
/usr/local/mysql/bin/mysqladmin shutdown -S/data/mysqld/3307/mysql.sock -uroot -p111111
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysqld/3307/my.cnf &
三、分别Slave上把Master_1和Master_2的数据导入Slave服务器,在导入前先修改MySQL存储master-info和relay-info的方式,即从文件存储改为表存储,在my.cnf里添加以下选择:
master_info_repository=TABLE
relay_log_info_repository=TABLE
更多的详细解析可以参考:http://dev.mysql.com/doc/refman/5.7/en/slave-logs.html
/data/software/mysql-5.7.12/bin/mysqld_safe --defaults-file=/data/mysqld/3306/my.cnf --user=mysql --ledir=/data/software/mysql-5.7.12/bin &
下面进行数据导入:
/data/software/mysql-5.7.12/bin/mysql -S/data/mysqld/3308/tmp/mysql.sock -uroot -pmk1218Pass! -e "source /data/tmp/sz0105.sql" &
[root@Slave mysql]# mysql -uroot -p <./xuanzhi.sql source ./activity.sql【登录mysql导入】
[root@Slave mysql]# mysql -uroot -p123456 <./xuanzhi_2.sql
分别找出Master_1和Master_2的binlog位置和Pos位置:
[root@Slave mysql]# cat xuanzhi.sql |grep " CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='Master_1-bin.000001', MASTER_LOG_POS=1539;
[root@Slave mysql]# cat xuanzhi_2.sql |grep " CHANGE MASTER"
-- CHANGE MASTER TO MASTER_LOG_FILE='Master_2-bin.000003', MASTER_LOG_POS=630;
[root@Slave mysql]#
四、登录Slave进行同步操作,分别change master到两台Master服务器,后面以FOR CHANNEL 'CHANNEL_NAME'区分
<Slave> [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.10.128',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='Master_1-bin.000001',MASTER_LOG_POS=1539 FOR CHANNEL 'Master_1';
Query OK, 0 rows affected, 2 warnings (0.05 sec)
<Slave> [(none)]> CHANGE MASTER TO MASTER_HOST='192.168.10.129',MASTER_USER='repl', MASTER_PASSWORD='123456',MASTER_LOG_FILE='Master_2-bin.000003',MASTER_LOG_POS=630 FOR CHANNEL 'Master_2';
Query OK, 0 rows affected, 2 warnings (0.04 sec)
最后开启主从:
start slave all;