mysql多主以从的配置
环境：mysql5.7版本。myql5.7版本开始才支持多源复制
三台服务器：centos7.6，两台master主机，一台slave主机
IP分别为
192.168.0.111 master1主机
192.168.0.109 master2主机
192.168.0.106 slave主机安装好mysql后
修改配置文件
在master1主机上
~]# vim /etc/my.cnf
server-id=111 //id为唯一的
log_bin=mysql-bin //定义binlog日志的名字
binlog-do-db=test1 //定义同步的库
character-set-server=utf8mb4 //支持utf8mb4编码（中文）
在master2主机上
~]# vim /etc/my.cnf
server-id=109 //id为唯一的
log_bin=mysql-bin //定义binlog日志的名字
binlog-do-db=test2 //定义同步的库
character-set-server=utf8mb4 //支持utf8mb4编码（中文）
在slave主机上
~]# vim /etc/my.cnf
server_id=3 //id为唯一的
character-set-server=utf8mb4 //支持utf8mb4编码（中文）
master_info_repository=table
relay_log_info_repository=table //将中继日志写入表中
而后重启mysql授权和配置主从
master1主机上
mysql> create database test1; //创建一个和配置文件中定义的库
mysql> set global validate_password_policy=0; //设置密码之认证长度
mysql> set global validate_password_length=6; //密码长度为6位数修改密码
mysql> grant replication slave on test1.* to test@"192.168.0.%" identified by "123456";
~]# mysqldump -u root -p --databases test1 > test1.sql //对test1库进行备份
mysql> show master status; //查看binlog日志和偏移量
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 1078 | test1 | | |
+------------------+----------+--------------+------------------+-------------------+
再将备份文件传到slave主机上
master2主机上
mysql> create database test2; //创建一个和配置文件中定义的库
mysql> set global validate_password_policy=0; //设置密码之认证长度
mysql> set global validate_password_length=6; //密码长度为6位数修改密码
mysql> grant replication slave on test2.* to test@"%" identified by "123456";
~]# mysqldump -u root -p --databases test2 > test2.sql //对test2库进行备份
mysql> show master status; //查看binlog日志和偏移量
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 599 | test2 | | |
+------------------+----------+--------------+------------------+-------------------+
再将备份文件传到slave主机上
slave主机上
mysql> create database test2; //这里需要先创建对应的库再进行恢复，否则会报错
mysql> create database test1;
~]# mysql -u root -p --database test1 < test1.sql //将备份文件在slave上恢复到对应的库
~]# mysql -u root -p --database test2 < test2.sql
mysql> CHANGE MASTER TO MASTER_HOST='192.168.0.111',MASTER_USER='test', MASTER_PASSWORD='123qqq...A',MASTER_LOG_FILE=='mysql-bin.000001',MASTER_LOG_POS=1078 FOR CHANNEL 'Master_1';
mysql> change master to master_host='192.168.0.109', master_user='test', master_password='123qqq...A', master_log_file='mysql-bin.000001', master_log_pos=599 for channel 'Master_2';
这里是指定主服务器的地址、用户、密码、binlog日志、偏移量、定义master1和master2
mysql> start slave; //启动slave
mysql> show slave status\G //查看主从状态正确的主从
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.111
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1078
Relay_Log_File: server3-relay-bin-master_1.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 1078
Relay_Log_Space: 538
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: bc99243c-8869-11ea-8983-00155d01f330
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master_1
Master_TLS_Version:
*************************** 2. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.109
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 599
Relay_Log_File: server3-relay-bin-master_2.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 599
Relay_Log_Space: 538
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: a7986e16-a0ab-11ea-98db-00155d01f336
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name: master_2
Master_TLS_Version:
2 rows in set (0.00 sec)启动和停止主从
mysql> stop slave; //停止所有的主从
mysql> stop slave for channel 'master_2'; //停止master_2的主从
mysql> start slave for channel 'master_2'; //启动master_2的主从
主从配置时报错
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'test@192.168.0.111:3306' - retry-time: 60 retries: 27
Last_SQL_Errno: 0
Last_SQL_Error:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 2003
Last_IO_Error: error connecting to master 'test@192.168.0.111:3306' - retry-time: 60 retries: 27
Last_SQL_Errno: 0
Last_SQL_Error: 这个报错，可能是在指定主服务器是指定ip、用户名、密码错误，如没指定错，可能是因为防火墙的原因，关闭防火墙再重启slave即可
扫一扫
804
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
