mysqldump是逻辑备份,较适合备份数据量小的库。今天我们来看看几个比较常用的重要参数,并分析它们都做了什么,需要打开general_log。
set global general_log = 1;
+------------------+--------------------------------------+
| Variable_name | Value |
+------------------+--------------------------------------+
| general_log | ON |
| general_log_file | /data/mysql/mysql3317/data/bogon.log |
+------------------+--------------------------------------+
表结构如下:
*************************** 1. row ***************************
Table: zhangshuo
Create Table: CREATE TABLE `zhangshuo` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
1.不加参数备份zhangshuo表:
[root@bogon ~]# /usr/local/mysql/bin/mysqldump -S /tmp/mysql3317.sock zhangshuo zhangshuo >/tmp/test.sql
4 Init DB zhangshuo
4 Query SHOW TABLES LIKE 'zhangshuo'
4 Query LOCK TABLES `zhangshuo` READ /*!32311 LOCAL */
4 Query show table status like 'zhangshuo'
4 Query SET SQL_QUOTE_SHOW_CREATE=1
4 Query SET SESSION character_set_results = 'binary'
4 Query show create table `zhangshuo`
4 Query SET SESSION character_set_results = 'utf8'
4 Query show fields from `zhangshuo`
4 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zhangshuo`
4 Query SET SESSION character_set_results = 'binary'
4 Query use `zhangshuo`
4 Query select @@collation_database
4 Query SHOW TRIGGERS LIKE 'zhangshuo'
4 Query SET SESSION character_set_results = 'utf8'
4 Query UNLOCK TABLES
4 Quit
2.使用--single-transaction参数:
5 Query /*!40100 SET @@SQL_MODE='' */
5 Query /*!40103 SET TIME_ZONE='+00:00' */
5 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
5 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
5 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
5 Init DB zhangshuo
5 Query SHOW TABLES LIKE 'zhangshuo'
5 Query SAVEPOINT sp
5 Query show table status like 'zhangshuo'
5 Query SET SQL_QUOTE_SHOW_CREATE=1
5 Query SET SESSION character_set_results = 'binary'
5 Query show create table `zhangshuo`
5 Query SET SESSION character_set_results = 'utf8'
5 Query show fields from `zhangshuo`
5 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zhangshuo`
5 Query SET SESSION character_set_results = 'binary'
5 Query use `zhangshuo`
5 Query select @@collation_database
5 Query SHOW TRIGGERS LIKE 'zhangshuo'
5 Query SET SESSION character_set_results = 'utf8'
5 Query ROLLBACK TO SAVEPOINT sp
5 Query RELEASE SAVEPOINT sp
5 Quit
我们看到事物隔离级别改为RR,使用innodb的事物隔离性防止发生幻读。
3.使用--master-data=2 --single-transaction参数:
[root@bogon ~]# /usr/local/mysql/bin/mysqldump --single-transaction --master-data=2 -S /tmp/mysql3317.sock zhangshuo zhangshuo >/tmp/test2.sql
6 Connect root@localhost on
6 Query /*!40100 SET @@SQL_MODE='' */
6 Query /*!40103 SET TIME_ZONE='+00:00' */
6 Query FLUSH /*!40101 LOCAL */ TABLES
6 Query FLUSH TABLES WITH READ LOCK
6 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
6 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
6 Query SHOW VARIABLES LIKE 'gtid\_mode'
6 Query SHOW MASTER STATUS
6 Query UNLOCK TABLES
6 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
6 Init DB zhangshuo
6 Query SHOW TABLES LIKE 'zhangshuo'
6 Query SAVEPOINT sp
6 Query show table status like 'zhangshuo'
6 Query SET SQL_QUOTE_SHOW_CREATE=1
6 Query SET SESSION character_set_results = 'binary'
6 Query show create table `zhangshuo`
6 Query SET SESSION character_set_results = 'utf8'
6 Query show fields from `zhangshuo`
6 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `zhangshuo`
6 Query SET SESSION character_set_results = 'binary'
6 Query use `zhangshuo`
6 Query select @@collation_database
6 Query SHOW TRIGGERS LIKE 'zhangshuo'
6 Query SET SESSION character_set_results = 'utf8'
6 Query ROLLBACK TO SAVEPOINT sp
6 Query RELEASE SAVEPOINT sp
首先下发flush table with read lock(关闭已经打开的表)全局读锁后将事物隔离级别改为RR,并执行START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */创建一次性快照,接着查看是否开启GTID,获取gtid执行位置。数据备份时定义断点 SAVEPOINT sp,做完重新回到这个位置并开始下一张表,最后释放掉断点。
使用--master-data=2后我们再看看备份的SQL,可看到binlog执行位置,很方便搭建主从。
[root@bogon tmp]# grep "CHANGE MASTER TO" /tmp/test2.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=667;
总结:
备份innodb表推荐参数如下:
[root@bogon tmp]# /usr/local/mysql/bin/mysqldump --single-transaction --master-data=2 -S /tmp/mysql3317.sock zhangshuo zhangshuo >/tmp/test2.sql