MySQL主主复制
概念
主主复制:实际上与主从复制原理类似,就是两台mysql互为主从,既为主又为从
为什么,会有mysql的主主复制。因为在一些高可用的环境中,mysql的主从不能满足现实中的一些实际需求。比如,一些流量大的网站数据库访问有了瓶颈,需要负载均衡的时候就用两个或者多个的mysql服务器,而这些mysql服务器的数据库数据必须要保持一致,那么就会用到主主复制。
mysql主从架构中其实就一个主在工作,而从就相当于一个备份机器,从通过日志监测的方式来备份主库上的数据而保证主库的数据安全。在这种架构中如果从上的数据做了改变,主数据是不会用任何变化的。因为mysql主从架构主要是mysql从监控mysql主的日志变化来实现同步,相反的在这个架构中主并没有监控从的日志变化。所以,mysql从数据反生变化,主也就没有什么变化了。
通过上述描述,可以看到如果想实现主主复制,无非就是在mysql主从架构上让mysql主实现监测从的日志变化,从而实现两台机器相互同步。
部署MySQL主主复制
实验环境
两台已经安装好MySQL的Linux服务器
192.168.1.4
192.168.1.5
MySQL最好都是刚安装好的,安装步骤可参考部署MySQL
实验目的
当主服务器写入数据后,从服务器会自动更新主服务器的相同数据
实验步骤
192.168.1.4
修改配置文件
vim /etc/my.cnf
# 在[mysqld]模块下添加:
server-id = 1 # 集群中每台mysql的server-id不能重复,必须唯一
log-bin = master-bin # 开启二进制日志并指定以master-bin命名
log-slave-updates = true # 允许slave更新
# 以下两条为了防止主主复制的主键冲突
auto_increment_increment=2 # 每次递增的数值(每条数据的id号以2递增)
auto_increment_offset=1 # 第一个写入表的数据的默认id(从1开始)
# 以上两条表示数据在存储时,将会存储写入的第1、3、5...的数据
重启mysqld服务
systemctl restart mysqld
192.168.1.5
修改配置文件
vim /etc/my.cnf
# 在[mysqld]模块下添加:
server-id = 2
log-bin = master-bin # 因为这台也是一个主,所以也需要有二进制日志
log-slave-updates = true
auto_increment_increment=2 # 每次递增的数值(每条数据的id号以2递增)
auto_increment_offset=2 # 第一个写入表的数据的默认id(从2开始)
# 以上两条表示数据在存储时,将会存储写入的第2、4、6...的数据
重启mysqld服务
systemctl restart mysqld
192.168.1.4
登录mysql数据库
两台mysql需要互相授权,供对方来复制二进制日志文件
mysql -uroot
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.5' IDENTIFIED BY '123.com';
继续在1.4的mysql查看本机正在使用的日志名以及偏移量
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 334 | | | |
+-------------------+----------+--------------+------------------+-------------------+
以上需要的信息为File
字段和Position
字段,可以用来指定slave可以从master中的这个文件中的这个位置开始复制sql语句
192.168.1.5
登录mysql数据库
同样为1.4进行授权
mysql -uroot
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.1.4' IDENTIFIED BY '123.com';
同样在1.5也需要查看本机正在使用的日志名以及偏移量
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 | 334 | | | |
+-------------------+----------+--------------+------------------+-------------------+
在1.5写入1.4对应的日志名和偏移量,用来复制
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.4', /*master主机ip*/
-> MASTER_USER='slave', /*授权用户*/
-> MASTER_PASSWORD='123.com', /*用户密码*/
-> MASTER_LOG_FILE='master-bin.000001', /*在1.4查看到的二进制文件名*/
-> MASTER_LOG_POS=334; /*该二进制文件的偏移量位置*/
mysql> START SLAVE; /*开启从功能*/
查看slave状态,是否复制成功
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.4
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 334
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: master-bin.000001
Slave_IO_Running: connection
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: 334
Relay_Log_Space: 455
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: 22321d25-6a9e-11ea-9f6d-000c2922e28f
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
最主要的看两个信息,如果以下两个为Yes,则成功,可以看到IO为Connecting
,这种情况可以检查防火墙是否放行端口,或者关闭
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
现在我们将两台主机的3306端口在防火墙放行
firewall-cmd --add-port=3306/tcp --permanent
firewall-cmd --reload
将1.5的slave功能停止,重新启动,再次查看状态
mysql> STOP SLAVE;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.4
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 334
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: master-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: 334
Relay_Log_Space: 455
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: 2c90975a-69c3-11ea-99d9-000c2922e28f
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
已经可以看到两个Yes
192.168.1.4
在1.4写入1.5对应的日志名和偏移量,用来复制
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.5', /*master主机ip*/
-> MASTER_USER='slave', /*授权用户*/
-> MASTER_PASSWORD='123.com', /*用户密码*/
-> MASTER_LOG_FILE='master-bin.000001', /*在1.5查看到的二进制文件名*/
-> MASTER_LOG_POS=120; /*该二进制文件的偏移量位置*/
mysql> START SLAVE; /*开启从功能*/
查看slave状态,是否复制成功
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.5
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: master-bin.000001
Read_Master_Log_Pos: 334
Relay_Log_File: 192-relay-bin.000002
Relay_Log_Pos: 284
Relay_Master_Log_File: master-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: 334
Relay_Log_Space: 455
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: 220b4acd-6a9e-11ea-9f6d-000c298f2a3e
Master_Info_File: /usr/local/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
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
1 row in set (0.00 sec)
验证
在1.4创建数据库,查看1.5是否可以获取到
192.168.1.4
mysql> CREATE DATABASE pjf;
192.168.1.5
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| pjf |
| test |
+--------------------+
复制成功
在1.5创建数据库,查看1.4是否可以获取到
192.168.1.5
mysql> CREATE DATABASE cyj;
192.168.1.4
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| cyj |
| mysql |
| performance_schema |
| pjf |
| test |
+--------------------+
复制成功
问题
如果在做主主之前的环境已经做过主从,建议在start slave
时,先reset slave
,否则会启动失败。