MySQL备份和恢复

随着自动化办公与电子商务的不断发展，企业对于信息系统的依赖性越来越高，而数据库在信息系统中担任着非常重要的角色。尤其一些对数据可靠性要求非常高的行业，如银行、证券、电信等，如果发生意外宕机或数据丢失，其损失是非常严重的。为此数据库管理员必须针对具体的业务要求制定详细的数据库备份与灾难恢复的策略，并通过模拟故障对每种可能的情况进行严格的测试，从而保证数据的可靠性

1.1 MySQL数据库备份概述

        备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。

1.1.1 数据备份的重要性

        在企业中数据的价值至关重要，数据保障了企业业务的正常运行。因此，数据的安全性及数据的可靠性是运维的重中之重，任何数据的丢失都可能对企业产生严重的后果。通常情况下造成数据丢失的原因有如下几种：

1. 程序错误
2. 人为操作错误
3. 运算错误
4. 磁盘故障
5. 灾难（如火灾、地震）和盗窃

1.1.2 数据备份类型

1. 从物理与逻辑的角度分类

        数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的大型重要数据库。

物理备份又可以为冷备份（脱机备份）、热备份（联机备份）和温备份

• 冷备份：在数据库关闭状态下进行备份操作
• 热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖于数据库的日志文件
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

  逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构和内容的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器体系结构上重新创建数据

  2. 从数据库的备份策略角度分类

  从数据库的备份策略角度，数据库的备份可分为完全备份、差异备份和增量备份

          完全备份：每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份和增量备份基础。完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时间也很长

          差异备份：备注那些自从上次完全备份之后被修改的所有文件，备份的时间节点是从上次完整备份其，备份数据库会越来越大。恢复数据时，只需恢复上次完全备份与最近的一次的差异备份

          增量备份：只有那些在上次完全备份或增量备份后备修改的文件才会备份，以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份数据快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之间的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失

  1.1.3 常见的备份方法

          MySQL数据快的备份可以采用很多种方式，如直接打包数据库文件（物理冷备份）、专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等

  1. 物理冷备份

          物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复时也是最为简单的。通常通过直接打包数据库文件夹（这篇文章中的数据库文件夹位于/usr/local/mysql/data）来实现备份

  2. 专用备份工具mysqldump或mysqlhotcopy

          mysqldump 程序和 mysqlhotcopy 都可以做备份。mysqldump 是客户端常用逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的 SQL 语句。它可以转储一个到多个 MySQL 数据库，对其进行备份或传输到远程 SQL 服务器。mysqldump 更为通用，因为它可以备份各种表。mysqlhotcopy 仅适用于某些存储引擎

          mysqlhotcopy 是由 Tim Bunce 最初编写和贡献的 Perl 脚本。mysqlhotcopy 仅用于备份 MyISAM 和 ARCHIVE 表。它只能运行在 UNIX 或 Linux 上。因为使用范围很小，因此这章中不做详细介绍

  3. 通过启用二进制日志进行增量备份

          MySQL 支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份（包含自上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志

  4. 通过第三方工具备份

          Percona XtraBackup 是一个免费的 MySQL 热备份软件，支持在线热备份 Innodb 和 XtraDB，也可以支持 MySQL 表备份，不过 MyISAM 表的备份要在表锁的情况下进行。本节对于 Percona XtraBackupr 的叙述是基于 2.4 版本的

  Percona XtrBackup 有三个主要的工具：xtrabackup、innobackupex、xbstream

  • xtrabackup：是一个编译了的二进制文件，只能备份 Innodb/Xtradb 数据文件
  • innodbackupex ： 是一个封装了 xtrabackup 的 Perl 脚本 ，除了可以备份 Innodb/Xtradb 之外，还可以备份 MySIAM
  • xbstream：是一个新组件，能够允许将文件格式转成 xbstream 格式或从 xbstream 格式转到文件格式

    xtrabackup 工具可以单独使用，但推荐使用 innobackupex 来进行备份，这是因为 innobackupex 本身就已经包含了 xtrabackup 的所有功能

    xtrabackup 是基于 Innodb 的灾难恢复功能进行设计的，备份工具复制 Innodb 的数据文件。但是，由于不锁表，这样复制出来的数据将不一致。Innodb 维护了一个重做日志，包含 Innodb 数据的所有改动情况。在 xtrabackup 备份 Innodb 数据的同时，xtrabackup 还有另外一个线程用来监控重做日志，一但日志发生变化，就把发生变化的日志数据复制走。这样就可以利用重做日志做灾难恢复了

    Percona XtraBackup 对 MySIAM 的复制，是按这样的一个顺序进行的：首先锁定表，然后复制，再解锁表

    1.2 数据库完全备份操作

            上面提到根据数据库备份策略分类，备份可分为完全备份、差异备份和增量备份。在本章中我们只介绍完全备份与增量备份

    1.2.1 物理冷备份与恢复

            物理冷备份一般用tar命令直接打包数据库文件夹，而在进行备份之前需要使用”systemctl stop mysqld“命令关闭msqld服务

    1. 备份数据库

            创建一个/backup目录作为备份数据存储路径，使用tar创建备份文件。整个数据库文件夹备属于完全备份

    [root@node2 ~]# systemctl stop mysqld[root@node2 ~]# mkdir /backup[root@node2 ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/[root@node2 ~]# ls -l /backup/总用量 1324-rw-r--r-- 1 root root 1351897 3月   5 09:18 mysql_all-2024-03-05.tar.gz[root@node2 ~]#

    2. 恢复数据库

            执行下面操作将数据库文件/usr/local/mysql/data转移至bak目录下，模拟故障

    [root@node2 ~]# mkdir /bak[root@node2 ~]# mv /usr/local/mysql/data/ /bak

            执行下面操作可以从备份文件恢复数据

    [root@node2 ~]# tar zxf /backup/mysql_all-2024-03-05.tar.gz -C restore/[root@node2 ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/[root@node2 ~]# systemctl start mysqld

    1.2.2 mysqldump备份与恢复

            通过 mysqldump 命令可以将指定的库、表或全部的库导出为 SQL 脚本，便于该命令在不同版本的 MySQL 服务器上使用。例如，当需要升级 MySQL 服务器时，可以先使用 mysqldump 命令将原有库信息导出，然后直接在升级后的 MySQL 服务器中导入即可

    1. 备份数据库

            使用 mysqldump 命令导出数据时，默认会直接在终端显示，若要保存到文件，还需要结合 Shell 的“>”重定向输出操作，命令格式如下所示

    格式1：备份指定库中的部分表

    mysqldump    [选项]   库名    [表名1]    [表名2] ... > /备份路径/备份文件名

    格式2：备份一个或多个完整的库（包括其中的所有表）

    mysqldump    [选项]     --database    库名1    [库名2]    ....>   /备份路径/备份文件名

    格式3：备份MySQL服务器中所有的库

    mysqldump    [选项]    --all-databases  >  /备份路径/备份文件名

            其中，常用的选项包括“-u”、“-p”，分别用于指定数据库用户名、密码。例如，以下操作分别使用格式 1、格式 2，将 mysql 库中的 user 表导出为 mysql-user.sql，将整个 auth 库导出为 auth.sql 文件，所有操作均以 root 用户身份验证

    [root@node2 ~]# mysqldump -u root -p mysql user > mysql-user.sqlEnter password: [root@node2 ~]# mysqldump -u root -p --databases auth > auth.sqlEnter password: [root@node2 ~]# 

            若需要备份整个 MySQL 服务器中的所有库，应使用格式 3。当导出的数据量较大的时候，可以添加“--opt”选项以优化执行速度。例如，执行以下操作将创建备份文件 all-data.sql，其中包括 MySQL 服务器中的所有库

    [root@node2 ~]# mysqldump -u root -p --opt --all-databases > all-data.sqlEnter password: [root@node2 ~]#

    2. 查看备份文件

            通过 mysqldump 工具导出的 SQL 脚本是文本文件，其中“/*…*/”部分或以“--”开头的行表示注释信息。使用 grep、less、cat 等文本工具可以查看脚本内容。例如，执行以下操作可以过滤出 auth.sql 脚本中的数据库操作语句

    [root@node2 ~]# grep -v "^--" auth.sql | grep -v "^/" | grep -v "^$"CREATE DATABASE /*!32312 IF NOT EXISTS*/ `auth` /*!40100 DEFAULT CHARACTER SET utf8 */;USE `auth`;                //由于我数据库中没有表所有就没有显示备份[root@node2 ~]# 

    3. 恢复数据库

            使用 mysqldump 命令导出的 SQL 备份脚本，在需要恢复时可以通过 mysql 命令对其进行导入操作，命令格式如下所示

    mysql   [选项]    [库名]    [表名]    create database client;Query OK, 1 row affected (0.00 sec)mysql> use client;Database changedmysql> create table user_info(identitycard char(20) not null,name char(20) not null,gender char(4),UserID char(10) not null,expenses int(10));Query OK, 0 rows affected (0.00 sec)mysql> insert into user_info values('000006','张三','男','016','10');Query OK, 1 row affected (0.01 sec)mysql> insert into user_info values('000007','李四','女','017','91');Query OK, 1 row affected (0.00 sec)mysql> insert into user_info values('000008','王五','女','018','23');Query OK, 1 row affected (0.00 sec)mysql> select * from user_info;+--------------+--------+--------+--------+----------+| identitycard | name | gender | UserID | expenses |+--------------+--------+--------+--------+----------+| 000006 | 张三 | 男 | 016 | 10 || 000007 | 李四 | 女 | 017 | 91 || 000008 | 王五 | 女 | 018 | 23 |+--------------+--------+--------+--------+----------+3 rows in set (0.01 sec)mysql>

    （2）先进行一次完全备份

            为方便验证二进制日志的增量恢复功能，在插入三条用户数据后先对 client 数据库的user_info 表进行一次完全备份。然后在 Linux 系统命令行下执行“mysqladmin -uroot -p flush-logs”命令或在“mysql>”命令提示符下执行“flush logs;”生成新的二进制日志

    [root@node2 ~]# mkdir /mysql_bak[root@node2 ~]# mysqldump -u root -p client user_info > /mysql_bak/client_userinfo-$(date +%F).sqlEnter password: [root@node2 ~]# ls /mysql_bak/client_userinfo-2024-03-05.sql[root@node2 ~]# mysqladmin -u root -p flush-logsEnter password: [root@node2 ~]# ls -l /usr/local/mysql/data/mysql-bin.*-rw-r----- 1 mysql mysql 201 3月   5 10:39 /usr/local/mysql/data/mysql-bin.000001-rw-r----- 1 mysql mysql 154 3月   5 10:39 /usr/local/mysql/data/mysql-bin.000002-rw-r----- 1 mysql mysql  78 3月   5 10:39 /usr/local/mysql/data/mysql-bin.index[root@node2 ~]# 

    （3）继续录入新的数据并进行增量备份

            继续录入两个用户的数据，并执行“mysqladmin -uroot -p flush-logs”命令刷新二进制日志，进行增量备份。如此，二进制日志文件 mysql-bin.000002 中仅保留插入两个用户数据的操作

    mysql> use client;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+------------------+| Tables_in_client |+------------------+| user_info        |+------------------+1 row in set (0.00 sec)mysql> insert into user_info values('000009','赵六','男','019','37');Query OK, 1 row affected (0.02 sec)mysql> insert into user_info values('000010','孙七','男','020','36');Query OK, 1 row affected (0.00 sec)mysql> select * from user_info;+--------------+--------+--------+--------+----------+| identitycard | name   | gender | UserID | expenses |+--------------+--------+--------+--------+----------+| 000006       | 张三   | 男     | 016    |       10 || 000007       | 李四   | 女     | 017    |       91 || 000008       | 王五   | 女     | 018    |       23 || 000009       | 赵六   | 男     | 019    |       37 || 000010       | 孙七   | 男     | 020    |       36 |+--------------+--------+--------+--------+----------+5 rows in set (0.00 sec)[root@node2 ~]# mysqladmin -u root -p flush-logsEnter password: [root@node2 ~]# ls -l /usr/local/mysql/data/mysql-bin.*-rw-r----- 1 mysql mysql 201 3月   5 10:39 /usr/local/mysql/data/mysql-bin.000001-rw-r----- 1 mysql mysql 843 3月   5 10:41 /usr/local/mysql/data/mysql-bin.000002-rw-r----- 1 mysql mysql 154 3月   5 10:41 /usr/local/mysql/data/mysql-bin.000003-rw-r----- 1 mysql mysql 117 3月   5 10:41 /usr/local/mysql/data/mysql-bin.index[root@node2 ~]# cp /usr/local/mysql/data/mysql-bin.000002 /mysql_bak/

    （4）模拟误操作删除user_info表

    [root@node2 ~]# mysql -u root -p -e 'drop table client.user_info;'Enter password: [root@node2 ~]# mysql -u root -p -e 'select * from client.user_info;'Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist[root@node2 ~]# 

    （5）恢复操作

    再执行恢复操作时，需要先恢复完全备份，然后恢复增量备份

    [root@node2 ~]# mysql -u root -p client  
    2. 基于位置恢复
     
    由于前面已经做过备份操作，接下来直接进行模拟故障与数据恢复的操作
     
    [root@node2 ~]# mysql -u root -p -e 'drop table client.user_info;'//模拟故障删除表Enter password: [root@node2 ~]# mysql -u root -p -e 'select * from client.user_info;'//查看user_info表是否存在Enter password: ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist[root@node2 ~]# mysql -u root -p client  
            想要实现基于位置或时间点恢复数据,必须先通过查看二进制日志文件确定恢复的位置或时间点。使用“mysqlbinlog --no-defaults 二进制日志文件”可以查看二进制日志文件的具体内容
     
    [root@node2 ~]# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 ......//输出信息# at 219#240305 10:41:18 server id 1  end_log_pos 302 CRC32 0x6c659e09 Querythread_id=5exec_time=0error_code=0SET TIMESTAMP=1709606478/*!*/;SET @@session.pseudo_thread_id=5/*!*/;SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;SET @@session.sql_mode=1436549152/*!*/;SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;/*!\C utf8 *//*!*/;SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;SET @@session.lc_time_names=0/*!*/;SET @@session.collation_database=DEFAULT/*!*/;BEGIN/*!*/;# at 302#240305 10:41:18 server id 1  end_log_pos 444 CRC32 0xdd76a96a Querythread_id=5exec_time=0error_code=0use `client`/*!*/;SET TIMESTAMP=1709606478/*!*/;insert into user_info values('000009','赵六','男','019','37')/*!*/;# at 444#240305 10:41:18 server id 1  end_log_pos 475 CRC32 0x15c1a91a Xid = 34COMMIT/*!*/;# at 475#240305 10:41:25 server id 1  end_log_pos 540 CRC32 0xcdc07ba9 Anonymous_GTIDlast_committed=1sequence_number=2rbr_only=noSET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;# at 540#240305 10:41:25 server id 1  end_log_pos 623 CRC32 0x38a39cca Querythread_id=5exec_time=0error_code=0SET TIMESTAMP=1709606485/*!*/;BEGIN/*!*/;# at 623#240305 10:41:25 server id 1  end_log_pos 765 CRC32 0x896ad4ed Querythread_id=5exec_time=0error_code=0SET TIMESTAMP=1709606485/*!*/;insert into user_info values('000010','孙七','男','020','36')/*!*/;# at 765#240305 10:41:25 server id 1  end_log_pos 796 CRC32 0x3152fe00 Xid = 35COMMIT/*!*/;# at 796#240305 10:41:58 server id 1  end_log_pos 843 CRC32 0x02b3983b Rotate to mysql-bin.000003  pos: 4SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;DELIMITER ;# End of log file/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;[root@node2 ~]#
     
    通过查看日志文件的具体内容可以发现，在每进行一个操作之前都会有一个独特的编号，如“# at 623”。此编号随着操作数增多而变大，我们称之为操作 ID。在操作 ID 下面紧跟着的是时间标记。要实现基于位置或时间点恢复数据，需要分别依赖二进制日志文件中的操作 ID 或者时间标记。例如，通过二进制日志文件得知，在操作 ID 为“623”的时候，user_info 表中插入了“孙七”的用户数据。因此执行以下命令可以实现仅恢复到操作 ID 为“623”之前的数据，即不恢复“孙七”的信息。这时所恢复的数据是从二进制日志文件的开始位置直到指定位置
     
    [root@node2 ~]# mysqlbinlog --no-defaults --stop-position='623' /mysql_bak/mysql-bin.000002 |mysql -u root -pEnter password: [root@node2 ~]# mysql -u root -p -e 'select * from client.user_info;'Enter password: +--------------+--------+--------+--------+----------+| identitycard | name   | gender | UserID | expenses |+--------------+--------+--------+--------+----------+| 000006       | 张三   | 男     | 016    |       10 || 000007       | 李四   | 女     | 017    |       91 || 000008       | 王五   | 女     | 018    |       23 || 000009       | 赵六   | 男     | 019    |       37 |+--------------+--------+--------+--------+----------+[root@node2 ~]# 
     
    上述操作命令中，“--stop-position”指定的是停止的位置。如果仅恢复“孙七”的信息，跳过“赵六”的信息恢复，可以使用“--start-position”选项指定开始恢复数据的位置。这时所恢复的数据是从指定位置开始直到二进制日志文件的最后
     
    [root@node2 ~]# mysql -u root -p -e 'drop table client.user_info;'Enter password: [root@node2 ~]# mysql -u root -p client  
    3. 基于时间点恢复
     
    基于时间点的数据恢复所使用的选项是“--stop-datetime”，指定的时间同样也是查询二进制日志所得。执行以下操作可以实现仅恢复到 10∶41∶25 之前的数据，即不恢复“孙七” 的信息（这里的时间点根据上面的二进制日志文件可查看）
     
    [root@node2 ~]# mysql -u root -p -e 'drop table client.user_info;'Enter password: [root@node2 ~]# mysql -u root -p client  
    执行以下操作可以实现仅恢复“孙七”的信息，跳过“赵六”的信息恢复
     
    [root@node2 ~]# mysql -u root -p -e 'drop table client.user_info;'Enter password: [root@node2 ~]# mysql -u root -p client  
    1.4 制定企业备份策略的思路
     
            在企业中备份策略并不是千篇一律的，而是根据每个企业的实际生产环境与业务需求制定合适的备份策略。无论是选择完全备份，还是选择增量备份，都需考虑它们的优缺点，是否适合当前的生产环境。同时，为了保证恢复的完整性，建议开启二进制日志功能，二进制日志文件给恢复工作也带来了很大的灵活性，可以基于时间点或位置进行恢复。考虑到数据库性能，可以将二进制日志文件保存到其他安全的硬盘中
     
            在进行热备份时，备份操作和应用服务在同时运行，这样就十分消耗系统资源了，导致数据库服务性能下降，这就需要选择一个合适的时间（如在应用负担很小的时候）再来进行备份操作
     
            需要注意的是，不是备份完就万事大吉，最好确认备份是否可用，所以备份之后的恢复 测试是非常有必要的。同时备份时间也要灵活调整，如：
     

    • 数据更新频繁，则应该频繁地备份
    • 数据的重要性，在有适当更新时进行备份
    • 在数据库压力小的时间段进行备份，如一周一次完全备份，每天进行增量备份
    • 中小公司，完全备份一般一天一次即可
    • 大公司可每周进行一次完全备份，每天进行一次增量备份
    • 尽量为企业实现主从复制架构，以增加数据的可用性