什么是Mysql的邏輯備份與恢復

下文我給大家簡單講講關于什么是Mysql的邏輯備份與恢復，大家之前了解過相關類似主題內容嗎？感興趣的話就一起來看看這篇文章吧，相信看完什么是Mysql的邏輯備份與恢復對大家多少有點幫助吧。  

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

• 備份指定的數據庫或者此數據庫中的某些表。

    shell> mysqldump [options] dbname [tables]

• 備份指定的一個或多個數據庫。

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

• 備份所有數據庫。

    shell> mysqldump [options] --all-databases

  如果沒有指定數據庫中的任何表，默認導出所有數據庫中的所有表。

  例子：

  1) 備份所有數據庫

[root@rhel6 mysql]# mysqldump -uroot -p123456 --all-databases > all.sql

  2) 備份數據庫test

[root@rhel6 mysql]# mysqldump -uroot -p123456 --databases test > test.sql

  3) 備份數據庫test下的emp表

[root@rhel6 mysql]# mysqldump -uroot -p123456 test emp > test_emp.sql

  4) 備份數據庫test下的emp和ts表

[root@rhel6 mysql]# mysqldump -uroot -p123456 test emp ts > emp_ts.sql

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

[root@rhel6 tmp]# mysqldump -uroot -p123456 -T /tmp test emp --fields-terminated-by ','
Warning: Using a password on the command line interface can be insecure.
[root@rhel6 tmp]# ls
emp.sql  emp.txt
[root@rhel6 tmp]# more emp.txt 
1,zx,2016-01-01,9999-12-31,lx,50
1,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 

  完全恢復例子

--查看當前狀態
[root@rhel6 tmp]# mysql -uroot -p123456
Warning: 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 17
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.

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> exit
Bye
--做一次全備
[root@rhel6 tmp]# mysqldump -uroot -p -l -F test > test.sql
Enter password: 
-----  其中-l參數表示給所有的表加讀鎖，-F表示生成一個新的日志文件。
--查看emp當前數據，并做更改
[root@rhel6 tmp]# mysql -uroot -p123456
Warning: 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 20
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.

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> exit
Bye
--模擬恢復
[root@rhel6 tmp]# mysql -uroot -p test < test.sql 
Enter password: 
--查看恢復后的狀態
[root@rhel6 tmp]# mysql -uroot -p123456
Warning: 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 22
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.

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> exit
Bye
--使用binlog恢復上次全備后的日志，并指定stop-datetime為出故障的時間，同庫恢復時使用，避免應用恢復時產生的binlog
[root@rhel6 tmp]# mysqlbinlog /var/lib/mysql/mysqlbin.000033 --stop-datetime='2016-11-29 15:06:48' |mysql -uroot -p
Enter password: 
--查看emp表所有數據已全部恢復回來
[root@rhel6 tmp]# mysql -uroot -p123456
Warning: 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 26
Server 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 its
affiliates. Other names may be trademarks of their respective
owners.

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

大家覺得什么是Mysql的邏輯備份與恢復這篇文章怎么樣，是否有所收獲。如果想要了解更多相關，可以繼續關注我們的行業資訊板塊。