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表数据文本副本,这样导致了内部数据的不一致。但是,它后来执行崩溃恢复功能,使数据达到最终的一致,成为可用的数据库文件。
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