溫馨提示×

溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊×
其他方式登錄
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》

利用binlog進(jìn)行數(shù)據(jù)庫的還原

發(fā)布時間：2020-08-06 17:22:38 來源：ITPUB博客 閱讀：145 作者：wg0411 欄目：MySQL數(shù)據(jù)庫
前言：在學(xué)習(xí)mysql備份的時候，深深的感受到mysql的備份還原功能沒有oracle強(qiáng)大；比如一個很常見的恢復(fù)場景：基于時間點的恢復(fù)，oracle通過rman工具就能夠很快的實現(xiàn)數(shù)據(jù)庫的恢復(fù)，但是mysql在進(jìn)行不完全恢復(fù)的時候很大的一部分要依賴于mysqlbinlog這個工具運行binlog語句來實現(xiàn)，本文檔介紹通過mysqlbinlog實現(xiàn)各種場景的恢復(fù)；
一、測試環(huán)境說明：使用mysqlbinlog工具的前提需要一個數(shù)據(jù)庫的完整性備份，所以需要事先對數(shù)據(jù)庫做一個完整的備份，本文檔通過mysqlbackup進(jìn)行數(shù)據(jù)庫的全備（mysqlbackup的使用：http://blog.itpub.net/12679300/viewspace-1329578/）；

二、測試步驟說明：
數(shù)據(jù)庫的插入準(zhǔn)備工作
2.1 在時間點A進(jìn)行一個數(shù)據(jù)庫的完整備份；
2.2 在時間點B創(chuàng)建一個數(shù)據(jù)庫BKT,并在BKT下面創(chuàng)建一個表JOHN，并插入5條數(shù)據(jù)；
2.3 在時間點C往表JOHN繼續(xù)插入數(shù)據(jù)到10條；

數(shù)據(jù)庫的恢復(fù)工作
2.4 恢復(fù)數(shù)據(jù)庫到時間點A,然后檢查數(shù)據(jù)庫表的狀態(tài)；
2.5 恢復(fù)數(shù)據(jù)庫到時間點B,檢查相應(yīng)的系統(tǒng)狀態(tài)；
2.6 恢復(fù)數(shù)據(jù)庫到時間點C，并檢查恢復(fù)的狀態(tài)；
三、場景模擬測試步驟（備份恢復(fù)是一件很重要的事情）
3.1 執(zhí)行數(shù)據(jù)庫的全備份；

點擊(此處)折疊或打開

  1. [root@mysql01 backup]# mysqlbackup --user=root --password --backup-dir=/backup backup-and-apply-log //運行數(shù)據(jù)庫的完整備份
3.2 創(chuàng)建數(shù)據(jù)庫、表并插入數(shù)據(jù)

點擊(此處)折疊或打開

  1. mysql> SELECT CURRENT_TIMESTAMP;
  2. +---------------------+
  3. | CURRENT_TIMESTAMP |
  4. +---------------------+
  5. | 2014-11-26 17:51:27 |
  6. +---------------------+
  7. 1 row in set (0.01 sec)

  9. mysql> show databases; //尚未創(chuàng)建數(shù)據(jù)庫BKT
  10. +--------------------+
  11. | Database |
  12. +--------------------+
  13. | information_schema |
  14. | john |
  15. | mysql |
  16. | performance_schema |
  17. +--------------------+
  18. 4 rows in set (0.03 sec)

  20. mysql> Ctrl-C --
  21. Aborted
  22. [root@mysql02 data]# mysql -uroot -p
  23. Enter password:
  24. Welcome to the MySQL monitor. Commands end with ; or \\g.
  25. Your MySQL connection id is 2
  26. Server version: 5.5.36-log Source distribution
  27. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
  28. Oracle is a registered trademark of Oracle Corporation and/or its
  29. affiliates. Other names may be trademarks of their respective
  30. owners.
  31. Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
  32. mysql> show master status;
  33. +------------------+----------+--------------+------------------+
  34. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  35. +------------------+----------+--------------+------------------+
  36. | mysql-bin.000001 | 107 | | | //當(dāng)前數(shù)據(jù)庫log的pos狀態(tài)
  37. +------------------+----------+--------------+------------------+
  38. 1 row in set (0.00 sec)
  39. mysql> SELECT CURRENT_TIMESTAMP; //當(dāng)前的時間戳 當(dāng)前時間點A
  40. +---------------------+
  41. | CURRENT_TIMESTAMP |
  42. +---------------------+
  43. | 2014-11-26 17:54:12 |
  44. +---------------------+
  45. 1 row in set (0.00 sec)
  46. mysql> create database BKT; //創(chuàng)建數(shù)據(jù)庫BKT
  47. Query OK, 1 row affected (0.01 sec)
  48. mysql> create table john (id varchar(32));
  49. ERROR 1046 (3D000): No database selected
  50. mysql> use bkt;
  51. ERROR 1049 (42000): Unknown database \'bkt\'
  52. mysql> use BKT;
  53. Database changed
  54. mysql> create table john (id varchar(32));
  55. Query OK, 0 rows affected (0.02 sec)
  56. mysql> insert into john values(\'1\');
  57. Query OK, 1 row affected (0.01 sec)
  58. mysql> insert into john values(\'2\');
  59. Query OK, 1 row affected (0.01 sec)
  60. mysql> insert into john values(\'3\');
  61. Query OK, 1 row affected (0.00 sec)
  62. mysql> insert into john values(\'4\');
  63. Query OK, 1 row affected (0.01 sec)
  64. mysql> insert into john values(\'5\');
  65. Query OK, 1 row affected (0.01 sec)
  66. mysql> SELECT CURRENT_TIMESTAMP; //插入5條數(shù)據(jù)后數(shù)據(jù)庫的時間點B，記錄該點便于數(shù)據(jù)庫的恢復(fù)
  67. +---------------------+
  68. | CURRENT_TIMESTAMP |
  69. +---------------------+
  70. | 2014-11-26 17:55:53 |
  71. +---------------------+
  72. 1 row in set (0.00 sec)
  73.  
  74. mysql> show master status;
  75. +------------------+----------+--------------+------------------+
  76. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  77. +------------------+----------+--------------+------------------+
  78. | mysql-bin.000001 | 1204 | | | //當(dāng)前binlog的pos位置
  79. +------------------+----------+--------------+------------------+
  80. 1 row in set (0.00 sec)
 3.3 設(shè)置時間點C的測試

點擊(此處)折疊或打開

  1. mysql> insert into john values(\'6\');
  2. Query OK, 1 row affected (0.02 sec)
  3. mysql> insert into john values(\'7\');
  4. Query OK, 1 row affected (0.01 sec)
  5. mysql> insert into john values(\'8\');
  6. Query OK, 1 row affected (0.01 sec)
  7. mysql> insert into john values(\'9\');
  8. Query OK, 1 row affected (0.01 sec)
  9. mysql> insert into john values(\'10\');
  10. Query OK, 1 row affected (0.03 sec)
  11. mysql> show master status;
  12. +------------------+----------+--------------+------------------+
  13. | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
  14. +------------------+----------+--------------+------------------+
  15. | mysql-bin.000001 | 2125 | | |
  16. +------------------+----------+--------------+------------------+
  17. 1 row in set (0.00 sec)
  18. mysql> SELECT CURRENT_TIMESTAMP;
  19. +---------------------+
  20. | CURRENT_TIMESTAMP |
  21. +---------------------+
  22. | 2014-11-26 17:58:08 |
  23. +---------------------+
  24. 1 row in set (0.00 sec)
 3.4 以上的操作完成之后，便可以執(zhí)行數(shù)據(jù)庫的恢復(fù)測試

點擊(此處)折疊或打開

  1. [root@mysql02 data]# mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql --backup-dir=/backup/ copy-back
  2. MySQL Enterprise Backup version 3.11.0 Linux-3.8.13-16.2.1.el6uek.x86_64-x86_64 [2014/08/26]
  3. Copyright (c) 2003, 2014, Oracle and/or its affiliates. All Rights Reserved.
  4.  mysqlbackup: INFO: Starting with following command line ...
  5.  mysqlbackup --defaults-file=/backup/server-my.cnf --datadir=/data/mysql
  6.         --backup-dir=/backup/ copy-back
  7.  mysqlbackup: INFO:
  8. IMPORTANT: Please check that mysqlbackup run completes successfully.
  9.            At the end of a successful \'copy-back\' run mysqlbackup
  10.            prints \"mysqlbackup completed OK!\".
  11. 141126 17:59:58 mysqlbackup: INFO: MEB logfile created at /backup/meta/MEB_2014-11-26.17-59-58_copy_back.log
  12. --------------------------------------------------------------------
  13.                        Server Repository Options:
  14. --------------------------------------------------------------------
  15.   datadir = /data/mysql
  16.   innodb_data_home_dir = /data/mysql
  17.   innodb_data_file_path = ibdata1:10M:autoextend
  18.   innodb_log_group_home_dir = /data/mysql/
  19.   innodb_log_files_in_group = 2
  20.   innodb_log_file_size = 5242880
  21.   innodb_page_size = Null
  22.   innodb_checksum_algorithm = none
  23. --------------------------------------------------------------------
  24.                        Backup Config Options:
  25. --------------------------------------------------------------------
  26.   datadir = /backup/datadir
  27.   innodb_data_home_dir = /backup/datadir
  28.   innodb_data_file_path = ibdata1:10M:autoextend
  29.   innodb_log_group_home_dir = /backup/datadir
  30.   innodb_log_files_in_group = 2
  31.   innodb_log_file_size = 5242880
  32.   innodb_page_size = 16384
  33.   innodb_checksum_algorithm = none
  34.  mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
  35. 141126 17:59:58 mysqlbackup: INFO: Copy-back operation starts with following threads
  36.         1 read-threads 1 write-threads
  37.  mysqlbackup: INFO: Could not find binlog index file. If this is online backup then server may not have started with --log-bin.
  38.         Hence, binlogs will not be copied for this backup. Point-In-Time-Recovery will not be possible.
  39. 141126 17:59:58 mysqlbackup: INFO: Copying /backup/datadir/ibdata1.
  40.  mysqlbackup: Progress in MB: 200 400 600
  41. 141126 18:00:22 mysqlbackup: INFO: Copying the database directory \'john\'
  42. 141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'mysql\'
  43. 141126 18:00:23 mysqlbackup: INFO: Copying the database directory \'performance_schema\'
  44. 141126 18:00:23 mysqlbackup: INFO: Completing the copy of all non-innodb files.
  45. 141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile0\'
  46. 141126 18:00:23 mysqlbackup: INFO: Copying the log file \'ib_logfile1\'
  47. 141126 18:00:24 mysqlbackup: INFO: Creating server config files server-my.cnf and server-all.cnf in /data/mysql
  48. 141126 18:00:24 mysqlbackup: INFO: Copy-back operation completed successfully.
  49. 141126 18:00:24 mysqlbackup: INFO: Finished copying backup files to \'/data/mysql\'
  50. mysqlbackup completed //數(shù)據(jù)庫恢復(fù)完成
 授權(quán)并打開數(shù)據(jù)庫

點擊(此處)折疊或打開

  1. [root@mysql02 data]# chmod -R 777 mysql //需要授權(quán)后才能打開
  2. [root@mysql02 data]# cd mysql
  3. [root@mysql02 mysql]# ll
  4. 總用量 733220
  5. -rwxrwxrwx. 1 root root 305 11月 26 18:00 backup_variables.txt
  6. -rwxrwxrwx. 1 root root 740294656 11月 26 18:00 ibdata1
  7. -rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile0
  8. -rwxrwxrwx. 1 root root 5242880 11月 26 18:00 ib_logfile1
  9. drwxrwxrwx. 2 root root 4096 11月 26 18:00 john
  10. drwxrwxrwx. 2 root root 4096 11月 26 18:00 mysql
  11. drwxrwxrwx. 2 root root 4096 11月 26 18:00 performance_schema
  12. -rwxrwxrwx. 1 root root 8488 11月 26 18:00 server-all.cnf
  13. -rwxrwxrwx. 1 root root 1815 11月 26 18:00 server-my.cnf //沒有BKT數(shù)據(jù)庫
  14. [root@mysql02 mysql]# service mysqld start //啟動數(shù)據(jù)庫
 3.5 進(jìn)行數(shù)據(jù)庫的恢復(fù)到時間點B

點擊(此處)折疊或打開

  1. [root@mysql02 mysql2]# pwd //備份的時候，需要備份binlog日志，之前的binlog目錄為/data/mysql2
  2. /data/mysql2
  3. [root@mysql02 mysql2]# mysqlbinlog --start-position=107 --stop-position=1203 mysql-bin.000001| mysql -uroot -p //根據(jù)post的位置進(jìn)行恢復(fù)，當(dāng)前的pos位置為107，恢復(fù)到pos位置到1203
  4. Enter password:
  5. [root@mysql02 mysql2]# mysql -uroot -p
  6. Enter password:
  7. Welcome to the MySQL monitor. Commands end with ; or \\g.
  8. Your MySQL connection id is 3
  9. Server version: 5.5.36-log Source distribution
  10. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
  11. Oracle is a registered trademark of Oracle Corporation and/or its
  12. affiliates. Other names may be trademarks of their respective
  13. owners.
  14. Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
  15. mysql> show databases;
  16. +--------------------+
  17. | Database |
  18. +--------------------+
  19. | information_schema |
  20. | BKT |
  21. | john |
  22. | mysql |
  23. | performance_schema |
  24. +--------------------+
  25. 5 rows in set (0.02 sec)
  26. mysql> use BKT
  27. Database changed
  28. mysql> show tables;
  29. +---------------+
  30. | Tables_in_BKT |
  31. +---------------+
  32. | john |
  33. +---------------+
  34. 1 row in set (0.00 sec)
  35. mysql> select * from john;
  36. +------+
  37. | id |
  38. +------+
  39. | 1 |
  40. | 2 |
  41. | 3 |
  42. | 4 |
  43. | 5 |
  44. +------+
  45. 5 rows in set (0.01 sec) //查看數(shù)據(jù)庫恢復(fù)成功
 3.6 恢復(fù)數(shù)據(jù)庫到時間點C

點擊(此處)折疊或打開

  1. [root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\" mysql-bin.000001| mysql -uroot -p123456 //本次通過基于時間點的恢復(fù)，恢復(fù)到時間點C
  2. Warning: Using unique option prefix start-date instead of start-datetime is deprecated and will be removed in a future release. Please use the full name instead.
  3. Warning: Using unique option prefix stop-date instead of stop-datetime is deprecated and will be removed in a future release. Please use the full name instead.
  4. [root@mysql02 mysql2]# mysql -uroot -p
  5. Enter password:
  6. Welcome to the MySQL monitor. Commands end with ; or \\g.
  7. Your MySQL connection id is 6
  8. Server version: 5.5.36-log Source distribution
  9. Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
  10. Oracle is a registered trademark of Oracle Corporation and/or its
  11. affiliates. Other names may be trademarks of their respective
  12. owners.
  13. Type \'help;\' or \'\\h\' for help. Type \'\\c\' to clear the current input statement.
  14. mysql> show databases;
  15. +--------------------+
  16. | Database |
  17. +--------------------+
  18. | information_schema |
  19. | BKT |
  20. | john |
  21. | mysql |
  22. | performance_schema |
  23. +--------------------+
  24. 5 rows in set (0.00 sec)
  25. mysql> use BKT
  26. Database changed
  27. mysql> select * from john;
  28. +------+
  29. | id |
  30. +------+
  31. | 1 |
  32. | 2 |
  33. | 3 |
  34. | 4 |
  35. | 5 |
  36. | 6 |
  37. | 7 |
  38. | 8 |
  39. | 9 |
  40. | 10 |
  41. +------+
  42. 10 rows in set (0.00 sec) //經(jīng)過檢查成功恢復(fù)到時間點C
 
四、mysqlbinlog的其他總結(jié)：以上是利用binlog文件進(jìn)行基于時間點和binlog的POS位置恢復(fù)的測試，mysqlbinlog的使用還有很多功能，運行mysqlbinlog --help可以查看相應(yīng)參數(shù)；
4.1 查看binlog的內(nèi)容：[root@mysql02 mysql2]# mysqlbinlog mysql-bin.000001 
4.2 mysqlbinlog的其他常用參數(shù)：

-h  根據(jù)數(shù)據(jù)庫的IP
-P  根據(jù)數(shù)據(jù)庫所占用的端口來分
-server-id 根據(jù)數(shù)據(jù)庫serverid來還原（在集群中很有用）
-d  根據(jù)數(shù)據(jù)庫名稱

例如： [root@mysql02 mysql2]# mysqlbinlog -d BKT mysql-bin.000001//還原BKT數(shù)據(jù)庫的信息

參數(shù)的組合使用：

點擊(此處)折疊或打開

  1. [root@mysql02 mysql2]# mysqlbinlog --start-date=\"2014-11-27 09:21:56\" --stop-date=\"2014-11-27 09:22:33\" -d BKT -h 127.0.0.1 /var/lib/mysql/mysql-bin.000001 ｜mysql -u root -p
  2. #如果有多個binlog文件，用逗號隔開；
 
4.4 恢復(fù)是一件很重要的事情，如果不知道具體要恢復(fù)的時間點，請把binlog文件先轉(zhuǎn)換成文本文件，詳細(xì)查看完相應(yīng)的內(nèi)容再進(jìn)行恢復(fù)；
[root@mysql02 mysql2]# mysqlbinlog mysql-bin.000001 > /tmp/00001.sql

總結(jié)：備份有時候永遠(yuǎn)都用不上，但是你永遠(yuǎn)也不知道什么時候會用上，正所謂養(yǎng)兵千日用兵一時，作為一個合格的DBA有個可用的備份，就可以做到胸有成竹；




 




向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手