一、Mysql备份恢复简介

mysql的备份方式分为热备和冷备，热备分为逻辑备份和物理备份，逻辑备份Mysql提供了mysqldump的工具来完成备份，物理备份需要使用第三方出品的xtrabackup备份工具完成，冷备份需要将数据库服务实例停止，然后拷贝mysql Data目录。

二、Mysql逻辑备份

mysql 导出备份

-E 导出事件

-R 导出存储过程和自定义函数

--lock-tables 导出前锁定所有表

--add-drop-table 添加删除语句

导出所有数据库

mysqldump -uroot -p --all-databases>all_database.sql

导出多个数据库

mysqldump -uroot -p --database db1 db2 > bak.sql

导出整个数据库

mysqldump -uuser -ppass dbname >dbname.sql

导出一个表

mysqldump -uuser -ppass dbname users >dbname_user.sql

导出表结构

mysql -uuser -ppass -d --add-drop-table dbname >dbname.sql

导出数据库事件、存储过程、自定义函数

mysqldump -R -E -uuser -h127.0.0.1 -ppass dbname>dbname.sql

导出文本

在某些情况下，为了一些特定的目的，经常需要将表里的数据导出为某些符号分割的纯数据文本，而不是 SQL 语句，因为LOAD DATA 的加载速度比普通的 SQL 加载要快 20 倍以上

方法 1：使用 SELECT ...INTO OUTFILE ...命令来导出数据，具体语法如下。

mysql> SELECT * FROM tablename INTO OUTFILE 'target_file' [option];

select * from emp into outfile '/tmp/emp.txt' fields terminated by "," enclosed by '"';

select * from emp into outfile '/tmp/emp.txt' fields terminated by "," optionally enclosed by '"' ;

system more /tmp/emp.txt

MySQL5.7之后多了一个备份工具：mysqlpump。它是mysqldump的一个衍生，mysqldump就不多说明了，现在看看mysqlpump到底有了哪些提升，可以查看官方文档，这里针对如何使用做下说明。

#mysqlpump压缩备份vs数据库 三个并发线程备份，消耗时间：222s

mysqlpump -uzjy -p -h192.168.123.70 --single-transaction --default-character-set=utf8 --compress-output=LZ4 --default-parallelism=3 -B vs > /home/zhoujy/vs_db.sql.lz4

#mysqldump备份压缩vs数据库 单个线程备份，消耗时间：900s，gzip的压缩率比LZ4的高

mysqldump -uzjy -p -h192.168.123.70 --default-character-set=utf8 -P3306 --skip-opt --add-drop-table --create-options --quick --extended-insert --single-transaction -B vs | gzip > /home/zhoujy/vs.sql.gz

#mydumper备份vs数据库 三个并发线程备份，消耗时间：300s，gzip的压缩率比LZ4的高

mydumper -u zjy -p -h 192.168.123.70 -P 3306 -t 3 -c -l 3600 -s 10000000 -B vs -o /home/zhoujy/vs/

#mydumper备份vs数据库，五个并发线程备份，并且开启对一张表多个线程以chunk的方式批量导出，-r。消耗时间：180s

mydumper -u zjy -p -h 192.168.123.70 -P 3306 -t 5 -c -r 300000 -l 3600 -s 10000000 -B vs -o /home/zhoujy/vs/

mysql 导入恢复

方法1：source命令

进入mysql数据库控制台，如

mysql -uroot -p

mysql> set names utf8;

mysql> use 数据库

mysql> source dbname.sql 文件中的sql每次执行一句并输出，效率较低

方法2：使用命令直接导入

mysql -u root -p dbname < dbname.sql 一次执行多条，处理效率高

mysql -h127.0.0.1 -uuser -ppass --default-character-set=utf8 -e "use xlink;source /docker-volume/jenkins_task/xlink_mysql/xlink_r3.sql;"

方法3:

mysqlimport -u root hellodb/tmp/classes2.sql

mysqlimport -u root -p --local mytbl dump.txt

mysqlimport 程序是一个将以特定格式存放的文本数据（如通过"select * into OUTFILE from ..."所生成的数据文件）导入到指定的MySQL Server 中的工具程序，比如将一个标准的csv 文件导入到某指定数据库的指定表中。mysqlimport 工具实际上也只是"load data infile"命令的一个包装实现。

方法4:

LOAD DATA LOCAL INFILE 'E:\Desktop\pet.txt' INTO TABLE pet;

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl;

load data local infile "/var/lib/mysql/test/data.txt" into table person(name,city,salary);

load data infile '/tmp/emp.txt' into table emp fields terminated by ',' enclosed by'"' ;

三、xtrabackup物理备份

Percona-xtrabackup是 Percona公司开发的一个用于MySQL数据库物理热备的备份工具，支持MySQL、Percona server和MariaDB，开源免费，是目前较为受欢迎的主流备份工具。xtrabackup只能备份innoDB和xtraDB两种数据引擎的表，而不能备份MyISAM数据表。

MySQL冷备、mysqldump、MySQL热拷贝都无法实现对数据库进行增量备份。mysqldump备份方式是采用的逻辑备份，其最大的缺陷是备份和恢复速度较慢，在实际生产环境中增量备份是非常实用的，如果数据大于50G或100G，存储空间足够的情况下，可以每天进行完整备份，如果每天产生的数据量较大，需要定制数据备份策略。例如每周实用完整备份，周一到周六实用增量备份。而Percona-Xtrabackup就是为了实现增量备份而出现的一款主流备份工具，xtrabakackup有2个工具，分别是xtrabakup、innobakupe。

Xtrabackup安装完成后有4个可执行文件，其中2个比较重要的备份工具是innobackupex、xtrabackup

1）xtrabackup 是专门用来备份InnoDB表的，和mysql server没有交互；

2）innobackupex 是一个封装xtrabackup的Perl脚本，支持同时备份innodb和myisam，但在对myisam备份时需要加一个全局的读锁。

3）xbcrypt 加密解密备份工具

4）xbstream 流传打包传输工具，类似tar

Xtrabackup优点

（1）备份速度快，物理备份可靠

（2）备份过程不会打断正在执行的事务（无需锁表）

（3）能够基于压缩等功能节约磁盘空间和流量

（4）自动备份校验

（5）还原速度快

（6）可以流传将备份传输到另外一台机器上

（7）在不增加服务器负载的情况备份数据

1、xtrabackup的安装

下载地址：

wget

yum install -y percona-xtrabackup-24-2.4.9-1.el7.x86_64.rpm

2.Xtrabackup中主要包含两个工具：

xtrabackup：是用于热备innodb，xtradb表中数据的工具，不能备份其他类型的表，也不能备份数据表结构；

innobackupex：是将xtrabackup进行封装的perl脚本，提供了备份myisam表的能力。

常用选项:

--host 指定主机

--user 指定用户名

--password 指定密码

--port 指定端口

--databases 指定数据库

--incremental 创建增量备份

--incremental-basedir 指定包含完全备份的目录

--incremental-dir 指定包含增量备份的目录

--apply-log 对备份进行预处理操作

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。"准备"的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

--redo-only 不回滚未提交事务

--copy-back 恢复备份目录

Xtrabackup备份原理

3.备份原理

备份开始时首先会开启一个后台检测进程，实时检测mysq redo的变化，一旦发现有新的日志写入，立刻将日志记入后台日志文件xtrabackup_log中，之后复制innodb的数据文件一系统表空间文件ibdatax，复制结束后，将执行flush tables with readlock,然后复制.frm MYI MYD等文件，最后执行unlock tables,最终停止xtrabackup_log

输出如下提示信息

xtrabackup: Transaction log of lsn (2543172) to (2543181) was copied.

171205 10:17:52 completed OK!

4.创建备份用户

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY '123456';　　#创建用户

mysql> REVOKE ALL PRIVILEGES,GRANT OPTION FROM 'bkpuser';　　#回收此用户所有权限

mysql> GRANT RELOAD,LOCK TABLES,RELICATION CLIENT ON *.* TO 'bkpuser'@'localhost';　　#授权刷新、锁定表、用户查看服务器状态

mysql> FLUSH PRIVILEGES;　　#刷新授权表

5.备份恢复命令

备份：

innobackupex --user=DBUSER --password=DBUSERPASS --defaults-file=/etc/my.cnf /path/to/BACKUP-DIR/

恢复：

innobackupex --apply-log /backups/2018-07-30_11-04-55/

innobackupex --copy-back --defaults-file=/etc/my.cnf /backups/2018-07-30_11-04-55/

6.基于全量备份的增量备份与恢复

做一次增量备份（基于当前最新的全量备份）

innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --incremental /backups/ --incremental-basedir=/backups/2018-07-30_11-01-37

1. 准备基于全量

innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37

2. 准备基于增量

innobackupex --user=root --password=root --defaults-file=/etc/my.cnf --apply-log --redo-only /backups/2018-07-30_11-01-37 --incremental-dir=/backups/2018-07-30_13-51-47/

3. 恢复

innobackupex --copy-back --defaults-file=/etc/my.cnf /opt/2017-01-05_11-04-55/