MonthFebruary 2016

跨版本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.
Checking if update is needed.
重复执行上面语句,恢复正常
mysql_upgrade是一个检查和升级表的命令,用于检查当前表是否和mysql server版本兼容,例如从低版本的mysqldump导入了高版本的情况。也可以用于升级系统表。
执行该命令的时候,如果该命令发现有表的兼容性有问题,那么会做表升级,
如果发现表有问题,会做表修复,如果该命令无法修复,则需要用重建的方式手工修复表或索引。
推荐每次升级后都执行mysql_upgrade

mysql_upgrade参数-s的意思是只升级系统表,不修复数据表
–upgrade-system-tables, -s
Upgrade only the system tables, do not upgrade data.

mysql的GTID复制和多源复制

配置基于GTID的复制
——————————————–
在参数文件/etc/my.cnf增加下面内容:
主库
master_info_repository=TABLE
relay_log_info_repository=TABLE
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
sync-master-info=1
slave-parallel-workers=2
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
report-host=10.45.10.209
report-port=3306
server_id = 2091
从库除了上面的参数外,还要增加
relay-log = relay-log
relay-log-index = relay-log.index
配置GTID的slave
GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’ IDENTIFIED BY ‘repl123′;
flush privileges;
change master to master_host=’10.45.10.209′, master_user=’repl’,master_password=’repl123′,master_auto_position=1;

GTID复制的好处:
对运维人员来说应该是一件大喜的事情,在主从切换后,在传统的方式里,你需要找到binlog和POS点,然后change master to指向,
而不是很有经验的运维,往往会将其找错,造成主从同步复制报错,在mysql5.6里,你无须再知道binlog和POS点,
你只需要知道master的IP、端口,账号密码即可,因为同步复制是自动的,mysql通过内部机制GTID自动找点同步。

—————————————-
配置多源复制:
即多个主,复制到1个从库,所有db汇总到一个slave的实例
注意:
多源数据库不能有同名库,否则会导致多源复制失败
例如,在220的slave 3307实例上面配置多源复制,汇集3个主的数据
CHANGE MASTER TO MASTER_HOST=’10.45.10.209′, MASTER_USER=’repl’, MASTER_PORT=3307, MASTER_PASSWORD=’repl123′,MASTER_AUTO_POSITION=1 FOR CHANNEL ‘master209-2′;
CHANGE MASTER TO MASTER_HOST=’10.45.10.218′, MASTER_USER=’repl’, MASTER_PORT=3307, MASTER_PASSWORD=’repl123′,MASTER_AUTO_POSITION=1 FOR CHANNEL ‘master218-2′;
CHANGE MASTER TO MASTER_HOST=’10.45.10.219′, MASTER_USER=’repl’, MASTER_PORT=3307, MASTER_PASSWORD=’repl123′,MASTER_AUTO_POSITION=1 FOR CHANNEL ‘master219-2’;

常用管理命令:
mysql> select * from mysql.slave_master_info;

mysql> START SLAVE io_thread FOR CHANNEL ‘master209-2’;
mysql> START SLAVE FOR CHANNEL ‘master209-2’;

mysql> show SLAVE status FOR CHANNEL ‘master209-2’\G
多源复制的好处:
对于备份目的的主备架构,多个主可以备份到一台物理备机上,节约资源

© 2019 Init dba

Theme by Anders NorenUp ↑