mysql主从 1677 mysql主从不一致解决办法

转载

mob64ca1417eedd 2023-08-23 14:39:47

文章标签 mysql主从 1677 mysql 子程序服务器 文章分类 MySQL 数据库

安装：（两个工具都是percona-tools中的）
yum -y install https://www.percona.com/downloads/percona-release/redhat/0.1-6/percona-release-0.1-6.noarch.rpm yum -y install yum install percona-toolkit

情景一：
跳过错误gtid事物(办法一)
由于是运行在GTID模式下，所以不支持sql_slave_skip_counter语法，如果想跳过，就必须把事务ID设置为空值。
Retrieved_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1-2
Executed_Gtid_Set: cf716fda-74e2-11e2-b7b7-000c290a6b8f:1
根据在show slave stauts\G中获得的信息，观察Retrieved_Gtid_Set和Executed_Gtid_Set这两行内容，第一行代表接收到的事务，第二行代表已经执行完的事务。
也就是说，在执行cf716fda-74e2-11e2-b7b7-000c290a6b8f:2这个事务时报错了，这时，只需跳过这个错误事务就可，如下所示：
mysql> stop slave;
Query OK, 0 rows affected (0.07 sec)
mysql> SET GTID_NEXT=‘cf716fda-74e2-11e2-b7b7-000c290a6b8f:2’;
Query OK, 0 rows affected (0.01 sec)
mysql> begin;commit;
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.02 sec)
mysql> SET GTID_NEXT=“AUTOMATIC”;
Query OK, 0 rows affected (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)
show slave status \G;
跳过错误gtid事物(办法二)从库执行
pt-slave-restart -h 127.0.0.1 -uroot -p123456 --skip-count 100
或
pt-slave-restart -h 127.0.0.1 -uroot -p123456 --error-numbers=1236

主从进程必须正常，否则无法将检查表同步到从库(解决办法一跳过错误事物，二从库建检查表)且不一致的表必须有主键或唯一索引，否则执行会很慢，且修复不成功
pt-table-checksum --nocheck-binlog-format --nocheck-plan --nocheck-replication-filters -h主库地址 -P3306 -uroot --ask-pass --recursion-method=processlist
在主库执行，填写主库IP地址和登录信息
pt-table-sync --print --sync-to-master h=‘从库地址’,P=3306,u=root --ask-pass > ./sync.sql
在主库执行，填写从库IP地址和登录信息
pt-table-sync --print --sync-to-master h=‘从库地址’,P=3306,u=root --ask-pass
或
pt-table-sync --execute --sync-to-master h=‘从库地址’,P=3306,u=root --ask-pass

手动创建checksums表语句
create database percona default character set utf8;
CREATE TABLE IF NOT EXISTS percona.checksums (db char(64) NOT NULL,tbl char(64) NOT NULL,chunk int NOT NULL,chunk_time float NULL,chunk_index varchar(200) NULL,lower_boundary text NULL,upper_boundary text NULL,this_crc char(40) NOT NULL,this_cnt int NOT NULL,master_crc char(40) NULL,master_cnt int NULL,ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,PRIMARY KEY(db, tbl, chunk),INDEX ts_db_tbl (ts, db, tbl)) ENGINE=InnoDB;

情景二：
mysql主二报错：
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: ‘binlog truncated in the middle of event; consider out of disk space on master; the first event ‘mysql-bin.000026’ at 1744412540, the last event read from ‘/var/lib/mysql/mysql-bin.000026’ at 1744412540, the last byte read from ‘/var/lib/mysql/mysql-bin.000026’ at 1744416768.’
读取binlog日志错误
解决办法：
主二上停止slave;
mysql>stop slave;

登录主一：
mysql> show master status;
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
| mysql-bin.000027 | 12371 | | | 359ba917-1346-11e6-8583-00163e161146:1-3844526,
46cfede8-dc45-11e8-b9de-00163e1627f2:4-103939 |
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
mysql> flush logs;
Query OK, 0 rows affected (0.08 sec)
记录master的bin的位置:
mysql> show master status;
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
| mysql-bin.000028 | 231 | | | 359ba917-1346-11e6-8583-00163e161146:1-3844526,
46cfede8-dc45-11e8-b9de-00163e1627f2:4-103939 |
±-----------------±---------±-------------±-----------------±----------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

因为刷新日志file的位置会+1，即File变成为:mysqld-bin.000028

主二重新读取binlog进行同步

mysql> change master to master_log_file=‘mysql-bin.000028’, master_log_pos=231;
ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first
mysql> stop slave;

Query OK, 0 rows affected (1 min 15.11 sec)

mysql>
mysql> change master to master_log_file=‘mysql-bin.000028’, master_log_pos=231;
Query OK, 0 rows affected (0.19 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
注意：必须停止slave
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.46.173.141
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000028
Read_Master_Log_Pos: 6148
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 4661
Relay_Master_Log_File: mysql-bin.000028
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

状态为均为yes，问题解决

情况三：1418错误
因为CREATE PROCEDURE, CREATE FUNCTION, ALTER PROCEDURE,ALTER FUNCTION,CALL, DROP PROCEDURE, DROP FUNCTION等语句都会被写进二进制日志,然后在从服务器上执行。但是，一个执行更新的不确定子程序(存储过程、函数、触发器)是不可重复的，在从服务器上执行(相对与主服务器是重复执行)可能会造成恢复的数据与原始数据不同，从服务器不同于主服务器的情况。

为了解决这个问题，MySQL强制要求：
在主服务器上，除非子程序被声明为确定性的或者不更改数据，否则创建或者替换子程序将被拒绝。
这意味着当创建一个子程序的时候，必须要么声明它是确定性的，要么它不改变数据。

第二种是信任子程序的创建者，禁止创建、修改子程序时对SUPER权限的要求，设置log_bin_trust_routine_creators全局系统变量为1。设置方法有三种:
1.在客户端上执行SET GLOBAL log_bin_trust_function_creators = 1;
2.MySQL启动时，加上–log-bin-trust-function-creators选贤，参数设置为1
3.在MySQL配置文件my.ini或my.cnf中的[mysqld]段上加log-bin-trust-function-creators=1

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。