mysql備份與還原操作手冊

一、備份常用操作基本指令

1、備份指令mysqldump格式

格式：mysqldump -h主機名  -P端口 -u使用者名 -p密碼 –database 資料庫名 > 檔案名.sql
           

2、備份MySQL資料庫為帶删除表的格式

備份MySQL資料庫為帶删除表的格式，能夠讓該備份覆寫已有資料庫而不需要手動删除原有資料庫。

mysqldump  --add-drop-table -uusername -ppassword -database databasename > backupfile.sql
           

3、直接将MySQL資料庫壓縮備份

mysqldump -hhostname -u uname -ppassword -database dbname | gzip > backupfile.sql.gz
           

4、備份MySQL資料庫某個(些)表

mysqldump -hhostname -uname -ppassword dbname specific_table1 specific_table2 > backupfile.sql
           

5、同時備份多個MySQL資料庫

mysqldump -hhostname -uuname -ppassword –databases dbname1 dbname2 dbname3 > multibackupfile.sql
           

6、僅備份份資料庫結構

mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
           

7、備份伺服器上所有資料庫

mysqldump –all-databases > allbackupfile.sql
           

8、還原MySQL資料庫的指令

mysql -hhostname -uusername -ppassword databasename < backupfile.sql
           

9、還原壓縮的MySQL資料庫

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
           

10、将資料庫轉移到新伺服器

mysqldump -uusername -ppassword databasename | mysql –host=*.*.*.* -C databasename
           

11、--master-data 和--single-transaction

• 在mysqldump中使用--master-data=2，會記錄binlog檔案和position的資訊 。--single-transaction會将隔離級别設定成repeatable-commited

12、導入資料庫

• 常用source指令，用use進入到某個資料庫，mysql>source d:test.sql，後面的參數為腳本檔案。

13、檢視binlog日志

• 檢視binlog日志可用用指令 mysqlbinlog binlog日志名稱|more

14、general_log

• General_log記錄資料庫的任何操作，檢視general_log 的狀态和位置可以用指令show variables like "general_log%" ,開啟general_log可以用指令set global general_log=on

二、增量備份

小量的資料庫可以每天進行完整備份，因為這也用不了多少時間，但當資料庫很大時，就不太可能每天進行一次完整備份了，這時候就可以使用增量備份。增量備份的原理就是使用了mysql的binlog志。

1、首先做一次完整備份：

mysqldump -h10.6.208.183 -utest2 -p123  -P3310 --single-transaction  --master-data=2  test>test.sql這時候就會得到一個全備檔案test.sql
           

在sql檔案中我們會看到：

-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;是指備份後所有的更改将會儲存到bin-log.000002二進制檔案中。
           

2、在test庫的t_student表中增加兩條記錄，然後執行flush logs指令。這時将會産生一個新的二進制日志檔案bin-log.000003，bin-log.000002則儲存了全備過後的所有更改，既增加記錄的操作也儲存在了bin-log.00002中。

3、再在test庫中的a表中增加兩條記錄，然後誤删除t_student表和a表。a中增加記錄的操作和删除表a和t_student的操作都記錄在bin-log.000003中。

三、恢複

1、首先導入全備資料

mysql -h10.6.208.183 -utest2 -p123  -P3310 < test.sql，
也```  
可以直接在mysql指令行下面用source導入

2、恢複bin-log.000002           

mysqlbinlog bin-log.000002 |mysql -h10.6.208.183 -utest2 -p123 -P3310

3、恢複部分 bin-log.000003

   在general_log中找到誤删除的時間點，然後更加對應的時間點到bin-log.000003中找到相應的position點，需要恢複到誤删除的前面一個position點。
可以用如下參數來控制binlog的區間           

--start-position 開始點 --stop-position 結束點

--start-date 開始時間 --stop-date 結束時間

找到恢複點後，既可以開始恢複。           

mysqlbinlog mysql-bin.000003 --stop-position=208 |mysql -h10.6.208.183 -utest2 -p123 -P3310