详解MySQL主从不一致情形与解决方法

innodb_flush_logs_at_trx_commit = 1
innodb-support_xa = 1 # Mysql 5.0 以上
innodb_safe_binlog   # Mysql 4.0

skip_slave_start
read_only

mysql>show processlist;

show master status;

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File       | Position | Binlog_Do_DB | Binlog_Ignore_DB       |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 |   3260 |       | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

mysql> show slave status\G                        

Slave_IO_Running: Yes
Slave_SQL_Running: No

stop slave;

set global sql_slave_skip_counter =1;
start slave;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql> flush tables with read lock;

[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql

mysql> show master status; 
+——————-+———-+————–+——————————-+ 
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+——————-+———-+————–+——————————-+ 
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema | 
+——————-+———-+————–+——————————-+ 
1 row in set (0.00 sec)

[root@server01 mysql]# scp mysql.bak.sql root@192.168.1.206:/tmp/

mysql> stop slave;

mysql> source /tmp/mysql.bak.sql

change master to master_host = ‘192.168.1.206', master_user = ‘rsync', master_port=3306, master_password=”, master_log_file = ‘mysqld-bin.000001', master_log_pos=3260;

mysql> start slave;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

mysql> show slave status\G;
*************************** 1. row ***************************
        Slave_IO_State: Waiting for master to send event
         Master_Host: 192.168.1.205
         Master_User: repl
         Master_Port: 3306
        Connect_Retry: 30
       Master_Log_File: edu-mysql-bin.000008
     Read_Master_Log_Pos: 120
        Relay_Log_File: edu-mysql-relay-bin.000002
        Relay_Log_Pos: 287
    Relay_Master_Log_File: edu-mysql-bin.000008
       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: 120
       Relay_Log_Space: 464
       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: 205
         Master_UUID: 7402509d-fd14-11e5-bfd0-000c2963dd15
       Master_Info_File: /home/mysql/data/master.info
          SQL_Delay: 0
     SQL_Remaining_Delay: NULL
   Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
      Master_Retry_Count: 86400
         Master_Bind: 
   Last_IO_Error_Timestamp: 
   Last_SQL_Error_Timestamp: 
        Master_SSL_Crl: 
      Master_SSL_Crlpath: 
      Retrieved_Gtid_Set: 
      Executed_Gtid_Set: 
        Auto_Position: 0
1 row in set (0.00 sec)