下文我給大家簡(jiǎn)單講講關(guān)于什么是Mysql的邏輯備份與恢復(fù)，大家之前了解過(guò)相關(guān)類似主題內(nèi)容嗎？感興趣的話就一起來(lái)看看這篇文章吧，相信看完什么是Mysql的邏輯備份與恢復(fù)對(duì)大家多少有點(diǎn)幫助吧。
MySQL中的邏輯備份是將數(shù)據(jù)庫(kù)中的數(shù)據(jù)備份為一個(gè)文本文件，備份的文件可以被查看和編輯。在MySQL中，可以使用mysqldump工具來(lái)完成邏輯備份。我們可以使用以下3種方法調(diào)用mysqldump。
備份指定的數(shù)據(jù)庫(kù)或者此數(shù)據(jù)庫(kù)中的某些表。
shell> mysqldump [options] dbname [tables]
備份指定的一個(gè)或多個(gè)數(shù)據(jù)庫(kù)。
shell> mysqldump [options] --databases db1 [db2 db3 ...]
備份所有數(shù)據(jù)庫(kù)。
shell> mysqldump [options] --all-databases
如果沒(méi)有指定數(shù)據(jù)庫(kù)中的任何表，默認(rèn)導(dǎo)出所有數(shù)據(jù)庫(kù)中的所有表。
例子：
1) 備份所有數(shù)據(jù)庫(kù)
[root@rhel6 mysql]# mysqldump -uroot -p123456 --all-databases > all.sql
2) 備份數(shù)據(jù)庫(kù)test
[root@rhel6 mysql]# mysqldump -uroot -p123456 --databases test > test.sql
3) 備份數(shù)據(jù)庫(kù)test下的emp表
[root@rhel6 mysql]# mysqldump -uroot -p123456 test emp > test_emp.sql
4) 備份數(shù)據(jù)庫(kù)test下的emp和ts表
[root@rhel6 mysql]# mysqldump -uroot -p123456 test emp ts > emp_ts.sql
5) 備份數(shù)據(jù)庫(kù)test下的emp表為逗號(hào)分割的文檔，備份到/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
需要強(qiáng)調(diào)的是，為了保證數(shù)據(jù)備份的一致性，MyISAM存儲(chǔ)引擎在備份是需要加上-l參數(shù)，表示將所有表加上讀鎖，在備份期間，所有表將只能讀而不能進(jìn)行數(shù)據(jù)更新。但是對(duì)于事務(wù)存儲(chǔ)引擎(InnoDB和BDB)來(lái)說(shuō)，可以采用更好的選項(xiàng)--single-transaction，此選項(xiàng)將使得InnoDB存儲(chǔ)引擎得到一個(gè)快照(Snapshot)，使得備份的數(shù)據(jù)能夠保證一致性。
2、完全恢復(fù)
mysqldump的恢復(fù)也很簡(jiǎn)單，將備份作為輸入執(zhí)行即可，具體語(yǔ)法如下：
mysql -uroot -p dbname < bakfile
注意，將備份恢復(fù)后數(shù)據(jù)并不完整，還需要將備份后執(zhí)行的日志進(jìn)行重做，語(yǔ)法如下：
mysqlbinlog binlog-file |mysql -uroot -p
完全恢復(fù)例子
--查看當(dāng)前狀態(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表示生成一個(gè)新的日志文件。 --查看emp當(dāng)前數(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 --模擬恢復(fù) [root@rhel6 tmp]# mysql -uroot -p test < test.sql Enter password: --查看恢復(fù)后的狀態(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恢復(fù)上次全備后的日志，并指定stop-datetime為出故障的時(shí)間，同庫(kù)恢復(fù)時(shí)使用，避免應(yīng)用恢復(fù)時(shí)產(chǎn)生的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ù)已全部恢復(fù)回來(lá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 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、不完全恢復(fù)
由于誤操作，比如誤刪除了一張表，這時(shí)使用完全恢復(fù)是沒(méi)有用的，因?yàn)槿罩纠镞€存在誤操作語(yǔ)句，我們需要的是恢復(fù)到誤操作之前的狀態(tài)，然后跳過(guò)誤操作語(yǔ)句，再恢復(fù)后面執(zhí)行的語(yǔ)句，完成我們的恢復(fù)。這種恢復(fù)叫不完全恢復(fù)，在MySQL中，不完全恢復(fù)分為基于時(shí)間點(diǎn)的恢復(fù)和基于位置的恢復(fù)。
1)基于時(shí)間點(diǎn)的恢復(fù)操作步驟
a.如果上午10點(diǎn)發(fā)生了誤操作，可以用以下語(yǔ)句使用份和binlog將數(shù)據(jù)恢復(fù)到故障前
shell> mysqlbinlog --stop-datetime='20161129 09:59:59' /var/log/mysql/mysqlbin.000033 |mysql -uroot -p
b.跳過(guò)故障時(shí)的時(shí)間點(diǎn)，繼續(xù)執(zhí)行后面的binlog，完成恢復(fù)。
shell> mysqlbinlog --start-datetime='20161129 10:01:00' /var/log/mysql/mysqlbin.000033 |mysql -uroot -p
2)基于位置恢復(fù)
和基于時(shí)間點(diǎn)的恢復(fù)類似，但是更精確，因?yàn)橥粋€(gè)時(shí)間點(diǎn)可能有多條sql語(yǔ)句同時(shí)執(zhí)行。恢復(fù)的操作如下：
a.分析誤操作時(shí)間段的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中找到出錯(cuò)語(yǔ)句前后的位置號(hào)，假如前后位置號(hào)分別是3682和3685。
b.使用如下命令進(jìn)行恢復(fù)
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
大家覺(jué)得什么是Mysql的邏輯備份與恢復(fù)這篇文章怎么樣，是否有所收獲。如果想要了解更多相關(guān)，可以繼續(xù)關(guān)注我們的行業(yè)資訊板塊。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無(wú)理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國(guó)服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡(jiǎn)單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢(shì)，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場(chǎng)景需求。
重慶分公司
重慶分公司
