『Mysql集群』Mysql高可用集群之主从复制 (一)

mysql> show master status;

mysql>start slave;

mysql> show slave status \G;
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)

测试

通过Pos配置的主从复制已全部完成,现在我们通过测试,看是否配置成功.

在主服务器中,选择自己已创建的数据库

mysql> use gorgor;

在主服务器中, 创建表

mysql> CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 113.125.182.96
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4
               Relay_Log_File: VM-20-3-centos-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: 
             Slave_IO_Running: No
            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: 0
              Relay_Log_Space: 154
              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: 1236
                Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '', and the missing transactions are '458fa628-89dc-11ef-809d-00163e019b7a:1'.'
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 130
                  Master_UUID: f93b2c7b-8a01-11ef-b45c-fa163ec11a89
             Master_Info_File: /var/lib/mysql/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: 241015 18:04:31
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 1
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

异常信息

Got fatal error 1236 from master when reading data from binary log: 'The slave is connecting using CHANGE MASTER TO MASTER_AUTO_POSITION = 1, but the master has purged binary logs containing GTIDs that the slave requires. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. The GTID set sent by the slave is '', and the missing transactions are '458fa628-89dc-11ef-809d-00163e019b7a:1'.'

解决办法(从节点从来没有过gtid 值的时候）

在主节点执行：

mysql> show variables like '%gtid_purged%';
+---------------+----------------------------------------+
| Variable_name | Value                                  |
+---------------+----------------------------------------+
| gtid_purged   | 458fa628-89dc-11ef-809d-00163e019b7a:1 |
+---------------+----------------------------------------+
1 row in set (0.00 sec)

然后再在从节点设置gtid_purged为主节点gtid_purged值。

stop slave;
set global gtid_purged="458fa628-89dc-11ef-809d-00163e019b7a:1";
start slave;
show slave status\G

然后通过show slave status\G,发现Slave_IO及Slave_SQL进程都正常运行，即YES状态.

测试

在主服务器中,插入一条数据

INSERT INTO `gorgor`.`user`(`id`, `name`, `age`, `create_time`, `update_time`) VALUES (2, 'fairy', 28, '2024-10-15 17:47:56', NULL);

在从服务器中,查看表的数据,可以查出此数据

mysql> select * from user;
+----+--------+-----+---------------------+-------------+
| id | name   | age | create_time         | update_time |
+----+--------+-----+---------------------+-------------+
|  1 | gorgor |  30 | 2024-10-15 15:56:25 | NULL        |
|  2 | fairy  |  28 | 2024-10-15 17:47:56 | NULL        |
+----+--------+-----+---------------------+-------------+
2 rows in set (0.00 sec)