mysql5.7 多主一从,mysql5.7的多主一从的配置

最新推荐文章于 2022-02-07 17:23:52 发布

特效小哥studio

最新推荐文章于 2022-02-07 17:23:52 发布

阅读量258

点赞数

文章标签： mysql5.7 多主一从

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日志和偏移量

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

再将备份文件传到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日志和偏移量

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

+------------------+----------+--------------+------------------+-------------------+

再将备份文件传到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即可

特效小哥studio

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql5.7 多主一从,mysql5.7的多主一从的配置

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....
复制链接

扫一扫