MySQL数据备份和恢复

数据备份

mysqldump是MySQL数据库备份工具，可以备份MySQL数据库中的数据和结构，生成.sql文件，方便数据的迁移和恢复。

使用mysqldump工具前一定要配置环境变量

1. 打开开始菜单，搜索“环境变量”。
2. 点击“编辑系统环境变量”。
3. 点击“环境变量”按钮。
4. 在“系统变量”下，点击“新建”。
5. 输入“MYSQL_HOME”作为变量名，将MySQL安装目录的路径作为变量值（例如：C:Program FilesMySQLMySQL Server 5.7）。
6. 点击“确定”保存变量。
7. 在“系统变量”下，找到“Path”变量并点击“编辑”。
8. 在“变量值”字段的末尾添加“%MYSQL_HOME%in”（不包括引号）。
9. 点击“确定”保存变量。
10. 重启计算机以使更改生效。

mysqldump的常用参数

• –databases：备份多个数据库，后面跟着数据库名称，中间用空格分隔。
• –tables：备份多个表，后面跟着表名称，中间用空格分隔。
• –where：备份符合条件的数据，后面跟着条件语句。
• –lock-tables：备份时锁定表，防止备份过程中数据被修改。
• –single-transaction：在事务中备份，保证备份的数据一致性。
• –add-drop-database：备份时添加DROP DATABASE语句。
• –add-drop-table：备份时添加DROP TABLE语句。
• –add-locks：备份时添加锁定语句。
• –extended-insert：使用多行语法插入数据，提高备份效率。
• –quick：使用单行语法插入数据，提高备份效率。
• –compress：备份时压缩数据。
• –result-file：指定备份文件名及路径。
• –user：指定连接MySQL的用户名。
• –password：指定连接MySQL的密码。
• –no-data : 备份表结构但不包括表

导出单个数据库（包括数据库中的数据）

语法：

mysqldump --user=用户名 -p 数据库名称 > 目标目录及文件名称

例如：

mysqldump --user=root -p demo > E:数据库备份demo.sql

导出单个数据库（不包含数据库中的数据）

语法：

mysqldump --user=用户名 -p --no-data 数据库 > 目标目录及文件名称

例如：

mysqldump --user=root -p --no-data demo > E:数据库备份demo.sql

导出多个数据库

语法：

注意：数据库之间用空格分开

mysqldump --user=用户名 -p --databases 数据库1 数据库2 ... > 目标目录及文件名称

例如：

mysqldump --user=root -p --databases demo1 demo2 > E:数据库备份demo.sql

导出数据库中的某张表（包含数据）

语法：

mysqldump --user=用户名 -p 数据库名称 表名称 > 目标目录及文件名称

例如：

mysqldump --user=root -p demo t_student > E://demo_t_student.sql

导出数据库中的某张数据表（不包含数据）

语法：

mysqldump --user=用户名 -p --no-data 数据库名称 表名称 > 目标目录及文件名称

例如：

mysqldump --user=root -p --no-data demo t_student > E://demo_t_student.sql

导出多张表

语法：

mysqldump --user=用户名 -p 数据库名称 --tables 数据表1 数据表2 > 目标目录及文件名称

例如：

mysqldump --user=root -p demo --tables table1 table2 > E:数据库备份demo_tables.sql

备份时锁定表

语法：

mysqldump --user=用户名 -p --lock-tables 数据表名称 > 目标目录及文件名称

例如：

mysqldump --user=root -p --lock-tables demo >  E:数据库备份demo.sql

在事务中备份

语法：

mysqldump --user=root -p --single-transaction 数据库名称 > 目标目录及文件名称

例如：

mysqldump --user=root -p --single-transaction demo > E:数据库备份demo.sql

添加drop database语句：

语法：

mysqldump --user=root -p --add-drop-database 数据库名称 > 目标目录及文件名称

添加drop table语句：

语法：

mysqldump --user=root -p --add-drop-table 数据库名称 > 目标目录及文件名称

添加时锁定语句

语法：

mysqldump --user=root -p --add-lock 数据库名称 > 目标目录及文件名称

使用多行语法插入数据

语法：

mysqldump --user=root -p --extended-insert 数据库名称 > 目标目录及文件名称

使用单行语法插入数据

语法：

mysqldump --user=root -p --quick 数据库名称 > 目标目录及文件名称

备份时压缩数据

语法：

mysqldump --user=root -p --compress 数据库名称 > 目标目录及文件名称

指定备份文件及地址

mysqldump --user=username -p 数据库名称 --result-file= 目标目录及文件名称

数据恢复

• 创建一个新的数据库，用于恢复备份数据、

  create database demo1;
  

• 使用/登录到新的数据库

  use demo;
  mysql --user=root -p demo1
  

• 使用source命令导入备份的文件

  source E:数据库备份demo.sql
  

• 恢复后可以使用show查看数据表，select验证数据

  show tables;
  select * from table1;
  

总结：

• 数据备份
  • 可以保护数据免受意外删除，硬件故障，恶意软件等问题的影响
  • 要定期对数据进行备份，而且要在系统负载较小的时间段进行
  • 确保开启二进制日志，这样可以基于时间点或位置对数据进行恢复
  • 对特别重要的数据保留多份备份
  • 确定使用完整备份还是增量备份
    • 增量备份是指在一次完整备份或者上一次增量备份后，以后只需备份与前一个相比增加或者被修改的文件，
• 数据恢复
  • 可以帮助恢复丢失的数据，保证业务的连续性和稳定性
  • 定期做备份恢复测试，保证备份是有效的，并且是可以恢复的