备份和还原

摘要：本文学习了数据库的备份和还原。

1 分类

按备份类型可以分为两种：

• 完全备份：完全备份指的是备份整个数据集，即整个数据库。
• 部分备份：部分备份指的是备份部分数据集，支持指定表。部分备份可以细分两种：
  • 增量备份：增量备份指的是备份自上一次增量备份或完全备份以来变化的数据。其特点是节约空间，但是还原麻烦。
  • 差异备份：差异备份指的是备份自上一次完全备份以来变化的数据。其特点是浪费空间，但是还原比增量备份简单。

按备份方式可以分为三种：

• 热备份：热备份指的是当数据库进行备份时，数据库的读写操作均不是受影响。MyISAM引擎不支持，InnoDB引擎支持。
• 温备份：温备份指的是当数据库进行备份时，数据库的读操作可以执行，但是不能执行写操作。MyISAM引擎和InnoDB引擎都支持。
• 冷备份：冷备份指的是当数据库进行备份时，数据库不能进行读写操作，即数据库要下线。MyISAM引擎和InnoDB引擎都支持。

2 策略

针对不同的场景，应该制定不同的备份策略：

• 使用cp命令或者tar命令复制数据库文件，适用于数据量较小的环境。
• 使用mysqldump命令和二进制日志文件备份，适用于数据量一般的环境。
• 使用lvm2快照和二进制日志文件备份，适用于数据量较大的环境。
• 使用xtrabackup工具进行备份，适用于数据量很大的环境。

3 使用mysqldump命令

3.1 导出

使用mysqldump命令导出全部数据库：

1

mysqldump [连接选项] [导出选项]

连接选项：

• –user, -u：用户名。登录数据库的用户名，当前用户可省略该配置。
• –password, -p：密码。登录数据库的密码。
• –host, -h：主机地址。设置数据库所在的主机地址。
• –port, -P：端口号。设置用于连接的端口号。
• –protocol：连接协议。设置使用的连接协议，取值有TCP、SOCKET、PIPE、MEMORY四种。

导出选项：

• –all-databases, -A：导出全部数据库。不指定数据库，导出所有数据库。
• –databases, -B：导出指定数据库。导出指定的数据库。
• –tables：导出指定表。只能针对一个数据库进行导出，并且导出的内容中没有创建数据库的判断语句。
• –ignore-table：不导出指定表。指定忽略多个表时，需要重复多次，每个表都要指定数据库和表名。
• –no-create-db：不添加创建数据库语句。默认添加。
• –add-drop-database：添加删除数据库语句。默认不添加。
• –no-create-info：不添加创建表语句。默认添加。
• –skip-add-drop-table：不添加删除表语句。默认添加。
• –skip-extended-insert：不合并多个插入语句，一个插入语句插入一行数据。默认开启。
• –no-data：不导出数据只导出表结构。
• –default-character-set：设置默认的字符集。默认是utf8。
• –where：只导出符合指定条件的记录。如果条件包含命令解释符专用空格或字符，将条件引用起来。
• –complete-insert：使用包含列名称的完整插入语句。默认不使用列名插入。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
• –max_allowed_packet：服务器发送和接受的最大包长度。通信的缓存区最大为1GB。
• –insert-ignore：忽略插入错误。在插入语句中添加忽略错误语句，默认不添加。
• –force, -f：强制插入。在表转储过程中，即使出现SQL错误也继续。

导出全部数据库：

1

mysqldump -u root -p --all-databases > db_back.sql

导出指定数据库：

1

mysqldump -u root -p --databases test demo > db_back.sql

导出指定表：

1

mysqldump -u root -p --databases test --tables user bill > db_back.sql

不导出指定表：

1

mysqldump -u root -p --databases test --ignore-table=test.user > db_back.sql

设置默认字符集：

1

mysqldump -u root -p --all-databases --default-character-set=utf8mb4 > db_back.sql

使用完整的插入语句：

1

mysqldump -u root -p --all-databases --complete-insert

3.2 导入

导入数据有两种方式，一种是在Linux系统使用<命令导入，另一种是在MySQL数据库使用source命令导入。

3.2.1 在Linux系统导入

语法：

1

mysql [连接选项] 数据库名 < 文件位置

示例：

1

mysql -u root -p test < db_back.sql

3.2.2 在MySQL数据库导入

语法：

1

source 文件位置

示例：

1

source /home/db_back.sql