备份

  |   0 评论   |   0 浏览

前言

做备份的方法主要有两种,一种是逻辑备份它将所有数据库、表结构、数据和是存储历程导出到一组可以再次执行的 SQL 语句种,以重新创建数据库的状态;另一种是物理备份,它包含了系统上的所有文件,这里的系统是指数据库用于存储所有数据库的实体的系统

  • 逻辑备份工具: mysqldump、mysqlpump 和 mydumper(不随 MySQL 提供)
  • 物理备份工具: XstraBackup(不随 MySQL 提供)和普通文件备份。

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

使用 mysqldump 进行备份

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

语法如下:

mysqldump [options]

在该选项种,你可以指定连接数据库的用户名、密码和主机名

--user <user_name> --password <password>
或
-u <user_name> -p<password>

完整备份所有数据库

完整备份所有数据库可以通过以下方式完成:

mysqldump --all-databases > dump.sql

--all-databases 选项支持所有数据库和所有表的备份。 > 运算符将输出重定向到 dump.sql 文件。

在 MySQL8 之前,存储过程和事件存储在 MySQL.proc 和 MySQL.event 表种。从 MySQL8 开始,相应的对象的定义存储在数据字典种,但这些表不会被备份

要将存储过程和事件包含在使用 --all-databases 创建的备份种,请使用 --routines 和 --events 选项。

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

时间点恢复

要获得时间点恢复,应该指定 --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 语句来获取二进制日志的快照。当存在任何复杂事务时,这样做可能非常危险:

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

保存主库二进制日志位置

当在从服务器上进行备份时。要获取备份时主服务器的二进制日志位置,可以使用 --dump-slave 选项。如果在主服务器上备份,请使用 --master-data 选项:

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

指定数据库和表

要仅备份指定的数据库

mysqldump --databases employees > employees_backup.sql

要仅备份指定的表

mysqldump --databases employeess --tables employees > employees_backup.sql

忽略表

要忽略某些表,可以使用 --ignore-table = databases.table 选项。如果指定多个要忽略的表,请多次使用该指令:

mysqldump --databases employees --ignore-table=employees.salary > employees_backup.sql

指定行

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

mysqldump --databases employees --tables employees --databases employees --tables employees --where="hire_date>'2000-01-01'" > employees-after_2000.sql

从远程服务器备份

有时,你可能没有 SSH 访问数据库服务的权限。在这些情况下,可以使用 MySQLdump 从远程服务器备份到本地服务器。为此你需要使用 --hostname 选项提及主机名。确保用户具有适当的连接和执行备份的权限:

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

用于重建另一个具有不同 schema 的服务器的备份

我们有时候会希望在另一台服务器上拥有不同的 schema。在这种情况下,你必须备份和恢复 schema,根据需要更改 schema,然后备份并恢复数据。使用数据更改 schema 可能需要很长时间,具体取决与数据量。请注意,只有当修改后的 schema 与插入语句兼容时,此方法才生效。修改后的表可以有多余的列,但它应该包含原始表种的所有列。

仅备份不包含数据的 schema

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

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

仅备份不包含 schema 的数据

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

--complete-insert 将在 INSERT 语句种打印列名,如果修改的表中有更多列,这样做时有好处的:

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

用于与其他服务器合并数据的备份

可以通过备份来替换旧数据,或在发生冲突时保留旧数据。

用新数据替换

假设你想要将数据从生产数据库恢复到已有一些数据的开发服务器。如果要将生产数据合并到开发过程中,可以使用 --replace 选项,该选项将使用 REPLACE INTO 语句而不是 INSERT 语句。还应该包含 --skip-add-drop-table 选项,该选项不会将 DROP TABLE 语句写入 dump 文件。如果拥有相同数量的表和结构,则还可以包含 --no-create-info 选项,该选项将跳过 dump 文件中的 CREATE TABLE 语句:

mysqldump --databases employees --skip-add-drop-table --no-create-info --replace > to_development.sql

如果在生产服务器中有一些额外的表,则之前的 dump 在恢复时将会失败,因为该表在服务器上不存在。在这种情况下,你不应该添加 --no-create-info 选项,而应在恢复时使用 force 选项。否则,在 CREATE TABLE 中恢复将失败,表示该表已存在。

忽略数据

在写入 dump 文件时可以使用 INSERT IGNORE 语句代替 REPLACE 。这将保留服务器上的现有数据并插入新数据。

使用 MySQLpump 进行备份

mysqlpump 是一个类似于 mysqldump 的程序,但它带有一些额外的功能。

如何操作

并行处理

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

mysqlpump --default-parallelism=8 > full_backup.sql

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

mysqlpump -u root --password --parallel-schemas=4:employees --default-parallelism=2 > full_backup.sql

再来看一个分配线程的实例,其中 3 个线程用于 db1 和 db2 数据库,2 个线程用于 db3 和 db4 数据库,还有 4 个线程用于其他数据库:

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

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

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

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

如果希望排除,可以使用 --exclude-tables 选项来指定,该选项将排除所有数据库中名称为 test 的表

mysqlpump --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 系统数据库中:

mysqlpump --exclude-databases=% --users > users_backup.sql

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

mysqlpump --exclude-databasess=% --exclude-users=root --users > users_backup.sql

压缩备份

可以通过压缩备份来减少磁盘空间和网络带宽的占用。可以使用 --compress-output=lz4 或 --compress-output= zlib。

请注意,你需要有相应的解压缩工具:

mysqlpump -u root -p xxx --compress-output=lz4 > dump.lz4

执行下面的语句进行解压缩:

lz4_decompress dump.lz4 dump.sql

使用 zlib 执行此下面的语句:

mysqlpump -u root -p xxx --compress-output=zlib > dump.zlib

执行下面的语句进行解压缩

zlib_decompress dump.zlib dump.sql

加速重新加载

你会注意到,在输出中,辅助索引从 CREATE TABLE 语句中省略了。这将加速恢复过程。我们将使用 ALTER TABLE 语句在 INSERT 结尾处添加这些索引。

使用普通文件进行备份

这是一种物理备份方法,可以通过直接复制数据目录中的文件来进行备份。由于在复制文件时写入了新数据,因此备份将不一致并且无法使用。为了避免这种情况,必须先关闭 MySQL,复制文件,然后启动 MySQL。

此方法不适用于每日备份,但非常适合在维护时段进行升级或降级时使用,或者在进行主机交换时使用

如何操作

1.关闭 MySQL 服务器

sudo service mysqld stop
  1. 将文件复制到数据目录中(你的目录可能不同):
shell> sudo rsync -av /data/mysql /backups
or do rsync over ssh to remote server
shell> rsync -e ssh -az /data/mysql
backup_user@remote_server:/backups
  1. 启动 MySQL 服务器:
shell> sudo service mysqld start

使用 XtraBackup 进行备份

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

以下是 Percona XtraBackup 的工作原理

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

锁定实例进行备份

从 MySQL 8 开始,我们可以锁定实例进行备份了,这将允许在线备份期间的 DML,并阻止可能导致快照不一致的所有操作。

如何操作

在开始备份之前,请锁定需要备份的实例:

mysql> LOCK INSTANCE FOR BACKUP;

执行备份,完成后解锁实例:

mysql> UNLOCK INSTANCE;

使用二进制日志进行备份

如何操作

1.在服务器上创建要给复制用户,并设置一个强密码:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'binlog_user'@'%'
  1. 检查服务器上的二进制文件:
mysql> SHOW BINARY LOGS;

你可以在服务器上找到第一个可用的二进制日志,可以从这里开始备份。

  1. 登录到备份服务器并执行以下命令,会将二进制日志从 MySQL 服务器复制到备份服务器。你可以使用 nohup 或 disown:
shell> mysqlbinlog -u <user> -p<pass> -h <server> --read-from-remote-server --stop-never --to-last-log --raw server1.000008 &
shell> disown -a
  1. 验证是否正在备份二进制日志
shell> ls -lhtr server1.0000*