002MySQL学习013备份和还原

部分内容来自以下博客：

https://www.cnblogs.com/chenmh/p/5300370.html

1 概述

1.1 为什么需要数据备份

在生产环境中数据库可能会遭遇各种各样的不测从而导致数据丢失，大概分为以下几种：硬件故障、软件故障、自然灾害、黑客攻击、误操作（占比最大）。

所以，为了在数据丢失之后能够恢复数据，就需要定期的备份数据。

1.2 数据备份需要考虑的因素

备份数据的策略要根据不同的应用场景进行定制，大致有几个参考数值，可以根据这些数值从而定制符合特定环境中的数据备份策略：

能够容忍丢失多少数据。

恢复数据需要多长时间。

需要恢复哪一些数据。

1.3 数据备份类型

数据的备份类型根据其自身的特性主要分为以下几组：完全备份、部分备份。

1.3.1 完全备份

完全备份指的是备份整个数据集（即整个数据库）、部分备份指的是备份部分数据集（例如只备份一个表）

1.3.2 部分快递

部分备份分为以下两种：增量备份、差异备份。

增量备份指的是备份自上一次增量备份或完全备份以来变化的数据。其特点是节约空间，但是还原麻烦。

差异备份指的是备份自上一次完全备份以来变化的数据。其特点是浪费空间，但是还原比增量备份简单。

1.4 数据备份方式

1.4.1 备份方式

在MySQL中备份数据一般有几种方式：热备份、温备份、冷备份。

热备份指的是当数据库进行备份时，数据库的读写操作均不是受影响。

温备份指的是当数据库进行备份时，数据库的读操作可以执行，但是不能执行写操作。

冷备份指的是当数据库进行备份时，数据库不能进行读写操作，即数据库要下线。

1.4.2 引擎支持

MySQL中进行不同方式的备份还要考虑存储引擎是否支持：

MyISAM：不支持热备份，仅支持温备份和冷备份。

InnoDB：支持热备份，也支持温备份和冷备份。

1.5 数据备份策略

针对不同的场景，应该制定不同的备份策略，一般情况下，备份策略有以下三种：

使用cp命令或者tar命令复制数据库文件，适用于数据量较小的环境。

使用mysqldump命令和二进制日志文件备份，适用于数据量一般的环境。

使用lvm2快照和二进制日志文件备份，适用于数据量较大的环境。

使用xtrabackup工具进行备份，适用于数据量很大的环境。

2 常用命令

2.1 导出数据

导出2019年1月的数据，数据库是ide，表是user，要求不需要导出建库语句，要求使用完成的插入语句，并且合并插入：

[root@localhost ~]# mysqldump -h 127.0.0.1 -P 3306 -uroot -p'123456' --no-create-info --databases ide --tables user \
> --where=" date >= '2019-01-01' and date < '2019-02-01' " --complete-insert --extended-insert > '/data/dump/201901.txt'

2.2 导入数据

[root@localhost ~]# mysql -uroot -p123456 database < db_back.sql

3 导出数据

导出数据一般使用mysqldump命令在Linux系统执行。

3.1 语法

导出全部数据库：

mysqldump [OPTIONS] --all-databases [OPTIONS]

导出指定数据库：

mysqldump [OPTIONS] --databases DB1 [DB2 DB3...] [OPTIONS]

导出指定数据库的指定表：

mysqldump [OPTIONS] --database DB --tables TB [TB2 TB3...] [OPTIONS]

3.2 说明

--user, -u：用户名。登录数据库的用户名，当前用户可省略该配置。
--password, -p：密码。登录数据库的密码。
--host, -h：主机地址。设置数据库所在的主机地址，默认是localhost。
--port, -P：端口号。设置用于连接的端口号。
--protocol：连接协议。设置使用的连接协议，取值有TCP、SOCKET、PIPE、MEMORY四种。
--socket, -S：套接字文件。指定当连接localhost时的套接字文件位置，默认路径是/tmp/mysql.sock。
--all-databases, -A：导出全部数据库。不指定数据库，导出所有数据库。
--databases, -B：导出指定数据库。导出指定的数据库。
--tables：导出指定表。只能针对一个数据库进行导出，并且导出的内容中没有创建数据库的判断语句。
--ignore-table：不导出指定表。指定忽略多个表时，需要重复多次，每个表都要指定数据库和表名。
--all-tablespaces, -Y：导出全部表空间。
--no-tablespaces, -y：不导出表空间信息。
--no-create-db, -n：不添加创建数据库语句。默认添加。
--add-drop-database：添加删除数据库语句。默认不添加。
--no-create-info, -t：不添加创建表语句。默认添加。
--add-drop-table：添加删除表语句。默认添加。
--skip-add-drop-table：取消添加删除表语句。
--no-data, -d：不导出数据只导出表结构。
--routines, -R：导出存储过程以及自定义函数。
--events, -E：导出事件表。
--triggers：导出触发器。
--add-locks：在每个表导出前锁表并在导出后解锁。默认为打开状态。
--skip-add-locks：取消在每个表导出前锁表并在导出后解锁。
--default-character-set：设置默认的字符集。默认是utf8。
--compress, -C：在客户端和服务器之间启用压缩传递所有信息。
--result-file, -r：处理换行。直接输出到指定文件中。该选项应该用在使用回车换行对（\\r\\n）换行的系统上（例如：DOS，Windows）。该选项确保只有一行被使用。
--dump-date：添加导出时间。将导出时间添加到输出文件中。默认为打开状态。
--skip-dump-date：取消添加导出时间。
--where, -w：只导出符合指定条件的记录。如果条件包含命令解释符专用空格或字符，将条件引用起来。
--extended-insert：合并多个插入语句。默认开启。
--skip-extended-insert：取消合并多个插入语句。
--complete-insert, -c：使用完整的插入语句。使用包含列名称的完整插入语句。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。
--insert-ignore：忽略插入错误。在插入语句中添加忽略错误语句，默认不添加。
--max_allowed_packet：服务器发送和接受的最大包长度。客户端/服务器之间通信的缓存区最大为1GB。
--force, -f：强制插入。在表转储过程中，即使出现SQL错误也继续。
--lock-all-tables, -x：对所有数据备份时，对所有库的所有表加读锁，实现温备。
--lock-tables, -l：对指定的数据库进行备份时，对指定库的所有表加锁，实现温备。
--single-transaction：在备份InnoDB存储引擎的表时，让备份操作基于独立的事务进行，实现热备。该选项的优先级高于“--lock-all-tables”和“--lock-tables”，并且仅能用于InnoDB。

3.3 实例

导出全部数据库：

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases > db_back.sql

导出指定数据库：

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --databases test demo > db_back.sql

导出指定表：

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --database test --tables demo user > db_back.sql

不导出指定表：

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases \
> --ignore-table=database.table1 --ignore-table=database.table2 > db_back.sql

设置默认字符集：

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --default-character-set=latin1 > db_back.sql

处理换行：

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --result-file=/tmp/mysqldump_result_file.txt

只导出符合指定条件的记录：

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --where=" id=1 limit 10"

使用完整的插入语句：

[root@localhost ~]# mysqldump -h127.0.0.1 -uroot -p --all-databases --complete-insert

4 导入数据

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

4.1 语法

在Linux系统使用<命令：

mysql -u用户名 -p密码 数据库名 < 文件路径

在MySQL数据库使用source命令：

source 文件路径

4.2 实例

在Linux系统使用<命令：

[root@localhost ~]# mysql -uroot -p123456 database < db_back.sql

在MySQL数据库使用source命令：

mysql> source /home/user/data/db_back.sql

4.3 效率比较

在文件较小的情况下，source速度比mysql高。在导入大的文件时，建议使用mysql命令。

4.4 优化导入速度

对于百M级以上文件，根据MySQL官方建议，有几个措施可以极大提高导入的速度。

4.4.1 修改配置文件

配置如下：

[mysqld]
bulk_insert_buffer_size=2G;
innodb_log_buffer_size=2G;
innodb_autoinc_lock_mode=2;

说明：

对于MyISAM，调整系统参数：bulk_insert_buffer_size。改为至少单个文件大小的2倍以上。

对于InnoDB，调整系统参数：innodb_log_buffer_size。改为至少单个文件大小的2倍以上，导入完成后可以改回默认的8M，注意不是innodb_buffer_pool_size。

有自增列的，设置：innodb_autoinc_lock_mode的值为2。

4.4.2 删除索引

除主键外，删除其他索引，导入完成后重建索引。

4.4.3 执行批量脚本

bash脚本内容：

for SQL in *.sql;
do
    echo $SQL;
    sed -i '1i\SET autocommit=0;\nSET unique_checks=0;\nSET foreign_key_checks=0;' $SQL
    sed -i '$a\COMMIT;\nSET autocommit=1;\nSET unique_checks=1;\nSET foreign_key_checks=1;' $SQL
done

说明：

autocommit=0：关闭自动提交。
unique_checks=0：关闭唯一索引检查。
foreign_key_checks=0：关闭外键检查。