MySQL备份恢复

本文档主要围绕生产中经常使用的mysqldump、mydumper、xtrabackup等工具来进行备份恢复的学习。

1. MySQL 的备份方式

MySQL的备份方法，划分为如下三种：

冷备份
冷备是指在数据库关闭的情况下进行备份，这种备份非常简单，只需关闭数据库，复制相关的物理文件即可。
温备份
温备份也是在数据库运行的过程中进行备份，但是备份会对数据库操作有所影响。
热备份
热备份是指在数据库运行的过程中进行备份，对生产环境中的数据库运行没有任何影响。常见的热备份方案是利用mysqldump、xtrabackup等工具进行备份。

根据备份文件的类型，备份又可以划分如下两种：

物理备份
物理备份是指复制数据库的物理文件，既可以是在数据库运行中复制（如xtrabackup这类工具），也可以是在数据库停止运行时直接的数据文件复制。
逻辑备份
逻辑备份是指备份文件的内容是可读的，该文本一般是由一条条SQL语句或者表的实际数据组成。常见的逻辑备份方式有mysqldump、select * into outfile等方法。

按照备份数据库的内容来分，备份又可以分为：

完全备份
完全备份是指对数据库进行一个完整的备份。
增量备份
增量备份是指在上次完全备份的基础是，对于更改的数据进行备份。
日志备份
日志备份主要针对MySQL数据库binlog的备份，通过对一个完全备份进行binlog的重做（replay）来完成数据库的point-in-time的恢复工作。MySQL数据库复制（replication）的原理就是异步实时地将二进制日志重做传送并应用到（slave）数据库。

2. 冷备

冷备就是在数据库处于关闭状态下的备份，好处可以保证数据库的晚自习，备份过程简单并且恢复速度相对快一些。

冷备的备份与恢复过程：

1）停止MySQL服务

mysqladmin -S /tmp/mysql3306.sock shutdown

2) 备份数据目录

cd /data/mysql/
tar -cvjpf mysql3306.tar.bz2 mysql3306   #-c为创建一个打包文件，相应的-f后面接创建的文件的名称，使用了.tar.bz2后缀，-j标志使用bzip2压缩，最后面为具体的操作对象mysql3306目录

# 查看
tar -tvjf mysql3306.tar.bz2　　#-t为查看操作，则-f对应所查看的文件的名称，文件后缀显示使用bzip2进行压缩，所以加入-j选项，-v会显示详细的权限信息

3) 恢复数据

cd /data/mysql/
rm -rf mysql3306
tar -xvjf mysql3306.tar.bz2    #-x为解压操作，则-f指定的是解压使用的文件，文件后缀显示使用bzip2进行压缩，所以加入-j选项，即使用bzip2解压

3. 热备

热备是在数据库处于运行状态下的备份，不影响现有业务的正常进行。

热备又分为逻辑备份和物理备份。

3.1 逻辑备份之 - mysqldump

mysqldump客户端实用程序执行逻辑备份，生成一组SQL语句，可以执行这些语句来重现原始数据库对象定义和表数据。它转储一个或多个MySQL数据库以备份或传输到另一个SQL服务器。 mysqldump命令还可以生成CSV，其他分隔文本或XML格式的输出。

3.1.1 mysqldump语法如下

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

3.1.2 mysqldump常见的参数

mysqldump的参数有很多，可以通过使用mysqldump --help命令来查看所有参数，有些参数有缩写形式，如--all-databases的缩写形式为-A。这里列举一些常见的参数。

-u, --user=name
指定连接的用户名
-p, --password[=name]
指定用户的密码，这里可以指定参数名，若不指定参数值，mysqldump随后将提示输入密码，以保护账户口令的安全
-S, --socket=name
指定socket文件连接
-h, --host=name
指定连接的服务器名
-P, --port=#
指定连接的服务器端口号
–tables
导出指定的表对象，格式为 ‘dbname tablename’,如mysqldump -S /tmp/mysql3306.sock test t1，默认会覆盖-B, --databases参数
–single-transaction
在备份开始前，先执行start transaction命令，以此来获得备份的一致性，当前该参数只对InnoDB存储引擎有效。当启用该参数并进行备份时，确保没有其他任何的DDL语句执行（ALTER TABLE, DROP TABLE, RENAME TABLE,TRUNCATE TABLE）,因为一致性读并不能隔离DDL操作。启用此参数时，参数--lock-tables将自动禁用。
-A, --all-databases
备份所有数据库
-B, --databases
备份指定的数据库，如mysqldump -S /tmp/mysql3306.sock --database db1 db2
–default-character-set=name
设置字符集，默认为以服务器设置的字符集进行导出。
-l, --lock-tables
以只读方式依次锁住每个库下的所有表，默认启用。使用--skip-lock-tables禁用。不能保证所有库下的表备份一致。
-x, --lock-all-tables
在备份过程中，对所有库的所有表，同时锁定。若指定了本参数，则会自动禁用--single-transaction和-l, --lock-tables参数。
–add-drop-database
在任何创建库语句前，附加drop database语句。
–add-drop-table
在任何建表语句钱，附加drop table语句。默认启用。如果不希望生成drop table语句，可以通过--skip-add-drop-table参数禁用。
–add-drop-trigger
创建任何触发器前，附加drop trigger语句。
–add-locks
在生成的insert语句钱附加lock语句，默认启用。使用 --skip-add-locks来禁用。
–allow-keywords
允许创建使用关键字的列名。
–master-data[=#]
该参数有1和2两个值，如果值等于1，就会在备份文件中添加一个change master语句。如果值为2，就会在备份文件中添加一个带有注释符号的change master语句。
–dump-slave[=#]
该参数用于在从库备份数据，在线搭建新的从库时使用。此参数也有1和2两个值。值为1时，在备份文件中添加一个change master语句。值为2时，就会在备份文件中添加一个带有注释符号的change master语句。
-t, --no-create-info
备份过程中，只备份表数据，不备份表结构
-d, --no-data
备份过程中，只备份表结构，不备份表数据
-c, --complete-insert
使用完整的insert语句会包含表中的列信息，这么做可以提高插入效率。
-q, --quick
表示导出时不会先将数据加载至buffer中，而是直接输出。默认启用，使用 --skip-quick禁用。
-w, --where=name
导出给定条件的数据。

3.1.3 mysqldump备份流程

开启general_log以观察备份流程。

mysql> set global general_log = 1;

shell> mysqldump -S /tmp/mysql3306.sock --master-data=2 --single-transaction test > /tmp/testdb.sql

mysql> set global general_log = 0;

mysql> show variables like '%gen%';
+------------------+-----------------------------------------+
| Variable_name    | Value                                   |
+------------------+-----------------------------------------+
| general_log      | OFF                                     |
| general_log_file | /data/mysql/mysql3306/data/mysqldb1.log |
+------------------+-----------------------------------------+


1. FLUSH /*!40101 LOCAL */ TABLES
2. FLUSH TABLES WITH READ LOCK
3. SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
4. START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
5. SHOW VARIABLES LIKE 'gtid\_mode'
6. SHOW MASTER STATUS
7. UNLOCK TABLES
8. show databases;
9. show create database if not exists 'dbname';
10. SAVEPOINT sp
11. show tables
12. show table status like 'account'
13. SET SQL_QUOTE_SHOW_CREATE=1
14. SET SESSION character_set_results = 'binary'
15. show create table `account`
16. SET SESSION character_set_results = 'utf8'
17. show fields from `account`
18. SELECT /*!40001 SQL_NO_CACHE */ * FROM `account`
19. SET SESSION character_set_results = 'binary'
20. use `test`
21. select @@collation_database
22. SHOW TRIGGERS LIKE 'account'
23. SHOW CREATE TRIGGER `ins_sum`
24. SET SESSION character_set_results = 'utf8'
25. ROLLBACK TO SAVEPOINT sp
26. ...
27. ROLLBACK TO SAVEPOINT sp
28. RELEASE SAVEPOINT sp

3.1.4 mysqldump常见用法

1) 全库的备份恢复

备份全库

mysqldump -S /tmp/mysql3306.sock --master-data=2 --single-transaction -A >alldb-`date +%Y%M%d`.sql

恢复全库

mysql -S /tmp/mysql3306.sock < alldb-20180910.sql

2) 指定库的备份恢复

备份指定库

mysqldump -S /tmp/mysql3306.sock --single-transaction -B test >db-`date +%Y%m%d`.sql

恢复指定库

mysql -S /tmp/mysql3306.sock < db-20180910.sql

3）指定表的备份与恢复

备份t1与b表

mysqldump -S /tmp/mysql3306.sock --single-transaction test t1 b >db-`date +%Y%m%d`.sql

恢复t1与b表

mysql -S /tmp/mysql3306.sock  test < db-20180910.sql

4）只备份表的表结构信息

备份test库中t1与b表的表结构信息

mysqldump -S /tmp/mysql3306.sock --single-transaction test t1 b -d >db-`date +%Y%m%d`.sql

...
DROP TABLE IF EXISTS `b`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `b` (
  `b1` int(11) DEFAULT NULL,
  `name` varchar(10) NOT NULL DEFAULT 'w',
  KEY `idx_b1` (`b1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-09-10 16:27:50

5）只备份表的数据信息

备份test库中t1与b表的表结构信息

mysqldump -S /tmp/mysql3306.sock --single-transaction test t1 b -t >db-`date +%Y%m%d`.sql

...
 LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (1,'aaa'),(3,'aaa'),(4,'bbb'),(2,'ccc'),(5,'ccc'),(6,'zzz');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `b`
--

LOCK TABLES `b` WRITE;
/*!40000 ALTER TABLE `b` DISABLE KEYS */;
INSERT INTO `b` VALUES (1,'w'),(2,'w'),(3,'w'),(4,'w'),(5,'w');
/*!40000 ALTER TABLE `b` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

6）备份指定条件的数据

备份test库的t1表，并且大于5的数据。

mysqldump -S /tmp/mysql3306.sock --single-transaction test t1 --where='id>5'

-- MySQL dump 10.13  Distrib 5.7.23, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: test
-- ------------------------------------------------------
-- Server version	5.7.23-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `t1`
--

DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `t1`
--
-- WHERE:  id>5

LOCK TABLES `t1` WRITE;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` VALUES (6,'zzz');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-09-10 16:03:29

3.2 逻辑备份之 - `select ... into outfile`

SELECT … INTO 语句也是一种逻辑备份的方法，更准确地说是导出一张表中的数据。

3.2.1 语法

SELECT  [column 1],[column 2]... 
    INTO
    OUTFILE 'file_name'
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
 FROM TABLE WHERE ...

FIELDS TERMINATED BY 'string'：表示每个列的分隔符。

[OPTIONALLY] ENCLOSED BY 'char：表示对于字符串的包含符

ESCAPED BY 'char'：表示转义符

STARTING BY 'string'：表示每行的开始符号

TERMINATED BY 'string'：表示每行的结束符号

如果没有指定任何的FILEDS和LINES的选项，默认使用以下的设置：

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'

LINES STARTING BY '' TERMINATED BY '\n'

3.2.2 参数

要想支持select … into 语法，首先得设置参数secure-file-priv,此参数的值有以下：

empty string
如果为空，则变量无效。这不是一个安全的设置。
null值
如果设置为NULL，则服务器禁用导入和导出操作。
dirname
如果设置为目录名称，则服务器会将导入和导出操作限制为仅适用于该目录中的文件。目录必须存在;服务器不会创建它。

3.3.3 导出导入示例

select ... into outfile导出数据与LOAD DATA导入数据示例

root@localhost [test] 09:41:38> select * from t1 into outfile '/tmp/t1.txt';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

将参数设置为：
secure_file_priv = /data/mysql/mysql3306/outfile

导出数据
mysql> select * from t1 into outfile '/data/mysql/mysql3306/outfile/t1.txt';

# cat t1.txt 
1	aaa
3	aaa
4	bbb
2	ccc
5	ccc
6	zzz

恢复数据

mysql> delete from t1;

mysql> LOAD DATA INFILE '/data/mysql/mysql3306/outfile/t1.txt' into table t1;

mysql> select * from t1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  3 | aaa  |
|  4 | bbb  |
|  2 | ccc  |
|  5 | ccc  |
|  6 | zzz  |
+----+------+
6 rows in set (0.00 sec)

3.3.4 SELECT OUTFILE + awk

导出数据
mysql> select * from t1 into outfile '/data/mysql/mysql3306/outfile/t1.txt';

mysql> delete from t1;

利用awk生成导入sql
shell> cat t1.txt |awk '{print "insert into test.t1 values("$1",'\''"$2"'\'');"}' > into_t1.sql

# cat into_t1.sql
insert into test.t1 values(1,'aaa');
insert into test.t1 values(3,'aaa');
insert into test.t1 values(4,'bbb');
insert into test.t1 values(2,'ccc');
insert into test.t1 values(5,'ccc');
insert into test.t1 values(6,'zzz');

导入数据
shell> mysql -S /tmp/mysql3306.sock < into_t1.sql

mysql> select * from test.t1;
+----+------+
| id | name |
+----+------+
|  1 | aaa  |
|  3 | aaa  |
|  4 | bbb  |
|  2 | ccc  |
|  5 | ccc  |
|  6 | zzz  |
+----+------+
6 rows in set (0.00 sec)

3.4 逻辑备份之 - mysqlpump

mysqlpump和mysqldump一样，属于逻辑备份，备份以SQL形式的文本保存。逻辑备份相对物理备份的好处是不关心undo log的大小，直接备份数据即可。它最主要的特点是：

并行备份数据库和数据库中的对象的，加快备份过程。
更好的控制数据库和数据库对象（表，存储过程，用户帐户）的备份。
备份用户账号作为帐户管理语句（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。
备份出来直接生成压缩后的备份文件。
备份进度指示（估计值）。
重新加载（还原）备份文件，先建表后插入数据最后建立索引，减少了
索引维护开销，加快了还原速度。
备份可以排除或则指定数据库。

#mysqlpumpy压缩备份 以lz4压缩格式备份employees数据库

mysqlpump -S /tmp/mysql3306.sock --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -B employees > /tmp/employees_db.sql.lz4

#mysqldump备份压缩
mysqldump -S /tmp/mysql3306.sock --single-transaction -B employees | gzip > /tmp/employees.sql.gz

3.5 逻辑备份之 - mydumper

MySQL在备份方面包含了自身的mysqldump工具，但其只支持单线程工作，这就使得它无法迅速的备份数据。而mydumper作为一个实用工具，能够良好支持多线程工作，这使得它在处理速度方面十倍于传统的mysqldump。其特征之一是在处理过程中需要对列表加以锁定，因此如果我们需要在工作时段执行备份工作，那么会引起DML阻塞。但一般现在的MySQL都有主从，备份也大部分在从上进行，所以锁的问题可以不用考虑。这样，mydumper能更好的完成备份任务。

3.5.1 安装mydumper

yum install https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-1.el7.x86_64.rpm

3.5.2 mydumper和myloader重点参数介绍

mydumper参数介绍

参数名	说明
`-B, --database`	需要备份的数据库
`-T, --tables-list`	需要备份的表，多表间用逗号空格
`-O, --omit-from-file`	包含要跳过的database.table条目列表的文件，每行一个（在应用正则表达式选项之前跳过）
`-o, --outputdir`	输出文件的目录
`-s, --statement-size`	生成的insert语句的字节数，默认为 1000000
`-r, --rows`	将表按行分块时，指定的块行数，指定这个选项时，会关闭`--chunk-filesize`
`-F, --chunk-filesize`	将表按大小分块时，指定的块大小，单位是MB
`-c, --compress`	压缩输出文件
`-e, --build-empty-files`	即使表没有数据，还是会产生一个空文件
`-x, --regex`	正则表达式匹配’db.table’
`-i, --ignore-engines`	忽略的存储引擎，用逗号分隔
`-N, --insert-ignore`	使用INSERT IGNORE 备份数据
`-m, --no-schemas`	不导出表结构
`-d, --no-data`	不导出表数据
`-G, --triggers`	导出触发器
`-E, --events`	Dump events
`-R, --routines`	Dump stored procedures and functions
`-W, --no-views`	不导出视图
`-k, --no-locks`	不要执行临时共享读锁定. 警告：这将导致备份不一致
`--no-backup-locks`	不要使用Percona备份锁
`--less-locking`	最大限度地减少InnoDB表的锁定时间。
`-l, --long-query-guard`	设置长查询时间, 默认60s
`-K, --kill-long-queries`	kill 长时间执行的查询
`-D, --daemon`	启用守护进程模式
`-I, --snapshot-interval`	每个转储快照之间的间隔（以分钟为单位），需要–daemon模式下，默认为60s
`-L, --logfile`	要使用的日志文件名，默认情况下使用stdout
`--tz-utc`	备份的时候允许备份Timestamp，这样会导致不同时区的备份还原会出问题，默认关闭，参数：–skip-tz-utc to disable.
`--skip-tz-utc`
`--use-savepoints`	使用保存点来减少元数据锁定问题，需要SUPER权限
`--success-on-1146`	Not increment error count and Warning instead of Critical in case of table doesn’t exist
`--lock-all-tables`	使用LOCK TABLE代替FTWRL(FLUSH TABLE WITH READ LOCK)
`-U, --updated-since`	使用Update_time仅转储在过去天中更新的表
`--trx-consistency-only`	只包含一致性事务
`--complete-insert`	使用包含列名的完整INSERT语句
`-h, --host`	The host to connect to
`-u, --user`	Username with the necessary privileges
`-p, --password`	User password
`-a, --ask-password`	Prompt For User password
`-P, --port`	TCP/IP port to connect to
`-S, --socket`	UNIX domain socket file to use for connection
`-t, --threads`	使用的线程数，默认是4个
`-C, --compress-protocol`	Use compression on the MySQL connection
`-V, --version`	Show the program version and exit
`-v, --verbose`	更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
`--defaults-file`	Use a specific defaults file
`--ssl`	Connect using SSL
`--key`	The path name to the key file
`--cert`	The path name to the certificate file
`--ca`	The path name to the certificate authority file
`--capath`	The path name to a directory that contains trusted SSL CA certificates in PEM format
`--cipher`	A list of permissible ciphers to use for SSL encryption

myloader参数介绍

参数名	注释
`-d, --directory`	要导入的备份文件所在的目录
`-q, --queries-per-transaction`	每次事务执行的查询数量, default 1000
`-o, --overwrite-tables`	如果要恢复的表存在，则先drop表
`-B, --database`	指定需要还原数据到哪个数据库中
`-s, --source-db`	需要还原的数据库
`-e, --enable-binlog`	启用二进制日志恢复数据
`-h, --host`	The host to connect to
`-u, --user`	Username with the necessary privileges
`-p, --password`	User password
`-a, --ask-password`	Prompt For User password
`-P, --port`	TCP/IP port to connect to
`-S, --socket`	UNIX domain socket file to use for connection
`-t, --threads`	使用的线程数量, default 4
`-C, --compress-protocol`	连接上使用的压缩协议
`-V, --version`	Show the program version and exit
`-v, --verbose`	更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
`--defaults-file`	Use a specific defaults file

3.5.3 mydumper与myloader的用法

1）以压缩备份备份所有数据库,还原指定的sbtest库

shell> mydumper -S /tmp/mysql3306.sock  -c /data/mysql/mysql3306/outfile/

shell> ls
export-20180911-152023

从备份文件中还原指定库sbtest
shell> myloader -S /tmp/mysql3306.sock -s sbtest -B sbtest -d /data/mysql/mysql3306/outfile/export-20180911-152023/

【注意】：如果不加-s，会把所有库的表，还原到sbtest库中。

2）还原sbtest库中的sbtest1，sbtest2表

mysql> drop table sbtest1;
mysql> drop table sbtest2;

shell> myloader -S /tmp/mysql3306.sock -s sbtest -B sbtest -o sbtest1,sbtest2 -d /data/mysql/mysql3306/outfile/export-20180911-152023/

3.6 物理备份之 - XtraBackup

XtraBackup是Percona公司的开源的MySQL热备工具，在备份的过程中不会被锁在数据库中。该工具能够备份InnoDB、MyISAM表，甚至可以备份Percona XtraDB Cluster。支持流方式、压缩、加密和增量备份等。

XtraBackup的优点如下：

无须停止数据库进行InnoDB热备
增量备份MySQL
流压缩传输到其他服务器
在线移动表
能够比较容易地创建主从同步
备份MySQL时不会增大服务器负载

3.6.1 XtraBackup备份原理

XtraBackup是基于InnoDB自身的崩溃恢复机制完成备份的。它首先复制所有InnoDB表数据文本副本，这样导致了内部数据的不一致。但是，它后来执行崩溃恢复功能，使数据达到最终的一致，成为可用的数据库文件。

Percona XtraBackup 备份原理说明【转】

3.6.2 安装XtraBackup

shell> tar xfvz percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt11.tar.gz

shell> cd /usr/local

shell> ln -s /opt/percona-xtrabackup-2.4.12-Linux-x86_64 xtrabackup

shell> echo "export PATH=$PATH:/usr/local/xtrabackup/bin" >> /etc/profile

shell> source /etc/profile

3.6.3 XtraBackup需要的权限

RELOAD,LOCK TABLES
在备份时，需要执行 FLUSH TABLES WITH READ LOCK和 FLUSH ENGINE LOGS，然后开始复制数据。并且在使用 Backup Locks时，需要执行LOCK TABLES FOR BACKUP和 LOCK BINLOG FOR BACKUP。除非备份时，执行–no-lock选项，这样便不需要这两个权限了。
REPLICATION CLIENT
在备份时需要获得二进制日志的位置信息，需要该权限
CREATE TABLESPACE
使用 XtraBackup进行恢复独立表空间的时候，需要使用导入表空间，这时候需要该权限。
PROCESS
XtraBackup在备份的时候，利用该权限可以查看所有正在服务器端运行的线程情况。
SUPER
用在复制环境中开启和关闭 SLAVE线程。
CREATE
CREATE权限用于创建 PERCONA_SCHEMA.xtrabackup_ history数据库和表。
INSERT
INSERT权限用于将写入历史记录到 PERCONA_SCHEMA. xtrabackup_history数据库和表。
SELECT
当 innobackupex使用--incremental-history-name或--incremental-history-uuid选项时，可以使用 SELECT权限查询 PERCONA_SCHEMA. xtrabackup_history表的 innodb_to_lsn的值来满足该特性。

创建备份用户

mysql> create user 'backup'@'localhost' identified by 'mysql';

mysql> grant reload,lock tables,replication client,CREATE TABLESPACE,PROCESS,SUPER,CREATE,INSERT,SELECT on *.* to 'backup'@'localhost';

3.6.4 配置XtraBackup

在my.cnf配置文件，增加以下参数

[xtrabackup]
target_dir = /data/backup/

若没在my.cnf配置文件增加xtrabackup参数，可以在备份时指定备份目录，如：

xtrabackup -S /tmp/mysql3306.sock -ubackup -pmysql --backup --target-dir=/data/backup/

3.6.5 xtrabackup 备份实践

全备份

xtrabackup备份

xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/

prepare备份

xtrabackup --prepare --target-dir=/data/backup/

恢复

#恢复时，需MySQL数据目录为空。

shell> xtrabackup --copy-back --target-dir=/data/backup/


#恢复完成后，更改MySQL数据目录权限属主

shell> chown -R mysql:mysql data

启动不了，错误日志提示

2018-09-12T06:54:49.020755Z 0 [ERROR] InnoDB: Unable to open undo tablespace 'undolog/undo001'.
2018-09-12T06:54:49.020815Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error

查看undolog目录，发现undolog并没有恢复。。。

手动cp过来

然后在重新启动，就行了。

为什么undolog不还原呢？

配置文件中my.cnf的innodb_undo_directory参数需要指定绝对路径

innodb_undo_directory = /data/mysql/mysql3306/data/undolog

这样恢复就行了

增量备份

创建基础备份（全备份）

shell> mkdir /data/backup/base
shell> mkdir /data/backup/incre
shell> mkdir /data/backup/incre2
shell> chown mysql:mysql /data/backup/*

xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/base/

# more xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 4105224763
last_lsn = 4105224772
compact = 0
recover_binlog_info = 0

创建增量备份1

mysql> create database incre;
mysql> use incre;
mysql> create table t1 (id int);

shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/incre/ --incremental-basedir=/data/backup/base/

shell> more xtrabackup_checkpoints
backup_type = incremental
from_lsn = 4105224763
to_lsn = 4105229436
last_lsn = 4105229445
compact = 0
recover_binlog_info = 0

创建增量备份2

mysql> create table t2 (id int);
mysql> insert into t2 select 2;

shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/incre2/ --incremental-basedir=/data/backup/incre/

shell> more xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 4105229436
to_lsn = 4105233934
last_lsn = 4105233943
compact = 0
recover_binlog_info = 0

prepare 增量备份

shell> xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base

#日志显示：
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4105224781
InnoDB: Number of pools: 1
180912 15:42:08 completed OK!



#应用第一个增量备份
shell> xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base/ --incremental-dir=/data/backup/incre/


#应用第二个增量备份
shell> xtrabackup --prepare --target-dir=/data/backup/base/ --incremental-dir=/data/backup/incre2/


#日志显示：

InnoDB: Shutdown completed; log sequence number 4105234472
180912 15:51:27 completed OK!

shell> more /data/backup/base/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 4105233934
last_lsn = 4105233943
compact = 0
recover_binlog_info = 0

恢复备份

shell> mysqladmin -S /tmp/mysql3306.sock shutdown
shell> mv data data_bak
shell> mkdir data
shell> chown mysql:mysql data

shell> xtrabackup --copy-back --target-dir=/data/backup/base/

shell> chown -R mysql:mysql /data/mysql/mysql3306/data

shell> mysqld --defaults-file=/etc/my3306.cnf &

mysql> use incre

mysql> select * from t1;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> select * from t2;
+------+
| id   |
+------+
|    2 |
+------+
1 row in set (0.01 sec)

可以看到增量备份1与增量备份2的备份都恢复出来了。

压缩备份

使用--compress选项进行压缩备份

备份数据库

shell> mkdir compress
shell> chown mysql:mysql compress

shell> xtrabackup --backup --compress -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/compress/

#如果要加速压缩，可以使用并行压缩，可以使用xtrabackup --compress-threads选项启用。 以下示例将使用四个线程进行压缩：

shell> xtrabackup --backup --compress  --compress-threads=4 -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/compress/

prepare 备份

# 解压备份
shell> xtrabackup --decompress --target-dir=/data/backup/compress/

# xtrabackup --parallel可与xtrabackup --decompress选项一起使用，以同时解压缩多个文件。如：

shell> xtrabackup --decompress --parallel=4 --target-dir=/data/backup/compress/

#注意：使用--parallel选项时，需要先安装qpress插件，否则解压时，报如下错误：
sh: qpress: command not found
sh: qpress: command not found
sh: qpress: command not found
cat: write error: Broken pipe
sh: qpress: command not found
cat: write error: Broken pipe
cat: write error: Broken pipe
Error: decrypt and decompress thread 0 failed.
Error: decrypt and decompress thread 1 failed.
Error: decrypt and decompress thread 2 failed.
Error: decrypt and decompress thread 3 failed.

#安装qpress
shell> wget http://www.quicklz.com/qpress-11-linux-x64.tar
shell> tar xvf qpress-11-linux-x64.tar
shell> cp qpress /usr/bin

#prepare备份
shell> xtrabackup --prepare --target-dir=/data/backup/compress/

恢复备份

shell> xtrabackup --copy-back --target-dir=/data/backup/compress/

加密备份

创建密钥

shell> openssl rand -base64 24
SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8

或者

shell> openssl rand -base64 24
A0fYOUej9Vq66JWqfRnvENjtHa7ipNmy 

shell> echo -n "A0fYOUej9Vq66JWqfRnvENjtHa7ipNmy" > /data/backup/encry/keyfile

创建加密备份

shell> xtrabackup -S /tmp/mysql3306.sock -ubackup -pmysql --backup --target-dir=/data/backup/encry --encrypt=AES256 --encrypt-key="SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8"

或者

shell> xtrabackup -S /tmp/mysql3306.sock -ubackup -pmysql --backup --target-dir=/data/backup/encry --encrypt=AES256 --encrypt-key-file=/data/backup/encry/keyfile

prepare 备份

#解密备份
xtrabackup --decrypt=AES256 --encrypt-key="SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8" --target-dir=/data/backup/encry

#加上--remove-original参数可以在解密备份文件后，删除加密文件。

shell> xtrabackup --decrypt=AES256 --encrypt-key="SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8" --target-dir=/data/backup/encry --remove-original

#也可以加上 --parallel参数来提高解密文件速度

#prapare备份
shell> xtrabackup --prepare --target-dir=/data/backup/encry

恢复备份

shell> xtrabackup --copy-back --target-dir=/data/backup/encry

3.6.6 innobackupex 备份实践

全备份

备份

#备份到 /data/backup/当前时间/ 目录下
shell> innobackupex --defaults-file=/etc/my3306.cnf --user=backup --password=mysql /data/backup


# 或者指定--no-timestamp参数，将备份到指定backup-dir目录

shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql  /data/backup/backup-dir --no-timestamp

prepare备份

shell> innobackupex --apply-log /data/backup/2018-09-13_10-29-10/

恢复备份

shell> innobackupex --defaults-file=/etc/my3306.cnf --copy-back /data/backup/2018-09-13_10-29-10/

shell> chown -R mysql:mysql /data/mysql/mysql3306/data

增量备份

创建base备份

shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql /data/backup/base

shell> cat xtrabackup_checkpoints 

backup_type = full-backuped
from_lsn = 0
to_lsn = 4105235515
last_lsn = 4105235524
compact = 0
recover_binlog_info = 0

创建增量备份1

mysql> use incre
mysql> create table t3 (id int);
mysql> insert into t3 select 3;

shell> innobackupex --defaults-file=/etc/my3306.cnf --incremental -ubackup -pmysql /data/backup/incre --incremental-basedir=/data/backup/base/2018-09-13_10-51-46


shell> cat xtrabackup_checkpoints 

backup_type = incremental
from_lsn = 4105235515
to_lsn = 4105240026
last_lsn = 4105240035
compact = 0
recover_binlog_info = 0

创建增量备份2

mysql> use incre
mysql> create table t4 (id int);
mysql> insert into t4 select 4;

shell> innobackupex --defaults-file=/etc/my3306.cnf --incremental -ubackup -pmysql /data/backup/incre --incremental-basedir=/data/backup/incre/2018-09-13_10-58-04/

shell> cat xtrabackup_checkpoints 

backup_type = incremental
from_lsn = 4105240026
to_lsn = 4105244515
last_lsn = 4105244524
compact = 0
recover_binlog_info = 0

#也可以使用参数--incremental-lsn进行增量备份，如

shell> innobackupex --defaults-file=/etc/my3306.cnf --incremental -ubackup -pmysql /data/backup/incre --incremental-lsn=4105235515

shell> innobackupex --defaults-file=/etc/my3306.cnf --incremental -ubackup -pmysql /data/backup/incre --incremental-lsn=4105244515

prepare 增量备份

#prepare base备份

shell> innobackupex --apply-log --redo-only /data/backup/base/2018-09-13_10-51-46/

#日志显示
InnoDB: Shutdown completed; log sequence number 4105235533
InnoDB: Number of pools: 1
180913 11:14:03 completed OK!

#prepare 增量备份1
shell> innobackupex --apply-log --redo-only /data/backup/base/2018-09-13_10-51-46 --incremental-dir=/data/backup/incre/2018-09-13_10-58-04

#prepare 增量备份2

shell> innobackupex --apply-log /data/backup/base/2018-09-13_10-51-46 --incremental-dir=/data/backup/incre/2018-09-13_11-02-11

shell> innobackupex --apply-log /data/backup/base/2018-09-13_10-51-46/

5）恢复备份

shell> innobackupex --defaults-file=/etc/my3306.cnf --copy-back /data/backup/base/2018-09-13_10-51-46/

shell> chown -R mysql:mysql /data/mysql/mysql3306/data

流式化备份

打包备份

shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --stream=tar ./ > /data/backup/dbbackup`date +%Y%m%d_%H%M%S`.tar

#或者

shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --stream=tar --target-dir=/data/backup/ > /data/backup/db.tar


#解压到指定目录
shell> mkdir db20180913_141603
shell> tar -xvf db20180913_141603.tar -C ./db20180913_141603

#peapare备份
shell> innobackupex --apply-log /data/backup/db20180913_141603

带压缩的打包备份

shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --stream=tar ./ |gzip - > /data/backup/dbbackup`date +%Y%m%d_%H%M%S`.tar.gz

#或者

shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --stream=tar --target-dir=/data/backup/ |gzip - > /data/backup/db.tar.gz


shell> mkdir dbbackup20180913_150942
shell> tar -xvzf dbbackup20180913_150942.tar.gz -C ./dbbackup20180913_150942

#peapare备份
shell> innobackupex --apply-log /data/backup/dbbackup20180913_150942

基于SSH远程

shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --stream=tar ./ |ssh root@mysqldb2 "cat - > /data/backup/dbback`date +%Y%m%d_%H%M%S`.tar"

#或者

shell> xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --stream=tar  |ssh root@mysqldb2 "cat - > /data/backup/db.tar"

备份指定数据库

#备份指定数据库 用--database参数，如下：备份employees 与sbtest数据库

shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --database='employees sbtest' /data/backup

#prepare备份
shell> innobackupex --apply-log /data/backup/2018-09-13_16-13-47/


#恢复备份
#copy，因为是部分备份，不能直接用--copy-back，只能手动来复制需要的库，也要复制ibdata(数据字典）

shell> cp -r sbtest /data/mysql/mysql3306/data/
shell> cp -r employees /data/mysql/mysql3306/data/
shell> cp ibdata* /data/mysql/mysql3306/data/
shell> chown -R mysql:mysql /data/mysql/mysql3306/data

#备份指定表,如备份employees.dept与sbtest.sbtest1表：
shell> innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --database='employees.dept sbtest.sbtest1' /data/backup

收起

MYSQL备份

mysql备份

权限管理和备份

用户管理

mysql 备份

Mysql备份

1.冷备

cp

2.热备

mysqldump

二进制（binlog）

mysqldump全备 + binlog增备

mysqlhotcopy