数据备份与恢复

• 物理备份：备份的是完整的数据库目录和数据文件。采用该模式会进行大量的 IO 操作，但不含任何逻辑转换，因此备份和恢复速度通常都比较快。
• 逻辑备份：通过数据库结构和内容信息来进行备份。因为要执行逻辑转换，因此其速度较慢，并且在以文本格式保存时，其输出文件的大小大于物理备份。逻辑备份的还原的粒度可以从服务器级别（所有数据库）精确到具体表，但备份不会包括日志文件、配置文件等与数据库无关的内容。

• 全量备份：备份服务器在给定时间点上的所有数据。
• 增量备份：备份在给定时间跨度内（从一个时间点到另一个时间点）对数据所做的更改。

• 在线备份：数据库服务在运行状态下进行备份。此时其他客户端依旧可以连接到数据库，但为了保证数据的一致性，在备份期间可能会对数据进行加锁，此时客户端的访问依然会受限。
• 离线备份：在数据库服务停机状态下进行备份。此备份过程简单，但由于无法提供对外服务，通常会对业务造成比较大的影响。

• mysqldump ：这是 MySQL 自带的备份工具，其采用的备份方式是逻辑备份，支持全库备份、单库备份、单表备份。由于其采用的是逻辑备份，所以生成的备份文件比物理备份的大，且所需恢复时间也比较长。
• mysqlpump ：这是 MySQL 5.7 之后新增的备份工具，在 mysqldump 的基础上进行了功能的扩展，支持多线程备份，支持对备份文件进行压缩，能够提高备份的速度和降低备份文件所需的储存空间。
• Xtrabackup ：这是 Percona 公司开发的实时热备工具，能够在不停机的情况下进行快速可靠的热备份，并且备份期间不会间断数据库事务的处理。它支持数据的全备和增备，并且由于其采用的是物理备份的方式，所以恢复速度比较快。

# 备份数据库或数据库中的指定表
mysqldump [options] db_name [tbl_name ...]
# 备份多个指定的数据库
mysqldump [options] --databases db_name ...
# 备份当前数据库实例中的所有表
mysqldump [options] --all-databases

• --host=host_name， -h host_name 指定服务器地址。
• --user=user_name， -u user_name 指定用户名。
• --password[=password]， -p[password] 指定密码。通常无需在命令行中明文指定，按照提示输入即可。
• --default-character-set=charset_name 导出文本使用的字符集，默认为 utf8。
• --events， -E 备份包含数据库中的事件。
• --ignore-table=db_name.tbl_name 不需要进行备份的表，必须使用数据库和表名来共同指定。也可以作用于视图。
• --routines， -R 备份包含数据库中的存储过程和自定义函数。
• --triggers 备份包含数据库中的触发器。
• --where='where_condition'， -w 'where_condition' 在对单表进行导出时候，可以指定过滤条件，例如指定用户名 --where="user='jimf'" 或用户范围 -w"userid>1" 。
• --lock-all-tables， -x 锁定所有数据库中的所有表，从而保证备份数据的一致性。此选项自动关闭 --single-transaction 和 --lock-tables。
• --lock-tables， -l 锁定当前数据库中所有表，能够保证当前数据库中表的一致性，但不能保证全局的一致性。
• --single-transaction 此选项会将事务隔离模式设置为 REPEATABLE READ 并开启一个事务，从而保证备份数据的一致性。主要用于事务表，如 InnoDB 表。 但是此时仍然不能在备份表上执行 ALTER TABLE， CREATE TABLE， DROP TABLE， RENAME TABLE， TRUNCATE TABLE 等操作，因为 REPEATABLE READ 并不能隔离这些操作。 另外需要注意的是 --single-transaction 选项与 --lock-tables 选项是互斥的，因为 LOCK TABLES 会导致任何正在挂起的事务被隐式提交。转储大表时，可以将 --single-transaction 选项与 --quick 选项组合使用 。
• --quick， -q 主要用于备份大表。它强制 mysqldump 一次只从服务器检索一行数据，避免一次检索所有行而导致缓存溢出。
• --flush-logs, -F 在开始备份前刷新 MySQL 的日志文件。此选项需要 RELOAD 权限。如果此选项与 --all-databases 配合使用，则会在每个数据库开始备份前都刷新一次日志。如果配合 --lock-all-tables，--master-data 或 --single-transaction 使用，则只会在锁定所有表或者开启事务时刷新一次。
• --master-data[=value ] 可以通过配置此参数来控制生成的备份文件是否包含 CHANGE MASTER 语句，该语句中包含了当前时间点二进制日志的信息。该选项有两个可选值：1 和 2 ，设置为 1 时 CHANGE MASTER 语句正常生成，设置为 2 时以注释的方式生成。--master-data 选项还会自动关闭 --lock-tables 选项，而且如果你没有指定 --single-transaction 选项，那么它还会启用 --lock-all-tables 选项，在这种情况下，会在备份开始时短暂内获取全局读锁。

• mysqldump 的全量备份与恢复的操作比较简单，示例如下：

  # 备份雇员库
  mysqldump  -uroot -p --databases employees > employees_bak.sql
  # 恢复雇员库
  mysql -uroot -p  < employees_bak.sql
  

• 单表备份：

  # 备份雇员库中的职位表
  mysqldump  -uroot -p --single-transaction employees titles > titles_bak.sql
  # 恢复雇员库中的职位表
  mysql> use employees;
  mysql> source /root/mysqldata/titles_bak.sql;
  

mysqldump -uroot -p --master-data=2 --flush-logs employees titles > titles_bak.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=155;

mysqlbinlog --start-position=155 \
--database=employees  ${MYSQL_HOME}/data/mysql-bin.000004 > titles_inr_bak_01.sql

mysqlbinlog --database=employees  ${MYSQL_HOME}/data/mysql-bin.000005 > titles_inr_bak_02.sql
mysqlbinlog --database=employees  ${MYSQL_HOME}/data/mysql-bin.000006 > titles_inr_bak_03.sql
.....

• 能够并行处理数据库及其中的对象，从而可以加快备份进程；
• 能够更好地控制数据库及数据库对象（表，存储过程，用户帐户等）；
• 能够直接对备份文件进行压缩；
• 备份时能够显示进度指标（估计值）；
• 备份用户时生成的是 CREATE USER 与 GRANT 语句，而不是像 mysqldump 一样备份成数据，可以方便用户按需恢复。

• --default-parallelism=N 每个并行处理队列的默认线程数。默认值为 2。
• --parallel-schemas=[N:]db_list 用于并行备份多个数据库：db_list 是一个或多个以逗号分隔的数据库名称列表；N 为使用的线程数，如果没有设置，则使用 --default-parallelism 参数的值。
• --users 将用户信息备份为 CREATE USER 语句和 GRANT 语句 。如果想要只备份用户信息，则可以使用下面的命令：

  mysqlpump --exclude-databases=% --users
  

• --compress-output=algorithm 默认情况下，mysqlpump 不对备份文件进行压缩。可以使用该选项指定压缩格式，当前支持 LZ4 和 ZLIB 两种格式。需要注意的是压缩后的文件可以占用更少的存储空间，但是却不能直接用于备份恢复，需要先进行解压，具体如下：

  # 采用lz4算法进行压缩
  mysqlpump --compress-output=LZ4 > dump.lz4
  # 恢复前需要先进行解压
  lz4_decompress input_file output_file
  # 采用ZLIB算法进行压缩
  mysqlpump --compress-output=ZLIB > dump.zlib
  zlib_decompress input_file output_file
  

  MySQL 发行版自带了上面两个压缩工具，不需要进行额外安装。以上就是 mysqlpump 新增的部分常用参数，完整参数可以参考官方文档：mysqlpump — A Database Backup Program

# 安装Percona yum 源
yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
# 安装
yum install percona-xtrabackup-80

xtrabackup --backup  --user=root --password --parallel=3  --target-dir=/data/backups/

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

# 模拟数据异常丢失
rm -rf /usr/app/mysql-8.0.17/data/*
# 将备份文件拷贝到 data 目录下
xtrabackup --copy-back --target-dir=/data/backups/

chown -R mysql:mysql /usr/app/mysql-8.0.17/data

xtrabackup  --user=root --password --backup  --target-dir=/data/backups/base/

xtrabackup  --user=root --password --backup  --target-dir=/data/backups/inc1 \
--incremental-basedir=/data/backups/base

xtrabackup  --user=root --password --backup  --target-dir=/data/backups/inc2 \
--incremental-basedir=/data/backups/inc1

• 准备基础备份：

  xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base
  

• 将第一次备份作用于全备数据：

  xtrabackup --prepare --apply-log-only --target-dir=/data/backups/base \
  -- incremental-dir=/data/backups/inc1
  

• 将第二次备份作用于全备数据：

  xtrabackup --prepare --target-dir=/data/backups/base \
  -- incremental-dir=/data/backups/inc2
  

xtrabackup --copy-back --target-dir=/data/backups/base
# 必须修改文件权限，否则无法启动
chown -R mysql:mysql /usr/app/mysql-8.0.17/data

mysqlbinlog --read-from-remote-server --raw --stop-never \
--host=主机名 --port=3306 \
--user=用户名 --password=密码  初始复制时的日志文件名

• Chapter 7 Backup and Recovery
• mysqldump — A Database Backup Program
• mysqlpump — A Database Backup Program
• Percona XtraBackup - Documentation