Mysql主从---删除master.info和relya-log.info实验
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 ~]#
浙公网安备 33010602011771号