第21章：MySQL之备份和恢复
第21章：MySQL之备份和恢复
目录
修订日期：2021-01-08
一. 备份的类型
以下三种方式都是 全量备份 的方式
- 热备（Hot Backup）
- 在线备份
- 对应用无影响（应用程序不会被阻塞（其实有，只是时间很短），可以正常的读写，但是性能上还是有影响的）
- 冷备（Cold Backup）
- 备份数据文件，最可靠的备份
- 需要停机（最大的弊端）
- 备份datadir下的所有文件
- 温备（Warm Backup）
- 在线备份
- 对应用影响很大
- 通常加一个读锁（读不受影响，写被阻塞）
二. 备份工具
-
ibbackup
- 官方备份工具
- 收费
- 物理备份
-
Percona XtraBackup
- 开源社区备份工具（必须使用最新版本，否则备份出来的数据可能有问题）
- 开源免费
- 物理备份
- Xtrabackup老版本的问题
-
mysqldump/mysqlpump
- 官方自带备份工具，是可靠的，且备份文件相对较小
- 产生一组能够被执行以再现原始数据库对象定义和表数据的SQL语句
- 开源免费
- 逻辑备份
- 恢复速度较慢（需要重建索引等等）
-
mydumper
注意:
- 有的热备都只能使
InnoDB存储引擎表 其他存储引擎表只能是温备`
三. 备份方式
- 物理备份
- 备份了
表空间的数据，和冷备类似
- 备份了
- 逻辑备份
- 备份了
表中的数据（导出的是一条条SQL）
- 备份了
- 从以下维度选择备份方式：
- 备份速度
- 恢复速度
- 备份大小
- 对业务影响
四. 备份工具
4.1 mysqldump
4.1.1 mysqldump 简介
- 1.语法
Usage: mysqldump [OPTIONS] database [tables] --备份某个数据库下的表
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] --备份指定数据库
OR mysqldump [OPTIONS] --all-databases [OPTIONS] --备份所有数据库
-
2.常用参数
-
--all-databases：备份所有的数据库- mysqldump -uroot -p --all-databases > alldb.sql
-
--databasesDB1 [DB2 DB3] ：备份指定的数据库- mysqldump -uroot -p --databases db1 db2 > mulit.sql
-
--flush-logs,-F：设置导出前刷新binlog- mysqldump -uroot -p -F --databases db1 > db1.sql
-
--no-data，-d：设置导出CREATE TABLE表的语句- mysqldump -uroot -p -d --databases db1 > db1.sql
-
--opt：默认启用, 快速转储操作，并生成一个可以快速重新加载到MySQL服务器的转储文件- --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset参数组合
- mysqldump -uroot -p --opt --databases db1 > db1.sql
-
--default-character-set：设置导出的字符集- mysqldump -uroot -p --default-character-set=utf8 --databases db1 > db1.sql
-
--single-transaction： 在一个事物中导出，确保产生一致性的备份，适用于事务引擎，当前只针对innodb引擎- mysqldump --single-transaction -uroot -piforgot --databases db1 > db1.sql
-
--master-data：将CHANGE MASTER TO 语句写入导出的数据文件中- 如果为1，将会输出CHANGE MASTER 命令
- 如果为2，输出CHANGE MASTER 命令，但前面加注释
- mysqldump -uroot --single-transaction --master-data=2 --databases db1 > db1.sql
-
--set-gtid-purged：是否要把gtid信息写入导出的数据文件中- --set-gtid-purged=OFF： SET @@SESSION.SQL_LOG_BIN=0;不被添加到输出。
- --set-gtid-purged=ON： SET @@SESSION.SQL_LOG_BIN=0;被添加到输出。
- --set-gtid-purged=AUTO：SET @@SESSION.SQL_LOG_BIN=0;如果在正在备份的服务器上启用了GTID（即，如果AUTO 计算结果为ON），则会将其添加到输出中。
- mysqldump --single-transaction --master-data=2 -uroot -piforgot --set-gtid-purged=OFF --databases baktest >/data5/backup_sm.sql
-
--delete-master-logs- 在主复制服务器上，
PURGE BINARY LOGS执行转储操作后，通过向服务器发送语句来删除二进制日志。 - mysqldump --single-transaction --master-data=2 --delete-master-logs -uroot -piforgot --set-gtid-purged=OFF --databases mytest >/data5/backup_20180208_1_PM.sql
- 在主复制服务器上，
-
4.1.2 mysqldump演示
4.1.2.1 准备数据
mysql root@localhost:mytest> create database baktest;
Query OK, 1 row affected
Time: 0.001s
mysql root@localhost:mytest> use baktest;
You are now connected to database "baktest" as user "root"
Time: 0.002s
mysql root@localhost:baktest> create table bk like mytest.a;
Query OK, 0 rows affected
Time: 0.048s
mysql root@localhost:baktest> insert into bk select * from mytest.a;
Query OK, 5 rows affected
Time: 0.002s
mysql root@localhost:baktest> select * from bk;
+----+-----+
| id | num |
+----+-----+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| e | 177 |
+----+-----+
5 rows in set
Time: 0.011s
mysql root@localhost:baktest>
4.1.2.2 要对单一整数据库进行备份
[root@localhost-m(252) /data5]# mysqldump -uroot -piforgot --databases baktest >/data5/backup_20180208_1_PM.sql --备份baktest库进行备份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@localhost-m(252) /data5]# cat /data5/backup_20180208_1_PM.sql
-- MySQL dump 10.13 Distrib 5.7.18, for linux-glibc2.5 (x86_64) --版本信息
--
-- Host: localhost Database: baktest --主机 和库
-- ------------------------------------------------------
-- Server version 5.7.18-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 */;
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN; --设置session临时操作不记录到binlog中
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup --备份包括GTID信息
--
SET @@GLOBAL.GTID_PURGED='3eae5854-d89f-11e7-ab20-246e960a8d84:1-10189,
746122da-663e-11e7-9de1-b8ca3a6567c4:1-19113,
a57cd625-663e-11e7-9ba9-b8ca3a64d66c:18606-190213162:190249486-190252207:190327015-207860771:207860811-244861160:244862559-348804170'; --GTID信息
--
-- Current Database: `baktest` --备份的数据库
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `baktest` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */; --如果baktest库不存在就创建
USE `baktest`;
--
-- Table structure for table `bk` --bk表的信息
--
DROP TABLE IF EXISTS `bk`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bk` (
`id` char(1) DEFAULT NULL,
`num` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `bk`
--
LOCK TABLES `bk` WRITE;
/*!40000 ALTER TABLE `bk` DISABLE KEYS */;
INSERT INTO `bk` VALUES ('a',5),('b',10),('c',15),('d',10),('e',177);
/*!40000 ALTER TABLE `bk` ENABLE KEYS */;
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
/*!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-02-08 15:12:32 --dump完成时间
[root@localhost-m(252) /data5]#
4.1.2.3 对数据库进行恢复
-
方法一
-
会话A
mysql root@localhost:baktest> use mytest
You are now connected to database "mytest" as user "root"
Time: 0.002s
mysql root@localhost:mytest> drop database baktest;
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 1 row affected
Time: 0.002s
mysql root@localhost:mytest> select * from baktest.bk;
(1146, u"Table 'baktest.bk' doesn't exist")
mysql root@localhost:mytest>
- 会话B
[root@localhost-m(252) /data5]# mysql -uroot -piforgot </data5/backup_20180208_1_PM.sql
mysql: [Warning] Using a password on the command line interface can be insecure. --使用GTID模式的实例的导入,需要目标实例的GTID记录是空的,不然会报错
ERROR 1840 (HY000) at line 24: @@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty. --提示GTID_EXECUTED为非空,不能导入，所以导入前要先重置下binlog
- 会话A
mysql root@localhost:mytest> reset master; --重置了binlog那就没问题了,重新执行导入
Query OK, 0 rows affected
Time: 0.006s
mysql root@localhost:mytest>
- 会话B
[root@localhost-m(252) /data5]# mysql -uroot -piforgot </data5/backup_20180208_1_PM.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost-m(252) /data5]#
- 方法二
[root@localhost-m(252) /data5]# mysql -uroot -piforgot -e "source /data5/backup_20180208_1_PM.sql"
4.1.2.4 要对单表进行备份
[root@localhost-m(252) /data5]# mysqldump -uroot -piforgot mytest t >/data5/backup_mytest_tab_t.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@localhost-m(252) /data5]# egrep -v "^\/\*" /data5/backup_mytest_tab_t.sql
-- MySQL dump 10.13 Distrib 5.7.18, for linux-glibc2.5 (x86_64)
--
-- Host: localhost Database: mytest
-- ------------------------------------------------------
-- Server version 5.7.18-log
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;
--
-- GTID state at the beginning of the backup
--
SET @@GLOBAL.GTID_PURGED='3eae5854-d89f-11e7-ab20-246e960a8d84:1-10189,
746122da-663e-11e7-9de1-b8ca3a6567c4:1-19113,
a57cd625-663e-11e7-9ba9-b8ca3a64d66c:18606-190213162:190249486-190252207:190327015-207860771:207860811-244861160:244862559-348804170';
--
-- Table structure for table `t` --只导出mytest库下t表
--
DROP TABLE IF EXISTS `t`;
CREATE TABLE `t` (
`tid` int(11) NOT NULL AUTO_INCREMENT,
`tname` varchar(20) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
--
-- Dumping data for table `t`
--
LOCK TABLES `t` WRITE;
INSERT INTO `t` VALUES (1,'国安'),(2,'申花'),(3,'布尔联队'),(4,'国家'),(5,'国家1'),(6,'国家2'),(7,'国家3'),(8,'家2222'),(9,'家2222');
UNLOCK TABLES;
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
-- Dump completed on 2018-02-08 15:53:27
[root@localhost-m(252) /data5]#
4.1.2.5 单库导出多张表结构及表数据，用空格隔开
[root@localhost-m(252) /data5]# mysqldump -uroot -piforgot mytest t a >/data5/backup_mytest_muilt_tab.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@localhost-m(252) /data5]# egrep "Dumping data for table" /data5/backup_mytest_muilt_tab.sql
-- Dumping data for table `t` --导出mytest库t表
-- Dumping data for table `a` --导出mytest库a表
[root@localhost-m(252) /data5]#
4.1.2.6 要对多个数据库进行备份，用空格隔开
[root@localhost-m(252) /data5]# mysqldump -uroot -piforgot --databases baktest mytest >/data5/backup_multi.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@localhost-m(252) /data5]# cat /data5/backup_multi.sql |grep "CREATE DATABASE"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `baktest` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */; --备份了baktest库
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mytest` /*!40100 DEFAULT CHARACTER SET utf8 */; --备份了mytest库
[root@localhost-m(252) /data5]#
4.1.2.7 要对所有数据库进行备份
[root@localhost-m(252) /data5]# mysqldump -uroot -piforgot --all-databases >/data5/backup_20180208_all.sql
4.1.2.8 set-gtid-purged参数在备份中作用
[root@localhost-m(252) /data5]# mysqldump -uroot -piforgot --set-gtid-purged=OFF --databases baktest >/data5/backup_20180208_gtid_off.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost-m(252) /data5]# mysqldump -uroot -piforgot --databases baktest >/data5/backup_20180208_gtid_on.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.
[root@localhost-m(252) /data5]# diff /data5/backup_20180208_gtid_off.sql /data5/backup_20180208_gtid_on.sql --对比输出结果
16a17,26
> SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
> SET @@SESSION.SQL_LOG_BIN= 0;
>
> --
> -- GTID state at the beginning of the backup --默认输出gtid信息到backup_20180208_gtid_on.sql
> --
>
> SET @@GLOBAL.GTID_PURGED='3eae5854-d89f-11e7-ab20-246e960a8d84:1-10189,
> 746122da-663e-11e7-9de1-b8ca3a6567c4:1-19113,
> a57cd625-663e-11e7-9ba9-b8ca3a64d66c:18606-190213162:190249486-190252207:190327015-207860771:207860811-244861160:244862559-348804170';
47a58
> SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;
58c69
< -- Dump completed on 2018-02-08 16:11:42
---
> -- Dump completed on 2018-02-08 16:12:00
[root@localhost-m(252) /data5]#
4.1.2.9 通过将数据从一台MySQL服务器复制到另一台服务器， mysqldump对于填充数据库也非常有用,导入库可以不同名
- 会话A
mysql root@localhost:mytest> select * from baktest.bk;
+----+-----+
| id | num |
+----+-----+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| e | 177 |
+----+-----+
5 rows in set
Time: 0.011s
mysql root@localhost:mytest>
- 会话B
[root@localhost-m(252) /data5]# mysql -ugcdb -piforgot --socket=/r2/soft/dbtest/mysql-5.7.18/mysqldata/mysql.sock
(gcdb@localhost) 16:27:20 [(none)]> show variables like "port%";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3307 |
+---------------+-------+
1 row in set (0.00 sec)
(gcdb@localhost) 16:27:41 [(none)]> create database baktest; --3307上面创建baktest库
Query OK, 1 row affected (0.00 sec)
(gcdb@localhost) 16:28:04 [(none)]> create database bbb; --3307上面创建bbb库
Query OK, 1 row affected (0.00 sec)
-
会话C
-
语法：shell> mysqldump --opt db_name | mysql --host=remote_host -C db_name
--把3306上面的baktest库导入3307的baktest
[root@localhost-m(252) /data5]# mysqldump --set-gtid-purged=OFF --opt -uroot -piforgot baktest |mysql -ugcdb -piforgot --socket=/r2/soft/dbtest/mysql-5.7.18/mysqldata/mysql.sock -C baktest
--把3306上面的baktest库导入3307的bbb库
[root@localhost-m(252) /data5]# mysqldump --set-gtid-purged=OFF --opt -uroot -piforgot baktest |mysql -ugcdb -piforgot --socket=/r2/soft/dbtest/mysql-5.7.18/mysqldata/mysql.sock -C bbb
- 会话B
(gcdb@localhost) 16:28:43 [(none)]> select * from baktest.bk; --查询已导入baktest库里面的表
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| e | 177 |
+------+------+
5 rows in set (0.00 sec)
(gcdb@localhost) 16:30:29 [(none)]> select * from bbb.bk; --查询已导入bbb库里面的表
+------+------+
| id | num |
+------+------+
| a | 5 |
| b | 10 |
| c | 15 |
| d | 10 |
| e | 177 |
+------+------+
5 rows in set (0.00 sec)
另外也可以单表传输如：mysqldump -h192.168.48.168 -ugcdb -piforgot --set-gtid-purged=OFF mytest t6 | mysql -ugcdb -piforgot mytest
表示从服务器192.168.48.168上mysql,里面的mytest库中的t6表传递到本地的mytest库里面
4.1.3 对于InnoDB表进行在线备份的方式
- 1. --master-data参数
[root@localhost-m(252) /data5]# mysqldump -uroot -piforgot --set-gtid-purged=OFF --master-data=1 --databases baktest >/data5/bak-master1.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost-m(252) /data5]# mysqldump -uroot -piforgot --set-gtid-purged=OFF --master-data=2 --databases baktest >/data5/bak-master2.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost-m(252) /data5]# diff bak-master1.sql bak-master2.sql --对比差异
22c22
< CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=154; --"--master-data=1" 在文件中添加了CHANGE MASTER语句
---
> -- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=154; --##这里CHANGE MASTER前面加了“--”，表示注释
64c64
< -- Dump completed on 2018-02-08 18:13:25
---
> -- Dump completed on 2018-02-08 18:13:40
[root@localhost-m(252) /data5]#
- 2. --single-transaction --master-data参数结合
| 会话A | 会话B | 会话C |
|---|---|---|
| 开启 general log | 空 | tail 监控 general log |
| 空 | dump数据 | tail 监控 general log |
| 关闭 general log | 空 | tail 监控 general log |
会话A
mysql gcdb@localhost:mytest> set global general_log=1;
Query OK, 0 rows affected
Time: 0.002s
mysql gcdb@localhost:mytest> show variables like '%general%';
+------------------+---------------------------+
| Variable_name | Value |
+------------------+---------------------------+
| general_log | ON |
| general_log_file | /r2/mysqldata/general.log |
+------------------+---------------------------+
会话B
[root@localhost-m(252) /data5]# mysqldump --single-transaction --master-data=2 -uroot -piforgot --set-gtid-purged=OFF --databases baktest >/data5/backup_sm.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost-m(252) /data5]#
会话A
mysql gcdb@localhost:mytest> set global general_log=0;
Query OK, 0 rows affected
Time: 0.002s
会话C
[root@localhost-m(252) /data5]# tail -100 /r2/mysqldata/general.log
/usr/local/mysql/bin/mysqld, Version: 5.7.18-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306 Unix socket: /r2/mysqldata/mysql.sock
Time Id Command Argument
2018-02-08T22:09:22.663104+08:00 4602 Connect gcdb@192.168.48.168 on using TCP/IP
2018-02-08T22:09:22.663267+08:00 4602 Query select @@version_comment limit 1
2018-02-08T22:09:22.663549+08:00 4602 Query show global status where Variable_name in ("Com_select","Com_insert","Com_update","Com_delete","Innodb_buffer_pool_read_requests","Innodb_buffer_pool_reads","Innodb_rows_inserted","Innodb_rows_updated","Innodb_rows_deleted","Innodb_rows_read","Threads_running","Threads_connected","Threads_cached","Threads_created","Bytes_received","Bytes_sent","Innodb_buffer_pool_pages_data","Innodb_buffer_pool_pages_free","Innodb_buffer_pool_pages_dirty","Innodb_buffer_pool_pages_flushed","Innodb_data_reads","Innodb_data_writes","Innodb_data_read","Innodb_data_written","Innodb_os_log_fsyncs","Innodb_os_log_written")
2018-02-08T22:09:22.665220+08:00 4602 Quit
2018-02-08T22:09:26.083671+08:00 4603 Connect root@localhost on using Socket --回话C从这里开始连接备份
2018-02-08T22:09:26.083810+08:00 4603 Query /*!40100 SET @@SQL_MODE='' */
2018-02-08T22:09:26.083892+08:00 4603 Query /*!40103 SET TIME_ZONE='+00:00' */
2018-02-08T22:09:26.084000+08:00 4603 Query FLUSH /*!40101 LOCAL */ TABLES
2018-02-08T22:09:26.084653+08:00 4603 Query FLUSH TABLES WITH READ LOCK
2018-02-08T22:09:26.084757+08:00 4603 Query SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2018-02-08T22:09:26.084829+08:00 4603 Query START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2018-02-08T22:09:26.084910+08:00 4603 Query SHOW MASTER STATUS
2018-02-08T22:09:26.084999+08:00 4603 Query UNLOCK TABLES
2018-02-08T22:09:26.085167+08:00 4603 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 ('baktest'))) GROUP BY LOGFILE_GROUP_NAME, FILE_NAME, ENGINE, TOTAL_EXTENTS, INITIAL_SIZE ORDER BY LOGFILE_GROUP_NAME
2018-02-08T22:09:26.106530+08:00 4603 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 ('baktest')) ORDER BY TABLESPACE_NAME, LOGFILE_GROUP_NAME
2018-02-08T22:09:26.118618+08:00 4603 Query SHOW VARIABLES LIKE 'ndbinfo\_version'
2018-02-08T22:09:26.120755+08:00 4603 Init DB baktest
2018-02-08T22:09:26.120853+08:00 4603 Query SHOW CREATE DATABASE IF NOT EXISTS `baktest`
2018-02-08T22:09:26.120951+08:00 4603 Query SAVEPOINT sp
2018-02-08T22:09:26.121012+08:00 4603 Query show tables
2018-02-08T22:09:26.121207+08:00 4603 Query show table status like 'bk'
2018-02-08T22:09:26.121480+08:00 4603 Query SET SQL_QUOTE_SHOW_CREATE=1
2018-02-08T22:09:26.121580+08:00 4603 Query SET SESSION character_set_results = 'binary'
2018-02-08T22:09:26.121674+08:00 4603 Query show create table `bk`
2018-02-08T22:09:26.121790+08:00 4603 Query SET SESSION character_set_results = 'utf8'
2018-02-08T22:09:26.121868+08:00 4603 Query show fields from `bk`
2018-02-08T22:09:26.122291+08:00 4603 Query show fields from `bk`
2018-02-08T22:09:26.122687+08:00 4603 Query SELECT /*!40001 SQL_NO_CACHE */ * FROM `bk`
2018-02-08T22:09:26.122915+08:00 4603 Query SET SESSION character_set_results = 'binary'
2018-02-08T22:09:26.123011+08:00 4603 Query use `baktest`
2018-02-08T22:09:26.123071+08:00 4603 Query select @@collation_database
2018-02-08T22:09:26.123180+08:00 4603 Query SHOW TRIGGERS LIKE 'bk'
2018-02-08T22:09:26.123572+08:00 4603 Query SET SESSION character_set_results = 'utf8'
2018-02-08T22:09:26.123671+08:00 4603 Query ROLLBACK TO SAVEPOINT sp
2018-02-08T22:09:26.123771+08:00 4603 Query RELEASE SAVEPOINT sp
2018-02-08T22:09:26.124825+08:00 4603 Quit
2018-02-08T22:09:30.707148+08:00 1883 Query set global general_log=0 --回话A关闭general_log
[root@localhost-m(252) /data5]#
- 3.备份的流程如下：
1.连接数据库（Connect root@localhost on using Socket）
2.设置sql_mode、time_zone
3.关闭所有打开的表并清空缓存数据（FLUSH /*!40101 LOCAL */ TABLES）
4.执行全局读锁（FLUSH TABLES WITH READ LOCK）
5.更改当前会话隔离级别为（SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ）
6.开始事务并生成一致性快照（START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */）
7.记录gtid_mode、binlog位置等信息（SHOW MASTER STATUS）
8.释放全局锁（UNLOCK TABLES）
9.设置一个savepoint
10.查看baktest库下面有没有分区表
11.查询baktest库下面有没有表空间
12.查询数据库NDB版本
13.获取库信息（SHOW CREATE DATABASE IF NOT EXISTS `baktest`）
14.设置快照点 (SAVEPOINT sp)
15.查询库下面有哪些表（show tables）
16.获取bk表状态信息
17.启用标识符（"`" ）重音符
18.设置返回字符集为二进制
19.获取bk表结构
20.设置返回字符集为utf8
21.获取列的信息 （show fields from `bk`）
22.获取表所有数据 (SELECT /*!40001 SQL_NO_CACHE */ * FROM `bk`)
23.设置返回字符集为二进制
24.进入baktest库
25.查询校验规则
26.获取表的触发器(SHOW TRIGGERS LIKE 'bk')
27.设置返回字符集为utf8
28.回滚到savepoint sp（ROLLBACK TO SAVEPOINT sp）
29.释放savepoint
30.退出
4.1.4 mysqldump备份恢复总结
- 1、mysqldump是通过
select * from table_name来获取表的数据进而完成备份。 - 2、
START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */必须放到FLUSH TABLES WITH READ LOCK和UNLOCK TABLES之间。- 放到
之前会造成START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */和FLUSH TABLES WITH READ LOCK之间执行的DML语句丢失， - 放到之后，会造成
从库重复插入数据。
- 放到
- 3、mysqldump只适合放到业务低峰期做，如果备份的过程中数据操作很频繁，会造成Undo表空间越来越大。
- 4、
--single-transaction开启事务一致性快照，开始savepoint，每次备份完执行rollback to savepoint为了防止表的DDL阻塞，而调整事务隔离界别RR为了保障会话的可重复读，从而达到备份数据的一致性。
4.1.5 mysqldump与锁
1.--lock-tables 会在整个导出过程 lock read local 所有的表。该锁不会阻止其它session读和插入，但是显然阻塞了update。
2.--lock-all-tables 它请求发起一个全局的读锁，会阻止对所有表的写入操作(insert,update,delete)，以此来确保数据的一致性。备份完成后，该会话断开，会自动解锁。
3.--single-transaction 单独使用，不会有任何锁。但是测试表明: 它也需要对备份的表持有 metadata lock 的共享锁。
4.--single-transaction 和 --master-data 结合使用时，也是在开始时，会短暂的请求一个全局的读锁，会阻止对所有表的写入操作。
4.1.6 mysqldump详细参数
--all-databases , -A
导出全部数据库。
mysqldump -uroot -p --all-databases
--all-tablespaces , -Y
导出全部表空间。
mysqldump -uroot -p --all-databases --all-tablespaces
--no-tablespaces , -y
不导出任何表空间信息。
mysqldump -uroot -p --all-databases --no-tablespaces
--add-drop-database
每个数据库创建之前添加drop数据库语句。
mysqldump -uroot -p --all-databases --add-drop-database
--add-drop-table
每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)
mysqldump -uroot -p --all-databases (默认添加drop语句)
mysqldump -uroot -p --all-databases –skip-add-drop-table (取消drop语句)
--add-locks
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(默认为打开状态，使用--skip-add-locks取消选项)
mysqldump -uroot -p --all-databases (默认添加LOCK语句)
mysqldump -uroot -p --all-databases –skip-add-locks (取消LOCK语句)
--allow-keywords
允许创建是关键词的列名字。这由表名前缀于每个列名做到。
mysqldump -uroot -p --all-databases --allow-keywords
--apply-slave-statements
在'CHANGE MASTER'前添加'STOP SLAVE'，并且在导出的最后添加'START SLAVE'。
mysqldump -uroot -p --all-databases --apply-slave-statements
--character-sets-dir
字符集文件的目录
mysqldump -uroot -p --all-databases --character-sets-dir=/usr/local/mysql/share/mysql/charsets
--comments
附加注释信息。默认为打开，可以用--skip-comments取消
mysqldump -uroot -p --all-databases (默认记录注释)
mysqldump -uroot -p --all-databases --skip-comments (取消注释)
--compatible
导出的数据将和其它数据库或旧版本的MySQL 相兼容。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options等，
要使用几个值，用逗号将它们隔开。它并不保证能完全兼容，而是尽量兼容。
mysqldump -uroot -p --all-databases --compatible=ansi
--compact
导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments --skip-disable-keys
mysqldump -uroot -p --all-databases --compact
--complete-insert, -c
使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
mysqldump -uroot -p --all-databases --complete-insert
--compress, -C
在客户端和服务器之间启用压缩传递所有信息
mysqldump -uroot -p --all-databases --compress
--create-options, -a
在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)
mysqldump -uroot -p --all-databases
--databases, -B
导出几个数据库。参数后面所有名字参量都被看作数据库名。
mysqldump -uroot -p --databases test mysql
--debug
输出debug信息，用于调试。默认值为：d:t,/tmp/mysqldump.trace
mysqldump -uroot -p --all-databases --debug
mysqldump -uroot -p --all-databases --debug=” d:t,/tmp/debug.trace”
--debug-check
检查内存和打开文件使用说明并退出。
mysqldump -uroot -p --all-databases --debug-check
--debug-info
输出调试信息并退出
mysqldump -uroot -p --all-databases --debug-info
--default-character-set
设置默认字符集，默认值为utf8
mysqldump -uroot -p --all-databases --default-character-set=utf8
--delayed-insert
采用延时插入方式（INSERT DELAYED）导出数据
mysqldump -uroot -p --all-databases --delayed-insert
--delete-master-logs
master备份后删除日志. 这个参数将自动激活--master-data。
mysqldump -uroot -p --all-databases --delete-master-logs
--disable-keys
对于每个表，用/*!40000 ALTER TABLE tbl_name DISABLE KEYS */;和/*!40000 ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地导入dump出来的文件，因为它是在插入所有行后创建索引的。该选项只适合MyISAM表，默认为打开状态。
mysqldump -uroot -p --all-databases
--dump-slave
该选项将主的binlog位置和文件名追加到导出数据的文件中(show slave status)。设置为1时，将会以CHANGE MASTER命令输出到数据文件；设置为2时，会在change前加上注释。该选项将会打开--lock-all-tables，除非--single-transaction被指定。该选项会自动关闭--lock-tables选项。默认值为0。
mysqldump -uroot -p --all-databases --dump-slave=1
mysqldump -uroot -p --all-databases --dump-slave=2
--master-data
该选项将当前服务器的binlog的位置和文件名追加到输出文件中(show master status)。如果为1，将会输出CHANGE MASTER 命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。该选项自动关闭--lock-tables选项。
mysqldump -uroot -p --host=localhost --all-databases --master-data=1;
mysqldump -uroot -p --host=localhost --all-databases --master-data=2;
--events, -E
导出事件。
mysqldump -uroot -p --all-databases --events
--extended-insert, -e
使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。默认为打开状态，使用--skip-extended-insert取消选项。
mysqldump -uroot -p --all-databases
mysqldump -uroot -p --all-databases--skip-extended-insert (取消选项)
--fields-terminated-by
导出文件中忽略给定字段。与--tab选项一起使用，不能用于--databases和--all-databases选项
mysqldump -uroot -p test test --tab=”/home/mysql” --fields-terminated-by=”#”
--fields-enclosed-by
输出文件中的各个字段用给定字符包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#”
--fields-optionally-enclosed-by
输出文件中的各个字段用给定字符选择性包裹。与--tab选项一起使用，不能用于--databases和--all-databases选项
mysqldump -uroot -p test test --tab=”/home/mysql” --fields-enclosed-by=”#” --fields-optionally-enclosed-by =”#”
--fields-escaped-by
输出文件中的各个字段忽略给定字符。与--tab选项一起使用，不能用于--databases和--all-databases选项
mysqldump -uroot -p mysql user --tab=”/home/mysql” --fields-escaped-by=”#”
--flush-logs
开始导出之前刷新日志。
请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。除使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs。
mysqldump -uroot -p --all-databases --flush-logs
--flush-privileges
在导出mysql数据库之后，发出一条FLUSH PRIVILEGES 语句。为了正确恢复，该选项应该用于导出mysql数据库和依赖mysql数据库数据的任何时候。
mysqldump -uroot -p --all-databases --flush-privileges
--force
在导出过程中忽略出现的SQL错误。
mysqldump -uroot -p --all-databases --force
--help
显示帮助信息并退出。
mysqldump --help
--hex-blob
使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。
mysqldump -uroot -p --all-databases --hex-blob
--host, -h
需要导出的主机信息
mysqldump -uroot -p --host=localhost --all-databases
--ignore-table
不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2 ……
mysqldump -uroot -p --host=localhost --all-databases --ignore-table=mysql.user
--include-master-host-port
在--dump-slave产生的'CHANGE MASTER TO..'语句中增加'MASTER_HOST=<host>，MASTER_PORT=<port>'
mysqldump -uroot -p --host=localhost --all-databases --include-master-host-port
--insert-ignore
在插入行时使用INSERT IGNORE语句.
mysqldump -uroot -p --host=localhost --all-databases --insert-ignore
--lines-terminated-by
输出文件的每行用给定字符串划分。与--tab选项一起使用，不能用于--databases和--all-databases选项。
mysqldump -uroot -p --host=localhost test test --tab=”/tmp/mysql” --lines-terminated-by=”##”
--lock-all-tables, -x
提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction 和--lock-tables 选项。
mysqldump -uroot -p --host=localhost --all-databases --lock-all-tables
--lock-tables, -l
开始导出前，锁定所有表。用READ LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。
请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。
mysqldump -uroot -p --host=localhost --all-databases --lock-tables
--log-error
附加警告和错误信息到给定文件
mysqldump -uroot -p --host=localhost --all-databases --log-error=/tmp/mysqldump_error_log.err
--max_allowed_packet
服务器发送和接受的最大包长度。
mysqldump -uroot -p --host=localhost --all-databases --max_allowed_packet=10240
--net_buffer_length
TCP/IP和socket连接的缓存大小。
mysqldump -uroot -p --host=localhost --all-databases --net_buffer_length=1024
--no-autocommit
使用autocommit/commit 语句包裹表。
mysqldump -uroot -p --host=localhost --all-databases --no-autocommit
--no-create-db, -n
只导出数据，而不添加CREATE DATABASE 语句。
mysqldump -uroot -p --host=localhost --all-databases --no-create-db
--no-create-info, -t
只导出数据，而不添加CREATE TABLE 语句。
mysqldump -uroot -p --host=localhost --all-databases --no-create-info
--no-data, -d
不导出任何数据，只导出数据库表结构。
mysqldump -uroot -p --host=localhost --all-databases --no-data
--no-set-names, -N
等同于--skip-set-charset
mysqldump -uroot -p --host=localhost --all-databases --no-set-names
--opt
等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 该选项默认开启, 可以用--skip-opt禁用.
mysqldump -uroot -p --host=localhost --all-databases --opt
--order-by-primary
如果存在主键，或者第一个唯一键，对每个表的记录进行排序。在导出MyISAM表到InnoDB表时有效，但会使得导出工作花费很长时间。
mysqldump -uroot -p --host=localhost --all-databases --order-by-primary
--password, -p
连接数据库密码
--pipe(windows系统可用)
使用命名管道连接mysql
mysqldump -uroot -p --host=localhost --all-databases --pipe
--port, -P
连接数据库端口号
--protocol
使用的连接协议，包括：tcp, socket, pipe, memory.
mysqldump -uroot -p --host=localhost --all-databases --protocol=tcp
--quick, -q
不缓冲查询，直接导出到标准输出。默认为打开状态，使用--skip-quick取消该选项。
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-quick
--quote-names,-Q
使用（"`"）引起表和列名。默认为打开状态，使用--skip-quote-names取消该选项。
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-quote-names
--replace
使用REPLACE INTO 取代INSERT INTO.
mysqldump -uroot -p --host=localhost --all-databases --replace
--result-file, -r
直接输出到指定文件中。该选项应该用在使用回车换行对（\\r\\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。
mysqldump -uroot -p --host=localhost --all-databases --result-file=/tmp/mysqldump_result_file.txt
--routines, -R
导出存储过程以及自定义函数。
mysqldump -uroot -p --host=localhost --all-databases --routines
--set-charset
添加'SET NAMES default_character_set'到输出文件。默认为打开状态，使用--skip-set-charset关闭选项。
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-set-charset
--single-transaction
该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables 选项是互斥的，因为LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick 选项。
mysqldump -uroot -p --host=localhost --all-databases --single-transaction
--dump-date
将导出时间添加到输出文件中。默认为打开状态，使用--skip-dump-date关闭选项。
mysqldump -uroot -p --host=localhost --all-databases
mysqldump -uroot -p --host=localhost --all-databases --skip-dump-date
--skip-opt
禁用–opt选项.
mysqldump -uroot -p --host=localhost --all-databases --skip-opt
--socket,-S
指定连接mysql的socket文件位置，默认路径/tmp/mysql.sock
mysqldump -uroot -p --host=localhost --all-databases --socket=/tmp/mysqld.sock
--tab,-T
为每个表在给定路径创建tab分割的文本文件。注意：仅仅用于mysqldump和mysqld服务器运行在相同机器上。注意使用--tab不能指定--databases参数
mysqldump -uroot -p --host=localhost test test --tab="/home/mysql"
--tables
覆盖--databases (-B)参数，指定需要导出的表名，在后面的版本会使用table取代tables。
mysqldump -uroot -p --host=localhost --databases test --tables test
--triggers
导出触发器。该选项默认启用，用--skip-triggers禁用它。
mysqldump -uroot -p --host=localhost --all-databases --triggers
--tz-utc
在导出顶部设置时区TIME_ZONE='+00:00' ，以保证在不同时区导出的TIMESTAMP 数据或者数据被移动其他时区时的正确性。
mysqldump -uroot -p --host=localhost --all-databases --tz-utc
--user, -u
指定连接的用户名。
--verbose, --v
输出多种平台信息。
--version, -V
输出mysqldump版本信息并退出
--where, -w
只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。
mysqldump -uroot -p --host=localhost --all-databases --where=” user=’root’”
--xml, -X
导出XML格式.
mysqldump -uroot -p --host=localhost --all-databases --xml
--plugin_dir
客户端插件的目录，用于兼容不同的插件版本。
mysqldump -uroot -p --host=localhost --all-databases --plugin_dir=”/usr/local/lib/plugin”
--default_auth
客户端插件默认使用权限。
mysqldump -uroot -p --host=localhost --all-databases --default-auth=”/usr/local/lib/plugin/<PLUGIN>”
4.2. mysqlpump
4.2.1 mysqlpump简介
- 1.语法
MySQL utility for dumping data from databases to external file.
Usage: mysqlpump [OPTIONS] [--all-databases]
OR mysqlpump [OPTIONS] --databases DB1 [DB2 DB3...]
OR mysqlpump [OPTIONS] database [tables]
注意 : 在MySQL 5.7.11之前，--single-transaction选项的使用与并行性是互斥的。要使用 --single-transaction，通过设置--default-parallelism为0来禁用并行性， 而不是使用以下任何实例 --parallel-schemas,命令mysqlpump --single-transaction --default-parallelism=0
-
2.主要的特点：
并行备份数据库和数据库中的对象的，加快备份过程。- 更好的控制数据库和数据库对象
（表，存储过程，用户帐户）的备份。 - 备份用户账号作为帐户管理语句
（CREATE USER，GRANT），而不是直接插入到MySQL的系统数据库。 - 备份出来直接生成
压缩后的备份文件。 - 备份
进度指示（估计值）。 - 重新加载（还原）备份文件，
先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。 - 备份可以
排除或则指定数据库。 - 绝大部分参数和mysqldump一致
-
3.mysqlpump 架构图
1.mysqlpump架构采用
队列+线程模式。
2.mysqlpump的备份是基于表并行的，对于每张表的导出只能是单个线程的。
4.2.2 mysqlpump重要参数应用
-
1.并发相关
- 1.
--default-parallelism=#线程数，默认开 2 个线程进行并发备份 - 2.
--parallel-schemas=name哪些数据库进行并发
- 1.
-
2.压缩相关
--compress-output：默认不压缩输出，目前可以使用的压缩算法有LZ4和ZLIB
4.2.3 mysqlpump演示
- 1.对employees数据库进行备份
[root@node3 bak_mysql]# mysqlpump -ugcdb -piforgot --set-gtid-purged=OFF -B employees > /bak_mysql/employees.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 0/7 tables, 250/3707242 rows
Dump progress: 0/7 tables, 544603/3707242 rows
Dump progress: 0/7 tables, 961877/3707242 rows
Dump progress: 0/7 tables, 984377/3707242 rows
Dump progress: 0/7 tables, 995627/3707242 rows
Dump progress: 0/7 tables, 1094377/3707242 rows
Dump progress: 0/7 tables, 1109877/3707242 rows
Dump progress: 0/7 tables, 1278877/3707242 rows
Dump progress: 0/7 tables, 2047377/3707242 rows
Dump progress: 0/7 tables, 2887935/3707242 rows
Dump progress: 0/7 tables, 3679185/3707242 rows
Dump completed in 40057 milliseconds
[root@node3 bak_mysql]#
- 2.查看备份的占用的线程,--default-parallelism默认配置为2个
(gcdb@localhost) 17:08:54 [mytest]> show processlist;
+----+-------------+-----------+--------+---------+---------+--------------------------------------------------------+------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------+--------+---------+---------+--------------------------------------------------------+------------------------------------------------------------------------------------------+
| 9 | system user | | NULL | Connect | 8299671 | Reconnecting after a failed master event read | NULL |
| 10 | system user | | NULL | Connect | 8292334 | Slave has read all relay log; waiting for more updates | NULL |
| 45 | gcdb | localhost | mytest | Query | 0 | starting | show processlist |
| 61 | gcdb | localhost | NULL | Query | 0 | Opening tables | SHOW PROCEDURE STATUS WHERE db = 'mytest' |
| 62 | gcdb | localhost | NULL | Query | 0 | Sending to client | SELECT SQL_NO_CACHE `emp_no`,`title`,`from_date`,`to_date` FROM `employees`.`titles` |
| 63 | gcdb | localhost | NULL | Query | 0 | Sending to client | SELECT SQL_NO_CACHE `emp_no`,`salary`,`from_date`,`to_date` FROM `employees`.`salaries` |
+----+-------------+-----------+--------+---------+---------+--------------------------------------------------------+------------------------------------------------------------------------------------------+
6 rows in set (0.09 sec)
--同时并发两个线程ID为62和63
- 3.多队列和多线程并发备份测试
[root@node3 bak_mysql]# mysqlpump -ugcdb -piforgot --single-transaction --parallel-schemas=4:employees,dbt3 --parallel-schemas=2:mytest --set-gtid-purged=OFF -B employees dbt3 mytest > /bak_mysql/all.sql
mysqlpump: [Warning] Using a password on the command line interface can be insecure.
Dump progress: 0/6 tables, 250/8339454 rows
Dump progress: 0/61 tables, 302750/12066883 rows
Dump progress: 0/66 tables, 594749/12066905 rows
Dump progress: 0/66 tables, 1032499/12066905 rows
Dump progress: 0/66 tables, 1599352/12066905 rows
----------省略部分输出-------------
Dump progress: 0/66 tables, 12192731/12066905 rows
Dump progress: 0/66 tables, 12375981/12066905 rows
Dump progress: 0/66 tables, 12544731/12066905 rows
Dump completed in 37470 milliseconds
[root@node3 bak_mysql]#
--
-- parallel-schemas=4:employees,dbt3 表示备份employees和dbt3库使用4个线程
-- parallel-schemas=2:mytest 表示备份mytest库使用2个线程
-- -B employees dbt3 mytest 表示指定备份 employees 、dbt3、mytest 这三个库
--
(gcdb@localhost) 17:36:50 [dbt3]> show processlist;
+-----+-------------+-----------+------+---------+---------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-----+-------------+-----------+------+---------+---------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 9 | system user | | NULL | Connect | 8301327 | Reconnecting after a failed master event read | NULL |
| 10 | system user | | NULL | Connect | 8293990 | Slave has read all relay log; waiting for more updates | NULL |
| 45 | gcdb | localhost | dbt3 | Query | 1 | starting | show processlist |
| 93 | gcdb | localhost | NULL | Sleep | 11 | | NULL |
| 94 | gcdb | localhost | NULL | Sleep | 10 | | NULL |
| 95 | gcdb | localhost | NULL | Sleep | 10 | | NULL |
| 96 | gcdb | localhost | NULL | Query | 11 | Sending data | SELECT SQL_NO_CACHE `o_orderkey`,`o_custkey`,`o_orderstatus`,`o_totalprice`,`o_orderDATE`,`o_orderpr |
| 97 | gcdb | localhost | NULL | Query | 2 | Sending data | SELECT SQL_NO_CACHE `emp_no`,`title`,`from_date`,`to_date` FROM `employees`.`titles` |
| 98 | gcdb | localhost | NULL | Query | 11 | Sending data | SELECT SQL_NO_CACHE `l_orderkey`,`l_partkey`,`l_suppkey`,`l_linenumber`,`l_quantity`,`l_extendedpric |
| 99 | gcdb | localhost | NULL | Query | 5 | Sending data | SELECT SQL_NO_CACHE `emp_no`,`salary`,`from_date`,`to_date` FROM `employees`.`salaries` |
| 100 | gcdb | localhost | NULL | Sleep | 11 | | NULL |
| 101 | gcdb | localhost | NULL | Sleep | 9 | | NULL |
+-----+-------------+-----------+------+---------+---------+--------------------------------------------------------+------------------------------------------------------------------------------------------------------+
12 rows in set (0.10 sec)
(gcdb@localhost) 17:36:52 [dbt3]>
- 4.compress压缩
[root@node3 bak_mysql]# time mysqlpump -h192.168.48.168 -ugcdb -piforgot --compress-output=LZ4 --set-gtid-purged=OFF -B employees >/bak_mysql/employees.lz4
Dump completed in 54850 milliseconds
real 0m54.882s
user 0m10.670s
sys 0m13.026s
[root@node3 bak_mysql]# time mysqlpump -h192.168.48.168 -ugcdb -piforgot --compress-output=ZLIB --set-gtid-purged=OFF -B employees >/bak_mysql/employees.zlib
Dump completed in 49801 milliseconds
real 0m49.866s
user 0m32.088s
sys 0m15.821s
[root@node3 bak_mysql]#
[root@node3 bak_mysql]# ll
total 207536
-rw-r--r-- 1 root root 139632963 Feb 11 09:46 employees.lz4
-rw-r--r-- 1 root root 72878405 Feb 11 09:48 employees.zlib
[root@node3 bak_mysql]# lz4_decompress employees.lz4 employees
[root@node3 bak_mysql]# ll
total 534204
-rw-r--r-- 1 root root 334538646 Feb 11 10:04 employees
-rw-r--r-- 1 root root 139727837 Feb 11 09:58 employees.lz4
-rw-r--r-- 1 root root 72752356 Feb 11 09:56 employees.zlib
[root@node3 bak_mysql]# zlib_decompress employess.zlib employees
- 5.还原
(gcdb@localhost) 10:08:28 [mytest]> source /bak_mysql/employees
- 6.总结：
- lz4压缩快，但是压缩比相对比较低，zlib反之
- 恢复要先解压缩，再恢复
4.2.4 mysqlpump原理分析
- 会话A
(gcdb@localhost) 17:46:20 [dbt3]> set global log_output=`table`;
Query OK, 0 rows affected (0.03 sec)
(gcdb@localhost) 18:04:11 [mysql]> truncate general_log;
Query OK, 0 rows affected (0.09 sec)
(gcdb@localhost) 18:05:24 [mysql]> set global general_log=1;
Query OK, 0 rows affected (0.04 sec)
- 会话B
[root@node3 bak_mysql]# mysqlpump -ugcdb -piforgot --set-gtid-purged=OFF -B mytest > /bak_mysql/bak_mytest.sql
- 会话A
(gcdb@localhost) 17:52:29 [mysql]> set global general_log=0;
Query OK, 0 rows affected (0.11 sec)
(gcdb@localhost) 18:05:44 [mysql]> set global general_log=0;
Query OK, 0 rows affected (0.00 sec)
(gcdb@localhost) 18:17:19 [mysql]> select event_time,thread_id,command_type,argument from general_log where argument!="SHOW WARNINGS" limit 49;
+----------------------------+-----------+--------------+--------------------------------------------------------------------------------------+
| event_time | thread_id | command_type | argument |
+----------------------------+-----------+--------------+--------------------------------------------------------------------------------------+
| 2018-02-09 18:06:37.195264 | 105 | Connect | gcdb@localhost on using Socket |
| 2018-02-09 18:06:37.243112 | 105 | Query | FLUSH TABLES WITH READ LOCK | -- 线程105加了全局读锁，保证数据不会被更改
| 2018-02-09 18:06:37.286500 | 105 | Query | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | -- 线程105设置会话隔离级别为RR级别
| 2018-02-09 18:06:37.289670 | 105 | Query | START TRANSACTION WITH CONSISTENT SNAPSHOT | -- 线程105开启一个事物
| 2018-02-09 18:06:37.291053 | 106 | Connect | gcdb@localhost on using Socket |
| 2018-02-09 18:06:37.291654 | 106 | Query | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | -- 线程106设置会话隔离级别为RR级别
| 2018-02-09 18:06:37.291959 | 106 | Query | START TRANSACTION WITH CONSISTENT SNAPSHOT | -- 线程106开启一个事物
| 2018-02-09 18:06:37.292446 | 107 | Connect | gcdb@localhost on using Socket |
| 2018-02-09 18:06:37.293239 | 107 | Query | SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ | -- 线程107设置会话隔离级别为RR级别
| 2018-02-09 18:06:37.294565 | 107 | Query | START TRANSACTION WITH CONSISTENT SNAPSHOT | -- 线程107开启一个事物
| 2018-02-09 18:06:37.295263 | 105 | Query | UNLOCK TABLES | -- 解锁
| 2018-02-09 18:06:37.295507 | 107 | Query | SET SQL_QUOTE_SHOW_CREATE= 1 |
| 2018-02-09 18:06:37.296072 | 107 | Query | SET TIME_ZONE='+00:00' |
| 2018-02-09 10:06:37.296962 | 107 | Query | SELECT @@global.gtid_mode |
| 2018-02-09 10:06:37.298902 | 107 | Query | SELECT @@GLOBAL.GTID_EXECUTED |
| 2018-02-09 10:06:37.321746 | 107 | Query | SHOW DATABASES |
| 2018-02-09 18:06:37.331762 | 106 | Query | SET SQL_QUOTE_SHOW_CREATE= 1 |
| 2018-02-09 18:06:37.332070 | 106 | Query | SET TIME_ZONE='+00:00' |
| 2018-02-09 10:06:37.417200 | 107 | Query | SHOW CREATE DATABASE IF NOT EXISTS `information_schema` |
| 2018-02-09 10:06:37.451626 | 107 | Query | SHOW TABLE STATUS FROM `information_schema` |
| 2018-02-09 10:06:37.496588 | 107 | Query | SHOW COLUMNS IN `CHARACTER_SETS` FROM `information_schema` |
| 2018-02-09 18:06:37.332070 | 106 | Query | SET TIME_ZONE='+00:00'
------------------省略部分输出---------------------
| 2018-02-09 10:06:37.822234 | 107 | Query | SHOW CREATE DATABASE IF NOT EXISTS `baktest` |
| 2018-02-09 10:06:37.824470 | 107 | Query | SHOW TABLE STATUS FROM `baktest` |
| 2018-02-09 10:06:37.846610 | 107 | Query | SHOW COLUMNS IN `bk` FROM `baktest` |
| 2018-02-09 10:06:37.851510 | 107 | Query | SHOW CREATE TABLE `baktest`.`bk` |
| 2018-02-09 18:06:37.872647 | 105 | Query | SET SQL_QUOTE_SHOW_CREATE= 1 |
| 2018-02-09 10:06:37.873512 | 107 | Query | SHOW TRIGGERS FROM `baktest` LIKE 'bk' |
| 2018-02-09 10:06:37.881774 | 107 | Query | SHOW FUNCTION STATUS WHERE db = 'baktest' |
| 2018-02-09 18:06:37.882369 | 105 | Query | SET TIME_ZONE='+00:00' |
| 2018-02-09 10:06:37.882740 | 105 | Query | SELECT `COLUMN_NAME`, `EXTRA` FROM `INFORMATION_SCHEMA`.`COLUMNS`WHERE TABLE_SCHEMA ='baktest' AND TABLE_NAME ='bk' |
| 2018-02-09 10:06:37.887489 | 105 | Query | SELECT SQL_NO_CACHE `id`,`num` FROM `baktest`.`bk` |
| 2018-02-09 10:06:37.919537 | 107 | Query | SHOW PROCEDURE STATUS WHERE db = 'baktest' |
| 2018-02-09 10:06:37.928912 | 107 | Query | SHOW EVENTS FROM `baktest`
------------------省略部分输出---------------------
| 2018-02-09 10:07:29.959885 | 107 | Query | SELECT CONCAT(QUOTE(user),'@',QUOTE(host)) FROM mysql.user |
| 2018-02-09 10:07:29.961826 | 107 | Query | SHOW CREATE USER 'gcdb'@'%' |
| 2018-02-09 10:07:29.961997 | 107 | Query | SHOW GRANTS FOR 'gcdb'@'%' |
| 2018-02-09 10:07:29.962212 | 107 | Query | SHOW CREATE USER 'monitor'@'%' |
| 2018-02-09 10:07:29.962360 | 107 | Query | SHOW GRANTS FOR 'monitor'@'%' |
| 2018-02-09 10:07:29.962578 | 107 | Query | SHOW CREATE USER 'repl'@'%' |
| 2018-02-09 10:07:29.962735 | 107 | Query | SHOW GRANTS FOR 'repl'@'%' |
| 2018-02-09 10:07:29.962883 | 107 | Query | SHOW CREATE USER 'dbbackup'@'localhost' |
| 2018-02-09 10:07:29.963039 | 107 | Query | SHOW GRANTS FOR 'dbbackup'@'localhost' |
| 2018-02-09 10:07:29.963242 | 107 | Query | SHOW CREATE USER 'mysql.sys'@'localhost' |
| 2018-02-09 10:07:29.963388 | 107 | Query | SHOW GRANTS FOR 'mysql.sys'@'localhost' |
| 2018-02-09 10:07:29.963610 | 107 | Query | SHOW CREATE USER 'operator'@'localhost' |
| 2018-02-09 10:07:29.963778 | 107 | Query | SHOW GRANTS FOR 'operator'@'localhost' |
| 2018-02-09 10:07:29.963960 | 107 | Query | SHOW CREATE USER 'root'@'localhost' |
| 2018-02-09 10:07:29.964222 | 107 | Query | SHOW GRANTS FOR 'root'@'localhost' |
| 2018-02-09 10:07:29.968148 | 107 | Quit | |
| 2018-02-09 10:07:29.969577 | 105 | Quit | |
| 2018-02-09 10:07:29.969806 | 106 | Quit | |
+----------------------------+-----------+--------------+--------------------------------------------------------------------------------------+
1652 rows in set (0.01 sec)
- 导出流程
- 1.线程
105进行FLUSH TABLES WITH READ LOCK，对表加一个全局读锁。 - 2.线程
105、106、107由于之前加了全局读锁,然后分别开启一个事物（RR隔离级别）并生成一致性快照去备份数据，所以这三个线程备份出的数据是一致性的 - 3.线程
105解锁UNLOCK TABLE。 - 4.查询库表各种状态，然后
select导出 - 5.根据配置参数是否导出权限等
- 1.线程
4.2.5 mysqlpump详细参数
1:--add-drop-database：在建立库之前先执行删库操作。
DROP DATABASE IF EXISTS `...`;
2:--add-drop-table：在建表之前先执行删表操作。
DROP TABLE IF EXISTS `...`.`...`;
3:`--add-drop-user`：在CREATE USER语句之前增加DROP USER，注意：这个参数需要和`--users`一起使用，否者不生效。
DROP USER 'backup'@'192.168.24.%';
4:--add-locks：备份表时，使用LOCK TABLES和UNLOCK TABLES。注意：这个参数不支持并行备份，需要关闭并行备份功能：`--default-parallelism=0`
LOCK TABLES `...`.`...` WRITE;
...
UNLOCK TABLES;
5：--all-databases：备份所有库，-A。
6：--bind-address：指定通过哪个网络接口来连接Mysql服务器（一台服务器可能有多个IP），防止同一个网卡出去影响业务。
7：--complete-insert：dump出包含所有列的完整insert语句。
8：--compress： 压缩客户端和服务器传输的所有的数据，-C。
9：`--compress-output`：默认不压缩输出，目前可以使用的压缩算法有LZ4和ZLIB。
shell> mysqlpump --compress-output=LZ4 > dump.lz4
shell> lz4_decompress dump.lz4 dump.txt
shell> mysqlpump --compress-output=ZLIB > dump.zlib
shell> zlib_decompress dump.zlib dump.txt
10：--databases：手动指定要备份的库，支持多个数据库，用空格分隔，-B。
11：--default-character-set：指定备份的字符集。
12：`--default-parallelism`：指定并行线程数，默认是2，如果设置成0，表示不使用并行备份。注意：每个线程的备份步骤是：先create table但不建立二级索引（主键会在create table时候建立），再写入数据，最后建立二级索引。
13：`--defer-table-indexes`：延迟创建索引，直到所有数据都加载完之后，再创建索引，默认开启。若关闭则会和mysqldump一样：先创建一个表和所有索引，再导入数据，因为在加载还原数据的时候要维护二级索引的开销，导致效率比较低。关闭使用参数：--skip--defer-table-indexes。
14：--events：备份数据库的事件，默认开启，关闭使用--skip-events参数。
15：`--exclude-databases`：备份排除该参数指定的数据库，多个用逗号分隔。类似的还有`--exclude-events、--exclude-routines、--exclude-tables、--exclude-triggers、--exclude-users`。
mysqlpump --exclude-databases=mysql,sys #备份过滤mysql和sys数据库
mysqlpump --exclude-tables=rr,tt #备份过滤所有数据库中rr、tt表
mysqlpump -B test --exclude-tables=tmp_ifulltext,tt #备份过滤test库中的rr、tt表
注意：要是只备份数据库的账号，需要添加参数--users，并且需要过滤掉所有的数据库，如：
mysqlpump --users --exclude-databases=sys,mysql,db1,db2 --exclude-users=dba,backup #备份除dba和backup的所有账号。
16：`--include-databases`：指定备份数据库，多个用逗号分隔，类似的还有`--include-events、--include-routines、--include-tables、--include-triggers、--include-users`，使用分法跟15一样。
17：--insert-ignore：备份用insert ignore语句代替insert语句。
18：--log-error-file：备份出现的warnings和erros信息输出到一个指定的文件。
19：--max-allowed-packet：备份时用于client/server直接通信的最大buffer包的大小。
20：--net-buffer-length：备份时用于client/server通信的初始buffer大小，当创建多行插入语句的时候，mysqlpump 创建行到N个字节长。
21：--no-create-db：备份不写CREATE DATABASE语句。要是备份多个库，需要使用参数-B，而使用-B的时候会出现create database语句，该参数可以屏蔽create database 语句。
22：--no-create-info：备份不写建表语句，即不备份表结构，只备份数据，-t。
23：--hex-blob： 备份binary字段的时候使用十六进制计数法，受影响的字段类型有BINARY、VARBINARY、BLOB、BIT。
24：--host ：备份指定的数据库地址，-h。
25：`--parallel-schemas=[N:]db_list`：指定并行备份的库，多个库用逗号分隔，如果指定了N，将使用N个线程的队列，如果N不指定，将由 `--default-parallelism`才确认N的值，可以设置多个--parallel-schemas。
mysqlpump --parallel-schemas=4:aa,bb --parallel-schemas=3:cc #4个线程备份aa和bb，3个线程备份cc。通过show processlist 可以看到有7个线程。
mysqlpump --parallel-schemas=ddd,eee --parallel-schemas=fff #默认2个线程，即2个线程备份ddd和eee，2个线程备份fff
如果硬盘IO允许的话，多开几个线程和数据库进行并行备份，IO不足反之。
26：--password：备份需要的密码。
27：--port ：备份数据库的端口。
28：--protocol={TCP|SOCKET|PIPE|MEMORY}：指定连接服务器的协议。
29：--replace：备份出来replace into语句。
30：--routines：备份出来包含存储过程和函数，默认开启，需要对 mysql.proc表有查看权限。生成的文件中会包含CREATE PROCEDURE 和 CREATE FUNCTION语句以用于恢复，关闭则需要用--skip-routines参数。
31：--triggers：备份出来包含触发器，默认开启，使用--skip-triggers来关闭。
31：--set-charset：备份文件里写SET NAMES default_character_set 到输出，此参默认开启。 -- skip-set-charset禁用此参数，不会在备份文件里面写出set names...
32：--single-transaction：该参数在事务隔离级别设置成Repeatable Read，并在dump之前发送start transaction 语句给服务端。这在使用innodb时很有用，因为在发出start transaction时，保证了在不阻塞任何应用下的一致性状态。对myisam和memory等非事务表，还是会改变状态的，当使用此参的时候要确保没有其他连接在使用ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE等语句，否则会出现不正确的内容或则失败。--add-locks和此参互斥，在mysql5.7.11之前，--default-parallelism大于1的时候和此参也互斥，必须使用--default-parallelism=0。5.7.11之后解决了--single-transaction和--default-parallelism的互斥问题。
33：`--skip-definer`：忽略那些创建视图和存储过程用到的 DEFINER 和 SQL SECURITY 语句，恢复的时候，会使用默认值，否则会在还原的时候看到没有DEFINER定义时的账号而报错。
34：`--skip-dump-rows`：只备份表结构，不备份数据，-d。注意：mysqldump支持--no-data，mysqlpump不支持--no-data
35：--socket：对于连接到localhost，Unix使用套接字文件，在Windows上是命名管道的名称使用，-S。
36：--ssl：--ssl参数将要被去除，用--ssl-mode取代。关于ssl相关的备份，请看官方文档。
37：--tz-utc：备份时会在备份文件的最前几行添加SET TIME_ZONE='+00:00'。注意：如果还原的服务器不在同一个时区并且还原表中的列有timestamp字段，会导致还原出来的结果不一致。默认开启该参数，用 --skip-tz-utc来关闭参数。
38：--user：备份时候的用户名，-u。
39：`--users`：备份数据库用户，备份的形式是CREATE USER...，GRANT...，只备份数据库账号可以通过如下命令：
mysqlpump --exclude-databases=% --users #过滤掉所有数据库
40：`--watch-progress`：定期显示进度的完成，包括总数表、行和其他对象。该参数默认开启，用--skip-watch-progress来关闭。
4.2.6 mysqldump和mysqlpump对比
| 备份工具/功能 | mysqldump | mysqlpump |
|---|---|---|
| 并行导出 | 否 | 基于表的多队列、多线程并行备份 |
| 导出CHANGE MASTER语句 | 支持 | 否 |
| 导出压缩 | 导出再用其他工具压缩 | 支持直接压缩导出导入，压缩格式lz4和zilb |
| 导出对象 | 表及表结构等，视图， 触发器 | 表及表结构等，视图，触发器，(存储过程，存储函数，事件) |
| 导出用户账号 | 直接插入mysql库 | 导出为管理帐号语句(CREATE USER，GRANT) |
| 导出显示进度 | 否 | 支持 |
| 导入动作 | 建表语句包含建立索引，插入数据同时更新索引 | 先建表后插入数据再建立索引 |
| 导出排除或则指定数据库 | 否 | 支持 |
4.3. Percona XtraBackup
4.3.1 Percona XtraBackup 简介
Percona XtraBackup（简称PXB）是Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持 MySQl（Oracle）、Percona Server 和 MariaDB，并且全部开源，真可谓是业界良心。
- 特点：
- (1)备份过程快速、可靠；
- (2)备份过程不会打断正在执行的事务；
- (3)能够基于压缩等功能节约磁盘空间和流量；
- (4)自动实现备份检验；
- (5)还原速度快；
[root@node3 software]# rpm -qpl percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm |grep '/usr/bin'
warning: percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
/usr/bin/innobackupex
/usr/bin/xbcloud --用于向云上面进行下载和上传xbstream存档的全部或部分内容。
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
[root@node3 software]#
- 工具集说明
- xtrabackup：是用于热备份innodb, xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；
- innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。
- xbcrypt：用于加密和解密备份文件
- xbstream ：类似于tar，允许从xbstream格式流式解压文件
4.3.2 Percona XtraBackup 权限
-
需要的权限：
-
系统权限：
- 连接到服务器是为了执行备份，需要在
datadir上有read，write和execute权限。
- 连接到服务器是为了执行备份，需要在
-
数据库中需要以下权限：
RELOAD和LOCK TABLES在开始复制文件之前，有RELOAD和LOCK TABLES权限（除非指定了--no-lock选项）才能使FLUSH TABLES WITH READ LOCK和FLUSH ENGINE LOGS；在LOCK TABLES FOR BACKUP和LOCK BINLOG FOR BACKUP需要备份锁时，就需要这些权限，REPLICATION CLIENT为了获得二进制日志位置，CREATE TABLESPACE权限为了导入表，用户表级别的恢复PROCESS进程以运行SHOW ENGINE INNODB STATUS（这是必需的），并且可以选择查看服务器上正在运行的所有线程，SUPER为了在复制环境中启动/停止slave线程，使用XtraDB改变页跟踪增量备份和跟进FLUSH TABLES WITH READ锁操作，CREATE权限为了创建PERCONA_SCHEMA.xtrabackup_history数据库和表，INSERT为了将历史记录添加到PERCONA_SCHEMA.xtrabackup_history表中，SELECT权限，以便使用innobackupex --incremental-history-name和innobackupex --incremental-history-uuid查找PERCONA_SCHEMA.xtrabackup_history表中innodb_to_lsn的值
-
(root@localhost) 16:54:48 [(none)]> CREATE USER 'xtruser'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) 16:55:56 [(none)]> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO 'xtruser'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)
(root@localhost) 16:55:59 [(none)]> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) 16:56:20 [(none)]> show grants for 'xtruser'@'localhost';
+----------------------------------------------------------------------------------------+
| Grants for xtruser@localhost |
+----------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtruser'@'localhost' |
+----------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
(root@localhost) 16:56:23 [(none)]>
4.3.3 Percona XtraBackup 演示
4.3.3.1 创建全备和恢复
- 全备
[root@node3 bak_mysql]# innobackupex --user=xtruser --password=123456 /bak_mysql/
180211 17:22:43 innobackupex: Starting the backup operation
IMPORTANT: Please check that the backup run completes successfully.
At the end of a successful backup run innobackupex
prints "completed OK!".
180211 17:22:44 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/r2/mysqldata/mysql.sock' as 'xtruser' (using password: YES).
180211 17:22:44 version_check Connected to MySQL server
180211 17:22:44 version_check Executing a version check against the server...
180211 17:22:44 version_check Done.
180211 17:22:44 Connecting to MySQL server host: localhost, user: xtruser, password: set, port: 3306, socket: /r2/mysqldata/mysql.sock
Using server version 5.7.18-log
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /r2/mysqldata
xtrabackup: open files limit requested 1024000, set to 1024000
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 2147483648
xtrabackup: using O_DIRECT
InnoDB: Number of pools: 1
180211 17:22:44 >> log scanned up to (4150097469)
xtrabackup: Generating a list of tablespaces
InnoDB: Allocated tablespace ID 2 for mysql/plugin, old maximum was 0
180211 17:22:44 [01] Copying ./ibdata1 to /bak_mysql/2018-02-11_17-22-43/ibdata1
180211 17:22:45 >> log scanned up to (4150097469)
180211 17:22:46 >> log scanned up to (4150097469)
180211 17:22:47 >> log scanned up to (4150097469)
180211 17:22:48 >> log scanned up to (4150097469)
180211 17:22:49 >> log scanned up to (4150097469)
180211 17:22:50 [01] ...done
180211 17:22:50 [01] Copying ./mysql/plugin.ibd to /bak_mysql/2018-02-11_17-22-43/mysql/plugin.ibd
---------------省略-------------------------
1180211 17:22:53 [01] ...done
180211 17:22:53 Finished backing up non-InnoDB tables and files
180211 17:22:53 [00] Writing /bak_mysql/2018-02-11_17-22-43/xtrabackup_binlog_info
180211 17:22:53 [00] ...done
180211 17:22:53 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '4150097460'
xtrabackup: Stopping log copying thread.
.180211 17:22:53 >> log scanned up to (4150097469)
180211 17:22:53 Executing UNLOCK TABLES
180211 17:22:53 All tables unlocked
180211 17:22:53 [00] Copying ib_buffer_pool to /bak_mysql/2018-02-11_17-22-43/ib_buffer_pool
180211 17:22:53 [00] ...done
180211 17:22:53 Backup created in directory '/bak_mysql/2018-02-11_17-22-43/'
MySQL binlog position: filename 'binlog.000002', position '146328959', GTID of the last change '803de1b2-be09-11e7-8cba-000c29c9f498:1-1503,
9dc847d8-bf72-11e7-9ec4-000c2998e4f1:1-28'
180211 17:22:53 [00] Writing /bak_mysql/2018-02-11_17-22-43/backup-my.cnf
180211 17:22:53 [00] ...done
180211 17:22:53 [00] Writing /bak_mysql/2018-02-11_17-22-43/xtrabackup_info
180211 17:22:53 [00] ...done
xtrabackup: Transaction log of lsn (4150097460) to (4150097469) was copied.
180211 17:22:53 completed OK!
[root@node3 mysqldata]# cd /bak_mysql/2018-02-11_17-02-26/
[root@node3 2018-02-11_17-02-26]# ls
backup-my.cnf employees ibdata1 mytest sys xtrabackup_binlog_info xtrabackup_info
baktest ib_buffer_pool mysql performance_schema t1 xtrabackup_checkpoints xtrabackup_logfile
- 各文件说明：
- (1)xtrabackup_checkpoints --备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN(日志序列号)范围信息；
- (2)xtrabackup_binlog_info --mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。
- (3)xtrabackup_binlog_pos_innodb --二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。
- (4)xtrabackup_binary --备份中用到的xtrabackup的可执行文件；
- (5)backup-my.cnf --备份命令用到的配置选项信息；
在使用innobackupex进行备份时，还可以使用
--no-timestamp选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。
- 2.恢复
[root@node3 bak_mysql]# innobackupex --apply-log /bak_mysql/2018-02-11_17-22-43 --prepare：通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。
---------------省略-------------------------
InnoDB: 5.7.13 started; log sequence number 4150097941
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4150097960
180211 17:34:11 completed OK!
[root@node3 bak_mysql]# systemctl stop mysql --模拟数据库损坏
[root@node3 bak_mysql]# mv /r2/mysqldata/ /r2/mysqldata_bak --模拟数据库损坏
[root@node3 bak_mysql]# mkdir -p /r2/mysqldata_bak/ --模拟数据库损坏
[root@node3 bak_mysql]# innobackupex --copy-back /bak_mysql/2018-02-11_17-22-43 --执行恢复操作，其通过复制所有数据相关的文件至mysql服务器DATADIR目录中来执行恢复过程
180211 17:36:10 innobackupex: Starting the copy-back operation
IMPORTANT: Please check that the copy-back run completes successfully.
At the end of a successful copy-back run innobackupex
prints "completed OK!".
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
180211 17:36:10 [01] Copying ib_logfile0 to /r2/mysqldata/ib_logfile0
180211 17:36:16 [01] ...done
180211 17:36:16 [01] Copying ib_logfile1 to /r2/mysqldata/ib_logfile1
180211 17:36:22 [01] ...done
180211 17:36:22 [01] Copying ibdata1 to /r2/mysqldata/ibdata1
180211 17:36:25 [01] ...done
---------------省略-------------------------
180211 17:36:29 [01] Copying ./ibtmp1 to /r2/mysqldata/ibtmp1
180211 17:36:29 [01] ...done
180211 17:36:29 [01] Creating directory /r2/mysqldata/2018-02-11_17-35-57
180211 17:36:29 [01] ...done.180211 17:36:29 completed OK!
[root@node3 bak_mysql]# chown -R mysql:mysql /r2/mysqldata --当数据恢复至DATADIR目录以后，还需要确保所有数据文件的属主和属组均为正确的用户
[root@node3 bak_mysql]# systemctl start mysql --启动mysql
[root@node3 bak_mysql]# mysql -uroot -p123456
(root@localhost) 17:40:23 [(none)]> SET PASSWORD FOR 'root'@'localhost' = 'iforgot';
Query OK, 0 rows affected (0.00 sec)
(root@localhost) 17:41:30 [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
-
全备恢复
- 1、prepare
- prepare，这个过程类似innodb的
crash recovery，也可以理解为是“apply”的过程。 - 全备prepare的命令：
innobackupex --apply-log $basedir。 - 在--apply-log的时候，可以指定
--use-memory，增大其值加快速度，若不指定，默认值为100MB。
- prepare，这个过程类似innodb的
- 2、恢复过程
- 全备和增备都是这一个恢复命令，只需要加上--copy-back参数即可
innobackupex --copy-back $basedir。 - 将
$basedir的数据恢复到datadir，datadir无需指定，将会读取my.cnf获得。 - 默认是需要
datadir内为空的（或者没有创建），如果要强制写，则需要加参数：--force-non-empty-directories
- 全备和增备都是这一个恢复命令，只需要加上--copy-back参数即可
- 1、prepare
4.3.3.2 增量备份和恢复
-
两种增量备份方法
- 第一种，总是针对basedir做增量，这个方式恢复起来就特别简单了，只需要将最后一次的增量备份合并到全量备份里，就可以恢复了。
- 第二种，总是针对上一次的增量，做增量备份。这个方式的恢复，就要逐一合并了。
-
下面模拟第二种，对空间有要求。
-
1.创建全备
[root@node3 bak_mysql]# innobackupex --user=xtruser --password=123456 --no-timestamp /bak_mysql/full_xtrbak
180212 08:55:12 innobackupex: Starting the backup operation
---------------省略-------------------------
80212 08:55:20 [00] Writing /bak_mysql/full_xtrbak/xtrabackup_binlog_info
180212 08:55:20 [00] ...done
180212 08:55:20 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '4150100711'
xtrabackup: Stopping log copying thread.
.180212 08:55:20 >> log scanned up to (4150100720)
180212 08:55:20 Executing UNLOCK TABLES
180212 08:55:20 All tables unlocked
180212 08:55:20 [00] Copying ib_buffer_pool to /bak_mysql/full_xtrbak/ib_buffer_pool
180212 08:55:20 [00] ...done
180212 08:55:20 Backup created in directory '/bak_mysql/full_xtrbak/'
MySQL binlog position: filename 'binlog.000001', position '1036', GTID of the last change '24b1d863-0f0f-11e8-a950-000c29c9f498:1-4,
803de1b2-be09-11e7-8cba-000c29c9f498:1-1333,
9dc847d8-bf72-11e7-9ec4-000c2998e4f1:1-28'
180212 08:55:20 [00] Writing /bak_mysql/full_xtrbak/backup-my.cnf
180212 08:55:20 [00] ...done
180212 08:55:20 [00] Writing /bak_mysql/full_xtrbak/xtrabackup_info
180212 08:55:20 [00] ...done
xtrabackup: Transaction log of lsn (4150100711) to (4150100720) was copied.
180212 08:55:21 completed OK!
[root@node3 bak_mysql]# ll
- 2.先模拟插入点数据，创建第一次增量
(gcdb@localhost) 10:09:20 [employees]> use mytest;
No connection. Trying to reconnect...
Connection id: 17
Current database: *** NONE ***
Database changed
(gcdb@localhost) 09:24:41 [mytest]> create table aaa(a int(10));
Query OK, 0 rows affected (0.02 sec)
(gcdb@localhost) 09:25:49 [mytest]> insert into aaa values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
(gcdb@localhost) 09:26:11 [mytest]> select * from aaa;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
(gcdb@localhost) 09:26:18 [mytest]>
- 3.创建第一个增量备份
[root@node3 bak_mysql]# innobackupex --user=xtruser --password=123456 --no-timestamp --incremental /bak_mysql/incr01 --incremental-basedir=/bak_mysql/full_xtrbak
180212 09:29:14 innobackupex: Starting the backup operation
180212 09:29:14 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;port=3306;mysql_socket=/r2/mysqldata/mysql.sock' as 'xtruser' (using password: YES).
---------------省略-------------------------
xtrabackup: Transaction log of lsn (4150107576) to (4150107585) was copied.
180212 09:29:23 completed OK!
- 4.再插入点数据，创建再次增量
(gcdb@localhost) 09:26:18 [mytest]> insert into aaa values(15),(16),(17);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
(gcdb@localhost) 09:33:59 [mytest]> select * from aaa;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 15 |
| 16 |
| 17 |
+------+
6 rows in set (0.00 sec)
(gcdb@localhost) 09:34:03 [mytest]>
- 5.再创建一个增量备份
[root@node3 bak_mysql]# innobackupex --user=xtruser --password=123456 --no-timestamp --incremental /bak_mysql/incr02 --incremental-basedir=/bak_mysql/incr01
180212 09:35:13 innobackupex: Starting the backup operation
---------------省略-------------------------
xtrabackup: Transaction log of lsn (4150109633) to (4150109642) was copied.
180212 09:35:22 completed OK!
- 6.增量数据大小
[root@node3 bak_mysql]# du -sh *
1.4G full_xtrbak
5.8M incr01
5.4M incr02
[root@node3 bak_mysql]#
- 7.增量恢复
预备增量备份需要2个步骤：
需要先预备全备，但是只重做已提交事务，不回滚未提交事务，然后应用到全备，也是只重做已提交事务，不回滚未提交事务
回滚未提交事务
如果已经回滚了未提交事务，那么就无法再应用增量备份。
[root@node3 bak_mysql]# innobackupex --apply-log --redo-only /bak_mysql/full_xtrbak/
180212 09:47:11 innobackupex: Starting the apply-log operation
---------------省略-------------------------
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 4150100729
InnoDB: Number of pools: 1
180212 09:47:12 completed OK!
[root@node3 bak_mysql]# innobackupex --apply-log --redo-only /bak_mysql/full_xtrbak/ --incremental-dir=/bak_mysql/incr01
180212 09:48:02 innobackupex: Starting the apply-log operation
---------------省略-------------------------
180212 09:48:04 [01] ...done
180212 09:48:04 [00] Copying /bak_mysql/incr01//xtrabackup_binlog_info to ./xtrabackup_binlog_info
180212 09:48:04 [00] ...done
180212 09:48:04 [00] Copying /bak_mysql/incr01//xtrabackup_info to ./xtrabackup_info
180212 09:48:04 [00] ...done
180212 09:48:04 completed OK!
[root@node3 bak_mysql]# innobackupex --apply-log --redo-only /bak_mysql/full_xtrbak/ --incremental-dir=/bak_mysql/incr02
180212 09:48:25 innobackupex: Starting the apply-log operation
---------------省略-------------------------
180212 09:48:27 [00] Copying /bak_mysql/incr02//xtrabackup_info to ./xtrabackup_info
180212 09:48:27 [00] ...done
180212 09:48:27 completed OK!
因为总共做了三次备份，所以先做三次prepare：
对全备做prepare： innobackupex --apply-log --redo-only /backup/all_backup/
后接下来做第一次增量备份的prepare：innobackupex --apply-log --redo-only /backup/all_backup/ --incremental-dir=/backup/incremental_1
对第二次的增量备份prepare，注意，第二次的增备是最后一次，所以不需要加上--redo-only参数：innobackupex --apply-log /backup/all_backup/ --incremental-dir=/backup/incremental_2
后将两次增量备份和全备做一次合并：innobackupex --apply-log /backup/all_backup/
恢复过程与全量恢复没有区别。
- 8.模拟故障
[root@node3 bak_mysql]# systemctl stop mysql --停止mysql
[root@node3 bak_mysql]# mv /r2/mysqldata/
auto.cnf employees/ ib_logfile0 performance_schema/ xtrabackup_binlog_pos_innodb
baktest/ error.log ib_logfile1 slow.log xtrabackup_info
binlog.000001 ib_buffer_pool mysql/ sys/
binlog.index ibdata1 mytest/ t1/
[root@node3 bak_mysql]# mv /r2/mysqldata/ /r2/mysqldata_bk/ -- 移除datadir
[root@node3 bak_mysql]# ps -ef |grep mysql
root 56950 4872 0 09:51 pts/3 00:00:00 grep --color=auto mysql
- 9.恢复数据
[root@node3 bak_mysql]# innobackupex --apply-log --redo-only /bak_mysql/full_xtrbak/ --恢复数据
180212 09:47:11 innobackupex: Starting the apply-log operation
IMPORTANT: Please check that the apply-log run completes successfully.
At the end of a successful apply-log run innobackupex
prints "completed OK!".
innobackupex version 2.4.9 based on MySQL server 5.7.13 Linux (x86_64) (revision id: a467167cdd4)
xtrabackup: cd to /bak_mysql/full_xtrbak/
xtrabackup: This target seems to be not prepared yet.
InnoDB: Number of pools: 1
xtrabackup: xtrabackup_logfile detected: size=8388608, start_lsn=(4150100711)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup: innodb_data_home_dir = .
xtrabackup: innodb_data_file_path = ibdata1:1G:autoextend
xtrabackup: innodb_log_group_home_dir = .
xtrabackup: innodb_log_files_in_group = 1
xtrabackup: innodb_log_file_size = 8388608
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 104857600 bytes for buffer pool (set by --use-memory parameter)
180212 09:56:51 [01] Copying ./xtrabackup_info to /r2/mysqldata/xtrabackup_info
180212 09:56:51 [01] ...done
180212 09:56:51 completed OK!
[root@node3 bak_mysql]# chown mysql:mysql -R /r2/mysqldata --修改新datadir的权限
[root@node3 bak_mysql]# systemctl start mysql --启动新mysql
[root@node3 bak_mysql]# mysql -ugcdb -piforgot
mysql: [Warning] Using a password on the command line interface can be insecure.
(gcdb@localhost) 09:58:34 [(none)]> select * from mytest.aaa; -- 检查一下，全备和两次增备的内容都已经被恢复回来了，也就是最后一次数据的状态。
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
| 15 |
| 16 |
| 17 |
+------+
6 rows in set (0.01 sec)
(gcdb@localhost) 09:58:46 [(none)]>
4.3.2.3 备份及恢复过程总结
- 总结一下xtrabackup备份及恢复全过程：
- 1、备份操作，需要提供具有足够权限的MySQL用户，并且mysqld启动用户需要对datadir有rwx的权限。
- 2、prepare，将未提交的事务回滚，将已提交的事务写入数据文件。
- 3、停止mysqld服务
- 4、mv data/ data_bak_.../
- 5、copyback回去
- 6、修改权限新的datadir权限
- 7、启动服务
4.3.2.4 备份压缩
- 1.压缩对比
如果你想加速压缩，你可以使用并行压缩，这可以通过--compress-threads=#选项来启用。
[root@node3 bak_mysql]# rm -rf ./* --清空备份目录
[root@node3 bak_mysql]# time innobackupex --user=xtruser --password=123456 --no-timestamp /bak_mysql/full_xtrbk --没压缩
xtrabackup: Transaction log of lsn (4150109736) to (4150109745) was copied.
180212 11:23:16 completed OK!
real 0m10.884s
user 0m1.784s --执行1.784秒
sys 0m4.836s
[root@node3 bak_mysql]# time innobackupex --user=xtruser --password=123456 --no-timestamp --compress /bak_mysql/full_compress_xtrbk --有压缩
-------省略------------------
xtrabackup: Transaction log of lsn (4150109736) to (4150109745) was copied.
180212 11:22:19 completed OK!
real 0m14.757s
user 0m3.732s
sys 0m8.423s
[root@node3 bak_mysql]# time innobackupex --user=xtruser --password=123456 --compress-threads=4 --no-timestamp --compress /bak_mysql/full_compress_xtrbk_4 --并发四个线程
-------省略------------------
180212 11:20:58 completed OK!
real 0m11.038s
user 0m3.766s
sys 0m4.449s
--并发四个线程
[root@node3 bak_mysql]# du -sh *
189M full_compress_xtrbk --压缩 189M,大概只有未压缩八份之一
189M full_compress_xtrbk_4 --并发四个线程
1.4G full_xtrbk --未压缩 1.4G
开启并发压缩，实际上就空间换时间，CPU一般都剩余，IO和磁盘空间不足
-
2.解压缩安装qpress
[root@node3 software]# wget http://www.quicklz.com/qpress-11-linux-x64.tar
--2018-02-12 11:48:22-- http://www.quicklz.com/qpress-11-linux-x64.tar
Resolving www.quicklz.com (www.quicklz.com)... 212.97.132.124
Connecting to www.quicklz.com (www.quicklz.com)|212.97.132.124|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 81920 (80K) [application/x-tar]
Saving to: ‘qpress-11-linux-x64.tar’
100%[==================================================================================================================================>] 81,920 57.7KB/s in 1.4s
2018-02-12 11:48:25 (57.7 KB/s) - ‘qpress-11-linux-x64.tar’ saved [81920/81920]
[root@node3 software]# ll
total 7612
-rw-r--r-- 1 root root 7704656 Nov 23 17:15 percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm
-rw-r--r-- 1 root root 81920 Sep 24 2010 qpress-11-linux-x64.tar
drwxr-xr-x 7 root root 4096 Nov 7 15:16 tpcc-mysql
[root@node3 software]# tar xvf qpress-11-linux-x64.tar
qpress
[root@node3 software]# cp qpress /usr/
bin/ etc/ games/ include/ lib/ lib64/ libexec/ local/ sbin/ share/ src/ tmp/
[root@node3 software]# cp qpress /bin/
Display all 2318 possibilities? (y or n)
[root@node3 software]# cp qpress /bin/
[root@node3 software]# ll /bin/qpress
-rwxr-xr-x 1 root root 75684 Feb 12 11:49 /bin/qpress
- 3.使用qpress解压缩所有文件
[root@node3 bak_mysql]# for bf in `find /bak_mysql/full_compress_xtrbk -iname "*\.qp"`; do qpress -d $bf $(dirname $bf) && rm -rf $bf; done
[root@node3 bak_mysql]# du -sh full_compress_xtrbk
1.4G full_compress_xtrbk
4.3.2.5 部分备份和还原
- 1.创建部分备份,部分备份有3个选项可以使用：
--include:设置正则表达式的格式，匹配的就备份--table-file:在文件中指定要备份的表，然后通过这个选项传入文件--database:指定数据库列表
需要启用
innodb_file_per_table,另外在还原时，prepare之后，并不能直接 --copy-back，而只能一个表一个表的import来还原。
-
- 使用database方式
innobackupex可以传递用空格隔开的数组，格式为：databasename[.tablename]
- 使用database方式
[root@node3 bak_mysql]# time innobackupex --user=xtruser --password=123456 --no-timestamp --databases mytest /bak_mysql/mytest
[root@node3 bak_mysql]# ls
full_compress_xtrbk full_compress_xtrbk_4 full_xtrbk mytest
[root@node3 bak_mysql]# ll /bak_mysql/mytest/
total 1048632
-rw-r----- 1 root root 429 Feb 12 14:01 backup-my.cnf
-rw-r----- 1 root root 30050 Feb 12 14:01 ib_buffer_pool
-rw-r----- 1 root root 1073741824 Feb 12 14:01 ibdata1
drwxr-x--- 2 root root 4096 Feb 12 14:01 mytest --只有备份mytest库
-rw-r----- 1 root root 105 Feb 12 14:01 xtrabackup_binlog_info
-rw-r----- 1 root root 119 Feb 12 14:01 xtrabackup_checkpoints
-rw-r----- 1 root root 624 Feb 12 14:01 xtrabackup_info
-rw-r----- 1 root root 2560 Feb 12 14:01 xtrabackup_logfile
[root@node3 bak_mysql]# time innobackupex --user=xtruser --password=123456 --no-timestamp --databases="mytest.aaa baktest mysql" /bak_mysql/muilt_dbs
--备份mytest库aaa表和baktest、mysql库
[root@node3 bak_mysql]# ll /bak_mysql/muilt_dbs/
total 1048628
-rw-r----- 1 root root 429 Feb 12 14:33 backup-my.cnf
drwxr-x--- 2 root root 45 Feb 12 14:33 baktest --备份baktest库
-rw-r----- 1 root root 30050 Feb 12 14:33 ib_buffer_pool
-rw-r----- 1 root root 1073741824 Feb 12 14:33 ibdata1
drwxr-x--- 2 root root 34 Feb 12 14:33 mytest --备份mytest库
-rw-r----- 1 root root 105 Feb 12 14:33 xtrabackup_binlog_info
-rw-r----- 1 root root 119 Feb 12 14:33 xtrabackup_checkpoints
-rw-r----- 1 root root 639 Feb 12 14:33 xtrabackup_info
-rw-r----- 1 root root 2560 Feb 12 14:33 xtrabackup_logfile
[root@node3 bak_mysql]# ll /bak_mysql/muilt_dbs/mytest/
total 108
-rw-r----- 1 root root 8554 Feb 12 14:33 aaa.frm --备份mytest库aaa表
-rw-r----- 1 root root 98304 Feb 12 14:33 aaa.ibd
[root@node3 bak_mysql]# innobackupex --user=root --password=iforgot --export --apply-log /bak_mysql/muilt_dbs/
[root@node3 bak_mysql]# systemctl stop mysql
[root@node3 bak_mysql]# mv /r2/mysqldata /r2/mysqldata01
[root@node3 bak_mysql]# innobackupex --user=root --password=iforgot --copy-back /bak_mysql/muilt_dbs/
[root@node3 bak_mysql]# chown -R mysql.mysql /r2/mysqldata
[root@node3 bak_mysql]# mysql -ugcdb -piforgot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.18-log MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
(gcdb@localhost) 15:21:59 [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| baktest |
| mysql |
| mytest |
+--------------------+
4 rows in set (0.01 sec)
(gcdb@localhost) 15:22:03 [(none)]>
[root@node3 bak_mysql]# systemctl start mysql
如果前面没有备份mysql数据库， 那么在删除数据文件后要重建mysql系统表，重建命令是sudo mysql_install_db --user=mysql，但重建会导致原有用户信息全部丢失
InnoDB: Failed to find tablespace for table `mysql`.`innodb_index_stats` in the cache. Attempting to load the tablespace with space id 14
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: Cannot open datafile for read-only: './mysql/innodb_index_stats.ibd' OS error: 71
InnoDB: Operating system error number 2 in a file operation.
InnoDB: The error means the system cannot find the path specified.
InnoDB: Could not find a valid tablespace file for `mysql/innodb_index_stats`. Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting-datadict.html for how to resolve the issue.
4.3.2.6 备份原理
- 1.
innobackupex在启动后，会先 fork 一个进程 ，启动xtrabackup进程，然后就等待xtrabackup备份完ibd数据文件； - 2.
xtrabackup在备份InnoDB相关数据时，是有2种线程的，1种是redo拷贝线程，负责拷贝redo 文件，1种是ibd 拷贝线程，负责拷贝ibd 文件；redo 拷贝线程只有一个，在 ibd 拷贝线程之前启动，在 ibd 线程结束后结束。xtrabackup进程开始执行后，先启动 redo 拷贝线程，从最新的 checkpoint 点开始顺序拷贝 redo 日志；然后再启动 ibd 数据拷贝线程，在xtrabackup拷贝 ibd 过程中，innobackupex进程一直处于等待状态（等待文件被创建）。 - 3.
xtrabackup拷贝完成idb后，通知innobackupex（通过创建文件），同时自己进入等待（redo 线程仍然继续拷贝）; - 4.
innobackupex收到xtrabackup通知后，执行FLUSH TABLES WITH READ LOCK(FTWRL)，取得一致性位点，然后开始备份非 InnoDB 文件（包括 frm、MYD、MYI、CSV、opt、par等）。拷贝非 InnoDB * 文件过程中，因为数据库处于全局只读状态，如果在业务的主库备份的话，要特别小心，非InnoDB表（主要是MyISAM）比较多的话整库只读时间就会比较长，这个影响一定要评估到。 - 5.当
innobackupex拷贝完所有非InnoDB表文件后，通知xtrabackup（通过删文件） ，同时自己进入等待（等待另一个文件被创建）； - 6.
xtrabackup收到innobackupex备份完非 InnoDB 通知后，就停止redo 拷贝线程，然后通知innobackupex redo log 拷贝完成（通过创建文件）； - 7.
innobackupex收到 redo 备份完成通知后，就开始解锁，执行UNLOCK TABLES； - 8.最后
innobackupex和xtrabackup进程各自完成收尾工作，如资源的释放、写备份元数据信息等，innobackupex等待xtrabackup 子进程结束后退出。
在上面描述的文件拷贝，都是备份进程直接通过操作系统读取数据文件的，只在执行 SQL 命令时和数据库有交互，基本不影响数据库的运行，在备份非 InnoDB 时会有一段时间只读（如果没有MyISAM表的话，只读时间在几秒左右），在备份 InnoDB 数据文件时，对数据库完全没有影响，是真正的热备。
InnoDB 和非 InnoDB 文件的备份都是通过拷贝文件来做的，但是实现的方式不同，前者是以page为粒度做的(xtrabackup)，后者是 cp 或者 tar 命令(innobackupex)，xtrabackup 在读取每个page时会校验 checksum 值，保证数据块是一致的，而 innobackupex 在 cp MyISAM 文件时已经做了flush（FTWRL），磁盘上的文件也是完整的，所以最终备份集里的数据文件都是写入完整的。
- 补充
- 1.通过以上流程图，可以发现一个重要的线索，如果进行物理备份，是不会备份binlog。
- 2.对于5.1 & 5.5，用xtrabackup for 5.1 or 5.5 在
innodb_flush_log_at_trx_commit=1情况下备份数据是一致的
- 3.对于5.6，用xtrabackup for 5.1 or 5.5，即便如果
innodb_flush_log_at_trx_commit=1，没有sync，有可能会丢失事务
- 4.对于5.6，用xtrabackup for 5.6，不会丢失事务。
4.3.5 xtrabackup详细参数
1、备份：
#常用参数
--user：该选项表示备份账号。
--password：该选项表示备份的密码。
--port：该选项表示备份数据库的端口。
--host：该选项表示备份数据库的地址。
--socket：该选项表示mysql.sock所在位置，以便备份进程登录mysql。
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置。
--databases：该选项接受的参数为数据名，如果要指定多个数据库，彼此间需要以空格隔开；如："db1 db2"，同时，在指定某数据库时，也可以只指定其中的某张表。 如："mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表。此外，此选项也可以接受一个文件为参数，文件中每一行为一个要备份的对象。
#压缩参数
--compress：该选项表示压缩innodb数据文件的备份。
--compress-threads：该选项表示并行压缩worker线程的数量。
--compress-chunk-size：该选项表示每个压缩线程worker buffer的大小，单位是字节，默认是64K。
#加密参数
--encrypt：该选项表示通过ENCRYPTION_ALGORITHM的算法加密innodb数据文件的备份，目前支持的算法有ASE128,AES192,AES256。
--encrypt-key：该选项使用合适长度加密key，因为会记录到命令行，所以不推荐使用。
--encryption-key-file：该选项表示文件必须是一个简单二进制或者文本文件，加密key可通过以下命令行命令生成：openssl rand -base64 24。
--encrypt-threads：该选项表示并行加密的worker线程数量。
--encrypt-chunk-size：该选项表示每个加密线程worker buffer的大小，单位是字节，默认是64K。
#加密参数
--encrypt：该选项表示通过ENCRYPTION_ALGORITHM的算法加密innodb数据文件的备份，目前支持的算法有ASE128,AES192,AES256。
--encrypt-key：该选项使用合适长度加密key，因为会记录到命令行，所以不推荐使用。
--encryption-key-file：该选项表示文件必须是一个简单二进制或者文本文件，加密key可通过以下命令行命令生成：openssl rand -base64 24。
--encrypt-threads：该选项表示并行加密的worker线程数量。
--encrypt-chunk-size：该选项表示每个加密线程worker buffer的大小，单位是字节，默认是64K。
#增量备份参数
--incremental：该选项表示创建一个增量备份，需要指定--incremental-basedir。
--incremental-basedir：该选项表示接受了一个字符串参数指定含有full backup的目录为增量备份的base目录，与--incremental同时使用。
--incremental-lsn：该选项表示指定增量备份的LSN，与--incremental选项一起使用。
--incremental-dir：该选项表示增量备份的目录。
--incremental-force-scan：该选项表示创建一份增量备份时，强制扫描所有增量备份中的数据页。
--incremental-history-name：该选项表示存储在PERCONA_SCHEMA.xtrabackup_history基于增量备份的历史记录的名字。Percona Xtrabackup搜索历史表查找最近 （innodb_to_lsn）成功备份并且将to_lsn值作为增量备份启动出事lsn.与innobackupex--incremental-history-uuid互斥。如果没有检测到有效的lsn，xtrabackup会返回error。
--incremental-history-uuid：该选项表示存储在percona_schema.xtrabackup_history基于增量备份的特定历史记录的UUID。
#主从
--slave-info：该选项表示对slave进行备份的时候使用，打印出master的名字和binlog pos，同样将这些信息以change master的命令写入xtrabackup_slave_info文件。可以通过基于这份备份启动一个从库。
--safe-slave-backup：该选项表示为保证一致性复制状态，这个选项停止SQL线程并且等到show status中的slave_open_temp_tables为0的时候开始备份，如果没有打开临时表，bakcup会立刻开始，否则SQL线程启动或者关闭知道没有打开的临时表。如果slave_open_temp_tables在--safe-slave-backup-timeount（默认300秒）秒之后不为0，从库sql线程会在备份完成的时候重启
--include：该选项表示使用正则表达式匹配表的名字[db.tb]，要求为其指定匹配要备份的表的完整名称，即databasename.tablename。
--tables-file：该选项表示指定含有表列表的文件，格式为database.table，该选项直接传给--tables-file。
--no-timestamp：该选项可以表示不要创建一个时间戳目录来存储备份，指定到自己想要的备份文件夹。
--rsync：该选项表示通过rsync工具优化本地传输，当指定这个选项，innobackupex使用rsync拷贝非Innodb文件而替换cp，当有很多DB和表的时候会快很多，不能--stream一起使用。
--stream：该选项表示流式备份的格式，backup完成之后以指定格式到STDOUT，目前只支持tar和xbstream。
--ibbackup：该选项指定了使用哪个xtrabackup二进制程序。IBBACKUP-BINARY是运行percona xtrabackup的命令。这个选项适用于xtrbackup二进制不在你是搜索和工作目录，如果指定了该选项,innoabackupex自动决定用的二进制程序。
--kill-long-queries-timeout：该选项表示从开始执行FLUSH TABLES WITH READ LOCK到kill掉阻塞它的这些查询之间等待的秒数。默认值为0，不会kill任何查询，使用这个选项xtrabackup需要有Process和super权限。
--kill-long-query-type：该选项表示kill的类型，默认是all，可选select。
--ftwrl-wait-threshold：该选项表示检测到长查询，单位是秒，表示长查询的阈值。
--ftwrl-wait-query-type：该选项表示获得全局锁之前允许那种查询完成，默认是ALL，可选update。
--galera-info：该选项表示生成了包含创建备份时候本地节点状态的文件xtrabackup_galera_info文件，该选项只适用于备份PXC。
--defaults-extra-file：该选项指定了在标准defaults-file之前从哪个额外的文件读取MySQL配置，必须在命令行的第一个选项的位置。一般用于存备份用户的用户名和密码的配置文件。
--defaults-group：该选项表示从配置文件读取的组，innobakcupex多个实例部署时使用。
--no-lock：该选项表示关闭FTWRL的表锁，只有在所有表都是Innodb表并且不关心backup的binlog pos点，如果有任何DDL语句正在执行或者非InnoDB正在更新时（包括mysql库下的表），都不应该使用这个选项，后果是导致备份数据不一致，如果考虑备份因为获得锁失败，可以考虑--safe-slave-backup立刻停止复制线程。
--tmpdir：该选项表示指定--stream的时候，指定临时文件存在哪里，在streaming和拷贝到远程server之前，事务日志首先存在临时文件里。在 使用参数stream=tar备份的时候，你的xtrabackup_logfile可能会临时放在/tmp目录下，如果你备份的时候并发写入较大的话 xtrabackup_logfile可能会很大(5G+)，很可能会撑满你的/tmp目录，可以通过参数--tmpdir指定目录来解决这个问题。
--history：该选项表示percona server 的备份历史记录在percona_schema.xtrabackup_history表。 --close-files：该选项表示关闭不再访问的文件句柄，当xtrabackup打开表空间通常并不关闭文件句柄目的是正确的处理DDL操作。如果表空间数量巨大，这是一种可以关闭不再访问的文件句柄的方法。使用该选项有风险，会有产生不一致备份的可能。
--compact：该选项表示创建一份没有辅助索引的紧凑的备份。
--throttle：该选项表示每秒IO操作的次数，只作用于bakcup阶段有效。apply-log和--copy-back不生效不要一起用。
2、prepare：
innobackupex --apply-log [--use-memory=B]
[--defaults-file=MY.CNF]
[--export] [--redo-only] [--ibbackup=IBBACKUP-BINARY]
BACKUP-DIR
--apply-log：该选项表示同xtrabackup的--prepare参数,一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据 文件仍处理不一致状态。--apply-log的作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态。
--use-memory：该选项表示和--apply-log选项一起使用，prepare 备份的时候，xtrabackup做crash recovery分配的内存大小，单位字节。也可(1MB,1M,1G,1GB)，推荐1G。
--defaults-file：该选项指定了从哪个文件读取MySQL配置，必须放在命令行第一个选项的位置。
--export：这个选项表示开启可导出单独的表之后再导入其他Mysql中。
--redo-only：这个选项在prepare base full backup，往其中merge增量备份（但不包括最后一个）时候使用。
3、解压解密：
innobackupex [--decompress] [--decrypt=ENCRYPTION-ALGORITHM]
[--encrypt-key=LITERAL-ENCRYPTION-KEY] | [--encryption-key-file=MY.KEY]
[--parallel=NUMBER-OF-FORKS] BACKUP-DIR
--decompress：该选项表示解压--compress选项压缩的文件。
--parallel：该选项表示允许多个文件同时解压。为了解压，qpress工具必须有安装并且访问这个文件的权限。这个进程将在同一个位置移除原来的压缩/加密文件。
--decrypt：该选项表示解密通过--encrypt选项加密的.xbcrypt文件。
4、还原
innobackupex --copy-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIR
innobackupex --move-back [--defaults-file=MY.CNF] [--defaults-group=GROUP-NAME] BACKUP-DIR
--copy-back：做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir。
--move-back：这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项移除backup文件，用时候必须小心。使用场景：没有足够的磁盘空间同事保留数据文件和Backup副本
注意：
1.datadir目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-backup选项不会覆盖
2.在restore之前,必须shutdown MySQL实例，你不能将一个运行中的实例restore到datadir目录中
3.由于文件属性会被保留，大部分情况下你需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户
chown -R mysql:mysql /data1/dbrestore
以上需要在用户调用Innobackupex之前完成
--force-non-empty-directories：指定该参数时候，使得innobackupex --copy-back或--move-back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备份目录拷贝一个在datadir已经存在的文件，会报错失败。
4.4. mydumper
4.4.1 mydumper介绍
mydumper是用于对MySQL数据库进行多线程备份和恢复的开源工具，备份方式为逻辑备份。
mydumper最新版本：0.9.5。
- mydumper主要特性：
- 事务性和非事务性表一致的快照
- 快速的文件压缩
- 支持导出binlog，默认不支持备份binlog，需要在编译时添加
-DWITH_BINLOG=ON参数 - 支持将备份文件切块
- 多线程备份和多线程恢复，支持对一张表进行多个线程备份
- 备份时对MyISAM表施加FTWRL(FLUSH TABLES WITH READ LOCK),会阻塞DML语句
- 以守护进程的工作方式，定时快照和连续二进制日志
- 支持备份指定库和表
4.4.2 mydumper和myloader原理
4.4.2.1 mydumper备份原理
- 主要步骤概括
- a、连接目标数据库；
- b、通过show processlist来判断是否有长查询，根据参数long-query-guard和kill-long-queries决定退出或杀掉长查询;
- c、锁定myisam表，flush tables with read lock；针对innodb table开启事务，start transaction；
- d、创建worker子线程，缺省为4个；
- e、确定候选表，根据类别分别插入innodb_table，non_innodb_table以及table_schemas链表(表结构)；
- f、将候选表通过g_async_queue_push加入任务队列(队列最后元素是thread shutdown)，由worker子线程从队列中读取表信息并执行数据导出
- g、执行unlock tables，处理完myisam表后立即解锁，以减少锁定时间；
- h、等待worker退出；。
- 备份文件相关信息
- a、所有的备份文件在一个目录中，未指定时为当前目录， 且自动生成备份日期时间文件夹，如export-20150703-145806
- b、目录中包含一个 metadata 文件，该文件记录了备份时间点的二进制日志文件名，日志的写入位置
- c、如果是在从库进行备份，还会记录备份时同步至主库的二进制日志文件及写入位置
- d、每个表有两个备份文件：database.table-schema.sql 表结构文件，database.table.sql 表数据文件
- e、如果对表文件分片，将生成多个备份数据文件，可以指定行数或指定大小分片
4.4.2.1 myloader还原原理
-
如上图所示，mydumper的回复可分为三个阶段：
-
a、首先由myloader主线程完成建库建表，依次将备份目录下dumpdb-schema-create.sql和dumpdb.dumptable-schema.sql中的建库和建表语句应用到目标数据库实例中；
-
b、接着myloader主线程会生成多个工作线程，由这些工作线程将所有dumpdb.dumptable..sql文件中的记录导入到对应表中，这个阶段是并行的，并行粒度为文件，工作线程完成所有dumpdb.dumptable..sql文件数据导入后销毁；
-
C、最后主线程将dumpdb-schema-post.sql、dumpdb.dumptable-schema-view.sql和dumpdb.dumptable-schema-triggers.sql文件中存在的schema导入对应数据库和表中
-
4.4.3 mydumper安装
mydumper安装所依赖的软件包：glibc、 zlib、 pcre、 pcre-devel、 gcc、 gcc-c++、cmake、make、mysql客户端库文件。
[root@localhost-m(252) /r2/soft]# yum install glib2-devel mysql-devel zlib-devel pcre-devel cmake
[root@localhost-m(252) /r2/soft]# wget https://launchpad.net/mydumper/0.9/0.9.1/+download/mydumper-0.9.1.tar.gz
[root@localhost-m(252) /r2/soft]# tar xzvf mydumper-0.9.1.tar.gz
[root@localhost-m(252) /r2/soft]# cd mydumper-0.9.1
[root@localhost-m(252) /r2/soft]# cmake .
[root@localhost-m(252) /r2/soft]# make
[root@localhost-m(252) /r2/soft]# make install
[root@localhost-m(252) /r2/soft]# mydumper -V
mydumper 0.9.1, built against MySQL 5.5.52-MariaDB
[root@localhost-m(252) /r2/soft]# ll /usr/local/bin
total 300
-rwxr-xr-x 1 root root 240440 12月 14 09:02 mydumper
-rwxr-xr-x 1 root root 62680 12月 14 09:02 myloader
mydumper主要有以下两个命令：mydumper用于备份，myloader用于恢复。
4.4.4 mydumper和myloader演示
- mydumper常用参数
- -t , --threads 使用的线程数，默认是4个
- -c , --compress 启用压缩
- -B , --database 对某个库进行备份
- -T , --tables-list 对某个表进行备份
- -r , --rows 将指定的行数拆分到chunks中去
- -G , --triggers 备份触发器
- -R , --routines 备份存储过程
- -v , --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
- -o , --outputdir 备份文件目录
[root@localhost-m(252) /data5/sqlbak]# mydumper -u root -p iforgot -t 8 -B baktest -o /data5/sqlbak/baktest -v -3
** Message: Connected to a MySQL server
** Message: Started dump at: 2018-02-13 12:14:39
** Message: Written master status
** Message: Thread 1 connected using MySQL connection ID 33702
** Message: Thread 2 connected using MySQL connection ID 33703 --启用8个线程
** Message: Thread 3 connected using MySQL connection ID 33704
** Message: Thread 4 connected using MySQL connection ID 33705
** Message: Thread 5 connected using MySQL connection ID 33706
** Message: Thread 6 connected using MySQL connection ID 33707
** Message: Thread 7 connected using MySQL connection ID 33708
** Message: Thread 8 connected using MySQL connection ID 33709
** Message: Non-InnoDB dump complete, unlocking tables
** Message: Thread 1 dumping data for `baktest`.`bk`
** Message: Thread 5 shutting down
** Message: Thread 2 dumping schema for `baktest`.`bk`
** Message: Thread 3 shutting down
** Message: Thread 4 shutting down
** Message: Thread 7 shutting down
** Message: Thread 6 shutting down
** Message: Thread 8 shutting down
** Message: Thread 1 shutting down
** Message: Thread 2 shutting down
** Message: Finished dump at: 2018-02-13 12:14:39
[root@localhost-m(252) /data5/sqlbak]# ll baktest/
total 16
-rw-r--r-- 1 root root 183 2月 13 12:14 baktest.bk-schema.sql --表结构语句
-rw-r--r-- 1 root root 166 2月 13 12:14 baktest.bk.sql --每个表一个文件
-rw-r--r-- 1 root root 66 2月 13 12:14 baktest-schema-create.sql --库结构语句
-rw-r--r-- 1 root root 357 2月 13 12:14 metadata --元数据 记录备份开始和结束时间，以及binlog日志文件位置。
[root@localhost-m(252) /data5/sqlbak]# cat baktest/metadata --记录master信息
Started dump at: 2018-02-13 13:29:27
SHOW MASTER STATUS:
Log: binlog.000002
Pos: 360
GTID:3eae5854-d89f-11e7-ab20-246e960a8d84:1-10198,
746122da-663e-11e7-9de1-b8ca3a6567c4:1-19113,
a57cd625-663e-11e7-9ba9-b8ca3a64d66c:18606-190213162:190249486-190252207:190327015-207860771:207860811-244861160:244862559-348804170
Finished dump at: 2018-02-13 13:29:27
[root@localhost-m(252) /data5/sqlbak]#
-
还原
-
myloader 常用参数
- -t , --threads 使用线程数，默认是4个
- -q , --queries-per-transaction 每个事务的query数量, 默认100
- -B , --database 指定需要还原的数据库
- -s , --source-db 还原的数据库
- -d , --directory 备份文件所在的目录
- -v , --verbose 更多输出, 0 = silent, 1 = errors, 2 = warnings, 3 = info, default 2
[root@localhost-m(252) /r2/soft]# mycli -uroot -piforgot
mysql root@localhost:(none)> drop database baktest; --删除baktest库
You're about to run a destructive command.
Do you want to proceed? (y/n): y
Your call!
Query OK, 1 row affected
Time: 0.086s
[root@localhost-m(252) /data5/sqlbak]# myloader -u root -p iforgot -t 8 -B baktest -d /data5/sqlbak/baktest -v -3 --还原baktest库
** Message: 8 threads created
** Message: Creating database `baktest`
** Message: Creating table `baktest`.`bk`
** Message: Thread 7 restoring `baktest`.`bk` part 0
** Message: Thread 3 shutting down
** Message: Thread 2 shutting down
** Message: Thread 6 shutting down
** Message: Thread 4 shutting down
** Message: Thread 1 shutting down
** Message: Thread 8 shutting down
** Message: Thread 5 shutting down
** Message: Thread 7 shutting down
[root@localhost-m(252) /data5/sqlbak]# mysql -uroot -piforgot -e "select * from baktest.bk"
mysql: [Warning] Using a password on the command line interface can be insecure.
+------+------+
| id | num |
+------+------+
| 0 | 5 |
| 0 | 10 |
| 0 | 15 |
| 0 | 10 |
| 0 | 177 |
+------+------+
[root@localhost-m(252) /data5/sqlbak]#
- mydumper支持一张表多个线程以chunk的方式批量导出，参数-r：试图用行块来分割表，该参数关闭--chunk-filesize参数。
--并发8个线程，每个线程用100000行块来分割表
[root@localhost-m(252) /data5/sqlbak]# mydumper -u root -p iforgot -t 8 -r 100000 -B dbt3 -o /data5/sqlbak/dbt3 -v -3
** Message: Connected to a MySQL server
** Message: Started dump at: 2018-02-13 13:29:46
** Message: Written master status
** Message: Thread 1 connected using MySQL connection ID 34188
** Message: Thread 2 connected using MySQL connection ID 34189
** Message: Thread 3 connected using MySQL connection ID 34190
** Message: Thread 4 connected using MySQL connection ID 34191
** Message: Thread 5 connected using MySQL connection ID 34192
** Message: Thread 6 connected using MySQL connection ID 34193
** Message: Thread 7 connected using MySQL connection ID 34194
** Message: Thread 8 connected using MySQL connection ID 34195
** Message: Thread 1 dumping data for `dbt3`.`customer` where `c_custkey` IS NULL OR (`c_custkey` >= 1 AND `c_custkey` < 150001)
** Message: Thread 2 dumping data for `dbt3`.`lineitem` where `l_orderkey` IS NULL OR (`l_orderkey` >= 1 AND `l_orderkey` < 101696)
** Message: Thread 4 dumping data for `dbt3`.`lineitem` where (`l_orderkey` >= 101696 AND `l_orderkey` < 203391)
** Message: Thread 3 dumping data for `dbt3`.`lineitem` where (`l_orderkey` >= 610171 AND `l_orderkey` < 711866)
** Message: Thread 8 dumping data for `dbt3`.`lineitem` where (`l_orderkey` >= 305086 AND `l_orderkey` < 406781)
** Message: Thread 5 dumping data for `dbt3`.`lineitem` where (`l_orderkey` >= 203391 AND `l_orderkey` < 305086)
** Message: Thread 7 dumping data for `dbt3`.`lineitem` where (`l_orderkey` >= 508476 AND `l_orderkey` < 610171)
** Message: Thread 6 dumping data for `dbt3`.`lineitem` where (`l_orderkey` >= 406781 AND `l_orderkey` < 508476)
---------------省略-------------------------
** Message: Thread 2 dumping data for `dbt3`.`partsupp` where (`ps_partkey` >= 28573 AND `ps_partkey` < 57145)
** Message: Thread 5 dumping data for `dbt3`.`partsupp` where (`ps_partkey` >= 57145 AND `ps_partkey` < 85717)
** Message: Thread 8 dumping data for `dbt3`.`partsupp` where (`ps_partkey` >= 85717 AND `ps_partkey` < 114289)
** Message: Thread 3 dumping data for `dbt3`.`partsupp` where (`ps_partkey` >= 114289 AND `ps_partkey` < 142861)
** Message: Thread 4 dumping data for `dbt3`.`partsupp` where (`ps_partkey` >= 142861 AND `ps_partkey` < 171433)
** Message: Thread 1 dumping data for `dbt3`.`partsupp` where (`ps_partkey` >= 171433 AND `ps_partkey` < 200005)
** Message: Thread 7 dumping data for `dbt3`.`region`
** Message: Thread 7 dumping data for `dbt3`.`supplier`
** Message: Thread 7 dumping data for `dbt3`.`time_statistics`
** Message: Thread 7 dumping schema for `dbt3`.`customer`
** Message: Thread 7 dumping schema for `dbt3`.`lineitem`
** Message: Thread 7 dumping schema for `dbt3`.`nation`
** Message: Thread 7 dumping schema for `dbt3`.`orders`
** Message: Thread 7 dumping schema for `dbt3`.`part`
** Message: Thread 7 dumping schema for `dbt3`.`partsupp`
** Message: Thread 7 dumping schema for `dbt3`.`region`
** Message: Thread 7 dumping schema for `dbt3`.`supplier`
** Message: Thread 7 dumping schema for `dbt3`.`time_statistics`
** Message: Thread 7 shutting down
** Message: Thread 5 shutting down
** Message: Thread 3 shutting down
** Message: Thread 8 shutting down
** Message: Thread 2 shutting down
** Message: Thread 4 shutting down
** Message: Thread 6 shutting down
** Message: Thread 1 shutting down
** Message: Finished dump at: 2018-02-13 13:29:49
[root@localhost-m(252) /data5/sqlbak]#
4.4.5 mydumper和myloader详细参数和用法
- mydumper参数
Usage:
mydumper [OPTION...] multi-threaded MySQL dumping
Help Options:
-?, --help Show help options
-B, --database 要备份的数据库，不指定则备份所有库
-T, --tables-list 需要备份的表，名字用逗号隔开
-o, --outputdir 备份文件输出的目录
-s, --statement-size 生成的insert语句的字节数，默认1000000(这个参数不能太小，不然会报 Row bigger than statement_size for tools.t_serverinfo)
-r, --rows 将表按行分块时，指定的块行数，指定这个选项会关闭 --chunk-filesize
-F, --chunk-filesize 将表按大小分块时，指定的块大小，单位是 MB
-c, --compress 压缩输出文件
-e, --build-empty-files 如果表数据是空，还是产生一个空文件（默认无数据则只有表结构文件）
-x, --regex 支持正则表达式匹配'db.table',如mydumper –regex '^(?!(mysql|test))'
-i, --ignore-engines 忽略的存储引擎，用都厚分割
-m, --no-schemas 不备份表结构
-d, --no-data 不备份表数据
-G, --triggers 备份触发器
-E, --events 备份事件
-R, --routines 备份存储过程和函数
-k, --no-locks 不使用临时共享只读锁，使用这个选项会造成数据不一致
--less-locking 减少对InnoDB表的锁施加时间（这种模式的机制下文详解）
-l, --long-query-guard 设定阻塞备份的长查询超时时间，单位是秒，默认是60秒（超时后默认mydumper将会退出）
--kill-long-queries 杀掉长查询 (不退出)
-b, --binlogs 导出binlog
-D, --daemon 启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份
-I, --snapshot-interval dump快照间隔时间，默认60s，需要在daemon模式下
-L, --logfile 使用的日志文件名(mydumper所产生的日志), 默认使用标准输出
--tz-utc 设置时区，只有备份应用到不同时区的时使用。默认是--skip-tz-utc是关闭的
--skip-tz-utc 同上
--use-savepoints 使用savepoints来减少采集metadata所造成的锁时间，需要 SUPER 权限
--success-on-1146 Not increment error count and Warning instead of Critical in case of table doesn't exist
-h, --host 连接的主机名
-u, --user 备份所使用的用户
-p, --password 密码
-P, --port 端口
-S, --socket 使用socket通信时的socket文件
-t, --threads 开启的备份线程数，默认是4
-C, --compress-protocol 压缩与mysql通信的数据
-V, --version 显示版本号
-v, --verbose 输出信息模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为 2
--lock-all-tables 锁全表，代替FLUSH TABLE WITH READ LOCK
-U, --updated-since Use Update_time to dump only tables updated in the last U days
--trx-consistency-only Transactional consistency only
- 2.myloader参数
Usage:
myloader [OPTION...] multi-threaded MySQL loader
-d, --directory 备份文件的文件夹
-q, --queries-per-transaction 每次事物执行的查询数量，默认是1000
-o, --overwrite-tables 如果要恢复的表存在，则先drop掉该表，使用该参数，需要备份时候要备份表结构
-B, --database 需要还原的数据库
-s, --source-db 还原的数据库
-e, --enable-binlog 启用还原数据的二进制日志
-h, --host 主机
-u, --user 还原的用户
-p, --password 密码
-P, --port 端口
-S, --socket socket文件
-t, --threads 还原所使用的线程数，默认是4
-C, --compress-protocol 压缩协议
-V, --version 显示版本
-v, --verbose 输出模式, 0 = silent, 1 = errors, 2 = warnings, 3 = info, 默认为2
- 3.命令用法
备份实例
备份wordpress库到/var/backup/wordpress文件夹中，并压缩备份文件
$ mydumper -u root -p root -h localhost -B wordpress -c -o /var/backup/wordpress
备份所有数据库，并备份二进制日志文件，备份至/var/backup/alldb文件夹
$ mydumper -u root -p root -h localhost -o /var/backup/alldb
备份wordpress.wp_posts表，且不备份表结构，备份至/var/backup/wordpress-01文件夹
$ mydumper -u root -p root -h localhost -B wordpress -T wp_posts -m -o /var/backup/wordpress-01
只导出数据不导出表结构
$ mydumper -h 127.0.0.1 -u root -p root --database wordpress --no-schemas
不指定输出目录会在当前目录下生成一个以当前时间命名的目录，类似export-20160517-114713
默认无数据则只有表结构文件，加上--build-empty-files参数后，即使是一张空表，仍然会创建一个文件。
$ mydumper -h 127.0.0.1 -u root -p root --build-empty-files
设置长查询的上限，如果存在比这个还长的查询则退出mydumper，也可以设置杀掉这个长查询
$ mydumper -h 127.0.0.1 -u root -p root --long-query-guard 300 --kill-long-queries
实现此功能需要指定--long-query-guard参数，后面加上限值即可。同时杀掉长查询，需要指定--kill-long-queries参数。
设置需要导出的列表-–tables-list，逗号分割
$ mydumper -h 127.0.0.1 -u root -p root --tables-list=test.mt_test,robin.test
只备份t_task和t_guid表
$ mydumper --database=tools --outputdir=/var/backup/tools/ --tables-list=t_task,t_guid
注意此参数需要加上数据库名字。执行完成后，在导出目录即可看到上述两张表的表结构以及数据。
通过regex设置正则表达式，需要设置数据库名字
$ mydumper -h 127.0.0.1 -u root -p root --regex="beebol.*|tools.*"
此功能跟上面的-–tables-list类似。
只备份以t_server开头的表
$ mydumper --database=tools --outputdir=/var/backup/tools/ --regex="tools.t_server*"
备份出了名称为tmp.*的表，并压缩备份文件
$ mydumper -u root -p 123456 -P 3306 -m -c -b --regex=tmp.* -B test -o /var/backup/tmp/
只备份abc、bcd、cde库
$ mydumper -u backup -p 123456 -h 192.168.180.13 -P 3306 -t 3 -c -l 3600 -s 10000000 -e --regex 'abc|bcd|cde' -o bbb/
不备份abc、mysql、test数据库
$ mydumper -u backup -p 123456 -h 192.168.180.13 -P 3306 -t 3 -c -l 3600 -s 10000000 -e --regex '^(?!(abc|mysql|test))' -o bbb/
把单表分成多个chunks
$ mydumper -h 127.0.0.1 -u root -p root --rows 2000
实现此功能，加上--rows参数。如果一张表的记录数超过设置的值，则这张表会拆分成多个SQL文件，命名规则如下：数据库名.表名.0000x.sql，x 从 0 开始。
过滤某个引擎的表
$ mydumper -h 127.0.0.1 -u root -p root -B test --ignore-engines=innodb
加上--ignore-engines参数后，指定的存储引擎就会被过滤，亦即不导出指定存储引擎的表。
查看详细日志
$ mydumper -h 127.0.0.1 -u root -p root -B test -v 3
加上-v参数即可查看日志，取值可以是 0、1、2、3，分别表示静默模式、只输出错误、只输出警告、详细信息，默认取值是2。
指定导出线程数
$ mydumper -h 127.0.0.1 -u root -p root -B test --threads 10
mydumper是多线程的。加上--threads参数后，可以指定线程数，如果导出的数据较多，建议指定此参数，并且设置一个合理的值。另外，加上此参数，明显导出速度快了很多，这就是多线程的优势。当然，多线程肯定会消耗更多的系统资源。
后台运行
$ mydumper -h 127.0.0.1 -u root -p root -B test --daemon
压缩导出的SQL文件
$ mydumper -h 127.0.0.1 -u root -p root -B test --compress
压缩后的SQL文件以.gz 结尾。我们可以使用gunzip命令来解压。具体用法是：gunzip –c filename.gz > filename
远程备份
$ mydumper -h 远程服务器地址 -u root -p root -o /var/backup/mydumper -v 3 -c 9 -C -e -t 8
还原实例
$ myloader -u root -p root -h localhost -B wordpress -d /var/backup/wordpress
还原到另一台服务器
$ myloader -u root -p 123456 -h 192.168.200.25 -P 3307 -B wordpress -d /var/backup/wordpress
如表存在先删除
$ myloader -u root -p 123456 -h 192.168.200.25 -P 3306 -o -B wordpress -d /var/backup/wordpress
这里需要注意使用该参数，备份目录里面需要有表结构的备份文件。
