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,否则会启动失败。

评论




正在载入...
PoweredHexo
HostedAliyun
DNSAliyun
ThemeVolantis
UV
PV
BY-NC-SA 4.0