问题分析与处理 登录数据库服务器查看top，iostat –x 1等查看操作系统负载。 top发现：CPU:load average 超过10且是mysqld进程占用，确定是MySQL等待。 通过 show processlist查看：发现大量的Waiting for global read lock 。（信息已经脱敏处理）
这里第一感觉是正在进行mysqldump备份数据，然后show processlit查看数据库的进程信息，果然发现了backup 用户正在 执行 FLUSH TABLES WITH READ LOCK 。
cd ${BACKDIR}/alldb_${TIME} $MYSQLDUMP --default-character-set=utf8 -R --hex-blob --single-transaction --master-data=2 --all-databases --log-error=alldb_${TIME}.err >alldb.${TIME}.sql echo "`date +"%F %T"` BACKUP all DATABASE one file FINISH" tee -a $logF
每个库一个备份文件的脚本
1 2 3 4 5 6 7 8
echo "DB HOST $IP IS BACKUP DATABASE per database one file " tee -a $logF for dbname in `$MYSQL -B -N -e "show databases"grep -vE "information_schemaperformance_schema"` do mkdir -p ${BACKDIR}/${dbname}_${TIME} cd ${BACKDIR}/${dbname}_${TIME} $MYSQLDUMP --default-character-set=utf8 -R --hex-blob --single-transaction --master-data=2 $dbname --log-error=${dbname}_${TIME}.err >${dbname}.${TIME}.sql echo "`date +"%F %T"` BACKUP $dbname one fie finish" tee -a $logF done
每个表一个备份文件的脚本
1 2 3 4 5 6 7 8 9 10 11 12 13 14
### per table per database echo "DB host $IP is backing" tee -a $logF for dbname in `$MYSQL -B -N -e "show databases"grep -vE "information_schemaperformance_schema"` do mkdir -p ${BACKDIR}/${dbname}_per_${TIME} cd ${BACKDIR}/${dbname}_per_${TIME} for table in `$MYSQL $dbname -e "show tables"sed '1d'grep -vE "yxxxxxxx"` do echo "`date +"%F %T"` begin backup ${dbname}.${table} TABLE" tee -a $logF $MYSQLDUMP --default-character-set=utf8 -R --hex-blob --single-transaction --master-data=2 $dbname$table --log-error=${dbname}_${TIME}.err >$dbname_$table.sql echo "`date +"%F %T"` BACKUP ${dbname}.$table FINISH" tee -a $logF done echo "`date +"%F %T"` BACKUP $dbname DATABASE FINISH" tee -a $logF done
root@hostname 13:14: [test]> show variables like '%gen%'; +------------------+------------------------------------------------+ Variable_name Value +------------------+------------------------------------------------+ general_log ON general_log_file /usr/local/mysql/data/BertLiudeMacBook-Pro.log +------------------+------------------------------------------------+
2017-08-28T15:18:00.613324Z 109 Connect root@localhost on using Socket 2017-08-28T15:18:00.614229Z 109 Query /*!40100 SET @@SQL_MODE='' */ 2017-08-28T15:18:00.615737Z 109 Query /*!40103 SET TIME_ZONE='+00:00' */ 2017-08-28T15:18:00.615939Z 109 Query SHOW VARIABLES LIKE 'gtid_mode' 2017-08-28T15:18:00.618627Z 109 Query SELECT @@GLOBAL.GTID_EXECUTED ………… 省略 2017-08-28T15:18:00.624351Z 109 Init DB test 2017-08-28T15:18:00.624422Z 109 Query SHOW TABLES LIKE 't' 2017-08-28T15:18:00.624843Z 109 Query LOCK TABLES `t` READ /*!32311 LOCAL */《--这里只锁定了 t 表。 2017-08-28T15:18:00.624967Z 109 Query show table status like 't' 2017-08-28T15:18:00.625452Z 109 Query SET SQL_QUOTE_SHOW_CREATE=1 2017-08-28T15:18:00.625514Z 109 Query SET SESSION character_set_results = 'binary' 2017-08-28T15:18:00.625566Z 109 Query show create table `t` 2017-08-28T15:18:00.625624Z 109 Query SET SESSION character_set_results = 'utf8' 2017-08-28T15:18:00.626177Z 109 Query show fields from `t` 2017-08-28T15:18:00.626474Z 109 Query show fields from `t` 2017-08-28T15:18:00.626725Z 109 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t` 2017-08-28T15:18:00.626896Z 109 Query SET SESSION character_set_results = 'binary' 2017-08-28T15:18:00.626978Z 109 Query use `test` 2017-08-28T15:18:00.627034Z 109 Query select @@collation_database 2017-08-28T15:18:00.627106Z 109 Query SHOW TRIGGERS LIKE 't' 2017-08-28T15:18:00.627399Z 109 Query SET SESSION character_set_results = 'utf8' 2017-08-28T15:18:00.627451Z 109 Query UNLOCK TABLES 2017-08-28T15:18:00.645805Z 109 Quit
发现：没有执行 FLUSH /*!40101 LOCAL */ TABLES、FLUSH TABLES WITH READ LOCK 。而是执行的LOCK TABLES t READ，对备份的 t 表进行一个lock。 备份全库使用--all-databases及--single-transaction --master-data=2 参数
22017-08-28T15:27:20.891774Z 112 Connect root@localhost on using Socket 2017-08-28T15:27:20.891932Z 112 Query /*!40100 SET @@SQL_MODE='' */ 2017-08-28T15:27:20.892941Z 112 Query /*!40103 SET TIME_ZONE='+00:00' */ 2017-08-28T15:27:20.893122Z 112 Query FLUSH /*!40101 LOCAL */ TABLES 2017-08-28T15:27:20.893404Z 112 Query FLUSH TABLES WITH READ LOCK 2017-08-28T15:27:20.893508Z 112 Query SHOW VARIABLES LIKE 'gtid_mode' 2017-08-28T15:27:20.897696Z 112 Query SELECT @@GLOBAL.GTID_EXECUTED 2017-08-28T15:27:20.897990Z 112 Query SHOW MASTER STATUS 2017-08-28T15:27:20.906499Z 112 Init DB test …………省略 2017-08-28T15:27:20.906595Z 112 Query SHOW TABLES LIKE 't' 2017-08-28T15:27:20.907204Z 112 Query show table status like 't' 2017-08-28T15:27:20.907865Z 112 Query SET SQL_QUOTE_SHOW_CREATE=1 2017-08-28T15:27:20.907973Z 112 Query SET SESSION character_set_results = 'binary' 2017-08-28T15:27:20.908068Z 112 Query show create table `t` 2017-08-28T15:27:20.908182Z 112 Query SET SESSION character_set_results = 'utf8' 2017-08-28T15:27:20.908294Z 112 Query show fields from `t` 2017-08-28T15:27:20.908705Z 112 Query show fields from `t` 2017-08-28T15:27:20.909113Z 112 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t` 2017-08-28T15:27:20.910751Z 112 Query SET SESSION character_set_results = 'binary' 2017-08-28T15:27:20.910859Z 112 Query use `test` 2017-08-28T15:27:20.910947Z 112 Query select @@collation_database 2017-08-28T15:27:20.911098Z 112 Query SHOW TRIGGERS LIKE 't' 2017-08-28T15:27:20.911609Z 112 Query SET SESSION character_set_results = 'utf8' 2017-08-28T15:27:20.928296Z 112 Quit
结论： 也就是说通过这几次实验发现只要添加了--master-data=2参数就会执行 FLUSH TABLES WITH READ LOCK全局锁，即使你是备份单个表也会锁整个库来获取show master status;从而导致整个库的一个等待。在业务低峰期，获取全局锁会很快，但是由于整个库有700多个表，有部分表在上午10点也就是业务高峰期还没备份完成，由于高峰期有大量的DML操作，从而获取全局锁更加困难，Waiting for global read lock 的时间会更长，从而影响了业务的正常运行。
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html#option_mysqlpump_single-transaction–single-transaction This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications. When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state. While a –single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE,DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqlpump to retrieve the table contents to obtain incorrect contents or fail. –add-locks and –single-transaction are mutually exclusive. 官档上的 –master-data https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html#option_mysqldump_master-data –master-data[=value] Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave. If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. If no option value is specified, the default value is 1. This option requires the RELOAD privilege and the binary log must be enabled. The –master-data option automatically turns off –lock-tables. It also turns on –lock-all-tables, unless–single-transaction also is specified, in which case, a global read lock is acquired only for a short time at the beginning of the dump (see the description for –single-transaction). In all cases, any action on logs happens at the exact moment of the dump. It is also possible to set up a slave by dumping an existing slave of the master, using the –dump-slave option, which overrides –master-data and causes it to be ignored if both options are used. 官档上的 FLUSH TABLES WITH READ LOCK https://dev.mysql.com/doc/refman/5.7/en/flush.html#flush-tables-with-read-lock
Closes all open tables and locks all tables for all databases with a global read lock. This is a very convenient way to get backups if you have a file system such as Veritas or ZFS that can take snapshots in time. Use UNLOCK TABLESto release the lock. FLUSH TABLES WITH READ LOCK acquires a global read lock rather than table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits:
UNLOCK TABLESimplicitly commits any active transaction only if any tables currently have been locked with LOCK TABLES. The commit does not occur for UNLOCK TABLES following FLUSH TABLES WITH READ LOCKbecause the latter statement does not acquire table locks.
Beginning a transaction causes table locks acquired with LOCK TABLESto be released, as though you had executed UNLOCK TABLES. Beginning a transaction does not release a global read lock acquired with FLUSH TABLES WITH READ LOCK.
这里第一感觉是正在进行mysqldump备份数据，然后show processlit查看数据库的进程信息，果然发现了
通过操作系统 