relay-log.info, master.info 这连个文件时在建立复制时产生的,现在主要说明以下问题:
1、如果修改删除master.info文件,复制会中断么?
不会,如果stop slave,然后start slave还是能正常启动起来,因为MySQL已经记住这里面的信息了,
但是当你重启mysql时,在start slave,这时重启失败,会提示你change master to
测试如下
这里我的主从环境是级联的,结构如下
master-->slave1-->slave2
data-1-1机器安装两个实例,一个是master(3306端口),一个是slave2(3307端口)
data-1-2机器安装一个实例,3306端口,作为slave1
结构图如下
两台机器的环境
#data-1-1机器 [root@data-1-1 ~]# ifconfig eth0 | grep "inet addr" inet addr:10.0.1.81 Bcast:10.0.1.255 Mask:255.255.255.0 [root@data-1-1 ~]# uname -mrm 2.6.32-504.el6.x86_64 x86_64 [root@data-1-1 ~]# cat /etc/redhat-release CentOS release 6.6 (Final) [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "select version();" +------------+ | version() | +------------+ | 5.5.32-log | +------------+ [root@data-1-1 ~]# #mysql路径 #master #/data/3306/ #slave2路径 #/data/3307/ #data-1-2机器 [root@data-1-2 ~]# ifconfig eth0 | grep "inet addr" inet addr:10.0.1.82 Bcast:10.0.1.255 Mask:255.255.255.0 [root@data-1-2 ~]# uname -rm 2.6.32-504.el6.x86_64 x86_64 [root@data-1-2 ~]# cat /etc/redhat-release CentOS release 6.6 (Final) #mysql路径 [root@data-1-2 ~]# ll /application/mysql/
删除slave2的master.info
[root@data-1-1 ~]# mv /data/3307/data/master.info master.info.bak [root@data-1-1 ~]# cat master.info.bak 18 mysql-bin.000004 2448 10.0.1.82 rep oldboy123 3306 60 0 0 1800.000 0 [root@data-1-1 ~]#
不重启slave2的mysq服务,主从不受影响,停止slave和启动slave后也主从是不受影响的
[root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "create database d18;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | d18 | | dd5 | | mysql | | performance_schema | | t1 | | test | +--------------------+ [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "stop slave;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "start slave;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "create database d19;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | d18 | | d19 | | dd5 | | mysql | | performance_schema | | t1 | | test | +--------------------+ [root@data-1-1 ~]#
重启slave2的mysql服务,再观察主从,出现报错提示change matser to
[root@data-1-1 ~]# /data/3307/mysql stop Stoping MySQL... [root@data-1-1 ~]# /data/3307/mysql start Starting MySQL... [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "start slave;" ERROR 1200 (HY000) at line 1: The server is not configured as slave; fix in config file or with CHANGE MASTER TO
在slave1上取位置点,也就是说再slave2的master上取位置点
先让日志滚动一下
下面的操作为了取位置点
[root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "create database d20;" [root@data-1-1 ~]#
slave1上取位置点
[root@data-1-2 ~]# mysqlbinlog /application/mysql/data/mysql-bin.000004 >bin4.log [root@data-1-2 ~]# tail -15 bin4.log # at 2529 #170118 17:31:51 server id 1 end_log_pos 2610 Query thread_id=185 exec_time=18 error_code=0 SET TIMESTAMP=1484731911/*!*/; create database d19 /*!*/; # at 2610 #170118 17:54:31 server id 1 end_log_pos 2691 Query thread_id=186 exec_time=17 error_code=0 SET TIMESTAMP=1484733271/*!*/; create database d20 /*!*/; DELIMITER ; # End of log file ROLLBACK /* added by mysqlbinlog */; /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/; [root@data-1-2 ~]#
salve2重新做主从
CHANGE MASTER TO MASTER_HOST='10.0.1.82', MASTER_PORT=3306, MASTER_USER='rep', MASTER_PASSWORD='oldboy123', MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=2610;
执行如下
[root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.1.82',MASTER_PORT=3306,MASTER_USER='rep',MASTER_PASSWORD='oldboy123',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=2610;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "start slave;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G;" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.1.82 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 2691 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 334 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql 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: 2691 Relay_Log_Space: 484 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: 12 [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "create database d21;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | d18 | | d19 | | d20 | | d21 | | dd5 | | mysql | | performance_schema | | t1 | | test | +--------------------+ [root@data-1-1 ~]#
2、master.info是在什么时候写入的呢?
change master to ..
经过我的测试,它的位置信息会变化的。
[root@data-1-1 ~]# cat /data/3307/data/master.info 18 mysql-bin.000004 2772 10.0.1.82 rep oldboy123 3306 60 0 0 1800.000 0 [root@data-1-1 ~]#
3.关于relay-log.info
删除relay-log.info之后,stop slave和start slave,主从依然正常
[root@data-1-1 ~]# mv /data/3307/relay-log.info relya-log.info.bak [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "create database d22;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show databases;" | grep d22 d22 [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | d18 | | d19 | | d20 | | d21 | | d22 | | dd5 | | mysql | | performance_schema | | t1 | | test | +--------------------+ [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "stop slave;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "start slave;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3306/mysql.sock -e "create database d23;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show databases;" +--------------------+ | Database | +--------------------+ | information_schema | | d18 | | d19 | | d20 | | d21 | | d22 | | d23 | | dd5 | | mysql | | performance_schema | | t1 | | test | +--------------------+ [root@data-1-1 ~]#
重启mysql服务后,主从同步报错
[root@data-1-1 ~]# /data/3307/mysql stop Stoping MySQL... [root@data-1-1 ~]# lsof -i:3307 [root@data-1-1 ~]# /data/3307/mysql start Starting MySQL... [root@data-1-1 ~]# lsof -i:3307 [root@data-1-1 ~]# lsof -i:3307 COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 94497 mysql 12u IPv4 1003041 0t0 TCP *:opsession-prxy (LISTEN) [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G;" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.1.82 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 2934 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1007 Last_Error: Error 'Can't create database 'd20'; database exists' on query. Default database: 'd20'. Query: 'create database d20' Skip_Counter: 0 Exec_Master_Log_Pos: 2610 Relay_Log_Space: 1295 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: 1007 Last_SQL_Error: Error 'Can't create database 'd20'; database exists' on query. Default database: 'd20'. Query: 'create database d20' Replicate_Ignore_Server_Ids: Master_Server_Id: 12 [root@data-1-1 ~]#
处理如下,跳过错误即可
stop slave;
set global sql_slave_skip_counter=1; (1是指跳过一个错误)
slave start;
处理如下
[root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "stop slave;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "set global sql_slave_skip_counter=1;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "start slave;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G;" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.1.82 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 2934 Relay_Log_File: relay-bin.000002 Relay_Log_Pos: 334 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: mysql Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1007 Last_Error: Error 'Can't create database 'd21'; database exists' on query. Default database: 'd21'. Query: 'create database d21' Skip_Counter: 0 Exec_Master_Log_Pos: 2691 Relay_Log_Space: 1591 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: 1007 Last_SQL_Error: Error 'Can't create database 'd21'; database exists' on query. Default database: 'd21'. Query: 'create database d21' Replicate_Ignore_Server_Ids: Master_Server_Id: 12 [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "stop slave;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "set global sql_slave_skip_counter=3;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "start slave;" [root@data-1-1 ~]# mysql -uroot -poldboy123 -S /data/3307/mysql.sock -e "show slave status\G;" *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 10.0.1.82 Master_User: rep Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 2934 Relay_Log_File: relay-bin.000007 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000004 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: mysql 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: 2934 Relay_Log_Space: 549 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: 12 [root@data-1-1 ~]#