环境说明
主机: Centos6.5
MySQL: 5.7.18
架构: 一主一从
为了节省机器，直接在多实例基础上搭建
Master:
IP: localhsot
port: 3306Slave:
IP: localhsot
port: 3307
原理图
相关配置参数(my.cnf)
master:
server_id=3306
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=table
relay-log-info-repository=table
log_slave_updates
log_bin=mysql-bin
binlog_format=row
binlog_row_image = full
expire_logs_days=0
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%slave:
server_id=3307
gtid-mode=on
enforce-gtid-consistency=1
master-info-repository=table
relay-log-info-repository=table
log_slave_updates
log_bin=mysql-bin
binlog_format=row
binlog_row_image = full
expire_logs_days=0
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=information_schema.%
replicate_wild_ignore_table=performance_schema.%重启服务
mysqld_multi relaod (单实例使用:service mysqld restart)创建复制账号
在master上，
mysql> create user rpl@'%' identified by 'rpl';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave on *.* to rpl@'%' ;
Query OK, 0 rows affected (0.01 sec)将slave指向master
在slave上，
mysql>change master to \
master_host='localhost',\
master_port=3306, \
master_user='rpl', \
master_password='rpl', \
master_auto_position=1 ;
Query OK, 0 rows affected, 2 warnings (0.04 sec)warning可以忽略
mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note | 1759 | Sending passwords in plain text without SSL/TLS is extremely insecure. |
| Note | 1760 | Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information. |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)启动slave
在slave上，
mysql> start slave ;
Query OK, 0 rows affected (0.03 sec)查看状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 6188
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 6401
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes #yes说明已经同步
Slave_SQL_Running: Yes #yes说明已经同步
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 6188
Relay_Log_Space: 6861
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: 3306
Master_UUID: d54fe35a-2a7c-11e7-b24b-000c29217b03
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: d54fe35a-2a7c-11e7-b24b-000c29217b03:1-24
Executed_Gtid_Set: d54fe35a-2a7c-11e7-b24b-000c29217b03:1-24,
f0c7d668-2a7c-11e7-b3c3-000c29217b03:1-3
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)相关问题：
1.创建账号
mysql> grant replication slave on *.* to rpl@'%' identified by 'rpl';
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql>
mysql>
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | Using GRANT for creating new user is deprecated and will be removed in future release. Create new user with CREATE USER statement. |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
提示 grant 方式创建账号已经不推荐了，所以我们上面没用这种方式创建。
2.Slave误提交
情景：误操作，在slave上进行了删除操作，使得复制不同步，报错了。
在slave上查看状态，
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: rpl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000006
Read_Master_Log_Pos: 6977
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 6664
Relay_Master_Log_File: mysql-bin.000006
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,information_schema,performance_schema
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,information_schema.%,performance_schema.%
Last_Errno: 1032
Last_Error: Could not execute Delete_rows event on table t100.t4; Can't find record in 't4', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000006, end_log_pos 6683
Skip_Counter: 0
Exec_Master_Log_Pos: 6451
Relay_Log_Space: 7650
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Delete_rows event on table t100.t4; Can't find record in 't4', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000006, end_log_pos 6683
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3306
Master_UUID: d54fe35a-2a7c-11e7-b24b-000c29217b03
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 170427 03:00:31
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: d54fe35a-2a7c-11e7-b24b-000c29217b03:1-27 #接收到的来自master的列表
Executed_Gtid_Set: d54fe35a-2a7c-11e7-b24b-000c29217b03:1-25,
f0c7d668-2a7c-11e7-b3c3-000c29217b03:1-4 #已经执行的列表
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.03 sec)从Retrieved_Gtid_Set 和 Executed_Gtid_Set 可以发现，slave执行到 sequence_number=25 的位置，在下一个位置报错了，也就是 sequence_number=26 没有执行。
我们可以使用 gtid_purged 逃过这个事务。
mysql> stop slave;
mysql> reset master;
mysql> set @@global.gtid_purged="d54fe35a-2a7c-11e7-b24b-000c29217b03:1-26";
Query OK, 0 rows affected (0.00 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.06 sec)
问题解决，具体原因可以查看中继日志和binlog。
1325
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
