MySQL的備份和恢複

mysqldump的使用

mysqldump -u 使用者名 -p 密碼 庫名 > /opt/myname.sql
egrep -v "#|\*|--|^$" /opt/myname.sql
過濾sql檔案中的空行以及注釋行
mysqldump -u root -p 123456 --default-character-set=utf8 myname > /opt/myname.sql
#指定對應的字元集備份
mysqldump -u 使用者名 -p 密碼 -B 庫名 > /opt/myname.sql
PS:加-B參數的作用是增加建立資料庫和連接配接資料庫的指令
即:create database myname; use myname，也可以連接配接多個庫
ps：mysqldump指令備份資料的過程，實際上就是把資料庫從MySQL庫裡以邏輯的sql語句的形式直接輸出或者生成備份檔案的過程
           

指定壓縮指令來壓縮

mysqldump -u root -p 123456 myname|gzip > /opt/myname.sql.gz 
           

指定需要備份的庫

mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|information_schema|performance_schema|sys"|sed -r 's#^([a-z].*$)#mysqldump -uroot -p123456 -B \1|gzip >/opt/\1.sql.gz#g'|bash


root@mfgdevops:~# mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|information_schema|performance_schema|sys"|sed -r 's#^([a-z].*$)#mysqldump -uroot -p123456 -B \1|gzip >/opt/\1.sql.gz#g'|bash
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump -uroot -p123456 -B metest|gzip >/opt/metest.sql.gz
mysqldump -uroot -p123456 -B mfgyang|gzip >/opt/mfgyang.sql.gz
mysqldump -uroot -p123456 -B mysql|gzip >/opt/mysql.sql.gz
mysqldump -uroot -p123456 -B myyang|gzip >/opt/myyang.sql.gz
mysqldump -uroot -p123456 -B sys|gzip >/opt/sys.sql.gz
mysqldump -uroot -p123456 -B test|gzip >/opt/test.sql.gz
           

備份單個表

mysqldump -uroot -p123456 myname test > /opt/test.sql
ps:後面跟庫名和表名，此時不能加大B,否則後面預設跟的都是庫名
mysqldump -uroot -p123456 庫名 表名1 表名2 > /opt/test.sql
           

備份表結構

mysqldump -uroot -p123456 -d myname test > /opt/test.sql
ps：-d參數隻備份表結構
           

隻備份資料

mysqldump -uroot -p123456 -t myname test > /opt/test.sql
--no-create-infor：隻導表資料
           

-A -F 的功能

-F 備份的時候會重新整理binlog日志,切割binlog
-A 是備份資料庫的所有資料
           

--master-data參數

--master-data參數會自動為你找到binlog的檔案和位置
mysqldump -uroot -p123456 --master-data=1 myname > /opt/test.sql
此時可以看到會增加binlog日志檔案名以及對應的位置點
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154;
--master-data=2時會将CHANGE這行進行注釋
           

總結

關鍵參數mysqldump --help
1:-B指定多個庫，增加建庫語句和use語句
2:--compact 去掉備份檔案中的注釋，适合調試輸出，生産不用
3:-A 備份所有庫
4:-F 重新整理binlog日志，用來做增量備份
5:--master-data增加binlog日志檔案名及對應的位置點
6:-x --lock-all-tables locks all tables across all datbases.
7:-l --lock-tables 隻讀鎖表
8:-d 隻備份表結構
9:-t 隻備份資料
10: --single-transaction 适合innodb事務資料庫備份
ps:innodb表在備份時，通常啟用選項--single-transaction來保證備份的一緻性，實際上它的工作原理是設定本次回話的隔離級别為：REPEATABLE READ，以確定本次會話(dump)時，不會看到其他會話已經送出了的資料。
myisam:
mysqldump -uroot -p123456 -A -B --master-data=2 -x --events|gzip > /opt/all.sql.gz
innodb:
mysqldump -uroot -p123456 -A -B --master-data=2 --single-transaction --events|gzip>/opt/all.sql.gz
           

還原

1:利用source
先進入資料庫，如果指定了-B 參數可以直接使用 source /opt/mytest.sql；如果沒有指定-B 參數，需要先use資料庫然後使用source。