【MySQL】MySQL备份与恢复
1. 备份数据的意义
针对不同业务，7*24小时提供服务和数据的重要性不同。
数据库数据是比较核心的数据，对企业的经营至关重要，数据库备份显得尤为重要。
2. 备份数据库
MySQL数据库自带的备份命令 `mysqldump`，基本使用方法：
语法：`mysqldump -u username -p password dbname > filename.sql`
执行备份命令
`mysqldump -uroot -pmysqladmin db_test > /opt/mysql_bak.sql`
查看备份内容
`grep -v "#|\*|--|^$" /opt/mysql_bak.sql`
> 导出的格式没有加字符集，会出现乱码，一般恢复到数据库会正常。INSERT语句是批量插入的方式，这样恢复的效率会更高。
指定对应的字符集备份，默认字符集为 `default-character-set=latinl`
`mysqldump -uroot -p'mysqladmin' --default-character-set=latinl db_test > /opt/mysql_bak.sql`
恢复数据
`mysql -uroot -p'mysqladmin' db_test < /opt/mysql_bak.sql`
参数`-B`的使用
`mysqldump -uroot -pmysqladmin -B db_test > /opt/mysql_bak_B.sql`
> 使用 -B 参数的作用是增加创建数据库和连接数据的命令。即：
CREATE DATABASE /*!32312 IF NOT EXISTS*/`db_test`
USE `db_test`;
恢复的时候也不用指定库名称。
优化备份文件大小减少输出注释
利用`mysqldump`的`--compact`参数优化备份结果
--compact参数说明：
优化输出内容，输出更简洁，适合调试。
--skip-add-drop-table
--no-set-names
--skip-disable-keys
--skip-add-locks
指定压缩命令压缩备份MySQl数据
`mysqldump -uroot -p'mysqladmin' -B db_test|gzip > /opt/mysql_bak.sql.gz`
```
1. 导出数据用`-B`参数（带创建数据库语句）
2. 使用 `|gzip` 对数据进行压缩
```
3. mysqldump备份原理
mysqldump 实际上就是把数据以sql语句的形式直接输出。（逻辑备份）
恢复的过程就是把sql文件进行执行。
4. 备份多个库
`mysqldump -uroot -p'mysqladmin' -B db_test db_test1 |gzip > /opt/all_bak.sql.gz`
-B（--database） 参数是关键，表示连接多个库，并且增加 `CREATE DATABASE db_name` 、 `USE db_name`的信息。
-B 后的参数将被作为数据库名，该参数较常用。
当 -B 后的数据库全部列出时，同 -A 参数。
5. 分库备份
分库备份实际上就是将每个库执行一条命令单独备份，使用对应的库名作为备份文件，以`.sql` 结尾。备份多个库的命令如下：
1. 查询所有的数据库
`mysql -uroot -p'mysqladmin' -e "show databases;" |grep -Evi "database|info|perfor"`
2. 在所有库之前加上备份命令
`mysql -uroot -p'mysqladmin' -e "show databases;" |grep -Evi "database|info|perfor" | sed 's#^#mysqldump -uroot -p'mysqladmin' -B #g'`
3. 将备份内容输出到文件
`mysql -uroot -p'mysqladmin' -e "show databases;" |grep -Evi "database|info|perfor" | sed -r 's#^([a-z].*$)#mysqldump -uroot -p'mysqladmin' -B \1|gzip > /opt/\1.sql.gz#g'`
4. 执行上述输出的命令 `|bash`
`mysql -uroot -p'mysqladmin' -e "show databases;" |grep -Evi "database|info|perfor" | sed -r 's#^([a-z].*$)#mysqldump -uroot -p'mysqladmin' --events -B \1|gzip > /opt/\1.sql.gz#g'| bash`
6. 分库备份2
```bash
for dbname in `mysql -uroot -p'mysqladmin' -e "show databases;" |grep -Evi "database|info|perfor"`; do
mysqldump -uroot -p'mysqladmin' --events -B $dbname | gzip > /opt/bak/${dbname}_bak.sql.gz
done
```
7. 多表备份
`mysqldump -uroot -p'mysqladmin' db_name tb_test1 tb_test2 `
> 备份多表不使用`-B` 参数，第一个为数据库名称，之后的为表名称
8. 分表备份
和分库备份类似，每执行一条语句备份一个表，生成不同的文件。
> 分表备份缺点：文件较多
- 完整备份
- 分库备份
- 分表备份
- 批量恢复
9. 备份表结构
`mysqldump -uroot -p'mysqladmin' --compact -d db_test tb_test`
10. 备份表数据
`mysqldump -uroot -p'mysqladmin' --compact -t db_test tb_test`
11. 刷新binlog参数
```
mysqldump -uroot -p'mysqladmin' -A -B --events|gzip > /opt/a.sql.gz
mysqldump -uroot -p'mysqlamdin' -A -B -F --events|gzip > /opt/a.sql.gz
-F 切割binlog
```
12. 记录binlog位置
`mysqldump -uroot -p'mysqladmin' --master-data=1 --compact db_test`
`--master-data=1`
`--master-data=2` 将CHANGE语句加上注释
--master-data 作用：
--master-data=1: 从库
`CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000020',MASTER_LOG_POS=1191;`
**mysqldump 的关键参数说明**
`mysqldump --help`
-B 指定多个库，增加建库语句和 `USE` 语句
--compact 去掉注释，调试使用
-A 所有库
-F 刷新binlog日志
--master-data 增加binlog日志文件名及对应的位置点
-x, --lock-all-tables 锁表
-l, --lock-tables 只读锁表
-d 只备份表结构
-t 只备份数据
--single-transaction 适合innodb事务数据库备份
InnoDB 表在备份时，通常启用选项 --single-transaction 来保证备份的一致性，实际上它的工作原理是设定本次会话的隔离级别为：REPEATABLE READ，以确保本次会话（dump）时，不会看到其它会话已经提交了的数据。
MyIsam备份命令：
mysqldump -uroot -p'mysqladmin' -A -B --master-data=2 -x --events|gzip > /opt/all.sql.gz
Innodb备份命令（推荐）:
mysqldump -uroot -p'mysqladmin' -A -B --master-data=2 --single-transaction --events|gzip > /opt/all.sql.gz
### 数据库恢复
1. source 数据恢复
`systel ls `执行系统命令
`source *.sql` 文件默认为登录mysql前的系统路径
2. 标准恢复命令
```mysql
mysql -uroot -p'mysqladmin' -e "USE db_test; DROP tables; SHOW tables;"
mysql -uroot -p'mysqladmin' db_test < /opt/mysql_bak.sql
mysql -uroot -p'mysqladmin' -e "USE db_test;SHOW tables;"
```
提取目录下的备份文件名
```bash
ls| awk -F "." '{print $1}'
sed 's#_bak.sql.gz##g'
```
循环恢复
```bash
for dbname in `sed 's#_bak.sql.gz##g'`; do
gzip -d ${dbname}_bak.sql.gz;
mysql -uroot -p'mysqladmin' $dbname < ${dbname}_bak.sql;
done
```
