使用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;      #開啟二進位日記