詳解mysql之資料備份與恢復

該文使用mysql5.5 centos6.5 64位元（自己使用rpm安裝mysql，資料庫的安裝目錄預設）

##一、資料備份注意事項

讀鎖問題：資料庫（或某個表）一旦進行讀鎖操作則影響資料庫的寫入操作所以此時不能對資料庫進行寫入操作。之所以在資料備份的時候進行讀鎖操作是為了確保備份資料的完整性一致性，並在資料備份完成後自動解鎖。

更新日誌問題：每次備份資料的時候新產生一個日誌文件，這樣資料庫在備份一段時間後突然崩潰可以透過該bin-log日誌還原該時間段的數據。資料復原的步驟:恢復備份的資料+還原最新bin-log日誌中的資料

#二、使用mysqldump進行書籍備份

mysqldump：指令將資料庫中的資料備份成一個文字檔。表格的結構和表格中的資料將儲存在產生的文字檔案中。

mysqldump指令的工作原理：它先查出需要備份的表的結構，再在文字檔中產生一個CREATE語句。然後，將表中的所有記錄轉換成一條INSERT語句。然後透過這些語句，就能夠建立表格並插入資料。

將test資料庫備份到/tmp/mysql_back/目錄下

 [root@localhost tmp]# mysqldump -uroot -p111111 test -l -F > '/tmp/mysql_back/test.sql';

參數解讀：

1、mysqldump mysql資料備份的指令

2、-uroot -p111111  使用者名稱密碼

3、test：要備份的資料庫名稱

4、-l：進行讀取鎖定控制

#5 、-F:產生新日誌檔

執行該語句後會發現在/var/lib/mysql 目錄下會產生一個新的bin-log日誌mysql-bin.000002

6 、/tmp/mysql_back/test.sql ：產生備份檔案的位置及名稱

#三、資料復原

1、備份資料的還原：

　[root@localhost tmp]# mysql -uroot -p111111 test -v -f</tmp/mysql_back/test.sql

　　-v：查看導入的詳細資訊

　　-f：導入過程中遇到錯誤時可以skip過，繼續執行下面的語句。

2、bin-log日誌中資料的還原：

　　該日誌在/var/lib/mysql下mysql-bin.000002日誌中所有的記錄

　　[ root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002 |mysql -uroot -p111111 test;

#3、還原bin-log日誌中指定位置的資料

#透過position位置還原資料

BEGIN
/*!*/;
# at 175
#170206 22:55:48 server id 1  end_log_pos 263     Query    thread_id=17    exec_time=0    error_code=0
use `test`/*!*/;
SET TIMESTAMP=1486392948/*!*/;
insert into t1 values(10)
/*!*/;
# at 263
#170206 22:55:48 server id 1  end_log_pos 290     Xid = 178
COMMIT/*!*/;
# at 290
#170206 22:55:54 server id 1  end_log_pos 358     Query    thread_id=17    exec_time=0    error_code=0
SET TIMESTAMP=1486392954/*!*/;
BEGIN
/*!*/;
# at 358
#170206 22:55:54 server id 1  end_log_pos 446     Query    thread_id=17    exec_time=0    error_code=0
SET TIMESTAMP=1486392954/*!*/;
insert into t1 values(12)
/*!*/;
# at 446
#170206 22:55:54 server id 1  end_log_pos 473     Xid = 179
COMMIT/*!*/;
# at 473
#170206 22:56:42 server id 1  end_log_pos 547     Query    thread_id=17    exec_time=0    error_code=0
SET TIMESTAMP=1486393002/*!*/;
truncate t1

還原該bin-log日誌中position 位置在172，473 中的資料(end_log_pos)

[root@localhost mysql]# mysqlbinlog --no-defaults mysql -bin.000002 --start-position="175" --stop-position="473"|mysql -uroot -p111111 test;

4、還原bin-log日誌中指定時間段的資料# #####格式：將3中即可######--start-position="175"  替換為--start-date="2016-12-30 21:30:34"### ###--stop-position="473" 替換為--stop-date="2016-12-30 23:30:34"###

以上是詳解mysql之資料備份與恢復的詳細內容。更多資訊請關注PHP中文網其他相關文章！