mysqldump，select into outfile备份

mysql备份

mysql备份的方式有两种：
冷备：数据库处于关闭状态下的备份，备份简单，恢复快，影响现有业务的运行，常见的备份方式就是停库，把数据打包压缩备份。
热备：数据库运行状态下的备份，不影响现有业务的正常运行。

本文介绍mysql最常用的两种备份mysqldump,select…into outfile.

一）mysqldump备份

先看看其参数
–single-transaction用于保证innodb备份数据时的一致性，当发起事物时，读取一个数据的快照，直到备份结束时，都不会读取到本事物开始之后的任何数据。

–all-databases(-A) 备份所有数据库。
–master-data：该参数有1和2两个值，
1：就会在备份出来的文件中添加一个change master 的语句
2：就会在备份出来的文件中添加一个change master 的语句，并在语句前面加注释符号。

–no-create-info：备份过程中，只备份表数据，并不备份表结构。

–no-data：备份过程中，只备份表结构，不备份表数据。

–databases(-B):备份多个数据库，如 --database db1 db2

全库备份：
mysqldump --single-transaction -uroot -proot -A >a.sql
备份完成，进行删库，发现information_schema权限等级很高，即使root都删不了，其他的都删除了。

全库恢复：
mysql -uroot -proot <a.sql
也可以连接mysql，source a.sh恢复数据，且更直观。

恢复后发现sys,performance_schema,3个库没有恢复^-,说明mysqldumo默认不备份这3个库，
测试过程最好不要删除这两个库，看看悲剧：
mysql> show global variables like ‘%gtid%’;
ERROR 1146 (42S02): Table ‘performance_schema.global_variables’ doesn’t exist
mysql> show status;
ERROR 1146 (42S02): Table ‘performance_schema.session_status’ doesn’t exist

使用mysqldump备份时，很有可能会遇到数据库性能抖动的问题，出现性能急剧下降的现象，，mysqldump备份时先从buffer中找到想要备份的内容，如果buffer中没有，就需要访问磁盘中的数据文件，然后不数据调回到内存，再形成备份文件，关键的问题就在于把数据从磁盘调回到内存时，就有可能把内存里面的热数据给冲掉，影响现有业务的访问。

mysql5.7之后新增加了一个innodb_buffer_pool_dump_pct参数，用来控制每隔innodbbuffer中转储活跃使用innodb buffer pages的比例，只有当数据在1s内再次被访问时，才能放到热区域内，这样就避免了热数据被冲走的情况，默认25%

show global variables like ‘innodb_buffer_pool_dump_pct’;
±----------------------------±------+
| Variable_name | Value |
±----------------------------±------+
| innodb_buffer_pool_dump_pct | 25 |
±----------------------------±------+
1 row in set (0.00 sec)

二）select … into outfile

select … into outfile是一种逻辑备份，恢复速度非常快，只能备份表中的数据，不包含表的结构。

导出数据备份：
select * from t1 into outfile ‘./t1.sql’;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

果然各种坑_{_},
按照提示看看参数，应该是secure_file_priv，没有指定路径的缘故，
show global variables like ‘%secure%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | NULL |
±-------------------------±------+
这是一个read only参数，修改，重新启动，
show global variables like ‘%secure%’;
±-------------------------±------+
| Variable_name | Value |
±-------------------------±------+
| require_secure_transport | OFF |
| secure_auth | ON |
| secure_file_priv | /tmp/ |
±-------------------------±------+
3 rows in set (0.00 sec)

mysql> select * from t1 into outfile ‘/tmp/t1.sql’;
Query OK, 3 rows affected (0.00 sec)

well，再次导出数据成功。
可以直接查看备份的数据。
cat t1.sql
1 aa
2 bb
3 cc

恢复测试：
mysql> select * from t1;
±—±-----+
| id | name |
±—±-----+
| 1 | aa |
| 2 | bb |
| 3 | cc |
±—±-----+
3 rows in set (0.00 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.02 sec)

mysql> select * from t1;
Empty set (0.00 sec)

mysql> load data infile ‘/tmp/t1.sql’ into table test.t1;
Query OK, 3 rows affected (0.01 sec)
Records: 3 Deleted: 0 Skipped: 0 Warnings: 0

mysql> select * from t1;
±—±-----+
| id | name |
±—±-----+
| 1 | aa |
| 2 | bb |
| 3 | cc |
±—±-----+
3 rows in set (0.00 sec)

load data 插入数据的效率大概是insert 效率的12倍。