查收mysql配置文件_20个MySQL运维案例，请查收！

原标题：20个MySQL运维案例，请查收！

墨墨导读：日常MySQL运维中，会遇到各种各样的问题，下面分享二十个MySQL运维案例，附有问题的分析和解决办法，希望你遇到同样的问题的时候，可以淡定地处理。

数据技术嘉年华，十周年盛大开启， 点我立即报名 ！ 大会以“自研·智能·新基建——云和数据促创新 生态融合新十年” 为主题，相邀数据英雄，总结过往十年历程与成绩，展望未来十年趋势与目标！近60场演讲，大咖云集，李飞飞、苏光牛、林晓斌、黄东旭...，快来pick你喜欢的嘉宾主题吧！

一. 执行存储过程/函数报错账号不存在

问题描述

执行存储过程报错

mysql>call create_no_by_day('STUDENT','CREATE_TIME');

ERROR 1449 (HY000):The user specified as a definer ('TEST_111'@'172.%.%.%') does not exist

分析

这情况是因为当时机器上这存储过程是由用户’TEST_111’@'172.%.%.%‘创建，但是存储过程导入到了新的机器后，这新机器没有这个用户而报错。

三种解决办法：

1、重建这个存储过程，把definer那段代码取消。

2、在新机器上建立这个用户’TEST_111’@‘172.%.%.%’。

3、修改定义者，替换所有存储过程是这个定义者的为新机器已有账号。

解决：修改定义者

1.查看机器不存在这个账号

selecthost, userfrommysql.user wherehost= '172.%.%.%'anduser= 'TEST_111';

2.替换所有存储过程是这个定义者的为新机器已有账号。

UPDATEmysql.proc setDEFINER= 'TEST_222@172.%.%.%'whereDEFINER= 'TEST_111@172.%.%.%';

二. 遇到DDL变更的时候发生阻塞

问题描述

添加字段、添加索引等DDL语句时候会被阻塞，show processlist 会看到显示 Waiting for table metadata lock. 后续的对这些表的select也会被阻塞

分析：autocommit=0，怀疑有未提交事务，导致产生了元数据锁。

解决：

1. 如果打开了performance_schema 监控，通过语句定位未提交事务：

SELECT

locked_schema,

locked_table,

locked_type,

waiting_processlist_id,

waiting_age,

waiting_query,

waiting_state,

blocking_processlist_id,

blocking_age,

substring_index(sql_text,“transaction_begin;” ,-1) AS blocking_query,

sql_kill_blocking_connection

FROM

(

SELECT

b.OWNER_THREAD_ID AS granted_thread_id,

a.OBJECT_SCHEMA AS locked_schema,

a.OBJECT_NAME AS locked_table,

“Metadata Lock” AS locked_type,

c.PROCESSLIST_ID AS waiting_processlist_id,

c.PROCESSLIST_TIME AS waiting_age,

c.PROCESSLIST_INFO AS waiting_query,

c.PROCESSLIST_STATE AS waiting_state,

d.PROCESSLIST_ID AS blocking_processlist_id,

d.PROCESSLIST_TIME AS blocking_age,

d.PROCESSLIST_INFO AS blocking_query,

concat('KILL ', d.PROCESSLIST_ID) AS sql_kill_blocking_connection

FROM

performance_schema.metadata_locks a

JOIN performance_schema.metadata_locks b ON a.OBJECT_SCHEMA = b.OBJECT_SCHEMA

AND a.OBJECT_NAME = b.OBJECT_NAME

AND a.lock_status = ‘PENDING’

AND b.lock_status = ‘GRANTED’

AND a.OWNER_THREAD_ID <> b.OWNER_THREAD_ID

AND a.lock_type = ‘EXCLUSIVE’

JOIN performance_schema.threads c ON a.OWNER_THREAD_ID = c.THREAD_ID

JOIN performance_schema.threads d ON b.OWNER_THREAD_ID = d.THREAD_ID

) t1,

(

SELECT

thread_id,

group_concat( CASE WHEN EVENT_NAME = ‘statement/sql/begin’ THEN “transaction_begin” ELSE sql_text END ORDER BY event_id SEPARATOR “;” ) AS sql_text

FROM

performance_schema.events_statements_history

GROUP BY thread_id

) t2

WHERE

t1.granted_thread_id = t2.thread_id

2. 杀进程kill blocking_processlist_id

3. 没有打开wait/lock/metadata/sql/mdl情况下，针对sleep进程执行kill

SELECT concat(‘kill ‘,processlist_id,’;’)

FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING(thread_id)

JOIN information_schema.processlist c ON b.processlist_id = c.id

WHERE a.sql_text NOT LIKE ‘%performance%’ and command=‘sleep’ order by c.time desc;

三. 批量更新数据卡住

问题描述

批量更新很慢，没添加索引，添加索引被阻塞

分析

事务不自动提交容易造成元数据锁冲突

解决

执行SELECT concat(‘kill ‘,processlist_id,’;’)

FROM performance_schema.events_statements_current a JOIN performance_schema.threads b USING(thread_id)

JOIN information_schema.processlist c ON b.processlist_id = c.id

WHERE a.sql_text NOT LIKE ‘%performance%’ andcommand=‘ sleep’ order by c.time desc;

杀进程

四. 一个环境数据库占用空间满情况

问题描述

一个环境数据库空间已满。

分析

数据没有定时清理，没有监控报警。一个保存实时消息的大表上百亿数据占用过大。

问题解决

1.能登陆mysql情况下，truncate table 大表(无用数据，可清除)，回收空间

2.不能登陆mysql情况下，删除部分binlog日志，让mysql启动起来，再清理其他数据。

五. 数据库迁移

问题描述

已备份了数据，想把数据库导进另一个环境，但是想换一个数据库名字，以免把另一个环境的同名数据库覆盖

问题解决

1.导出数据

# mysqldump -uroot -pxxx_001 --set-gtid-purged=OFF admin > admin.sql

2． 建新库

mysql>create database admin1;

3． 建立账号，授权

mysql>CREATE USER admin1@'%' identified by 'Admin1_!@#';

mysql>GRANT ALL PRIVILEGES ON admin1.* TO admin1@'%';

mysql>flush privileges;

4． 导入数据到新库admin1

# mysql -uroot -pxxx_001 admin1

这样，就把原库admin导出的表数据，导入了新库admin1里面。新账号admin1已授权访问新库admin1,新账号密码是Admin1_!@#

注：操作2,3是sql语句，在mysql里面执行。操作1,4是在linux命令行上操作。

六. 数据库日志出现多个断连记录

问题描述

数据库日志出现多个断连记录，显示为Got an error writing communication packets

分析

有可能是客户端异常退出了，应用重启，也有可能是网络链路异常。这种提示一般是[NOTE],属于提示信息。

问题解决：

关闭警报

set global log_warning=1;

另：若是出现Got timeout reading communication packets或者Got timeout writing communication packets，属于客户端的空连接时间过长，超过了wait_timeout和interactive_timeout的时间，可以调整wait_timeout/interactive_timeout

七. 非法断电造成mysql启动报错

问题描述：非法断电造成mysql数据损坏

分析：

突然断电造成缓存数据丢失，跟已刷盘的数据不一致。需要重做从库。

问题解决：

到主库物理备份数据，恢复到从库，恢复主从同步。

物理备份恢复过程：

主库:

mysqlbackup --user=root --password=xxx_001 --backup-dir=/mysql/data/backup --backup-image=./dball.mbi --with-timestamp --compress-level=9 --compress-method=zlib --skip-binlog --skip-relaylog backup-to-image #备份数据

scp dball.mbi root@192.168.137.111:/mysql/data/backup/ #拷贝到目标机器backup目录

从库:

cd /mysql/data/backup/

chown mysql.mysql dball.mbi

su - mysql

mysql.server stop

cd /mysql/data/undo

rm -rf * #清空undo日志

cd /mysql/data/redo

rm -rf * #清空redo日志

cd /mysql/data/dbs

rm -rf * #清空数据

mysqlbackup --backup-image=/mysql/data/backup/dball.mbi --backup-dir=/mysql/data/backup --uncompress copy-back-and-apply-log --force #恢复数据

mysql.server start #启动mysql

mysql -uroot -p

reset slave all; #重置

change master to master_host = ‘192.168.137.110’, master_port = 3310, master_user = ‘rpl_user’, master_password = ‘rpl_001’, master_auto_position=1 ;#设置主从同步复制

start slave;#启动同步

show slave statusG;#查看同步状况

八. 非法断电造成mysql同步复制无法启动

问题描述：

relay报错，error:look foring afer relay.000013

分析：

relay得到的gtid比执行的gtid少，得到的部分gtid丢失。

show slave statusG;

Master_UUID: 37be0d7b-e11e-11e9-bafb-fa163e9dcbee

Retrieved_Gtid_Set: 37be0d7b-e11e-11e9-bafb-fa163e9dcbee:2290-2302(少)

Executed_Gtid_Set: 37be0d7b-e11e-11e9-bafb-fa163e9dcbee:1-2352(大),

5324e653-f0c2-11e9-84d0-fa163e897a41:1-363,

5661dce0-e11e-11e9-ab09-fa163e897a41:1-318

问题解决：

调整gtid，以relay获得的gtid为准，重做主从同步

reset slave all;#重置

reset master;#重置

SET @@GLOBAL.GTID_PURGED=‘37be0d7b-e11e-11e9-bafb-fa163e9dcbee:1-2302(少),5324e653-f0c2-11e9-84d0-fa163e897a41:1-363,5661dce0-e11e-11e9-ab09-fa163e897a41:1-318’;#以relay获得的gtid为准，设置GTID_PURGED

change master to master_host = ‘192.168.137.110’, master_port = 3310, master_user = ‘rpl_user’, master_password = ‘rpl_001’, master_auto_position=1 ;#设置主从同步复制

start slave; #启动同步

show slave statusG;#查看同步状况

九. MySQL压力测试，插入时间增大，压不上去

问题描述：

压力测试，数据库插入出现延时情况。

分析：

沟通发现，mysql是个人安装，非标准化，设置不当，例如32G的内存，innodb_buffer_pool_size只默认128M。

问题解决：

2.为了永久生效，在my.cnf里面设置

innodb_buffer_pool_size=16G

innodb_buffer_pool_instances = 8

重启mysql

十. 通过恢复文件加载空间恢复表数据

问题描述：出现测试环境数据库ibdata1损坏，无法启动。

分析：通过开发环境的mysql全量备份，恢复到测试环境。由于开发环境的admin库数据库结构跟测试环境的admin库一样，但是数据不一样，需要用原测试环境数据文件恢复原表数据。

问题解决：

1.备份数据

mkdir backup

cp dbs/admin/* backup/

2.开发环境mysqlbackup全量备份恢复到测试环境

3.卸载库admin所有表空间

mysql -uroot -p -e “select concat(‘alter table ‘,table_name,’ DISCARD tablespace;’) from information_schema.TABLES where table_schema=‘admin’ and table_type=‘BASE TABLE’;” >admin_discard.sql

vi admin_discard.sql

删除第一行concat(‘alter table ‘,table_name,’ DISCARD tablespace;’)

mysql -uroot -p admin

4.拷贝回.ibd文件

cp -f backup/*.ibd dbs/admin/

5.加载库admin所有表空间

mysql -uroot -p -e “select concat(‘alter table ‘,table_name,’ IMPORT tablespace;’) from information_schema.TABLES where table_schema=‘admin’ and table_type=‘BASE TABLE’;” >admin_import.sql

vi admin_import.sql

删除第一行concat(‘alter table ‘,table_name,’ IMPORT tablespace;’)

mysql -uroot -p admin

6.启动mysql

mysql.server start

十一.XA事务未提交，更新数据超时

......

十二.mysql升级5.7.26，更换驱动后原账号连不上

......

十三.mysql无法启动，数据目录丢失

......

十四.无法登陆mysql，can’t connect。。。。mysql.sock

......

十五.数据目录存在非法目录

......

十六.错误日志报警告信息长度不匹配

......

十七.错误日志报警告信息ignored in --skip-name-resolve mode

......

十八.大量Waiting in connection_control plugin连接

......

十九.备库同步复制通道channel值为空

......

二十.mysql时区跟系统数据不一致

......

数据技术嘉年华，汇聚业内 MySQL 最佳实践和顶级技术专家，只为总结 2020 ，与您尽享技术前沿，领先一步卓立变革潮头！

2020 数据技术嘉年华，现在加入，尽享超低票价优惠：

责任编辑：