Mysql的邏輯備份與恢複

標籤：mysql 備份 恢複

  在MySQL中，邏輯備份的最大優點是對於各種儲存引擎都可以使用同樣的方法來備份；而物理備份則不同，不同的儲存引擎有著不同的備份方法。因此對於不同的儲存引擎混合的資料庫，用邏輯備份會更簡單一些。本文使用的MySQL環境是5.6.34。

1、備份

  MySQL中的邏輯備份是將資料庫中的資料備份為一個文字檔，備份的檔案可以被查看和編輯。在MySQL中，可以使用mysqldump工具來完成邏輯備份。我們可以使用以下3種方法調用mysqldump。

• 備份指定的資料庫或者此資料庫中的某些表。

    shell> mysqldump [options] dbname [tables]

• 備份指定的一個或多個資料庫。

    shell> mysqldump [options] --databases db1 [db2 db3 ...]

• 備份所有資料庫。

    shell> mysqldump [options] --all-databases

  如果沒有指定資料庫中的任何錶，預設匯出所有資料庫中的所有表。

  例子：

  1) 備份所有資料庫

[[email protected] mysql]# mysqldump -uroot -p123456 --all-databases > all.sql

  2) 備份資料庫test

[[email protected] mysql]# mysqldump -uroot -p123456 --databases test > test.sql

  3) 備份資料庫test下的emp表

[[email protected] mysql]# mysqldump -uroot -p123456 test emp > test_emp.sql

  4) 備份資料庫test下的emp和ts表

[[email protected] mysql]# mysqldump -uroot -p123456 test emp ts > emp_ts.sql

 5) 備份資料庫test下的emp表為逗號分割的文檔，備份到/tmp

[[email protected] tmp]# mysqldump -uroot -p123456 -T /tmp test emp --fields-terminated-by ‘,‘Warning: Using a password on the command line interface can be insecure.[[email protected] tmp]# lsemp.sql  emp.txt[[email protected] tmp]# more emp.txt 1,zx,2016-01-01,9999-12-31,lx,501,zx,2016-01-01,9999-12-31,zx,50

 擷取mysqldump的協助 mysqldump --help

  需要強調的是，為了保證資料備份的一致性，MyISAM儲存引擎在備份是需要加上-l參數，表示將所有表加上讀鎖，在備份期間，所有表將只能讀而不能進行資料更新。但是對於事務儲存引擎(InnoDB和BDB)來說，可以採用更好的選項--single-transaction，此選項將使得InnoDB儲存引擎得到一個快照(Snapshot)，使得備份的資料能夠保證一致性。

2、完全恢複

  mysqldump的恢複也很簡單，將備份作為輸入執行即可，具體文法如下：

  mysql -uroot -p dbname < bakfile

  注意，將備份恢複後資料並不完整，還需要將備份後執行的日誌進行重做，文法如下：

  mysqlbinlog binlog-file |mysql -uroot -p 

  完全恢複例子

--查看目前狀態[[email protected] tmp]# mysql -uroot -p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 17Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> select now();+---------------------+| now()               |+---------------------+| 2016-11-29 15:02:45 |+---------------------+1 row in set (0.00 sec)mysql> show master status;+-----------------+----------+--------------+------------------+-------------------+| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-----------------+----------+--------------+------------------+-------------------+| mysqlbin.000032 |    13477 |              |                  |                   |+-----------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> select @@autocommit;+--------------+| @@autocommit |+--------------+|            1 |+--------------+1 row in set (0.00 sec)mysql> show variables like ‘autocommit‘;+---------------+-------+| Variable_name | Value |+---------------+-------+| autocommit    | ON    |+---------------+-------+1 row in set (0.02 sec)mysql> exitBye--做一次全備[[email protected] tmp]# mysqldump -uroot -p -l -F test > test.sqlEnter password: -----  其中-l參數表示給所有的表加讀鎖，-F表示產生一個新的記錄檔。--查看emp當前資料，並做更改[[email protected] tmp]# mysql -uroot -p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 20Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> show master status;+-----------------+----------+--------------+------------------+-------------------+| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-----------------+----------+--------------+------------------+-------------------+| mysqlbin.000033 |      120 |              |                  |                   |+-----------------+----------+--------------+------------------+-------------------+1 row in set (0.00 sec)mysql> select now();+---------------------+| now()               |+---------------------+| 2016-11-29 15:06:11 |+---------------------+1 row in set (0.00 sec)mysql> select * from test.emp;+----+-------+------------+------------+-----+----------+| id | ename | hired      | separated  | job | store_id |+----+-------+------------+------------+-----+----------+|  1 | zx    | 2016-01-01 | 9999-12-31 | lx  |       50 ||  1 | zx    | 2016-01-01 | 9999-12-31 | zx  |       50 |+----+-------+------------+------------+-----+----------+2 rows in set (0.00 sec)mysql> insert into test.emp(id,ename,job,store_id) values(2,‘wl‘,‘wl‘,50);Query OK, 1 row affected (0.01 sec)mysql> select * from test.emp;+----+-------+------------+------------+-----+----------+| id | ename | hired      | separated  | job | store_id |+----+-------+------------+------------+-----+----------+|  1 | zx    | 2016-01-01 | 9999-12-31 | lx  |       50 ||  2 | wl    | 2016-01-01 | 9999-12-31 | wl  |       50 ||  1 | zx    | 2016-01-01 | 9999-12-31 | zx  |       50 |+----+-------+------------+------------+-----+----------+3 rows in set (0.00 sec)mysql> show master status;+-----------------+----------+--------------+------------------+-------------------+| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+-----------------+----------+--------------+------------------+-------------------+| mysqlbin.000033 |      362 |              |                  |                   |+-----------------+----------+--------------+------------------+-------------------+1 row in set (0.01 sec)mysql> select now();+---------------------+| now()               |+---------------------+| 2016-11-29 15:06:48 |+---------------------+1 row in set (0.01 sec)mysql> exitBye--類比恢複[[email protected] tmp]# mysql -uroot -p test < test.sql Enter password: --查看恢複後的狀態[[email protected] tmp]# mysql -uroot -p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 22Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> select * from test.emp;+----+-------+------------+------------+-----+----------+| id | ename | hired      | separated  | job | store_id |+----+-------+------------+------------+-----+----------+|  1 | zx    | 2016-01-01 | 9999-12-31 | lx  |       50 ||  1 | zx    | 2016-01-01 | 9999-12-31 | zx  |       50 |+----+-------+------------+------------+-----+----------+2 rows in set (0.00 sec)mysql> exitBye--使用binlog恢複上次全備後的日誌，並指定stop-datetime為出故障的時間，同庫恢複時使用，避免應用恢複時產生的binlog[[email protected] tmp]# mysqlbinlog /var/lib/mysql/mysqlbin.000033 --stop-datetime=‘2016-11-29 15:06:48‘ |mysql -uroot -pEnter password: --查看emp表所有資料已全部恢複回來[[email protected] tmp]# mysql -uroot -p123456Warning: Using a password on the command line interface can be insecure.Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 26Server version: 5.6.34-log MySQL Community Server (GPL)Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type ‘help;‘ or ‘\h‘ for help. Type ‘\c‘ to clear the current input statement.mysql> select * from test.emp;+----+-------+------------+------------+-----+----------+| id | ename | hired      | separated  | job | store_id |+----+-------+------------+------------+-----+----------+|  1 | zx    | 2016-01-01 | 9999-12-31 | lx  |       50 ||  2 | wl    | 2016-01-01 | 9999-12-31 | wl  |       50 ||  1 | zx    | 2016-01-01 | 9999-12-31 | zx  |       50 |+----+-------+------------+------------+-----+----------+3 rows in set (0.00 sec)

3、不完全恢複

  由於誤操作，比如誤刪除了一張表，這時使用完全恢複是沒有用的，因為日誌裡還存在誤動作陳述式，我們需要的是恢複到誤操作之前的狀態，然後跳過誤動作陳述式，再恢複後面執行的語句，完成我們的恢複。這種恢複叫不完全恢複，在MySQL中，不完全恢複分為基於時間點的恢複和基於位置的恢複。

  1)基於時間點的恢複操作步驟

    a.如果上午10點發生了誤操作，可以用以下語句使用份和binlog將資料恢複到故障前

    shell> mysqlbinlog --stop-datetime=‘20161129 09:59:59‘ /var/log/mysql/mysqlbin.000033 |mysql -uroot -p

    b.跳過故障時的時間點，繼續執行後面的binlog，完成恢複。

    shell> mysqlbinlog --start-datetime=‘20161129 10:01:00‘ /var/log/mysql/mysqlbin.000033 |mysql -uroot -p

    

  2)基於位置恢複

    和基於時間點的恢複類似，但是更精確，因為同一個時間點可能有多條sql語句同時執行。恢複的操作如下：

    a.分析誤操作時間段的binlog

    shell> mysqlbinlog --start-datetime=‘20161129 09:55:00‘ --stop-datetime=‘20161129 10:05:00‘ /var/log/mysql/mysqlbin.000033 > /tmp/mysql_restore.sql

    從mysql_restore.sql中找到出錯語句前後的位置號，假如前後位置號分別是3682和3685。

    b.使用如下命令進行恢複

    shell> mysqlbinlog --stop-position=3682 /var/log/mysql/mysqlbin.000033 |mysql -uroot -p

    shell> mysqlbinlog --start-position=3685 /var/log/mysql/mysqlbin.000033 |mysql -uroot -p

    

本文出自 “DBA Fighting!” 部落格，請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1877759

Mysql的邏輯備份與恢複