MySQL数据库怎样备份与恢复

2024-07-24 12:33:06

字体：大中小

来源：转载

供稿：网友

　　在任何数据库环境中，总会有不确定的意外情况发生，比如例外的停电、计算机系统中的各种软硬件故障、人为破坏、管理员误操作等是不可避免的，这些情况可能会导致数据的丢失、服务器瘫痪等严重的后果。存在多个服务器时，会出现主从服务器之间的数据同步问题。

　　为了有效防止数据丢失，并将损失降到最低，应定期对MySQL数据库服务器做备份。如果数据库中的数据丢失或者出现错误，可以使用备份的数据进行恢复。主从服务器之间的数据同步问题可以通过复制功能实现。

　　一、物理备份与逻辑备份
　　物理备份：备份数据文件，转储数据库物理文件到某一目录。物理备份恢复速度比较快，但占用空间比较大，MySQL中可以用xtrabackup工具来进行物理备份。

　　逻辑备份：对数据库对象利用工具进行导出工作，汇总入备份文件内。逻辑备份恢复速度慢，但占用空间小，更灵活。MySQL 中常用的逻辑备份工具为mysqldump。逻辑备份就是备份sql语句，在恢复的时候执行备份的sql语句实现数据库数据的重现。

　　二、mysqldump实现逻辑备份
　　mysqldump是MySQL提供的一个非常有用的数据库备份工具。

　　2.1 备份一个数据库
　　mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。

　　查出需要备份的表的结构，在文本文件中生成一个CREATE语句
　　将表中的所有记录转换成一条INSERT语句。

　　/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
　　/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
　　/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
　　/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
　　/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
　　/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
　　/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
　　-- Dump completed on 2022-01-07 9:58:23
　　--开头的都是sQL语句的注释；
　　以/* !开头、*/结尾的语句为可执行的MySQL注释，这些语句可以被MySQL执行，但在其他数据库管理系统中被作为注释忽略，这可以提高数据库的可移植性；
　　文件开头指明了备份文件使用的MySQLdump工具的版本号；接下来是备份账户的名称和主机信息，以及备份的数据库的名称；最后是MySQL服务器的版本号，在这里为8.0.26。
　　备份文件接下来的部分是一些SET语句，这些语句将一些系统变量值赋给用户定义变量，以确保被恢复的数据库的系统变量和原来备份时的变量相同，例如：
　　MySQL数据库如何备份与恢复
　　备份文件的最后几行MySQL使用SET语句恢复服务器系统变量原来的值，例如：
　　后面的DROP语句、CREATE语句和INSERT语句都是还原时使用的。例如，DROPTABLE IF EXISTS 'student'语句用来判断数据库中是否还有名为student的表，如果存在，就删除这个表；CREATE语句用来创建student的表； INSERT语句用来还原数据。
　　备份文件开始的一些语句以数字开头。这些数字代表了MySQL版本号，告诉我们这些语句只有在制定的MySQL版本或者比该版本高的情况下才能执行。例如，40101表明这些语句只有在MySQL版本号为4.01.01或者更高的条件下才可以被执行。文件的最后记录了备份的时间。
　　2.2 备份全部数据库
　　若想用mysqldump备份整个实例，可以使用--all-databases或-A参数：

　　mysqldump -uroot -pxxxxxx --all-databases > all_database.sql
　　mysqldump -uroot -pxxxxxx -A > all_database.sql
　　2.3 备份部分数据库
　　使用--databases或-B参数了，该参数后面跟数据库名称，多个数据库间用空格隔开。如果指定databases参数，备份文件中会存在创建数据库的语句，如果不指定参数，则不存在。语法如下:

　　mysqldump –u user –h host –p --databases [数据库的名称1 [数据库的名称2...]] > 备份文件名称.sql
　　举例

　　mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql
　　或

　　mysqldump -uroot -p -B atguigu atguigu12 > two_database.sql
　　2.4 备份部分表
　　比如，在表变更前做个备份。语法如下：

　　mysqldump –u user –h host –p 数据库的名称 [表名1 [表名2...]] > 备份文件名称.sql
　　举例：备份atguigu数据库下的book表

　　mysqldump -uroot -p atguigu book> book.sql#备份多张表 mysqldump -uroot -p atguigu book account > 2_tables_bak.sql

（编辑：武林网）

上一篇：union all在mysql中怎么运用

下一篇：MySQL查询语句的执行过程实例解析