1、主库
gtid_mode=on
enforce_gtid_consistency=on
log_bin=on
2、从库
servier-id
gtid_mode=on
enforce_gtid_consistency=on
查看 uuid
# 主库的
mysql> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 8fccbadb-9e86-11ea-8a13-000c293c7a8e |
+---------------+--------------------------------------+
# 从库的
mysql> show variables like '%uuid%';
+---------------+--------------------------------------+
| Variable_name | Value |
+---------------+--------------------------------------+
| server_uuid | 89d50119-d587-11ea-ac01-52540009b164 |
+---------------+--------------------------------------+
3、从库配置
change master to \
master_host='192.168.1.81', \
master_user='Mysync',master_password='Abc@123456',master_auto_position=1;
4、查看从库
# 启用
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.2.104
Master_User: mysync
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000004
Read_Master_Log_Pos: 932
Relay_Log_File: kvm108-relay-bin.000003
Relay_Log_Pos: 1145
Relay_Master_Log_File: mysql-bin.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: school
Replicate_Ignore_DB: mysql,information_schema
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: 932
Relay_Log_Space: 1353
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: 104
Master_UUID: 8fccbadb-9e86-11ea-8a13-000c293c7a8e
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:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 8fccbadb-9e86-11ea-8a13-000c293c7a8e:1-4 # 从库已经接收到主库的事务编号
Executed_Gtid_Set: 8fccbadb-9e86-11ea-8a13-000c293c7a8e:1-4 # 已经执行的事务编号
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
主从错误
1、1236错误
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.'
解决：
查看主库的 Executed_Gtid_Set
mysql> show master status;
+------------------+-----------+--------------+------------------+------------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+------------------------------------------------------------------------------------------------+
| mysql-bin.000616 | 479509052 | | | 010af9c9-89eb-11ea-ab3f-ec0d9ac00bc4:1-1138277,
01aeb901-89eb-11ea-8496-6c92bf464e20:1-1012605 |
+------------------+-----------+--------------+------------------+------------------------------------------------------------------------------------------------+
设置从库的 gtid_purged 值为 主库的 Executed_Gtid_Set
mysql> stop slave;
mysql> reset slave;
mysql> reset master;
mysql> set global gtid_purged="010af9c9-89eb-11ea-ab3f-ec0d9ac00bc4:1-1138277, \
01aeb901-89eb-11ea-8496-6c92bf464e20:1-1012605";
mysql> start slave;
2、跳过错误
stop slave;
# 设置下一个事务，这里的 n 是 Executed_Gtid_Set 值加1
set gtid_next='6d257f5b-5e6b-11e8-b668-5254003de1b6:n';
begin;commit;
set session gtid_next='AUTOMATIC';
start slave;
