mysql dump 1449_跨版本mysqldump恢復報錯Errno1449

已經有一套主從mysql,新增兩個slave

主庫Server version: 5.6.22-log MySQL Community Server (GPL)

舊從庫Server version: 5.6.28-log MySQL Community Server (GPL)

新增SLAVE 1： Server version: 5.6.22-log MySQL Community Server (GPL)

新增SLAVE 2： Server version: 5.7.10-log MySQL Community Server (GPL)

重新初始化新的兩個slave后，從就從庫的mysqldump文件導入恢復，因為增加了--dump-slave參數，可以看到主庫的位置。

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001196', MASTER_LOG_POS=71925475;

dump文件導入完成后，在新增SLAVE 1執行：

change master to master_host='192.168.72.142', master_user='repl',master_password='password',master_port=3306,MASTER_LOG_FILE='mysql-bin.001196', MASTER_LOG_POS=71925475,MASTER_CONNECT_RETRY=30;

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Queueing master event to the relay log

Master_Host: 192.168.72.142

Master_User: repl

Master_Port: 3306

Connect_Retry: 30

Master_Log_File: mysql-bin.001201

Read_Master_Log_Pos: 821264848

Relay_Log_File: relay-bin.000002

Relay_Log_Pos: 792405

Relay_Master_Log_File: mysql-bin.001196

Slave_IO_Running: Yes

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: 1449

Last_Error: Error 'The user specified as a definer ('usr_pre'@'%') does not exist' on query. Default database: 'pre'. Query: 'SELECT `pre`.`_getAutoIncrement`(_utf8'taobaoSoHeader' COLLATE 'utf8_general_ci')'

Skip_Counter: 0

Exec_Master_Log_Pos: 72717597

Relay_Log_Space: 6129100284

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: 1449

Last_SQL_Error: Error 'The user specified as a definer ('usr_pre'@'%') does not exist' on query. Default database: 'pre'. Query: 'SELECT `pre`.`_getAutoIncrement`(_utf8'taobaoSoHeader' COLLATE 'utf8_general_ci')'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 142

Master_UUID: 6552d58f-7323-11e5-bc52-24b6fdf64510

Master_Info_File: /mysqldata/mysql_data/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State:

Master_Retry_Count: 86400

Master_Bind:

Last_IO_Error_Timestamp:

Last_SQL_Error_Timestamp: 160217 09:44:22

Master_SSL_Crl:

Master_SSL_Crlpath:

Retrieved_Gtid_Set:

Executed_Gtid_Set:

Auto_Position: 0

1 row in set (0.00 sec)

用工具查看binlog日志內容，發現也出錯了，網上查了一下，說是BUG

-bash-4.1$ mysqlbinlog --start-position=72717597 --stop-position=72717598 -d pre mysql-bin.001201

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#160216 20:15:34 server id 142 end_log_pos 120 CRC32 0xd9c6730a Start: binlog v 4, server v 5.6.22-log created 160216 20:15:34

BINLOG '

5hLDVg+OAAAAdAAAAHgAAAAAAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpz

xtk=

'/*!*/;

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 577005919, event_type: 111

ERROR: Could not read entry at offset 72717597: Error in log format or read error.

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

-bash-4.1$ mysqlbinlog --start-position=72717597 --stop-position=72717598 mysql-bin.001201

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#160216 20:15:34 server id 142 end_log_pos 120 CRC32 0xd9c6730a Start: binlog v 4, server v 5.6.22-log created 160216 20:15:34

BINLOG '

5hLDVg+OAAAAdAAAAHgAAAAAAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpz

xtk=

'/*!*/;

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 577005919, event_type: 111

ERROR: Could not read entry at offset 72717597: Error in log format or read error.

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

-bash-4.1$ mysqlbinlog --start-position=72717597 --offset=1 mysql-bin.001201

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;

/*!40019 SET @@session.max_insert_delayed_threads=0*/;

/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;

DELIMITER /*!*/;

# at 4

#160216 20:15:34 server id 142 end_log_pos 120 CRC32 0xd9c6730a Start: binlog v 4, server v 5.6.22-log created 160216 20:15:34

BINLOG '

5hLDVg+OAAAAdAAAAHgAAAAAAAQANS42LjIyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAQpz

xtk=

'/*!*/;

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 577005919, event_type: 111

ERROR: Could not read entry at offset 72717597: Error in log format or read error.

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

懷疑權限問題

主庫5.6.22上執行

mysql> show grants for 'usr_pre'@'%';

+--------------------------------------------------------------------------------------------------------+

| Grants for usr_pre@% |

+--------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'usr_pre'@'%' IDENTIFIED BY PASSWORD '*EA8D6BC15C61128B3E5AB994B3EA12A9092114C5' |

| GRANT ALL PRIVILEGES ON `pre`.* TO 'usr_pre'@'%' |

+--------------------------------------------------------------------------------------------------------+

2 rows in set (0.02 sec)

舊SLAVE 5.6.28 備份導出的從庫

mysql> show grants for 'usr_pre'@'%';

+--------------------------------------------------------------------------------------------------------+

| Grants for usr_pre@% |

+--------------------------------------------------------------------------------------------------------+

| GRANT USAGE ON *.* TO 'usr_pre'@'%' IDENTIFIED BY PASSWORD '*EA8D6BC15C61128B3E5AB994B3EA12A9092114C5' |

| GRANT ALL PRIVILEGES ON `pre`.* TO 'usr_pre'@'%' |

+--------------------------------------------------------------------------------------------------------+

2 rows in set (0.01 sec)

新SLAVE 1是5.6.22，執行報錯

mysql> show grants for 'usr_pre'@'%';

ERROR 1141 (42000): There is no such grant defined for user 'usr_pre' on host '%'

在新SLAVE 1執行

mysql> GRANT USAGE ON *.* TO 'usr_pre'@'%' IDENTIFIED BY PASSWORD '*EA8D6BC15C61128B3E5AB994B3EA12A9092114C5' ;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON `pre`.* TO 'usr_pre'@'%';

ERROR 1133 (42000): Can't find any matching row in the user table

flush privileges;

show grants for 'usr_pre'@'%';

發現已經恢復正常

開啟slave后，可以正常復制。

-----------------------

處理新增SLAVE 2:跨一個大版本，5.7.10

執行下面語句時報錯：

mysql> change master to master_host='192.168.72.142', master_user='repl2',master_password='password',master_port=3306,MASTER_LOG_FILE='mysql-bin.001196', MASTER_LOG_POS=71925475,MASTER_CONNECT_RETRY=30;

ERROR 1805 (HY000): Column count of mysql.slave_master_info is wrong. Expected 25, found 23. The table is probably corrupted

處理方法：

[mysql@lt-mysql02 mysql_backup]$ mysql_upgrade -s -uroot -p

Enter password:

The --upgrade-system-tables option was used, databases won't be touched.

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

The sys schema is already up to date (version 1.5.0).

Found 0 sys functions, but expected 21. Re-installing the sys schema.

Upgrading the sys schema.

Upgrade process completed successfully.