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

发布于:2024-10-09 ⋅ 阅读:(29) ⋅ 点赞:(0)

基于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