在MySQL的master-slave或dual master的架构中,要重新开始复制可以使用change master to命令,而参数的选择可以来自简单的show slave status命令,举例说明如下:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.2.216
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000001
Read_Master_Log_Pos: 2218353
Relay_Log_File: relaylog.000003
Relay_Log_Pos: 4
Relay_Master_Log_File: bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,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: 2218353
Relay_Log_Space: 107
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: 2003
Last_IO_Error: error connecting to master ‘repl@192.168.2.216:3306′ – retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Connecting to master
Master_Host: 192.168.2.216
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000001
Read_Master_Log_Pos: 2218353
Relay_Log_File: relaylog.000003
Relay_Log_Pos: 4
Relay_Master_Log_File: bin.000001
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,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: 2218353
Relay_Log_Space: 107
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: 2003
Last_IO_Error: error connecting to master ‘repl@192.168.2.216:3306′ – retry-time: 60 retries: 86400
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 0
1 row in set (0.00 sec)
我们看到这个库无法和Master数据库连接了,这时可以先停掉复制:mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
我们都知道目前MySQL的版本slave端是通过2个线程实现复制的,一个是I/O Thread负责连接到master接收binlog信息,在从服务器端这部分信息叫做relay log。另一个SQL Thread负责读取relay log并执行其中的event(在statement format的时候其实就是SQL语句)。这从以上输出的信息也能看出来,IO线程在连接但是连不上,而SQL线程正在执行:Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
接下来我们再用change master to命令修改连接参数和复制起始点,其中重要的MASTER_LOG_FILE和MASTER_LOG_POS参数,这两个参数对应的是IO线程生成relay log的信息,因此和Relay_Master_Log_File和Exec_Master_Log_Pos是对应的,具体命令如下:mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.0.35′,
-> MASTER_PORT = 3306,
-> MASTER_USER=’banping_repl’,
-> MASTER_PASSWORD=’slavepassword’,
-> MASTER_LOG_FILE=’bin.000001′,
-> MASTER_LOG_POS=2218353;
Query OK, 0 rows affected (0.00 sec)
然后再开始复制:mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
再查看slave的状态:mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.35
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: bin.000002
Read_Master_Log_Pos: 1819899
Relay_Log_File: relaylog.000003
Relay_Log_Pos: 76625
Relay_Master_Log_File: bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: mysql,performance_schema,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: 76485
Relay_Log_Space: 1820328
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: 50236
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
1 row in set (0.00 sec)
可见已经恢复了复制。完整的命令如下:
CHANGE MASTER TO option [, option] ...
option:
MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| MASTER_LOG_FILE = 'master_log_name'
| MASTER_LOG_POS = master_log_pos
| RELAY_LOG_FILE = 'relay_log_name'
| RELAY_LOG_POS = relay_log_pos
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'ca_file_name'
| MASTER_SSL_CAPATH = 'ca_directory_name'
| MASTER_SSL_CERT = 'cert_file_name'
| MASTER_SSL_KEY = 'key_file_name'
| MASTER_SSL_CIPHER = 'cipher_list'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)