下文我給大家簡單講講關于什么是MySQL的邏輯備份與恢復，大家之前了解過相關類似主題內容嗎？感興趣的話就一起來看看這篇文章吧，相信看完什么是Mysql的邏輯備份與恢復對大家多少有點幫助吧。
為乳源等地區(qū)用戶提供了全套網(wǎng)頁設計制作服務，及乳源網(wǎng)站建設行業(yè)解決方案。主營業(yè)務為成都網(wǎng)站建設、成都網(wǎng)站制作、乳源網(wǎng)站設計，以傳統(tǒng)方式定制建設網(wǎng)站，并提供域名空間備案等一條龍服務，秉承以專業(yè)、用心的態(tài)度為用戶提供真誠的服務。我們深信只要達到每一位用戶的要求，就會得到認可，從而選擇與我們長期合作。這樣，我們也可以走得更遠！
MySQL中的邏輯備份是將數(shù)據(jù)庫中的數(shù)據(jù)備份為一個文本文件，備份的文件可以被查看和編輯。在MySQL中，可以使用mysqldump工具來完成邏輯備份。我們可以使用以下3種方法調用mysqldump。
備份指定的數(shù)據(jù)庫或者此數(shù)據(jù)庫中的某些表。
shell> mysqldump [options] dbname [tables]
備份指定的一個或多個數(shù)據(jù)庫。
shell> mysqldump [options] --databases db1 [db2 db3 ...]
備份所有數(shù)據(jù)庫。
shell> mysqldump [options] --all-databases
如果沒有指定數(shù)據(jù)庫中的任何表，默認導出所有數(shù)據(jù)庫中的所有表。
例子：
1) 備份所有數(shù)據(jù)庫
[root@rhel6 mysql]# mysqldump -uroot -p123456 --all-databases > all.sql
2) 備份數(shù)據(jù)庫test
[root@rhel6 mysql]# mysqldump -uroot -p123456 --databases test > test.sql
3) 備份數(shù)據(jù)庫test下的emp表
[root@rhel6 mysql]# mysqldump -uroot -p123456 test emp > test_emp.sql
4) 備份數(shù)據(jù)庫test下的emp和ts表
[root@rhel6 mysql]# mysqldump -uroot -p123456 test emp ts > emp_ts.sql
5) 備份數(shù)據(jù)庫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
需要強調的是，為了保證數(shù)據(jù)備份的一致性，MyISAM存儲引擎在備份是需要加上-l參數(shù)，表示將所有表加上讀鎖，在備份期間，所有表將只能讀而不能進行數(shù)據(jù)更新。但是對于事務存儲引擎(InnoDB和BDB)來說，可以采用更好的選項--single-transaction，此選項將使得InnoDB存儲引擎得到一個快照(Snapshot)，使得備份的數(shù)據(jù)能夠保證一致性。
2、完全恢復
mysqldump的恢復也很簡單，將備份作為輸入執(zhí)行即可，具體語法如下：
mysql -uroot -p dbname < bakfile
注意，將備份恢復后數(shù)據(jù)并不完整，還需要將備份后執(zhí)行的日志進行重做，語法如下：
mysqlbinlog binlog-file |mysql -uroot -p
完全恢復例子
--查看當前狀態(tài) [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參數(shù)表示給所有的表加讀鎖，-F表示生成一個新的日志文件。 --查看emp當前數(shù)據(jù)，并做更改 [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: --查看恢復后的狀態(tài) [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表所有數(shù)據(jù)已全部恢復回來 [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、不完全恢復
由于誤操作，比如誤刪除了一張表，這時使用完全恢復是沒有用的，因為日志里還存在誤操作語句，我們需要的是恢復到誤操作之前的狀態(tài)，然后跳過誤操作語句，再恢復后面執(zhí)行的語句，完成我們的恢復。這種恢復叫不完全恢復，在MySQL中，不完全恢復分為基于時間點的恢復和基于位置的恢復。
1)基于時間點的恢復操作步驟
a.如果上午10點發(fā)生了誤操作，可以用以下語句使用份和binlog將數(shù)據(jù)恢復到故障前
shell> mysqlbinlog --stop-datetime='20161129 09:59:59' /var/log/mysql/mysqlbin.000033 |mysql -uroot -p
b.跳過故障時的時間點，繼續(xù)執(zhí)行后面的binlog，完成恢復。
shell> mysqlbinlog --start-datetime='20161129 10:01:00' /var/log/mysql/mysqlbin.000033 |mysql -uroot -p
2)基于位置恢復
和基于時間點的恢復類似，但是更精確，因為同一個時間點可能有多條sql語句同時執(zhí)行?；謴偷牟僮魅缦拢?/p>
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的邏輯備份與恢復這篇文章怎么樣，是否有所收獲。如果想要了解更多相關，可以繼續(xù)關注我們的行業(yè)資訊板塊。
重慶分公司
重慶分公司
