【2024】基于mysqldump的数据备份与恢复

基于mysqldump备份与恢复

mysqldump是一个用于备份 MySQL 数据库的实用工具。

它可以将数据库的结构（如数据库、表、视图、存储过程等的定义）和数据（表中的记录）导出为文本文件，这些文本文件可以包含 SQL 语句，用于重新创建数据库结构和插入数据，从而实现数据库的备份和恢复功能。

优点：

• 简单易用：
  它是MySQL自带工具，安装了MySQL即可使用此工具，不需要额外安装。
• 备份灵活：
  可以备份整个数据库、单个数据库中的某些表，甚至可以只备份数据库的结构而不备份数据。
• 跨平台兼容：
  由于它基于 SQL 语句进行备份，备份文件本质上是 SQL 脚本，所以在不同的操作系统和 MySQL 版本之间具有较好的兼容性。
• 数据一致性好：
  在备份过程中，mysqldump会尽量保证数据的一致性。

缺点：

• 性能问题：
  对于大型数据库，备份和恢复过程可能会比较慢。
• 备份文件大小问题：
  备份文件是文本格式，包含了 SQL 语句，相比一些二进制的备份方式，文件大小可能会比较大。
• 锁表问题：
  当使用默认的备份方式（获取全局读锁）时，会对数据库中的表进行锁定，这期间其他对这些表的写入操作（如插入、更新、删除）会被阻塞。

使用方法：

• 备份整个数据库
  此命令为将student库备份到/backup目录下

[root@localhost ~]# mysqldump -uroot -p student > /backup/student_backup.sql
Enter password: 

• 备份多个数据库
  通过--databases参数添加多个需要备份得数据库

mysqldump -uroot -p --databases db1 db2 > /backup/dbs_backup.sql

• 备份数据库中的部分表

mysqldump -uroot -p mydb table1 table2 > partial_tables_backup.sql

• 只备份数据结构
  可以使用-d参数

mysqldump -uroot -p -d mydb > mydb_structure_backup.sql

• 恢复备份
  登录到MySQL，进入到命令行页面；
  创建一个空数据库（如果恢复的数据库不存在）；
  进入到数据库；
  恢复备份。

案例：模拟student库被删除，从备份中恢复数据

• 登录到数据库

[root@localhost ~]# mysql -uroot -p
Enter password: 

• 模拟误删库，删除student库

mysql> DROP DATABASE IF EXISTS student;
Query OK, 1 row affected (0.16 sec)mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.02 sec)

可以看到已经没有student库了

• 创建一个新的student库

mysql> CREATE DATABASE student;
Query OK, 1 row affected (0.91 sec)

• 进入到需要恢复数据的库

mysql> USE student;
Database changed

• 进行数据恢复

mysql> SOURCE /backup/student_backup.sql
Query OK, 0 rows affected (0.00 sec)

• 查看是否恢复成功

mysql> SHOW TABLES;
+-------------------+
| Tables_in_student |
+-------------------+
| grade_1           |
+-------------------+
1 row in set (0.01 sec)mysql> SELECT * FROM grade_1;
+----+--------+-----+--------+------------+
| id | name   | age | gender | birth_date |
+----+--------+-----+--------+------------+
|  1 | 张三   |   7 | 男     | 2017-05-10 |
|  2 | 赵六   |   7 | 女     | 2017-07-12 |
|  3 | 孙七   |   7 | 男     | 2017-04-18 |
|  4 | 周八   |   7 | 女     | 2017-08-05 |
|  5 | 熊大   |   7 | 男     | 2017-09-10 |
|  6 | 熊二   |   7 | 男     | 2017-09-11 |
+----+--------+-----+--------+------------+
6 rows in set (0.00 sec)

可以看到数据已经恢复了。

除上述案例外还有一些参数可能会用到：

• -R：备份数据库中的存储过程和函数
• --where：根据指定的条件备份表中的数据
• --column-statistics：用于决定是否在备份文件中包含列统计信息
• --single-transaction：对于 InnoDB 存储引擎的数据库，这个参数可以在备份过程中保证数据的一致性
• events：用于备份数据库中的事件

更多参数用法，请参考mysqldump --help。