mysql错误信息

多线程

When using a multi-threaded slave (in other wordsslave_parallel_workersis greater than 0), stopping the slave can cause“gaps” in the sequence of transactions that have been executed from the relay log, regardless of whether the slave was stopped intentionally or otherwise. When such gaps exist, issuingCHANGE MASTER TOfails. The solution in this situation is to issueSTART SLAVE UNTIL SQL_AFTER_MTS_GAPSwhich ensures that the gaps are closed.

解答:Replication cannot use Unix socket files. You must be able to connect to the master MySQL server using TCP/IP.

Unix Socket(同一台机器上面)与 IP Socket (TCP/IP)(不同机器上面):IP socket单次请求要4次经过tcp的四层协议

对主库执行HA切换

sip没有替换,但是uuid变了;会有复制连接重试

重置mysql复制链接

执行reset master;stop slave ;reset slave; start slave;运行start slave出现问题:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘slave stop’ at line 1

解决方法:CHANGE MASTER TO MASTER_HOST = ‘XXX’,MASTER_PORT = XX,MASTER_USER = ‘XX’,MASTER_PASSWORD = ‘XXXX’;然后在运行start slave;

系统变量取值导致sql执行错误

@@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1不支持CREATE TABLE … SELECT:ERROR 1786 (HY000): CREATE TABLE … SELECT is forbidden when @@GLOBAL.ENFORCE_GTID_CONSISTENCY = 1.

从机使用相同的server_id或server_uuid错误

使用相同server_id或者server_uuid从主库同步数据抛出异常1236 A slave with the same server server_uuid/server_id as this slave has connected to the master,正常情况下,相同uuid的dump链接,之前的会断开

问题排查场景

在性能测试的过程中:

checksum table 表名;

md5sum jar包或者文件

根据时间点查看日志:

mysqlbinlog -vv –base64-output=’decode-rows’ –start-datetime=’2017-03-21 21:28:00′ mysql-bin.000004 |less

根据文件、pos查看日志:

mysqlbinlog -vv –base64-output=’decode-rows’ in mysql-bin.000004 from position(position通过show binlog events,或者master status查看)

查找指定的gtid对应日志:

mysqlbinlog -vv –base64-output=’decode-rows’ –include-gtids=’21894c7a-0a1b-11e7-9a17-0636ce000465:2956-2961′  mysql-bin.000004|grep 21894c7a-0a1b-11e7-9a17-0636ce000465 -A 400 > /data/binlog.sql

查找日志文件最后一个事务日志(回放结束时间)

mysqlbinlog -vv –base64-output=’decode-rows’  mysql-bin.000004|grep End -A50 

查找日志文件中commit日志

mysqlbinlog -vv –base64-output=’decode-rows’  mysql-bin.000004|grep COMMIT -A5 -B5

查看未提交的binlog

在group_commit中,通过show global variables like ‘%gtid%’;查看gtid_owned,来显示未提交的binlog

根据起始位置点查看sql

mysqlbinlog -vv –base64-output=’decode-rows’ –start-position=1025527577 –stop-position=1033793818 -d test mysql-bin.000004 | grep 42255198-1068-11e7-832e-0636ce000465:62251 -A50 -B50 > gtid_62251.sql

列的大小存在问题

Data truncated for column ‘id’ at row 1

定位造成锁表的事务

select * from sys.innodb_lock_waitsG

定位sql语句,无法通过show processlist查看到线程3执行的sql,或者说线程3事务中的哪条sql造成的。

此时可以通过查看performance schema中的statement相关表来查看

可以通过thread id来进行查看events_statements_current  ##当前所有线程最后执行的sql。

events_statements_history  

保存线程最近performance_schema_events_statements_history_size 个数的event

events_statements_history_long  

保存最近performance_schema_events_statements_history_long_size个数的event statement

通过metadata_locks查看锁信息metadata_locks按时间排序(自动提交的除外);不能够同时执行多个事务;

持有锁与申请锁的依赖关系

show global variables like ‘%performance_schema_max_metadata_locks%’;

在使用之前设置状态,默认是没有开启(这里要记得修改)

select * from setup_instruments where NAME like ‘%meta%’

文章来源于互联网:mysql错误信息及问题排查

发表评论