（四）Mysql 数据库备份恢复全攻略

一、数据库备份

1. 数据库备份目的和数据库故障类型

   • 目的：
     • 当发生故障时，将损失降到最低。
     • 保证能够快速从备份数据中恢复，确保数据稳定运行。
   • 故障类型：
     • 程序错误：Mysql 服务器端程序故障无法使用。
     • 人为误操作：权限过大误删除数据。
     • 运算错误：数据增删改查询 sql 语句错误。
     • 磁盘故障：磁盘物理损坏存储数据无法读取。
     • 不可抗力自然灾害：地震、海啸、山洪、台风、火灾等。

2. 物理逻辑角度备份

   • 物理备份：
     • 冷备份：停止数据库服务，对数据存储位置进行备份，如/usr/local/mysql/data/。
     • 热备份：两地三中心数据备份，包括主从复制、主主复制、MHA、MGR 等方式。
     • 温和备份：禁止写入数据允许读取，对数据库进行备份，最大限度保证数据的完整性。
   • 数据库备份策略：
     • 完整备份：第一次使用备份时间长、速度慢、占用空间大。
     • 差异备份：在做完整备份后，对产生的数据使用差异备份，速度快、占用空间小。
   • 增量备份：针对数据库日志文件进行备份。

3. 备份常见的方式

   • 冷备份：可以使用tar、cp等命令。
   • 专用工具：Mysqldump、Mysqlhotcopy等。
   • 第三方工具：PXB、Xtrabackup、innobackupex、xbstream等。
   • 二进制日志：方便将数据恢复到指定的日志或者指定时间范围内。

二、Mysql 数据库备份恢复

1. Mysql 数据库冷备份

   • 停止数据库服务，使用命令[root@centos01 ~]# systemctl stop mysqld。
   • 备份数据，使用命令[root@centos01 ~]# tar zcf /opt/2024_09_12_mysql_data.tar.gz /usr/local/mysql/data。
   • 模拟误删除数据，使用命令[root@centos01 ~]# rm -rf /usr/local/mysql/data/*。
   • 解压恢复数据，使用命令[root@centos01 ~]# tar zxf /opt/2024_09_12_mysql_data.tar.gz -C /opt/。
   • 恢复数据，使用命令[root@centos01 ~]# cp -r /opt/usr/local/mysql/data/* /usr/local/mysql/data/将备份的数据恢复到 Mysql 数据根目录，然后使用[root@centos01 ~]# ls /usr/local/mysql/data/查看数据目录，最后使用[root@centos01 ~]# chown -R mysql:mysql /usr/local/mysql/data/修改数据所有者。
   • 启动服务，使用命令[root@centos01 ~]# systemctl start mysql。
   • 查询恢复数据，使用命令[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from hb3066.student;';。

2. mysqldump 备份

   • mysqldump选项：
     • --databases：备份数据库。
     • --all-database：备份所有数据库。
   • 备份指定的 hb3066 数据库的 student 表，使用命令[root@centos01 ~]# mysqldump -uroot -ppwd@123 hb3066 student >./student.sql，然后使用[root@centos01 ~]# less./student.sql查看备份文件。
   • 将 student 表恢复到 benet 数据库中，使用命令[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'create database benet;';创建数据库 benet，使用[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'show databases;';查看创建的 benet 数据库，然后使用[root@centos01 ~]# mysql -uroot -ppwd@123 benet <./student.sql恢复数据到 benet 数据库，最后使用[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.student;';查询恢复到 benet 数据库的 student 表。
   • 对数据库进行备份和恢复，使用命令[root@centos01 ~]# mysqldump -uroot -ppwd@123 --databases benet >./benet.sql备份 benet 数据库，使用[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop database benet;';模拟删除 benet 数据库，使用[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'show databases;';确认删除 benet 数据库，最后使用[root@centos01 ~]# mysql -uroot -ppwd@123 <./benet.sql恢复误删除的数据库 benet，并使用[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.student;';查询 benet 数据库的 student 表。
   • 备份所有数据库，使用命令[root@centos01 ~]# mysqldump -uroot -ppwd@123 --all-databases >./all_database.sql，然后使用[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop database benet;';模拟删除 benet 数据库，使用[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'show databases;';确认删除，最后使用[root@centos01 ~]# mysql -uroot -ppwd@123 <./all_database.sql恢复数据库。

3. 增量备份恢复作用和特点

   • 作用：对 Mysql 的数据库日志进行备份，发生误操作时通过日志实现数据恢复。
   • 特点：灵活性强，恢复简单方便。
   • 配置注意事项开启二进制日志：使用命令[root@centos01 ~]# vim /etc/my.cnf，在文件中添加[mysqld]和log-bin=mysql-bin设置二进制日志文件名字。
   • 阅读二进制日志，使用命令[root@centos01 ~]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000004。
   • 切割日志生成新的二进制日志，使用命令[root@centos01 ~]# mysqladmin -uroot -ppwd@123 flush-logs。
   • 插入最新数据，使用命令[root@centos01 ~]# mysql -uroot -ppwd@123进入 Mysql 命令行，然后使用mysql> insert into benet.student values ('张三','男',16,'333333333333333333');、mysql> insert into benet.student values ('张四','男',16,'335555533333333333');、mysql> insert into benet.student values ('张五','男',19,'335999999333355555');插入数据。
   • 备份二进制日志，使用命令[root@centos01 ~]# cp /usr/local/mysql/data/mysql-bin.000005./。

4. 增量恢二进制日志恢复

   • 模拟删除数据，使用命令[root@centos01 ~]# mysql -uroot -ppwd@123 benet <./student.sql。
   • 根据二进制日志文件恢复数据，使用命令[root@centos01 ~]# mysqlbinlog --no-defaults./mysql-bin.000005 | mysql -uroot -ppwd@123。

5. 增量二进制日志基于位置恢复数据

   • 位置选项：
     • --stop-position：结束位置往前恢复。
     • --start-position：从前往后恢复。
   • 模拟删除数据，使用命令[root@centos01 ~]# mysql -uroot -ppwd@123 benet <./student.sql。
   • 模拟从前往后恢复数据，使用命令[root@centos01 ~]# mysqlbinlog./mysql-bin.000005 | more读取二进制日志，然后使用[root@centos01 ~]# mysqlbinlog --start-position 4./mysql-bin.000005 | mysql -uroot -ppwd@123从前往后恢复。
   • 模拟从后往前恢复数据，再次模拟删除数据后，使用命令[root@centos01 ~]# mysqlbinlog --stop-position 620./mysql-bin.000005 | mysql -uroot -ppwd@123。

6. 增量二进制日志基于时间恢复数据

   • 时间选项：
     • --start-datetime：起始时间往后恢复。
     • --stop-datetime：结束时间往前恢复。
   • 起始时间往后恢复数据，模拟删除数据后，使用命令[root@centos01 ~]# mysqlbinlog --start-datetime='2024-09-12 19:22:01'./mysql-bin.000005 | mysql -uroot -ppwd@123。
   • 结束时间往前恢复数据，模拟删除数据后，使用命令[root@centos01 ~]# mysqlbinlog --stop-datetime='2024-09-12 19:29:07'./mysql-bin.000005 | mysql -uroot -ppwd@123。

通过以上各种备份恢复方式，我们可以在不同的情况下有效地保护和恢复 Mysql 数据库中的数据，确保数据的安全性和可用性。