备份

建立数据库后，下一个重要的事情就是设置备份。做备份的方法很多，但主要有2种，一种是逻辑备份，它将所有数据库、表结构、数据和存储例程导出到一组可以再次执行的SQL语句中，以重新创建数据库的状态；另一种是物理备份，它包含了系统上的所有文件，这里的系统是指数据库用于存储所有数据库实体的系统。

逻辑备份工具

mysqldump
mysqlpump
mydumper(不随MySQL提供)

物理备份工具

XtraBackup(不随MySQL提供)
普通文件备份

对于时间点恢复，备份应该能够提供开始做备份之前的二进制日志的位置。这被称为连续的备份。

强烈建议从一个从（slave）服务器备份到mount于其上的文件中。

一、使用mysqldump进行备份

mysqldump是一个广泛使用的逻辑备份工具。它提供了多种选项来包含或排除数据库、选择要备份的特定数据、仅备份不包含数据的schema，或者只备份存储例程而不包括其他任何东西，等等。

mysqldump与mysql二进制文件是一起提供的，不需要单独安装。

语法如下：

shell> mysqldump [options]

在选项中，可以指定连接到数据库的用户名、密码和主机名：

--user <username> --password <password>
或者
-u <username> -p<password>

完整备份所有数据库

shell> mysqldump --all-databases > dump.sql

–all-databases选项支持所有数据库和所有表的备份。

在MySQL8之前，存储过程和事件存储在mysql.proc和mysql.event表中。从MySQL8开始，相应对象的定义存储在数据字典表中，但这些表不会被备份。要将存储过程和事件包含在使用–all-databases创建的备份中，请使用–routines和–events选项。

shell> mysqldump --all-databases --routines --events > dump.sql

可以打开dump.sql文件查看它的结构。前几行是转储时的会话变量。接下来是CREATE DATABASE语句，后面跟着USE DATABASE命令。再接下来是DROP TABLE IF EXISTS语句，后面跟着CREATE TABLE,然后是插入数据的INSERT语句。由于数据被转储为SQL语句，因此这称为逻辑备份。

可以看到，当还原dump时，DROP TABLE语句将在创建表之前清除所有表。

时间点恢复

要获得时间点恢复，应该指定–single-transaction和–master-data。

–single-transaction选项在执行备份之前，通过将事务隔离模式更改为REPEATABLE READ模式，并执行START TRANSACTION来提供一致的备份。–single-transaction 选项仅适用于诸如InnoDB之类的事务表，因为它在START TRANSACTION执行时能保存数据库的一致状态而不阻塞任何应用程序。

–master-data 选项将服务器的二进制日志的位置输出到dump文件。如果–master-data = 2,它将打印为注释。它也使用FLUSH TABLES WITH READ LOCK语句来获取二进制日志的快照。当存在任何复杂事务时，这样做可能非常危险：

shell> mysqldump --all-databases --routines --events --single-transaction --master-data > dump.sql

保存主库二进制日志位置

备份始终在从服务器上进行。要获取备份时主服务器的二进制日志位置，可以使用–dump-slave选项。如果在从服务器上进行二进制日志备份，请使用此选项。否则，请使用–master-data选项：

shell> mysqldump --all-databases --routines --events --single-transaction --dump-slave > dump.sql

指定数据库和表

要仅备份指定的数据库，请执行以下操作：

[root@www ~]# mysqldump -uroot -pCom.123456 --databases employees > employees_backup.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@www ~]# ls -l employees_backup.sql 
-rw-r--r-- 1 root root 168377742 Oct  7 22:19 employees_backup.sql

要仅备份指定的表，请执行以下操作：

[root@www ~]# mysqldump -uroot -pCom.123456 --databases employees --tables employees > table_employees_backup.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@www ~]# ls -lh table_employees_backup.sql 
-rw-r--r-- 1 root root 17M Oct  7 22:21 table_employees_backup.sql

忽略表

要忽略某些表，可以使用–ignore-table=database.table选项。如果指定多个要忽略的表，可以多次使用该指令：

[root@www ~]# mysqldump -uroot -pCom.123456 --databases employees --ignore-table=employees.salary > employees_backup.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@www ~]# ls -lh employees_backup.sql 
-rw-r--r-- 1 root root 161M Oct  7 22:26 employees_backup.sql

指定行

mysqldump可以帮助你过滤备份的数据。假设你想对2000年之后加入的员工的信息进行备份：

[root@www ~]# mysqldump -uroot -p --databases employees --tables employees  --where="hire_date>'2000-01-01'" > employees_after_2000.sql
Enter password: 
[root@www ~]# ls -lh employees_after_2000.sql 
-rw-r--r-- 1 root root 3.1K Oct  7 22:33 employees_after_2000.sql

可以使用LIMIT子句来限制结果集：

[root@www ~]# mysqldump -uroot -p --databases employees --tables employees  --where="hire_date>'2000-01-01' LIMIT 10" > employees_after_2000_limit_10.sql
Enter password: 
[root@www ~]# ls -lh employees_after_2000_limit_10.sql 
-rw-r--r-- 1 root root 3.0K Oct  7 22:38 employees_after_2000_limit_10.sql

从远程服务器备份

有时，你可能没有SSH访问数据库服务器的权限。在这种情况下，可以使用mysqldump从远程服务器备份到本地服务器。需要使用–hostname选项指定主机名。要确保用户具有适当的连接和执行备份的权限：

shell> mysqldump --all-databases --routines --events --triggers  --hostname <remote_hostname> > dump.sql

仅备份不包含数据的schema

可以使用–no-data仅备份不包含数据的schema:

shell> mysqldump --all-databases --routines --events --triggers --no-data > schema.sql

仅备份不包含schema的数据

可以使用以下选项仅备份不包含schema的数据。

–complete-insert将在INSERT语句中打印列名，如果修改的表中有更多列，这样做是有好处的：

shell> mysqldump --all-databases --no-create-db --no-create-info  --complete-insert > data.sql

二、使用mysqlpump进行备份

mysqlpump是一个非常类似于mysqldump的程序，但它具有一些额外的功能。

并行处理

可以通过指定线程数量（根据CPU数量）加速备份过程。例如，使用8个线程进行完整备份：

[root@www ~]# mysqlpump -uroot -pCom.123456 --default-parallelism=8 > full_backup.sql
[root@www ~]# ls -lh full_backup.sql 
-rw-r--r-- 1 root root 168M Oct 27 22:46 full_backup.sql

甚至可以指定每个数据库的线程数。

[root@www ~]# mysqlpump -uroot -pCom.123456 --parallel-schemas=4:employees \
> --default-parallelism=2 > full_backup.sql

还可以分配的更细：

shell> mysqlpump --parallel-schemas=3:db1,db2 --parallel-schemas=2:db3,db4 \
--default-parallelism=4 > full_backup.sql

使用正则表达式排除/包含数据库对象

对以prod结尾的所有数据库进行备份：

shell> mysqlpump --include-databases=%prod --result-file=db_prod.sql

假设某些数据库中有一些测试表，希望将它们从备份中排除。可以使用–exclude-tables选项来指定，该选项将排除所有数据库中名称为test的表：

[root@www ~]# mysqlpump -uroot -pCom.123456 --exclude-tables=test --result-file=backup_excluding_test.sql

每个包含和排除选项的值都是适当对象类型以逗号分隔的名称列表。允许在对象名称中使用通配符：

%匹配零个或多个字符的任何序列
_匹配任何单个字符

除了数据库和表，还可以包含或排除触发器、例程、事件和用户，例如–include-routines，–include-events和–exclude-triggers。

备份用户

在mysqldump中，不会在CREATE USER或GRANT语句中获得用户的备份，必须备份mysql.user表。使用mysqlpump,可以将用户账号备份为账户管理语句（CREATE USER和GRANT）,而不是将用户账号插入mysql系统数据库中：

[root@www ~]# mysqlpump -uroot -pCom.123456 --exclude-databases=% --users > users_backup.sql

还可以通过指定–exclude-users选项来排除某些用户：

[root@www ~]# mysqlpump -uroot -pCom.123456 --exclude-databases=% --exclude-users=root --users > users_backup.sql

三. 使用mydumper进行备份

mydumper是一个类似mysqlpump的逻辑备份工具。与mysqldump相比，mydumper在以下方面具有优势：

并行（因此速度更快）和性能（避免使用复杂的字符集转换例程，因而代码总体上很高效）。
一致性。mydumper维护所有线程的快照，提供准确的主库和从库日志位置等。mysqlpump不保证一致性。
更易于管理输出（将表和元数据文件分离，并且方便查看/解析数据）。mysqlpump将所有内容写入一个文件，这限制了加载部分数据库对象的选项。
使用正则表达式包含和排除数据库对象。
有用于终止阻塞备份和所有后续查询的长事务的选项。

mydumper是一款开源的备份工具，需要单独安装。

[root@www ~]# wget -c https://github.com/maxbube/mydumper/releases/download/v0.9.5/mydumper-0.9.5-2.el7.x86_64.rpm
[root@www ~]# yum localinstall mydumper-0.9.5-2.el7.x86_64.rpm

完全备份

以下命令会将所有数据库备份到backups文件夹中：

[root@www backups]# mydumper -u root -p Com.123456 --outputdir ./backups

多个文件将在backups文件夹中被创建
每个数据库的CREATE DATABASE语句均为-schema-create.sql
每个表都有自己的schema和数据文件。schema文件存储为<database_name>.-schema.sql;数据文件存储为<database_name>.
视图存储为<database_name>.
存储例程、触发器和事件存储为<database_name>-schema-post.sql
如果有任何查询超过60秒，mydumper将失败并出现错误提示，可以使用–kill-long-queries选项或将–long-query-guard设置为更大的值。

一致的备份

备份目录中的元数据文件包含用于一致备份的二进制日志坐标。
在主服务器上，备份目录中的元数据文件会捕获二进制日志位置：

[root@www backups]# cat ../backups/metadata 
Started dump at: 2019-10-28 22:05:32
SHOW MASTER STATUS:
	Log: server1.000052
	Pos: 155
	GTID:

Finished dump at: 2019-10-28 22:05:37

在从服务器上，备份目录中的元数据文件会捕获主服务器和从服务器的二进制日志位置。

备份单独表

以下命令会将employees数据库的employees表备份backups/employees_table目录

[root@www backup]# mydumper -u root -p Com.123456 -B employees -T employees \
> --triggers --events --routines --outputdir ./backups/employees_table
[root@www backup]# ls -l backups/employees_table/
total 17324
-rw-r--r-- 1 root root      762 Oct 28 22:26 employees.employees-schema.sql
-rw-r--r-- 1 root root 17724225 Oct 28 22:26 employees.employees.sql
-rw-r--r-- 1 root root      133 Oct 28 22:26 employees-schema-create.sql
-rw-r--r-- 1 root root      134 Oct 28 22:26 metadata

使用正则表达式来备份特定的数据库

可以使用regex选项包含/排除特定数据库。以下命令将从备份中排除mysql和test开头的数据库：

[root@www backup]# mydumper -u root -p Com.123456 --regex '^(?!(mysql|test))' \
> --outputdir ./backups/specific_dbs

采用mydumper备份大表

为了加速大表的转储和恢复，可以将它分成小块。块的大小可以通过它包含的行数来指定，每个块将被写入一个单独的文件中。

[root@www backup]# mydumper -u root -p Com.123456 -B employees -T employees \
> --triggers --events --routines --rows=10000 -t 8 \
> --trx-consistency-only --outputdir ./backups/employee_table_chunks

-t：指定线程的数量
–trx-consistency-only：如果只使用事务表，例如InnoDB,那么使用此选项将使锁定最小化
–rows：将表分成这些行的块。

无阻塞备份

为了提供一致的备份，mydumper通过执行FLUSH TABLES WITH READ LOCK来获取GLOBAL LOCK。
如果有任何长时间运行的事务，使用FLUSH TABLES WITH READ LOCK是很危险的。为了避免这种情况，可以传递–kill-long-queries选项来终止阻塞查询，而不是中止mydumper。

压缩备份

可以指定–compress选项来进行压缩备份

仅备份数据

可以使用–no-schemas选项来跳过schema并且仅备份数据

四. 使用普通文件进行备份

这是一种物理备份方法，可以通过直接复制数据目录中的文件来进行备份。由于在复制文件时写入了新数据，因此备份将不一致并且无法使用。为了避免这种情况，必须先关闭MySQL，复制文件，然后启动MySQL。此方法不适用于每日备份，但非常适合在维护时段进行升级或降级时使用，或者在进行主机交换时使用。

关闭MySQL数据库服务器；
将文件复制到数据目录中：

[root@www backup]# rsync -av /var/lib/mysql/ /root/backup/mysql/

也可以备份到远程主机上

shell> rsync -e ssh -az /var/lib/mysql/ backup_user@remote_server:/backups

启动MySQL数据库服务。

五. 使用二进制日志进行备份

二进制日志是时间点恢复所需要的。如何备份二进制日志呢？该进程将二进制日志从数据库服务器流式传输到远程备份服务器。即可以从从服务器也可以从主服务器进行二进制日志备份。如果从从服务器进行二进制日志备份，并在从服务器进行实际备份，则应使用–dump-slave获取相应的主日志位置，如果使用的是mydumper或XtraBackup,则主和从二进制日志位置会被同时提供。

在服务器上创建一个复制用户，并设置一个强密码：

mysql> create user 'binlog_user'@'%' IDENTIFIED \
    -> with mysql_native_password by 'Com.123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant replication slave on *.* to 'binlog_user'@'%';
Query OK, 0 rows affected (0.00 sec)

检查服务器上的二进制日志：

mysql> show binary logs;
+----------------+-----------+-----------+
| Log_name       | File_size | Encrypted |
+----------------+-----------+-----------+
| server1.000064 |       155 | No        |
| server1.000065 |       665 | No        |
+----------------+-----------+-----------+
2 rows in set (0.00 sec)

登录到备份服务器并执行以下命令，会将二进制日志从MySQL服务器复制到备份服务器。你可以使用nohup或disown：

[root@ns1 ~]# mkdir /mysqlbinlog_backup
[root@ns1 ~]# cd /mysqlbinlog_backup/
[root@ns1 mysqlbinlog_backup]# mysqlbinlog -ubinlog_user -pCom.123456 -h 192.168.154.137 \
> --read-from-remote-server --stop-never --to-last-log --raw server1.000064 &
[root@ns1 mysqlbinlog_backup]# disown -a

验证是否正在备份二进制日志：

[root@ns1 mysqlbinlog_backup]# ls -l 
总用量 8
-rw-r-----. 1 root root 155 11月  1 23:46 server1.000064
-rw-r-----. 1 root root 665 11月  1 23:46 server1.000065

使用XtraBackup进行备份

XtraBackup是由Percona提供的开源备份软件。它能在不关闭服务器的情况下复制普通文件。但为了避免不一致，它会使用REDO日志文件。XtraBackup被许多公司广泛用作标准备份工具。与逻辑备份工具相比，其优势是备份速度非常快，恢复速度也非常快。

工作原理

XtraBackup复制InnoDB数据文件，这会导致内部不一致的数据，但是它会对文件执行崩溃恢复，以使其再次成为一个一致的可用数据库。
这样做是可行的，因为InnoDB维护一个REDO日志，也称为事务日志。REDO日志包含了InnoDB数据每次更改的记录。当InnoDB启动时，REDO日志会检查数据文件和事务日志，并执行两个步骤。它将已提交的事务日志条目应用于数据文件，并对任何修改了数据但未提交的事务执行undo操作。
XtraBackup会在启动时记住日志序列号（LSN）,然后复制数据文件。这需要一些时间来完成，如果文件正在改变，那么他们会在不同的时间点反映数据库的状态。同时，XtraBackup运行一个后台进程，用于监视事务日志文件，并从中复制更改。XtraBackup需要持续这样做，因为事务日志是以循环方式写入的，并且可以在一段时间后重新使用。XtraBackup开始执行后，需要复制每次数据文件更改对应的事务日志记录。

安装

访问官网：https://www.percona.com

[root@www ~]# wget -c https://www.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0-7/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm
[root@www ~]# yum localinstall percona-xtrabackup-80-8.0.7-1.el7.x86_64.rpm -y

全量备份和恢复

全量备份

[root@www ~]# xtrabackup --user=root --password=Com.123456 --backup --socket=/var/lib/mysql/mysql.sock --target-dir=/xtrabackup
[root@www ~]# ls -l  /xtrabackup/
total 77896
drwxr-x--- 2 root root       27 Oct 29 22:52 archive
-rw-r----- 1 root root      477 Oct 29 22:52 backup-my.cnf
drwxr-x--- 2 root root       25 Oct 29 22:52 bank
drwxr-x--- 2 root root      132 Oct 29 22:52 employees
-rw-r----- 1 root root    24638 Oct 29 22:52 ib_buffer_pool
-rw-r----- 1 root root 12582912 Oct 29 22:52 ibdata1
drwxr-x--- 2 root root      222 Oct 29 22:52 mysql
-rw-r----- 1 root root 24117248 Oct 29 22:52 mysql.ibd
drwxr-x--- 2 root root     8192 Oct 29 22:52 performance_schema
drwxr-x--- 2 root root       20 Oct 29 22:52 prod
-rw-r----- 1 root root      155 Oct 29 22:52 server1.000054
-rw-r----- 1 root root       35 Oct 29 22:52 server1.index
drwxr-x--- 2 root root       28 Oct 29 22:52 sys
drwxr-x--- 2 root root       22 Oct 29 22:52 test1
drwxr-x--- 2 root root       42 Oct 29 22:52 test2
drwxr-x--- 2 root root       40 Oct 29 22:52 testdb
drwxr-x--- 2 root root       21 Oct 29 22:52 test_db
-rw-r----- 1 root root 25165824 Oct 29 22:52 undo_001
-rw-r----- 1 root root 17825792 Oct 29 22:52 undo_002
-rw-r----- 1 root root       19 Oct 29 22:52 xtrabackup_binlog_info
-rw-r----- 1 root root       99 Oct 29 22:52 xtrabackup_checkpoints
-rw-r----- 1 root root      507 Oct 29 22:52 xtrabackup_info
-rw-r----- 1 root root     2560 Oct 29 22:52 xtrabackup_logfile
-rw-r----- 1 root root      248 Oct 29 22:52 xtrabackup_tablespaces

备份时间长短根据数据库大小有关，在备份的期间可以随时取消，因为xtrabackup命令不会对数据库进行操作。

全量恢复

停止数据库

[root@www ~]# systemctl stop mysqld.service

[root@www ~]# rm -rf /var/lib/mysql/*

执行恢复命令

[root@www ~]# xtrabackup --prepare --target-dir=/xtrabackup/
[root@www ~]# xtrabackup --copy-back --target-dir=/xtrabackup/

对文件授权并启动服务

[root@www ~]# chown -R mysql:mysql /var/lib/mysql/*
[root@www ~]# systemctl start mysqld.service

–copy-back命令表示将备份复制到datadir目录下，如果不想保留备份，可以使用–move-back命令，直接将备份移动到datadir目录下。

增量备份

在进行增量备份之前，通过先进行一次全量备份。XtraBackup通过二进制方式在备份目录下写入xtrabackup_checkpoints文件。该文件其中一行会显示to_lsn，该参数记录了数据库备份完成的LSN(日志序列号)。

方式一

基于base的备份

[root@www ~]# xtrabackup --user=root --password=Com.123456 --backup --socket=/var/lib/mysql/mysql.sock --target-dir=/xtrabackup/base

xtrabackup_checkpoints的内容:

[root@www ~]# cat /xtrabackup/base/xtrabackup_checkpoints 
backup_type = full-backuped   #备份类型
from_lsn = 0
to_lsn = 2621995904
last_lsn = 2621995904    #最后的LSN
flushed_lsn = 0

在进行了全量备份后，我们可以通过增量备份的命令进行备份：

基于base的incr1备份

[root@www ~]# xtrabackup --user=root --password=Com.123456 --backup --socket=/var/lib/mysql/mysql.sock \
> --target-dir=/xtrabackup/incr1 --incremental-basedir=/xtrabackup/base

xtrabackup_checkpoints的内容:

[root@www ~]# cat /xtrabackup/incr1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2621995904
to_lsn = 2622011882
last_lsn = 2622011882
flushed_lsn = 0

基于incr1的incr2备份

[root@www ~]# xtrabackup --user=root --password=Com.123456 --backup --socket=/var/lib/mysql/mysql.sock \
> --target-dir=/xtrabackup/incr2 --incremental-basedir=/xtrabackup/incr1

xtrabackup_checkpoints的内容:

[root@www ~]# cat /xtrabackup/incr2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2622011882
to_lsn = 2622028259
last_lsn = 2622028259
flushed_lsn = 0

结论：假设周一是基于base的备份，周二是基于base的incr1备份，周三是基于incr1的incr2备份，在恢复数据库的时候，需要使用base，incr1，incr2三个备份都存在时，才能进行完整的恢复，每个备份的from_lsn都是基于上一个备份的to_lsn，所以缺一不可。

方式二：

基于base的备份

[root@www ~]# xtrabackup --user=root --password=Com.123456 --backup --socket=/var/lib/mysql/mysql.sock \
> --target-dir=/xtrabackup/base

[root@www ~]# cat /xtrabackup/base/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 2622028259
last_lsn = 2622028259
flushed_lsn = 0

基于base的incr1备份:

[root@www ~]# xtrabackup --user=root --password=Com.123456 --backup --socket=/var/lib/mysql/mysql.sock \
> --target-dir=/xtrabackup/incr1 --incremental-basedir=/xtrabackup/base

[root@www ~]# cat /xtrabackup/incr1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2622028259
to_lsn = 2622036184
last_lsn = 2622036184
flushed_lsn = 0

基于base的incr2备份：

[root@www ~]# xtrabackup --user=root --password=Com.123456 --backup --socket=/var/lib/mysql/mysql.sock \
> --target-dir=/xtrabackup/incr2 --incremental-basedir=/xtrabackup/base

[root@www ~]# cat /xtrabackup/incr2/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2622028259
to_lsn = 2622043407
last_lsn = 2622043407
flushed_lsn = 0

结论：假设周一是基于base的备份，周二是基于base的incr1备份，周三是基于base的incr2备份，在恢复数据库的时候，需要使用base和incr1，incr2两个备份中的其中一个，才能进行完整的恢复，因为incr1和incr2的from_lsn都是基于base备份中的to_lsn，所以恢复数据库时，只需要base和任意一个基于base的增量备份。

增量备份恢复

增量备份和全量备份的–prepare执行的方式是不一样的。在全量备份中，可以通过两种操作保持数据库的一致性:已提交的事务将根据数据文件和日志文件进行重放操作，并回滚未提交的事务。在准备增量备份时，必须跳过未提交事务的回滚，因为在备份的过程中，可能存在进行中且未提交的事务，并且这些事务很可能在下一次的增量备份中才进行提交，所以必须使用–apply-log-only选项来防止回滚操作。

基于方式一的恢复

执行恢复命令

[root@www ~]# xtrabackup --user=root --password=Com.123456 --prepare --apply-log-only --target-dir=/xtrabackup/base
[root@www ~]# xtrabackup --user=root --password=Com.123456 --prepare --apply-log-only \
> --target-dir=/xtrabackup/base --incremental-dir=/xtrabackup/incr1
[root@www ~]# xtrabackup --user=root --password=Com.123456 --prepare  \
> --target-dir=/xtrabackup/base --incremental-dir=/xtrabackup/incr2
[root@www ~]# xtrabackup --copy-back --target-dir=/xtrabackup/base/

[root@www ~]# chown -R mysql:mysql /var/lib/mysql

基于方式二的恢复

恢复命令

[root@www ~]# xtrabackup --user=root --password=Com.123456 --prepare --apply-log-only --target-dir=/xtrabackup/base
[root@www ~]# xtrabackup --user=root --password=Com.123456 --prepare \
> --target-dir=/xtrabackup/base --incremental-dir=/xtrabackup/incr2
[root@www ~]# xtrabackup --copy-back --target-dir=/xtrabackup/base/

[root@www ~]# chown -R mysql:mysql /var/lib/mysql

重要

–apply-log-only命令应该用在所有增量备份（除最后一次增量备份），这就是为什么恢复脚本中，最后一次的命令不包括–apply-log-only。即使–apply-log-only在最后一次增量备份时被使用，备份仍将是一致的，但在这种情况下，数据库会执行回滚的操作。

恢复数据

本章将学习各种备份恢复方法。这里假定备份和二进制日志（binary log）在服务器上是可用的。

从mysqldump和mysqlpump中恢复

逻辑备份工具mysqldump和mysqlpump将数据写入单个文件。

如何操作

登录备份所在的服务器：

shell> cat /backups/full_backup.sql | mysql -u <user> -p
或者
shell> mysql -u <user> -p < /backups/full_backup.sql

要在远程服务器上恢复，可以使用-h <主机名> 选项：

shell> cat /backups/full_backup.sql | mysql -u <user> -p -h <remote_hostname>

当恢复一个备份时，该备份的语句将被记录到二进制日志中，这可能会拖慢恢复过程。如果不希望恢复过程被写入二进制日志，则可以使用 SET SQL_LOG_BIN = 0;选项在session(会话)级别关闭这个功能：

shell> (echo "SET SQL_LOG_BIN = 0;"; cat /backups/full_backup.sql) | mysql \
> -u <user> -p -h <remote_hostname>
或者使用
mysql> SET SQL_LOG_BIN=0; source full_backup.sql

使用myloader从mydumper中恢复

myloader是多线程恢复mydumper备份集的工具。myloader与mydumper是一起的，不需要单独安装。

如何操作

myloader的常用选项有：要连接的MySQL服务器的主机名（默认为localhost）、用户名、密码和端口。

恢复完整的数据库

shell> myloader --directory=/backups --user=<user> --password=<password> \
> --queries-per-transaction=5000 --threads=8 --compress-protocol --overwrite-tables

–overwrite-tables：这个选项会删除已经存在的表；
–compress-protocol：该选项在MySQL连接上使用压缩；
–threads：该选项指定要使用的线程数量，默认值是4；
–queries-per-transaction：指定每个事务的查询数量，默认值是1000；
–directory：指定要导入的转储目录。

恢复单个数据库

可以指定–source-db <db_name>,仅恢复单个数据库。假设你想恢复company数据库：

shell> myloader --directory=/backups --queries-per-transaction=5000 --threads=6 \
> --compress-protocol --user=<user> --password=<password> \
> --source-db company --overwrite-tables

恢复单个表

mydumper将每个表的备份写入单独的.sql文件。你可以选择这个.sql文件并恢复：

shell> mysql -u <user> -p<password> -h <hostname> company -A -f < company.payments.sql

如果这个表被拆分为chunk(块)，则可以将与这个表相关的所有chunk和信息复制到一个目录并指定其位置。
复制所需的文件：

shell> cp /backups/employee_table_chunks/employees.employees.* \
/backups/employee_table_chunks/employees.employees-schema.sql \
/backups/employee_table_chunks/employees-schema-create.sql \
/backups/employee_table_chunks/metadata \
/backups/single_table/

使用myloader加载，它会自动检测chunk并加载它们：

shell> myloader --directory=/backups/single_table/ --queries-per-transaction=5000 --threads=6 \
> --compress-protocol --overwrite-tables

从普通文件备份中恢复

从普通文件备份中恢复，需要先关闭MySQL服务器，替换所有文件，更改权限，然后再启动MySQL。

停止MySQL服务器的运行；
将文件移至数据目录：

shell> mv /backup/mysql /var/lib

将所有权更改为mysql:

shell> chown -R mysql:mysql /var/lib/mysql

启动MySQL。

执行时间点恢复

一旦恢复完整的备份后，仍需要恢复二进制日志以获得时间点（point-in-time）恢复。备份集提供截止到备份可用时的二进制日志坐标。
应该根据mysqldump中指定的–dump-slave或–master-data选项从备份所在的服务器中选择二进制日志备份。

如何操作

根据情况不同，有很多操作细节需要注意

mysqldump或mysqlpump

根据你传给mysqldump/mysqlpump的选项，二进制日志信息被作为CHANGE MASTER TO 命令存储在SQL文件中。

如果你用了–master-data,则应使用从服务器的二进制日志：

shell> head -30 /backups/dump.sql
....略
-- Position to start replication or point-in-time recovery from
--
CHANGE MASTER TO MASTER_LOG_FILE='server1.000008', MASTER_LOG_POS=154;

在这种情况下，应该从位于从服务器，位置为154处的server1.000008文件开始恢复。

shell> mysqlbinlog --start-position=154 --disable-log-bin \
> /backups/binlogs/server1.000008 | mysql -u<user> -p -h <host> -f

如果你用了–dump-slave,则应该使用主服务器上的二进制日志：

--
-- Position to start replication or point-in-time recovery from (the master of this slave)
--
CHANGE MASTER TO MASTER_LOG_FILE='centos7-bin.000001',MASTER_LOG_POS=463;

在这种情况下，应该从位于主服务器，位置为463处的centos7-bin.000001文件开始恢复。

shell> mysqlbinlog --start-position=463 --disable-log-bin \
> /backups/binlogs/centos7-bin.000001 | mysql -u<user> -p -h <host> -f

mydumper

二进制日志信息可以从元数据中获取：

[root@www backups]# cat ../backups/metadata 
Started dump at: 2019-10-28 22:05:32
SHOW MASTER STATUS:
	Log: server1.000012
	Pos: 155
	GTID:
SHOW SLAVE STATUS:
    Host: 35.186.158.188
    Log: centos7-bin.000001
    Pos: 463
    GTID:
Finished dump at: 2019-10-28 22:05:37

如果你已经从从服务器中获取二进制日志备份，则应从位置为155（SHOW MASTER STATUS）的server1.000012文件开始恢复：

shell> mysqlbinlog --start-position=155 --disable-log-bin  \
> /backups/binlogs/server1.000012 | mysql -u<user> -p -h <host> -f

如果你有来自主服务器的二进制日志备份，则应从位置为463（SHOW SLAVE STATUS）的centos7-bin.000001文件开始恢复：

shell> mysqlbinlog --start-position=463 --disable-log-bin \
> /backups/binlogs/centos7-bin.000001 | mysql -u<user> -p -h <host> -f

文章目录

备份

逻辑备份工具

物理备份工具

一、使用mysqldump进行备份

完整备份所有数据库

时间点恢复

保存主库二进制日志位置

指定数据库和表

忽略表

指定行

从远程服务器备份

仅备份不包含数据的schema

仅备份不包含schema的数据

二、使用mysqlpump进行备份

并行处理

使用正则表达式排除/包含数据库对象

备份用户

三. 使用mydumper进行备份

完全备份

一致的备份

备份单独表

使用正则表达式来备份特定的数据库

采用mydumper备份大表

无阻塞备份

压缩备份

仅备份数据

四. 使用普通文件进行备份

五. 使用二进制日志进行备份

使用XtraBackup进行备份

工作原理

安装

全量备份和恢复

全量备份

全量恢复

增量备份

方式一

方式二：

增量备份恢复

基于方式一的恢复

基于方式二的恢复

重要

恢复数据

从mysqldump和mysqlpump中恢复

如何操作

使用myloader从mydumper中恢复

如何操作

恢复完整的数据库

恢复单个数据库

恢复单个表

从普通文件备份中恢复

执行时间点恢复

如何操作

mysqldump或mysqlpump

mydumper

相关文章

最近文章

分类

标签

友情链接

其它