MySQL的mysqldump备份恢复

mysqldump

mysqldump它可以把整个数据库装载到一个单独的文本文件中。这个文件包含有所有重建您的数据库所需要的SQL命令。这个命令取得所有的模式并且将其转换成DDL语法，并且从这些数据中创建INSERT语句。因为所有的东西都被包含到了一个文本文件中。

优点：
1.不需要安装下载
2.备份出来的是sql文本格式，可读性高，便于备份处理
3.压缩比较高，节省备份的磁盘空间

缺点：
依赖于数据库引擎，需要从磁盘把数据读出，然后转换成sql进行转储，比较消耗资源，数据量大的话效率较低

参数：

-A  全备
[root@db01 ~]# mysqldump -uroot -p123  -A >/data/backup/full.sql 

-B  备份指定库
[root@db01 ~]# mysqldump -uroot -p123  -B  库名>/data/backup/full1.sql 

备单表，没有参数，直接跟上库名和表名
[root@db01 ~]# mysqldump -uroot -p123 库名 表名 >/data/backup/full2.sql

高级参数：

--triggers:导出触发器
-R：（ --routines）导出存储过程以及自定义函数 
-E： (--events）导出事件
--master-data=2:把备份开始时间点的position号记录在30行左右
--single-transaction:对于innodb引擎的表进行快照备份
--set-gtid-purget=off：记录gtid号，个人建议在单纯的备份时要关上，在搭建主从复制时，必须要=on或者=auto

以上六个参数建议在备份时必须加上

模拟故障案例并恢复：

某公司每天凌晨0点进行全备，周三下午两点误删除数据库
db01 [(none)]>show databases;
 +----------------------------------+
 | Database                         |
 +-------------------- --------------
 | information_schema               |
 | mysql                            |
 | performance_schema               |
 | sys                              |
 | world                            |
 +---------------------------------+
模拟周二晚上全备
[root@db01 ~]# mysqldump -uroot -p -A -R -E --triggers --master-data=2  --single-transaction --set-gtid-purged=OFF >/test/full.sql 
[root@db01 ~]#  ll /test/
 -rw-r--r-- 1 root root 1026001 May 24 20:18 full.sql

全备做完，模拟周二全备后产生的数据，创建test库，t1表，插入数据
db01 [test1]>select * from t1;
 +------+
 | id   |
 +------+
 |    1 |
 |    2 |
 |    3 |
 |    1 |
 |    2 |
 |    3 |
 |    1 |
 |    2 |
 |    3 |
 |    1 |
 |    2 |
 |    3 |
 +------+

模拟误删除rm -rf /data/mysql/* 删除数据库中所有文件
此时，我们有一个全备，二进制文件时完整的，所以，我们可以利用全备恢复到周二凌晨，用二进制文件恢复两点之前的数据变化

查看全备文件中取到二进制文件的position号，从此号开始截取二进制日志

[root@db01 ~]# cat /test/full.sql
找出下面这句话
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000023', MASTER_LOG_POS=577;
得出是23号文件577号开始，所以开始截取二进制文件
[root@db01 ~]# mysqlbinlog --start-position=577 --skip-gtids  /data/binlog/mysql-bin.000023 >/test/bin.sql [root@db01 ~]# ll /test -rw-r--r-- 1 root root    1628 May 24 20:31 bin.sql -rw-r--r-- 1 root root 1026001 May 24 20:18 full.sql
到这一步，我们有周二的全备，有新的数据变化二进制日志，开始恢复
db01 [(none)]>source /test/full.sql;
db01 [(none)]>source /test/bin.sql;

恢复完，进行检查

db01 [(none)]>use test
db01 [(none)]>elect * from t1;

MySQL数据和结构分开备份方法：

只导出结构不导出数据
mysqldump --opt -d 数据库名 -uroot  -p  >xxx.sql

导出数据不导出结构
mysqldump -t 数据库名 -uroot -p >xxx.sql

导出特定表结构
mysqldump  -uroot  -p  -B 数据库名  --table  表名 >xxx.sql

从mysqldump 全备中获取库和表的备份

1、获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql

2、获得INSERT INTO 语句，用于数据的恢复

# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &

3.获取单库的备份

# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

利用concat生成整个world库下的所有表的单独备份语句

就是拼接备份语句，自己添加的需要用引号引起来
db01 [information_schema]>SELECT CONCAT("mysqldump -uroot -p123 ",TABLE_SCHEMA ," ",table_name,"
>/bak/",TABLE_SCHEMA,"_",table_name,".sql")  
    -> FROM tables
    -> WHERE table_schema='world'
    -> INTO OUTFILE '/tmp/bak.sh';
Query OK, 3 rows affected (0.41 sec)
[root@db01 ~]# cat /tmp/bak.sh 
 mysqldump -uroot -p123 world city>/bak/world_city.sql
 mysqldump -uroot -p123 world country>/bak/world_country.sql
 mysqldump -uroot -p123 world countrylanguage>/bak/world_countrylanguage.sql

去掉where，就是所有库的所有表的单独备份语句