DevOps开发运维
成长之路

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,就是所有库的所有表的单独备份语句
赞(0)

评论 抢沙发

评论前必须登录!

 

LNMP社群 不仅仅是技术

关于我们网站地图