使用mysqldump对数据库备份和恢复

数据库存储引擎：InnoDB

启用binlong

一、恢复误删除的数据库到最新状态

1、备份数据库

mysqldump -A -F -uroot -p --master-data=2 --single-transaction > /backups/all-`date +%F_%T`.sql

2、对数据库误删除

MariaDB [reset_gq]> drop database reset_gq;

3、对数据库添加新数据

MariaDB [(none)]> create database kuc ;
MariaDB [(none)]> use kuc;
MariaDB [kuc]> create table cun_dg(id int);

4、发现问题对数据库进行恢复

MariaDB [(none)]> flush tables with read lock; #全局锁，数据库只读
MariaDB [(none)]> flush logs; #刷新日志

cp /www/server/data/mysql-bin.000015 /backups/ #拷贝一份修改
mysqlbinlog --start-position=366 mysql-bin.000015 > bin.sql

修改bin.sql，找到误操作命令注释或删除

MariaDB [(none)]> set sql_log_bin=0; #暂停二进制日记
MariaDB [(none)]> unlock tables; #解锁
cp /backups/all-2018-02-27_17\:50\:26.sql /backups/mysql.sql #改名
MariaDB [(none)]> source /backups/mysql.sql; #恢复数据库到备份状态
MariaDB [reset_gq]> source /backups/bin.sql; #恢复数据库到最新状态
MariaDB [kuc]> set sql_log_bin=1; #启动二进制日记

5、查询恢复是否完整

二、基于LVM的备份还原

1、分离数据和binlog到各自的逻辑卷

2、锁定所有表

MariaDB [(none)]> flush tables with read lock;

3、记录二进制日记文件及事件位置

MariaDB [(none)]> flush logs; #刷新日记
mysql -uroot -p -e 'show master logs' > /app/pos.log #将日记位置记录到文件

4、创建快照

lvcreate -n mysqldata-snapshot -s -p r -L 3G /dev/vg0/mysqldata

5、释放锁，修改部分数据

MariaDB [(none)]> unlock tables;
MariaDB [(none)]> create database lvsceshi;

6、挂载快照卷，执行数据备份

mkdir /mnt/snap
mount -o nouuid,norecovery /dev/vg0/mysqldata-snapshot /mnt/snap
mkdir /backups
cp -a /mnt/snap/* /backups/

7、备份完成后，删除快照卷

umount /mnt/snap/
lvremove /dev/vg0/mysqldata-snapshot

8、破坏数据库

systemctl stop mysqld
rm -rf /data/mysqldata/*

9、还原

cp -a /backups/* /data/mysqldata/
systemctl start mysqld

10、binlong还原到最新状态

MariaDB [(none)]>flush tables with read lock; #锁定所有表
cp -a /data/binlongs/mysql-bin.00000{4..6} /app/
cd /app/
mysqlbinlog --start-position=385 mysql-bin.000004 > bin.sql
mysqlbinlog mysql-bin.000005 >> bin.sql
mysqlbinlog mysql-bin.000006 >> bin.sql
MariaDB [(none)]> set sql_log_bin=0; #临时关闭二进制日记
MariaDB [(none)]> unlock tables; #解锁
MariaDB [(none)]> source /app/bin.sql #还原
MariaDB [(none)]> set sql_log_bin=1; #开启二进制日记