基於percona xtrabackup之innobackupex實現基於時間點資料庫恢復
前言
資料庫在執行期間，可能是因為人為原因誤操作或者出現儲存故障，導致某些表不能正常訪問，此時，可以使用基於時間點的恢復，採用資料庫備份及二進位制日誌，把誤操作或出現故障的資料還原回來，建議不要在生產系統中進行基於時間點的恢復，可以在一個臨時的環境把資料恢復出來，然後讓業務及開發確認，然後再把資料導回到生產資料庫，這樣也不影響資料庫中其它業務的執行。
基於時間點或基於二進位制日誌位置的資料庫恢復
1,資料庫的當前資料庫
mysql> select * from zxydb.t_go;
+----+------+
| a | b |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
| 8 | 8 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
+----+------+
8 rows in set (0.00 sec)
2，構建資料庫的基準備份
--構建儲存資料庫基準備份的目錄
[root@standbygtid mysql]# mkdir -p /database_base_dir
[root@standbygtid mysql]# innobackupex -uroot -psystem /database_base_dir --no-timestamp
[root@standbygtid mysql]# cd /database_base_dir/
[root@standbygtid database_base_dir]# ll
總用量 77868
-rw-r----- 1 root root 418 11月 5 00:56 backup-my.cnf
drwxr-x--- 2 root root 4096 11月 5 00:56 completedb
-rw-r----- 1 root root 79691776 11月 5 00:56 ibdata1
drwxr-x--- 2 root root 4096 11月 5 00:56 mysql
drwxr-x--- 2 root root 4096 11月 5 00:56 performance_schema
drwxr-x--- 2 root root 4096 11月 5 00:56 test
-rw-r----- 1 root root 18 11月 5 00:56 xtrabackup_binlog_info
-rw-r----- 1 root root 115 11月 5 00:56 xtrabackup_checkpoints
-rw-r----- 1 root root 507 11月 5 00:56 xtrabackup_info
-rw-r----- 1 root root 2560 11月 5 00:56 xtrabackup_logfile
drwxr-x--- 2 root root 4096 11月 5 00:56 zxydb
3，準備用於恢復上述的基準備份
（注：其實就是用線上日誌應用到基準備份，確保資料一致性）
[root@standbygtid database_base_dir]# innobackupex -uroot -psystem --apply-log /database_base_dir
[root@standbygtid database_base_dir]# ll
總用量 196652
-rw-r----- 1 root root 418 11月 5 00:56 backup-my.cnf
drwxr-x--- 2 root root 4096 11月 5 00:56 completedb
-rw-r----- 1 root root 79691776 11月 5 01:01 ibdata1
-rw-r----- 1 root root 50331648 11月 5 01:01 ib_logfile0
-rw-r----- 1 root root 50331648 11月 5 01:01 ib_logfile1
-rw-r----- 1 root root 12582912 11月 5 01:01 ibtmp1
drwxr-x--- 2 root root 4096 11月 5 00:56 mysql
drwxr-x--- 2 root root 4096 11月 5 00:56 performance_schema
drwxr-x--- 2 root root 4096 11月 5 00:56 test
-rw-r----- 1 root root 18 11月 5 00:56 xtrabackup_binlog_info
-rw-r--r-- 1 root root 19 11月 5 01:01 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 115 11月 5 01:01 xtrabackup_checkpoints
-rw-r----- 1 root root 507 11月 5 00:56 xtrabackup_info
-rw-r----- 1 root root 8388608 11月 5 01:01 xtrabackup_logfile
drwxr-x--- 2 root root 4096 11月 5 00:56 zxydb
4，檢視上述基準備份對應的二進位制日誌及位置
（注：如下基於時間點恢復就是從這個二進位制日誌及位置開始恢復到某個時間點，很重要喲）
[root@standbygtid database_base_dir]# more xtrabackup_binlog_info
binlog.000001 120
[root@standbygtid database_base_dir]#
5，為了更好模擬基於時間點恢復，切換二進位制日誌，產生新的二進位制日誌
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
6，檢視當前的二進位制日誌及位置
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000002 | 337 | | | |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
--共計2個二進位制日誌
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| binlog.000001 | 164 |
| binlog.000002 | 337 |
+---------------+-----------+
2 rows in set (0.00 sec)
7，產生資料變更
mysql> use zxydb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from t_go;
+----+------+
| a | b |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
| 8 | 8 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
+----+------+
8 rows in set (0.00 sec)
mysql> insert into t_go select 13,13;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
8，檢視當前二進位制日誌對應的事件資訊
mysql> show binlog events in 'binlog.000002';
+---------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
| binlog.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-enterprise-commercial-advanced-log, Binlog ver: 4 |
| binlog.000002 | 120 | Query | 1 | 201 | BEGIN |
| binlog.000002 | 201 | Query | 1 | 306 | use `zxydb`; insert into t_go select 13,13 |
| binlog.000002 | 306 | Xid | 1 | 337 | COMMIT /* xid=41 */ |
+---------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
4 rows in set (0.00 sec)
9，繼續變化資料
mysql> insert into t_go select 18,18;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> show binlog events in 'binlog.000002';
+---------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+---------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
| binlog.000002 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.25-enterprise-commercial-advanced-log, Binlog ver: 4 |
| binlog.000002 | 120 | Query | 1 | 201 | BEGIN |
| binlog.000002 | 201 | Query | 1 | 306 | use `zxydb`; insert into t_go select 13,13 |
| binlog.000002 | 306 | Xid | 1 | 337 | COMMIT /* xid=41 */ |
| binlog.000002 | 337 | Query | 1 | 418 | BEGIN |
| binlog.000002 | 418 | Query | 1 | 523 | use `zxydb`; insert into t_go select 18,18 |
| binlog.000002 | 523 | Xid | 1 | 554 | COMMIT /* xid=48 */ |
+---------------+-----+-------------+-----------+-------------+----------------------------------------------------------------------+
7 rows in set (0.00 sec)
10，因為基於時間點恢復要用於二進位制日誌，備份下述的二進位制檔案到另一個目錄
（注：即使二進位制日誌非常重要，一定要定期進行備份，不然真要進行基於時間點恢復，而所需要的二進位制日誌又沒有了，哪就會導致資料損失了）
[root@standbygtid database_base_dir]# cd /var/lib/mysql
[root@standbygtid mysql]# ll
總用量 188464
-rw-rw---- 1 mysql mysql 56 11月 4 23:52 auto.cnf
-rw-rw---- 1 mysql mysql 164 11月 5 01:06 binlog.000001
-rw-rw---- 1 mysql mysql 554 11月 5 01:12 binlog.000002
-rw-rw---- 1 mysql mysql 32 11月 5 01:06 binlog.index
drwxr-x--- 2 mysql mysql 4096 11月 4 23:48 completedb
-rw-r----- 1 mysql mysql 79691776 11月 5 01:12 ibdata1
-rw-r----- 1 mysql mysql 50331648 11月 5 01:12 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 11月 4 23:48 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 11月 4 23:48 ibtmp1
drwxr-x--- 2 mysql mysql 4096 11月 4 23:48 mysql
srwxrwxrwx 1 mysql mysql 0 11月 4 23:52 mysql.sock
-rw------- 1 root root 159 11月 4 23:52 nohup.out
drwxr-x--- 2 mysql mysql 4096 11月 4 23:48 performance_schema
-rw-r----- 1 mysql root 2508 11月 4 23:52 standbygtid.err
-rw-rw---- 1 mysql mysql 6 11月 4 23:52 standbygtid.pid
drwxr-x--- 2 mysql mysql 4096 11月 4 23:48 test
drwxr-x--- 2 mysql mysql 4096 11月 4 23:48 zxydb
[root@standbygtid mysql]# mysqlbinlog binlog.000001 binlog.000002 --start-position=120 --stop-position=337 > /base_binlog_pos.sql
[root@standbygtid mysql]#
11,關閉資料庫
mysqladmin -uroot -psystem shutdown
12，物理刪除資料檔案
[root@standbygtid mysql]# pwd
/var/lib/mysql
[root@standbygtid mysql]# rm -rf *
13，還原上述的備份到資料庫的資料檔案目錄
[root@standbygtid mysql]# innobackupex --datadir=/var/lib/mysql --copy-back /database_base_dir
14，授權資料庫的資料檔案目錄
[root@standbygtid mysql]# chown -Rf mysql:mysql *
[root@standbygtid mysql]# ll
總用量 188448
drwxr-x--- 2 mysql mysql 4096 11月 5 01:49 completedb
-rw-r----- 1 mysql mysql 79691776 11月 5 01:49 ibdata1
-rw-r----- 1 mysql mysql 50331648 11月 5 01:49 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 11月 5 01:49 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 11月 5 01:49 ibtmp1
drwxr-x--- 2 mysql mysql 4096 11月 5 01:49 mysql
drwxr-x--- 2 mysql mysql 4096 11月 5 01:49 performance_schema
drwxr-x--- 2 mysql mysql 4096 11月 5 01:49 test
-rw-r----- 1 mysql mysql 19 11月 5 01:49 xtrabackup_binlog_pos_innodb
-rw-r----- 1 mysql mysql 507 11月 5 01:49 xtrabackup_info
drwxr-x--- 2 mysql mysql 4096 11月 5 01:49 zxydb
15，刪除上述與xtrabackup相關的檔案
[root@standbygtid mysql]# rm -rf xtrabackup_*
16，重啟資料庫
[root@standbygtid mysql]# nohup mysqld_safe --user=mysql&
17，基於時間點恢復
（注：我們就恢復到二進位制日誌的 337，見上述的第9步）
[root@standbygtid mysql]# mysql -uroot -psystem < /base_binlog_pos.sql
Warning: Using a password on the command line interface can be insecure.
[root@standbygtid mysql]#
mysql> select * from zxydb.t_go;
+----+------+
| a | b |
+----+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 5 | 5 |
| 8 | 8 |
| 10 | 10 |
| 11 | 11 |
| 12 | 12 |
| 13 | 13 |
+----+------+
9 rows in set (0.00 sec)
相關文章
基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6
http://blog.itpub.net/9240380/viewspace-2662613/
基於percona xtrabackup之xtrabackup實現mysql增量備份及恢復資料庫
https://mp.weixin.qq.com/s?__biz=MzIwMjU2MjI1OQ==&mid=2247484166&idx=1&sn=3a838774506ed31a7a936690e9c80060&chksm=96dd8cbaa1aa05ac86340db0b5da6444fd4f4d6f0b89af21bad1f0c220918a9eee37cb935eda&token=1922309711&lang=zh_CN#rd
聯絡方式
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-2662688/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 備份與恢復：Polardb資料庫資料基於時間點恢復資料庫
- ORACLE資料庫基於時間點的不完全恢復Oracle資料庫
- 基於percona xtrabackup 2.4.14的增量備份恢復還原mysql 5.6MySql
- mongodb 基於oplog的時間點恢復MongoDB
- DM8 基於時間點的恢復
- MySQL binlog基於時間點恢復資料失敗是什麼鬼？MySql
- MySQL備份與恢復——基於Xtrabackup物理備份恢復MySql
- 7.5 使用binary log 做基於時間點的恢復
- 基於 Vuex 的時移操作（撤回/恢復）實現Vue
- 基於GTID恢復誤篡改資料
- 基於Docker搭建Percona XtraDB Cluster資料庫叢集Docker資料庫
- mongodb異機做時間點恢復(基於時間範圍查詢匯出oplog)MongoDB
- 基於MySQL資料庫討論虛擬機器資料恢復MySql資料庫虛擬機資料恢復
- 2.MongoDB 4.2副本集環境基於時間點的恢復MongoDB
- 資料庫系列——基於Canal實現MySQL增量資料同步資料庫MySql
- oracle基於SCN增量恢復Oracle
- 1.MongoDB 2.7主從複製(master –> slave)環境基於時間點的恢復MongoDBAST
- 基於json資料格式實現的簡單資料庫——jsonDBJSON資料庫
- [python] 基於PyOD庫實現資料異常檢測Python
- 《PostgreSQL 指南：內幕探索》之基礎備份與時間點恢復（上）SQL
- 聊聊基於docker部署的mysql如何進行資料恢復DockerMySql資料恢復
- 快速啟動：基於CRaC實現Spring Boot 3恢復預熱Spring Boot
- 基於linux系統，fsck後資料丟失的資料恢復方案Linux資料恢復
- DM7使用DMRMAN恢復資料庫到指定時間點/LSN資料庫
- 利用innobackupex備份集恢復指定庫
- oracle資料庫損壞的恢復過程-基於IBM伺服器儲存Oracle資料庫IBM伺服器
- 基於C++11的資料庫連線池實現C++資料庫
- SpringBoot基於資料庫實現簡單的分散式鎖Spring Boot資料庫分散式
- 基於資料庫、redis和zookeeper實現的分散式鎖資料庫Redis分散式
- PostgreSQL 時間點恢復SQL
- 大事務導致資料庫恢復時間長資料庫
- 堅如磐石：TiDB 基於時間點的恢復（PiTR）特性最佳化之路丨6.5 新特性解析TiDB
- 基於webrtc實現點對點桌面分享Web
- 基於Mybatis-Plus實現自動化操作建立時間和修改時間MyBatis
- 【資料庫資料恢復】SQL Server資料庫磁碟空間不足的資料恢復案例資料庫資料恢復SQLServer
- 【DATAGUARD】Oracle 通過Dataguard指定恢復時間用於找回丟失資料Oracle
- 基於redis實現定時任務Redis
- python基於opencv 實現影像時鐘PythonOpenCV