转 mysql 备份导致 waiting for global read lock
######转 https://blog.csdn.net/weixin_34038652/article/details/92129498
近业务高峰期间经常会有开发跳起来说应用连接数据库超时了!
我们来看下mysql的运行状态
Waiting for release of readlock:等待释放全局锁
The thread is waiting for a global read lock obtained by another thread (with FLUSH TABLES WITH READ LOCK) to be released.This state was removed in MySQL 5.5.8; Waiting for global read lock or Waiting for commit lock are used instead.
Waiting for table:等待表
Waiting for tables, Waiting for table, Waiting for table flush
The thread got a notification that the underlying structure for a table has changed and it needs to reopen the table to get the new structure. However, to reopen the table, it must wait until all other threads have closed the table in question.
This notification takes place if another thread has used FLUSH TABLES or one of the following statements on the table in question: FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
In MySQL 5.5.6, Waiting for table was replaced with Waiting for table flush.
线程获得一个通知,底层表结构已经发生变化,它需要重新打开表来获取新的结构。然而,重新打开表,它必须等到所有其他线程关闭这个有问题的表。
这个通知产生通常因为另一个线程对问题表执行了FLUSH TABLES或者以下语句之一:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE, or OPTIMIZE TABLE.
查看crontab,每天定时执行备份任务
/usr/local/mysql/bin/mysqldump --user=$bakuser --patestdbword=$bakpwd --skip-opt --master-data=2 --single-transaction --add-drop-table --create-options --quick --extended-insert --set-charset --disable-keys --triggers -R --flush-logs --databases testdb > testdb.sql
--master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
--lock-all-tables on, unless --single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about --single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns --lock-tables off.
这个参数会运行--lock-all-tables,将master的binlog和postion信息写入SQL文件的头部,除非结合--single-transaction(但并不是说就完全的不会锁表了,执行的时候也会添加短暂的全局读锁)
我们来重现一下这个场景
/usr/local/mysql/bin/mysqldump -u root -p --skip-opt --master-data=2 --single-transaction --add-drop-table --create-options --quick --extended-insert --set-charset --disable-keys --triggers -R --flush-logs --databases testdb > testdb.sql
执行插入
mysql> call insT1(10000000);
30s后执行【如果同时执行,效果不明显】
/usr/local/mysql/bin/mysqldump -u root -p --skip-opt --master-data=2 --single-transaction --add-drop-table --create-options --quick --extended-insert --set-charset --disable-keys --triggers -R --flush-logs --databases testdb > testdb1.sql
执行插入
mysql> call insT2(1000000);
等待刷表
不使用--single-transaction
等待全局读锁释放
不使用--master-data,再跑上面的2个场景,mysql不会加锁,所以SQL很快执行完成
结论:因为选用--master-data参数在SQL文件的头部会写入binlog和position信 息,所以在执行备份前mysql需要执行flush tables,搭建过从库的同学都了解,我们在获取完整备份前都要执行FLUSH TABLES WITH READ LOCK;来获取这些主库当前信息,这里也是这样。 www.it165.net
生产环境还是复杂的,大家会注意到我们同时使用了--msater-date和--single-transation但还是出现了全局读锁,可是在测试环境,只有不加--single-transation的时候才会出现。
解决方法:
1.如果你只需要文件备份,不需要经常建立从库,那么可以去掉--master-data。
2.如果你的数据量很大 or 备份时的master信息非常需要,那么可以调整备份周期,避开两次备份出现重叠的情况。
##sample
1. ps -ef|grep mysql show backup is doing
[root@pzabbixdb01 tmp]# ps -ef|grep mysql
root 2406 1 0 Apr09 ? 00:00:00 /bin/sh /db/mysql/app/mysql/bin/mysqld_safe --defaults-file=/db/mysql/app/mysql/my.cnf --datadir=/db/mysql/data/mydata --user=mysql
mysql 3076 2406 16 Apr09 ? 34-04:12:09 /db/mysql/app/mysql/bin/mysqld --defaults-file=/db/mysql/app/mysql/my.cnf --basedir=/db/mysql/app/mysql --datadir=/db/mysql/data/mydata --plugin-dir=/db/mysql/app/mysql/lib/plugin --user=mysql --log-error=/db/mysql/data/mydata/mysql-error.log --open-files-limit=65535 --pid-file=/db/mysql/app/mysql/mysql.pid --socket=/db/mysql/data/mysqltmp/mysql.sock --port=3306
root 10345 3784 0 21:01 pts/0 00:00:00 mysql -uroot -px xxx
root 11688 10389 0 21:18 pts/1 00:00:00 grep mysql
root 32759 1 0 19:00 ? 00:00:00 bphdb -sb -rdbms oracle -S pnbumaster -to 3600 -c mysql_zabbixdb -s full -clnt pzabbixdb01 -FULL -kl 28 -b pzabbixdb01_1572519604 -jobid 7409084
root 32761 32759 0 19:00 ? 00:00:00 /bin/sh /db/mysql/app/shell/mysql_backup.sh >/dev/null 2>/dev/null
root 32767 32761 7 19:00 ? 00:10:08 /opt/mysql/meb-3.12/bin/mysqlbackup --user=root --password=xxxxx --backup-image=sbt:bkpsbtNB20191031190005 --sbt-lib-path=/usr/openv/netbackup/bin/libobk.so64 --sbt-environment=NB_ORA_SERV=pnbumaster,NB_ORA_CLIENT=pzabbixdb01,NB_ORA_POLICY=mysql_zabbixdb,NB_ORA_SCHED=Default-Application-Backup,ORACLE_HOME=/db/mysql --backup-dir=/usr/openv/meb_bkdir/ backup-to-image --socket=/db/mysql/data/mysqltmp/mysql.sock --port=3306
2.
lock inf: Waiting for global read lock
| 16449640 | zabbix | 10.200.:50083 | zabbix | Query | 2166 | Waiting for global read lock | update httptest set nextcheck=1572524839 where httptestid=15 |
| 16449641 | zabbix | 10.200.:50084 | zabbix | Query | 1735 | Waiting for global read lock | insert into events (eventid,source,object,objectid,clock,ns,value) values (38773114,0,0,128316,15725 |
3.workaroud:
kill -9 <backup_process>
相关文章
- 【MySQL】一次修改mysql 默认路径的经历
- dba+开源工具:MHA复刻版,轻松实现MySQL高可用故障转移(附下载)
- 第二百八十六节,MySQL数据库-MySQL事务操作(回滚)
- 腾讯云备份数据库.xb恢复(mysql)
- mysql自动备份策略
- Percona Xtrabackup备份mysql(转)
- mysql 重新整理——sql 执行语句的顺序[五]
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (
- Windows 下 MySQL 简单定时自动备份、删除过期备份
- 数据库基础之Mysql(3)mysql删除历史binlog
- mysql查询的时候区分大小写
- MySQL中的char和varchar&mysql中varchar能存多少汉字、数字,以及varchar(100)和varchar(10)的区别
- Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/mysql.pid).
- mysql索引的使用傻瓜教程
- Python MySQL创建表
- Mysql连接报错:1130-host ... is not allowed to connect to this MySql server如何处理
- mysql 故障整理(2)
- MySQL 可以用localhost 连接,但不能用IP连接的问题,局域网192.168.*.* 无法连接mysql
- MySQL Study之--Mysql无法启动“mysql.host”
- 常用Mysql或者PostGresql或者Greenplum的语句总结。
- mysql不能插入中文
- MySQL提权之启动项提权——开机启动的程序,那时候启动的程序权限都是system
- 如何实现MySQL表数据随机读取?从mysql表中读取随机数据
- mysql三张表关联查询