MySQL备份恢复

 

MySQL备份恢复

 

 

1. MySQL 的备份方式
2. 冷备
3. 热备
3.1 逻辑备份之 - mysqldump
3.1.1 mysqldump语法如下
3.1.2 mysqldump常见的参数
3.1.3 mysqldump备份流程
3.1.4 mysqldump常见用法
3.2 逻辑备份之 - select ... into outfile
3.2.1 语法
3.2.2 参数
3.3.3 导出导入示例
3.3.4 SELECT OUTFILE + awk
3.4 逻辑备份之 - mysqlpump
3.5 逻辑备份之 - mydumper
3.5.1 安装mydumper
3.5.2 mydumper和myloader重点参数介绍
3.5.3 mydumper与myloader的用法
3.6 物理备份之 - XtraBackup
3.6.1 XtraBackup备份原理
3.6.2 安装XtraBackup
3.6.3 XtraBackup需要的权限
3.6.4 配置XtraBackup
3.6.5 xtrabackup 备份实践
3.6.6 innobackupex 备份实践

 

 

本文档主要围绕生产中经常使用的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;

# 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
# 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');

导入数据
# 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库

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

# ls
export-20180911-152023

从备份文件中还原指定库sbtest
# 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;

# 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

# tar xfvz percona-xtrabackup-2.4.12-Linux-x86_64.libgcrypt11.tar.gz

# cd /usr/local

# ln -s /opt/percona-xtrabackup-2.4.12-Linux-x86_64 xtrabackup

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

# 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 备份实践

全备份

1）xtrabackup备份
xtrabackup --backup -S /tmp/mysql3306.sock -ubackup -pmysql --target-dir=/data/backup/

2）prepare备份
xtrabackup --prepare --target-dir=/data/backup/


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

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


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

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

这样恢复就行了

 

增量备份

1) 创建基础备份（全备份）
# mkdir /data/backup/base
# mkdir /data/backup/incre
# mkdir /data/backup/incre2
# 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

2) 创建增量备份1
mysql> create database incre;
mysql> use incre;
mysql> create table t1 (id int);

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

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

2) 创建增量备份2
mysql> create table t2 (id int);
mysql> insert into t2 select 2;

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

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

3) prepare 增量备份
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!



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


#应用第二个增量备份
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!

# 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

4）恢复备份
# mysqladmin -S /tmp/mysql3306.sock shutdown
# mv data data_bak
# mkdir data
# chown mysql:mysql data

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



# chown -R mysql:mysql /data/mysql/mysql3306/data

# 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选项进行压缩备份

1) 备份数据库
# mkdir compress
# chown mysql:mysql compress

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

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

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


2） prepare 备份
# 解压备份
xtrabackup --decompress --target-dir=/data/backup/compress/

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

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
# wget http://www.quicklz.com/qpress-11-linux-x64.tar
# tar xvf qpress-11-linux-x64.tar
# cp qpress /usr/bin

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


3） 恢复备份
xtrabackup --copy-back --target-dir=/data/backup/compress/

 

加密备份

1) 创建密钥
# openssl rand -base64 24
SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8

或者

# openssl rand -base64 24
A0fYOUej9Vq66JWqfRnvENjtHa7ipNmy 

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


2） 创建加密备份
xtrabackup -S /tmp/mysql3306.sock -ubackup -pmysql --backup --target-dir=/data/backup/encry --encrypt=AES256 --encrypt-key="SuwhhXvOcYsqLQBd/C2cj5aR495A9LC8"

或者

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


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

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

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

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

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


4）恢复备份
xtrabackup --copy-back --target-dir=/data/backup/encry

 

3.6.6 innobackupex 备份实践

全备份

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


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

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


2）prepare备份
innobackupex --apply-log /data/backup/2018-09-13_10-29-10/


3) 恢复备份
innobackupex --defaults-file=/etc/my3306.cnf --copy-back /data/backup/2018-09-13_10-29-10/

# chown -R mysql:mysql /data/mysql/mysql3306/data

 

增量备份

1）创建base备份
innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql /data/backup/base

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


2) 创建增量备份1
mysql> use incre
mysql> create table t3 (id int);
mysql> insert into t3 select 3;

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


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


3) 创建增量备份2
mysql> use incre
mysql> create table t4 (id int);
mysql> insert into t4 select 4;

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

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

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

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

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


4）prepare 增量备份
#prepare base备份

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
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

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

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


5） 恢复备份
innobackupex --defaults-file=/etc/my3306.cnf --copy-back /data/backup/base/2018-09-13_10-51-46/

# chown -R mysql:mysql /data/mysql/mysql3306/data

 

流式化备份

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

或者

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


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

peapare备份
innobackupex --apply-log /data/backup/db20180913_141603


2） 带压缩的打包备份
innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --stream=tar ./ |gzip - > /data/backup/dbbackup`date +%Y%m%d_%H%M%S`.tar.gz

或者

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


# mkdir dbbackup20180913_150942
# tar -xvzf dbbackup20180913_150942.tar.gz -C ./dbbackup20180913_150942

peapare备份
innobackupex --apply-log /data/backup/dbbackup20180913_150942


3) 基于SSH远程
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"

或者

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

 

备份指定数据库

#备份指定数据库 用--database参数，如下：备份employees 与sbtest数据库
innobackupex --defaults-file=/etc/my3306.cnf -ubackup -pmysql --database='employees sbtest' /data/backup

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


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

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

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

 

 
posted @ 2018-09-14 11:35  DB-Engineer  阅读(699)  评论(0)  编辑  收藏  举报