MySQL實(shí)時(shí)在線備份恢復(fù)方案
? 開源Linux
長按二維碼加關(guān)注~
上一篇：2020年MySQL數(shù)據(jù)庫面試題總結(jié)
快照和復(fù)制技術(shù)的結(jié)合可以保證我們得到一個(gè)實(shí)時(shí)的在線MySQL備份解決方案。
當(dāng)主庫發(fā)生誤操作時(shí)，只需要恢復(fù)備庫上的快照，然后再根據(jù)binlog執(zhí)行point-in-time的恢復(fù)即可。
下面假定一個(gè)場景：
主從架構(gòu)，沒有延遲，某DBA誤操作：drop database
接下來我們按照以上場景進(jìn)行備份恢復(fù)模擬測試
1.主庫準(zhǔn)備測試數(shù)據(jù)
mysql> create database cnfol;Query OK, 1 row affected (0.00 sec)mysql> create table cnfol.t (id int primary key);Query OK, 0 rows affected (0.02 sec)mysql> insert into cnfol.t select 1;Query OK, 1 row affected (0.01 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> insert into cnfol.t select 2;Query OK, 1 row affected (0.00 sec)Records: 1 Duplicates: 0 Warnings: 0
到備庫確認(rèn)：
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || cnfol || mysql || test |+--------------------+4 rows in set (0.00 sec)mysql> select * from cnfol.t;+----+| id |+----+| 1 || 2 |+----+2 rows in set (0.00 sec)
2.加個(gè)全局讀鎖
在備庫
flush tables with read lock;Query OK, 0 rows affected (0.00 sec)
3.為備庫所在分區(qū)創(chuàng)建快照
[]Logical volume "backup_mysql" created[]LV VG Attr LSize Origin Snap% Move Log Copy% Convertbackup_mysql vg swi-a- 1.00G mysql 0.00mysql vg owi-ao 2.00G
4.獲取二進(jìn)制日志坐標(biāo)
在備庫：mysql> show master status;+------------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |+------------------+----------+--------------+------------------+| mysql-bin.000003 | 727 | | |+------------------+----------+--------------+------------------+1 row in set (0.00 sec)
解鎖
在備庫：
unlock tables;Query OK, 0 rows affected (0.00 sec)
6.掛載快照
[root@localhost ~]# mount /dev/vg/backup_mysql /mnt/backup[root@localhost ~]# cd /mnt/backup/mysql/data/cnfol/ && ls -alh總計(jì) 32Kdrwx------ 2 mysql dba 4.0K 10-14 09:57 .drwx------ 5 mysql dba 4.0K 10-14 09:57 ..-rw-rw---- 1 mysql dba 61 10-14 09:57 db.opt-rw-rw---- 1 mysql dba 8.4K 10-14 09:57 t.frm-rw-rw---- 1 mysql dba 14 10-14 09:57 t.MYD-rw-rw---- 1 mysql dba 2.0K 10-14 10:06 t.MYI
7.主庫某無經(jīng)驗(yàn)DBA誤操作
drop database cnfol;Query OK, 1 row affected (0.05 sec)
記錄下此時(shí)時(shí)間：2013-10-14 10:17:10
備庫確認(rèn)是否存在庫cnfol:
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || test |+--------------------+3 rows in set (0.01 sec)
8.備份快照
[root@localhost backup]# pwd/mnt/backup[root@localhost?backup]#?tar?-jcv?-f?/mnt/snapshot/mysql.tar.bz2?*
這里做備份的原因有2點(diǎn)
* 其一，昂貴的IO，因?yàn)榇蓬^要在快照區(qū)和系統(tǒng)區(qū)來回跑
* 其二，快照區(qū)空間不足，因?yàn)槭荂OW原理
9.刪除快照
[][]Logical?volume?"backup_mysql"?successfully?removed
10.格式化備庫所在分區(qū)
[]$ mysqladmin -uroot -poracle shutdown131014 10:32:40 mysqld_safe mysqld from pid file /mnt/lvm/mysql/data/localhost.localdomain.pid ended[]+ Done mysqld_safe[][][][]LV VG Attr LSize Origin Snap% Move Log Copy% Convertmysql vg -wi-ao 2.00G[]VGvg 4 1 0 wz--n- 3.81G 1.81G
11.解壓縮快照到備庫所在分區(qū)
[]/mnt/lvm[]lost+found mysql
12.啟動(dòng)MySQL
13.利用binlog執(zhí)行point-in-time恢復(fù)
[mysql@localhost ~]$ mysqlbinlog --stop-datetime="2013-10-14 10:17:10" /mnt/lvm/mysql/data/mysql-bin.000003 | mysql -uroot -poracle14.確認(rèn)數(shù)據(jù)
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || cnfol || mysql || test |+--------------------+4 rows in set (0.00 sec)mysql> select * from cnfol.t;+----+| id |+----+| 1 || 2 |+----+2 rows in set (0.00 sec)
原文鏈接：http://www.jh-floor.com/shujuku/Mariadb/10070.html
作者：jh-floor