不带MASTER_LOG_FILE,MASTER_LOG_POS参数,执行change master 语句复制异常

-------------分析日志-------------
1.先停止复制进程:
dbadmin@(none) 08:05:45>stop slave;
Query OK, 0 rows affected (0.10 sec)

2.查看丛库的状态及复制点坐标:
dbadmin@(none) 08:05:52>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: Master_Host
                  Master_User: Master_User
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004476
          Read_Master_Log_Pos: 289617713
               Relay_Log_File: mysqld-relay-bin.000067
                Relay_Log_Pos: 289617859
        Relay_Master_Log_File: mysql-bin.004476
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 289617713
              Relay_Log_Space: 289618059
              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: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 612
1 row in set (0.00 sec)

3.查看一下master.info与relay-log.info文件中的信息,此时文件中记录的信息与show slave status显示的信息一致。
dbadmin@(none) 08:05:55>system cat /vobiledata/mysqldata/master.info;
18
mysql-bin.004476
289617713
Master_Host
repl
pass
3306
60
0





0
0.000

0
dbadmin@(none) 08:07:52>system cat /vobiledata/mysqldata/relay-log.info;
./mysqld-relay-bin.000067
289617859
mysql-bin.004476
289617713

4.不带MASTER_LOG_FILE,MASTER_LOG_POS参数,执行change master 语句,按照mysql官方文档的说法,执行语句后的复制点应该与之前的复制点一致,然而事实并非如此,执行该语句后,复制点被清空,记录在master.info和relay-log.info文件中的关于复制点的信息也被清空,如下:
dbadmin@(none) 08:08:24>CHANGE MASTER TO MASTER_HOST='Master_Host', MASTER_USER='repl', MASTER_PASSWORD='pass';
Query OK, 0 rows affected (0.11 sec)

dbadmin@(none) 08:08:55>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: Master_Host
                  Master_User: Master_User
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 0
              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: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 612
1 row in set (0.00 sec)


dbadmin@(none) 08:09:01>system cat /vobiledata/mysqldata/master.info;
18

4
Master_Host
repl
pass
3306
60
0





0
1800.000

0
60
0





0
0.000

0
dbadmin@(none) 08:09:17>system cat /vobiledata/mysqldata/relay-log.info;
./mysqld-relay-bin.000001
4

0
7859
mysql-bin.004476
289617713

重启复制进程,发现复制点被莫名奇妙的变成了另外一个值,这个值既不是执行语句前的复制点,也不是执行语句后的复制点。
dbadmin@(none) 08:09:26>start slave;
Query OK, 0 rows affected (0.00 sec)

dbadmin@(none) 08:09:47>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: Master_Host
                  Master_User: Master_User
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004430
          Read_Master_Log_Pos: 9547576
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 360
        Relay_Master_Log_File: mysql-bin.004429
             Slave_IO_Running: Yes
            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: 214
              Relay_Log_Space: 53239
              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: 436462
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: 612
1 row in set (0.00 sec)

dbadmin@(none) 08:09:52>show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: Master_Host
                  Master_User: Master_User
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.004430
          Read_Master_Log_Pos: 61188972
               Relay_Log_File: mysqld-relay-bin.000003
                Relay_Log_Pos: 360
        Relay_Master_Log_File: mysql-bin.004429
             Slave_IO_Running: Yes
            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: 214
              Relay_Log_Space: 53239
              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: 436482
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: 612
1 row in set (0.00 sec)

赶紧停止复制,发现命令居然被长时间卡住,尝试停止mysql服务,超时异常,最后通过kill 系统进程,并重启mysql服务,重新设为正确的复制点。
dbadmin@(none) 08:10:12>stop slave;

查看error log,发现有如下日志:
160510  8:08:55 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='192.168.110.113', master_port='3306', master_log_file='mysql-bin.004476', master_log_pos='289617713'. New state master_host='192.168.110.113', master_port='3306', master_log_file='', master_log_pos='4'.
160510  8:09:47 [Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log './mysqld-relay-bin.000001' position: 4
160510  8:09:47 [Note] Slave I/O thread: connected to master 'repl@192.168.110.113:3306',replication started in log 'FIRST' at position 4

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值