超详细的数据库备份

建立数据库后，下一个重要的事情就是设置备份。做备份的方法很多，但主要有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_da