mysqldump客户端及恢复数据案例

1.常用参数

1.不加参数：用于备份单个表
    1）备份库
    [root@db02 ~]# mysqldump ku > /tmp/ku.sql
    2）备份表
    [root@db02 ~]# mysqldump ku test > /tmp/ku.sql
    3）备份多个表
    [root@db02 ~]# mysqldump ku test test2 test3 > /tmp/ku.sql
    #注意：当不加参数时命令后面跟的是库名，库的后面全都是必须是库下面的表名

2.连接服务端参数(基本参数)：-u -p -h -P -S

3.-A, --all-databases：全库备份

4.-B：指定库备份
[root@db01 ~]# mysqldump -uroot -p123 -B db1 > /backup/db1.sql
[root@db01 ~]# mysqldump -uroot -p123 -B db1 db2 > /backup/db1_db2.sql

5.-F：flush logs在备份时自动刷新binlog（不怎么常用）
[root@db01 backup]# mysqldump -uroot -p123 -A -F > /backup/full_2.sql

6.--master-data=2：备份时加入change master语句0没有1不注释2注释
	1）等于2：记录binlog信息，并注释（日常备份）
	2）等于1：记录binlog信息，不注释（扩展从库）
	0）等于0：不记录binlog信息
	[root@db01 backup]# mysqldump -uroot -p123 --master-data=2 >/backup/full.sql

7.--single-transaction：快照备份

8.-d：仅表结构
9.-t：仅数据

10.-R, --routines：备份存储过程和函数数据
11.--triggers：备份触发器数据
12.gzip:压缩备份
	#备份成压缩包
	[root@db01 ~]# mysqldump -uroot -p123 -A | gzip > /backup/full.sql.gz
	#恢复压缩包中的数据
	[root@db03 ~]# zcat /tmp/full.sql.gz | mysql -uroot -p123

#完整的备份命令：
mysqldump -uroot -p123 -A -R --triggers --master-data=2 –-single-transaction > /tmp/full.sql

2.注意

1）mysqldump在备份和恢复时都需要MySQL实例启动为前提
2）一般数据量级100G以内，大约15-30分钟可以恢复
3）mysqldump是以覆盖的形式恢复数据的

案例

1.背景

1.正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。
2.备份策略：每天23：00，计划任务调用mysqldump执行全备脚本
3.故障时间点：上午10点开发人员误删除一个核心业务表，如何恢复？

2.思路

1.停库，避免二次伤害
2.创建新库
3.倒入前一天的全备
4.通过binlog找到前一天23：00到第二天10点之间的数据
5.导入找到的新数据
6.恢复业务
	a.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）
	b.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）

3.模拟案例

1.模拟生产数据

mysql> create database dump;
mysql> use dump
mysql> create table dump(id int);
mysql> insert dump values(1),(2),(3),(4);
mysql> select * from dump;

2.模拟23:00全备

[root@db03 mysql]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql

3.模拟23：00到10:00的数据操作

mysql> use dump
mysql> insert dump values(1000),(2000),(3000),(4000);

4.模拟删库

mysql> drop database dump;

4.恢复数据

1.停库，避免二次伤害

[root@db03 mysql]# systemctl stop mysqld

2.在新服务器上安装新的 MySQL 数据库

# 在一台新的服务器上，安装一个新的 MySQL 数据库
# 如果之前安装过 MySQL， 可以将 $basedir/data/ 下的数据全部删除
[root@dbtest02 ~]# rm -rf /usr/local/mysql/data/*

# 再重新初始化，该数据库就又变成 "新数据库" 了
[root@dbtest02 ~]# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

3.导入前一天的全备

#老库将数据传输到新库
[root@db03 ~]# scp /tmp/full.sql 172.16.1.52:/tmp/
#新库导入全备数据
[root@db02 ~]# mysql < /tmp/full.sql

4.通过binlog找到前一天23：00到第二天10点之间的数据

1.找到binlog的起始位置点
[root@db03 data]# head -22 /tmp/full.sql | tail -1
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=971790;

2.找到结束位置点
[root@db03 data]# mysqlbinlog mysql-bin.000006 > 1.txt
[root@db03 data]# vim 1.txt
过滤 drop database dump，取出命令上面的位置点
# at 972068
......
drop database dump

3.取出位置点之间的数据
[root@db03 data]# mysqlbinlog -d dump --start-position=971790 --stop-position=972068 mysql-bin.000006 > /tmp/new.sql

5.导入找到的新数据

#老库将binlog数据传到新库
[root@db03 ~]# scp /tmp/new.sql 172.16.1.52:/tmp/

#新库导入新的数据
[root@db02 ~]# mysql < /tmp/new.sql

6.确认数据

mysql> use dump

mysql> show tables;
+----------------+
| Tables_in_dump |
+----------------+
| dump           |
+----------------+

mysql> select * from dump;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
| 1000 |
| 2000 |
| 3000 |
| 4000 |
+------+

5.恢复业务

1.直接使用临时库顶替原生产库，前端应用割接到新库（数据量特别大的时候）
2.将误删除的表单独导出，然后导入到原生产环境（数据量小的时候）

# 第二种恢复业务的操作
# 1）新库导出指定业务库，若不使用 -B 选项，就没有建库语句
[root@dbtest02 ~]# mysqldump -B business > /tmp/business.sql
# 2）新库将数据推送回老库
[root@dbtest02 ~]# scp /tmp/business.sql 172.16.1.121:/tmp
# 3）将恢复的数据导入老库
mysql> source  /tmp/business.sql