mysqldump
运行mysqldump需一定的权限。如,备份表的最低权限为select,备份触发器需show triggers权限。
(1)备份结果文件命令规范:dbname_port_$(date +%Y%m%d).bak
(2)gzip,tar是单线程压缩软件,只能用到一个cpu,效率比较低,备份的时候不建议压缩,如果空间实在不足,可以考录备份完成之后使用pigz多线程压缩软件;
(3)mysqldump是单线程工作,效率比较低,如果mysqldump备份需要很长时间,可以考虑使用mydumper支持多线程并发导出;
备份的参数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
|
mysql>create table t1(c1 int,c2 varchar(10));
mysql>insert into t1 values(1,
'aaa'
),(2,
'bbb'
),(3,
'ccc'
);
例1:
#指定备份单个库testdb:
[root@Darren2 tmp]
# mysqldump -uroot -p147258 testdb > /tmp/testdb1.bak
[root@Darren2 tmp]
# vim testdb1.bak
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0; --当还原的时候不记录binlog日志
SET @@GLOBAL.GTID_PURGED=
'83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914'
;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `t1` WRITE; --还原表的时候不允许其他会话读写t1表
INSERT INTO `t1` VALUES (1,
'aaa'
),(2,
'bbb'
),(3,
'ccc'
);
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
在本数据库上还原:
[root@Darren2 tmp]
# mysql -uroot -p147258 < testdb1.bak
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be
set
when @@GLOBAL.GTID_EXECUTED is empty.
实质报错:
root@localhost [testdb]>SET @@GLOBAL.GTID_PURGED=
'83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914'
;
ERROR 1840 (HY000): @@GLOBAL.GTID_PURGED can only be
set
when @@GLOBAL.GTID_EXECUTED is empty.
如果直接还原会报错,因为我开启了gtid_mode,此时可以show master status看一下executed_gtid_set参数不为空,需要在备份文件testdb1.bak中把
“SET @@GLOBAL.GTID_PURGED=
'83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10914'
;”注释掉能继续还原
如果希望能够传写入binlog,把还原也同步到从库,需要注释掉SET @@SESSION.SQL_LOG_BIN= 0;
root@localhost [testdb]>show master status;
+------------------+----------+--------------+------------------+----------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------------+
| mysql-bin.000004 | 6392 | | | 83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10931 |
+------------------+----------+--------------+------------------+----------------------------------------------+
还原命令:
[root@Darren2 tmp]
# mysql -uroot -p147258 testdb < testdb1.bak
#必须指定testdb库,否则报错找不到,如果还原testdb库之前被删除了,也会报错:
[root@Darren2 tmp]
# mysql -uroot -p147258 testdb <testdb1.bak
mysql: [Warning] Using a password on the
command
line interface can be insecure.
ERROR 1049 (42000): Unknown database
'testdb'
例2:
同时备份多个库-B,并且增加了create database testdb和use testdb的功能
[root@Darren2 tmp]
# mysqldump -uroot -p147258 -B testdb > testdb2.bak
[root@Darren2 tmp]
# vim testdb2.bak
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
SET @@GLOBAL.GTID_PURGED=
'83373570-fe03-11e6-bb0a-000c29c1b8a9:1-10931'
;
CREATE DATABASE `testdb` ;
USE `testdb`;
DROP TABLE IF EXISTS `t1`;
CREATE TABLE `t1` (
`c1` int(11) DEFAULT NULL,
`c2` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `t1` WRITE;
INSERT INTO `t1` VALUES (1,
'aaa'
),(2,
'bbb'
),(3,
'ccc'
);
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
还原:无需指定testdb库
[root@Darren2 tmp]
# mysql -uroot -p147258 < testdb2.bak
#同时备份多个库:
[root@Darren2 tmp]
# mysqldump -uroot -p147258 -B testdb mysql > testdbmysql.bak
还原多个库:
[root@Darren2 tmp]
# mysql -uroot -p147258 < testdbmysql.bak
例3:
压缩备份
[root@Darren2 logs]
# mysqldump -uroot -p147258 testdb | gzip > testdbgzip.bak.gz
还原:
[root@Darren2 tmp]
# gunzip < testdbgzip.bak.gz | mysql -uroot -p147258
例4:
只备份库中的表
格式:mysqldump -uroot -p147258 库名 表名1 表名2 ... >备份文件名
注意:不能加-B 参数,否则就是备份多个库了
#备份单个表:
[root@Darren2 tmp]
# mysqldump -uroot -p147258 testdb t1 > testdbt1.bak
#备份多个表:
[root@Darren2 tmp]
# mysqldump -uroot -p147258 testdb t1 t2 > testdbt1t2.bak
例5:
--master-data,指定日志文件从哪个位置开始,不用切割binlog日志了
[root@www ~]
# mysqldump -uroot -p147258 --master-data=1 testdb
CHANGE MASTER TO MASTER_LOG_FILE=
'mysql-bin.000011'
, MASTER_LOG_POS=107;
[root@www opt]
# mysqldump -uroot -p147258 --master-data=2 testdb
-- CHANGE MASTER TO MASTER_LOG_FILE=
'mysql-bin.000011'
, MASTER_LOG_POS=107;
--master-data=1时,备份文件中change master 是sql语句,在主从同步时用到
--master-data=2时,备份文件中--change master被注释掉,还原时不具有sql语句作用
例6:其它参数
--single-transaction 适合innodb事物数据库备份,原理是设定本次会话级别是repeatable
read
,保证本次会话备份时,不会看到其他已经提交的会话,保证数据一致性
-A, --all-databases Dump all the databases. This will be same as --databases
-F, --flush-logs 即刷新binlog
|
总结:
innodb引擎 :一般生产全备使用的方法
[root@Darren2 tmp]# mysqldump -uroot -p147258 -A -B --master-data=2 --events --single-transaction > /tmp/all_$(date +%Y%m%d).bak
备份的全过程
下面mysqldump备份的过程:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
|
root@localhost [testdb]>
set
global general_log=1;
[root@Darren1 data]
# cat /dev/null > general.log
[root@Darren1 ~]
# mysqldump -uroot -p147258 --single-transaction --master-data=2 testdb >testdb1_$(date +%Y%m%d)
[root@Darren1 data]
# cat general.log
2016-12-21T15:31:00.474824Z 14 Connect root@localhost on using Socket
2016-12-21T15:31:00.475031Z 14 Query /*!40100 SET @@SQL_MODE=
''
*/
2016-12-21T15:31:00.475194Z 14 Query /*!40103 SET TIME_ZONE=
'+00:00'
*/
2016-12-21T15:31:00.475282Z 14 Query FLUSH /*!40101 LOCAL */ TABLES --刷表,为了防止有表的DDL操作,如果备份的时候有表的DDL操作,flush tables一直处于等待状态,直到DDL动作结束,才执行flush tables
2016-12-21T15:31:00.475598Z 14 Query FLUSH TABLES WITH READ LOCK --全局锁表,所有会话不能对表进行任何DML和DDL操作,让数据处于一致性状态
2016-12-21T15:31:00.475661Z 14 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ --设置隔离界别为RR
2016-12-21T15:31:00.475728Z 14 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ --开始事物
2016-12-21T15:31:00.475805Z 14 Query SHOW VARIABLES LIKE
'gtid\_mode'
--查看GTID状态
2016-12-21T15:31:00.478393Z 14 Query SHOW MASTER STATUS --查看master
2016-12-21T15:31:00.478487Z 14 Query UNLOCK TABLES --解锁
2016-12-21T15:31:00.478625Z 14 Query SELECT LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE =
'UNDO LOG'
AND FILE_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IS NOT NULL AND LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE =
'DATAFILE'
AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (
'testdb'
))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2016-12-21T15:31:00.480360Z 14 Query SELECT DISTINCT TABLESPACE_NAME, FILE_NAME, LOGFILE_GROUP_NAME, EXTENT_SIZE, INITIAL_SIZE, ENGINE FROM INFORMATION_SCHEMA.FILES WHERE FILE_TYPE =
'DATAFILE'
AND TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_SCHEMA IN (
'testdb'
)) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2016-12-21T15:31:00.481072Z 14 Query SHOW VARIABLES LIKE
'ndbinfo\_version'
2016-12-21T15:31:00.483114Z 14 Init DB testdb
2016-12-21T15:31:00.483193Z 14 Query SAVEPOINT sp --创建事物的回滚点,如果下面一旦出错,可以回滚到回滚点之前的状态;
2016-12-21T15:31:00.483262Z 14 Query show tables
2016-12-21T15:31:00.483459Z 14 Query show table status like
't1'
2016-12-21T15:31:00.483711Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1
2016-12-21T15:31:00.483782Z 14 Query SET SESSION character_set_results =
'binary'
2016-12-21T15:31:00.483844Z 14 Query show create table `t1`
2016-12-21T15:31:00.483927Z 14 Query SET SESSION character_set_results =
'utf8'
2016-12-21T15:31:00.483998Z 14 Query show fields from `t1`
2016-12-21T15:31:00.484307Z 14 Query show fields from `t1`
2016-12-21T15:31:00.484551Z 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
2016-12-21T15:31:00.484758Z 14 Query SET SESSION character_set_results =
'binary'
2016-12-21T15:31:00.484828Z 14 Query use `testdb`
2016-12-21T15:31:00.484888Z 14 Query
select
@@collation_database
2016-12-21T15:31:00.484962Z 14 Query SHOW TRIGGERS LIKE
't1'
2016-12-21T15:31:00.485199Z 14 Query SET SESSION character_set_results =
'utf8'
2016-12-21T15:31:00.485255Z 14 Query ROLLBACK TO SAVEPOINT sp --回到回滚点
2016-12-21T15:31:00.485315Z 14 Query show table status like
't2'
2016-12-21T15:31:00.485464Z 14 Query SET SQL_QUOTE_SHOW_CREATE=1
2016-12-21T15:31:00.485515Z 14 Query SET SESSION character_set_results =
'binary'
2016-12-21T15:31:00.485567Z 14 Query show create table `t2`
2016-12-21T15:31:00.485635Z 14 Query SET SESSION character_set_results =
'utf8'
2016-12-21T15:31:00.485744Z 14 Query show fields from `t2`
2016-12-21T15:31:00.485968Z 14 Query show fields from `t2`
2016-12-21T15:31:00.486185Z 14 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `t2`
2016-12-21T15:31:00.486298Z 14 Query SET SESSION character_set_results =
'binary'
2016-12-21T15:31:00.486351Z 14 Query use `testdb`
2016-12-21T15:31:00.486407Z 14 Query
select
@@collation_database
2016-12-21T15:31:00.486468Z 14 Query SHOW TRIGGERS LIKE
't2'
2016-12-21T15:31:00.486792Z 14 Query SET SESSION character_set_results =
'utf8'
2016-12-21T15:31:00.486887Z 14 Query ROLLBACK TO SAVEPOINT sp --回到回滚点
2016-12-21T15:31:00.486943Z 14 Query RELEASE SAVEPOINT sp --释放回滚点
2016-12-21T15:31:00.513639Z 14 Quit --退出
|
总结:mysqldump的过程:
1
2
3
4
5
6
7
8
9
10
11
12
13
|
flush tables;
flush table with
read
lock;
set
tx_isolation=
'repeatable-read'
;
start transaction;
GTID_MODE;
show master stauts;
unlock tables;
SAVEPOINT sp
show create table `t1`
SELECT /*!40001 SQL_NO_CACHE */ * FROM `t1`
SHOW TRIGGERS LIKE
't1'
ROLLBACK TO SAVEPOINT sp
RELEASE SAVEPOINT sp
|
注意事项:
在使用mysqldump备份表的时候,如果对备份的表进行DDL操作可能使备份失败,因为DDL不在事物的框架中,mysql8.0以后可能会把DDL放在事物框架中;
当有大事物更新写入的时候,flush logs,flush tables要等待或者卡顿,也有可能失败,这是常见的坑;
本文转自 Darren_Chen 51CTO博客,原文链接:http://blog.51cto.com/darrenmemos/1918533,如需转载请自行联系原作者