开发者社区> miles_wong> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

mysql GTID主从复制 跳过复制错误

简介: 在mysqlGTID下,使用 SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n 会产生如下错误 ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.
+关注继续查看

在mysqlGTID下,使用

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n

会产生如下错误

ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction

根据报错提示,我们可以看到我们可以通过执行空事务来跳过复制错误

STOP SLAVE;
SET GTID_NEXT="7d72f9b4-8577-11e2-a3d7-080027635ef5:5";
BEGIN; COMMIT;
SET GTID_NEXT="AUTOMATIC";
START SLAVE;

但是当需要跳过的事务较多时,这个方法比较麻烦。可以使用MySQL Utilities中的mysqlslavetrx跳过错误
mysqlslavetrx使用说明

mysqlslavetrx --gtid-set=87fd24be-683d-11e6-ba97-1418774c98d8:3-40 --slaves=root:beijing@localhost:/home/mysql/my3306.sock

参数说明:

 --dryrun

Execute the utility in dry-run mode, show the transactions (GTID) that would have been skipped for each slave but without effectively skipping them. This option is useful to verify if the correct transactions will be skipped.

 --gtid-set=<gtid-set>

Set of Global Transaction Identifiers (GTID) to skip.

 --help

Display a help message and exit.

 --license

Display license information and exit.

 --slaves=<slaves_connections>

Connection information for slave servers. List multiple slaves in comma-separated list.

To connect to a server, it is necessary to specify connection parameters such as the user name, host name, password, and either a port or socket. MySQL Utilities provides a number of ways to supply this information. All of the methods require specifying your choice via a command-line option such as --server, --master, --slave, etc. The methods include the following in order of most secure to least secure.

Use login-paths from your .mylogin.cnf file (encrypted, not visible). Example : <login-path>[:<port>][:<socket>]

Use a configuration file (unencrypted, not visible) Note: available in release-1.5.0. Example : <configuration-file-path>[:<section>]

Specify the data on the command-line (unencrypted, visible). Example : <user>[:<passwd>]@<host>[:<port>][:<socket>]

 --ssl-ca

The path to a file that contains a list of trusted SSL CAs.

 --ssl-cert

The name of the SSL certificate file to use for establishing a secure connection.

 --ssl-key

The name of the SSL key file to use for establishing a secure connection.

 --ssl

Specifies if the server connection requires use of SSL. If an encrypted connection cannot be established, the connection attempt fails. Default setting is 0 (SSL not required).

 --verbose, -v

Specify how much information to display. Use this option multiple times to increase the amount of information. For example, -v = verbose, -vv = more verbose, -vvv = debug.

 --version

Display version information and exit.

也可以通过

set global gtid_purged='887fd24be-683d-11e6-ba97-1418774c98d8:3-40';

跳过已经purge的部分,之后重新开启复制即可。
完成这些操作后,如果对数据一致性的问题有顾虑,可以通过 pt-table-checksum来进行一致性检查。

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
mysql主从复制
mysql主从复制 (超简单) 怎么安装mysql数据库,这里不说了,只说它的主从复制,步骤如下: 1、主从服务器分别作以下操作:   1.
620 0
【MySql】主从复制
MySQL支持单向、异步复制,复制过程中一个Linux服务器充当主服务器,而一个或多个其它服务器充当从服务器。主服务器将更新写入二进制日志文件,并维护日志文件的一个索引以跟踪日志循环。
772 0
+关注
miles_wong
Java核心技术 数据存储与数据库 分布式系统与计算 系统研发与运维 编程语言
87
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载