背景
两个互为主备的数据库在不同步的情况下如何去配置同步，背景是之前配置过同步服务因服务器故障重启后同步失效。
1，10.52.39.28
2，10.246.16.11
检查数据库状态
查看10.52.39.28上的slave状态
在10.5239.28上查看slave状态，Slave_IO_Running为No，如下：
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.246.16.11
Master_User: root
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000017
Read_Master_Log_Pos: 326
Relay_Log_File: host-10-52-39-28-relay-bin.000003
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000017
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: 326
Relay_Log_Space: 248
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
Gtid_IO_Pos:
|
查看10.246.16.11上的slave状态
在10.246.16.11查看slave状态 Slave_IO_Running为NO，如下：
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.52.39.28
Master_User: root
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000017
Read_Master_Log_Pos: 326
Relay_Log_File: VM_16_11_centos-relay-bin.000046
Relay_Log_Pos: 613
Relay_Master_Log_File: mysql-bin.000017
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: 1
Exec_Master_Log_Pos: 326
Relay_Log_Space: 1285
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: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl:
Master_SSL_Crlpath:
Using_Gtid: No
|
配置步骤
1，检查master上的二进制文件
在10.246.16.11上执行SHOW MASTER STATUS;或SHOW BINARY LOGS;
MariaDB [(none)]> SHOW MASTER STATUS;
+----------------------------+--------------+----------------------+ ------------------+-----------
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------------------+--------------+----------------------+----------------------+--------
| mysql-bin.000001 | 106896 | | |
+----------------------------+--------------+----------------------+----------------------+-------
# 示例，并未执行查看
MariaDB [(none)]> SHOW BINARY LOGS;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 331 |
| mysql-bin.000002 | 331 |
| mysql-bin.000003 | 331 |
| mysql-bin.000004 | 331 |
| mysql-bin.000005 | 1814895 |
+------------------+-----------+
|
记录下file和Position
file： mysql-bin.000001
Position：106896
2，配置slave上的同步信息
在从服务上10.52.39.28上执行；
1，停止slave
2，根据master上的MASTER STATUS信息配置从服务同步
CHANGE MASTER TO
MASTER_HOST='10.246.16.11',
MASTER_USER='user',
MASTER_PASSWORD='pwd',
MASTER_PORT=3308,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=312;
|
MASTER_LOG_FILE:同步的文件。
MASTER_LOG_POS：同步的位置，从哪开始。应小于查询到的Position值。
3，启动从服务复制
4，检查状态
反过来一样配置，先查询master 10.52.39.28上的文件状态，在从服务上配置slave信息。
重置了复制文件
如有必要的话，当查询SHOW BINARY LOGS;两台数据库的复制文件相差很大的时候可重置复制文件；
执行以下两个命令可以清除以前的复制文件重新开始复制。
