備份 · MySQL學習筆記

# MySQL的備份-mysqldump命令的使用 [TOC] >[info] **數據備份的意義[運維的工作]** >1. 保護公司的數據 >2. 網站7*24小時提供服務利用mysqldump命令備份數據的過程，實際上就是把數據從mysql庫里以邏輯的sql語句的形式直接輸出或者生成備份的文件的過程。 ## 備份數據庫的參數 >[danger] **mysqldump參數** **-A**(`--all-databases`) ：備份所有庫 **-B**(`--databases`) ：連接多個數據庫,備份的數據中增加 `建庫和use庫`語句 (方便導入數據) **-d**(`--no-data`) ：只導出表結構 **-t**(`--no-create-info`) ：只導出表數據 **-x**(`--lock-all-tables`) ：鎖表(無法對數據庫進行操作，影響大) **-l**(`--lock-tables`) : 只讀鎖表 **-F**(`--flush-logs`) : 刷新binlog日志(備份數據時可能會用上)也可以在mysql客戶端執行`reset master;` **--master-date=1/2** ：增加binlog日志文件名以及對應的位置點。 **gzip** ：指定gzip進行備份sql壓縮 **--single-transaction** ：適合InnoDB事務數據庫備份（*InnoDB表在備份時,通常啟用該參數來保證備份的一致性，他的工作原理是設定本次回話的隔離級別為: repeatable read,以確保本次回話dump時，不會看到其他會話已經提交了的數據*） **--compact** ：參數優化備份文件大小減少輸出注釋(debug調試時使用) **--default-chatacter-set=utf8** ：指定默認字符集 mysql數據庫自帶一個很好用的備份命令**mysqldump**，它的語法為: `mysqldump -u 用戶名 -p 密碼數據庫名 [表名] > 備份的文件名` ### Myisam表常規備份(參數) >[info] `mysqldump -uroot -paaaaaa -A -B -F --flush-privileges --triggers --routines --events --hex-blob --master-data=1 -x|gzip > ~/all.sql.gz` ### InnoDB表常規備份(推薦使用的存儲引擎) >[info] `mysqldump -uroot -paaaaaa -A -B -F --flush-privileges --master-data=1 --triggers --routines --events --hex-blob --single-transaction|gzip > ~/all.sql.gz` ## 普通方式備份數據庫 ~~~ # 20160108新增一個數據庫備份語句(運維推薦這個參數理由:保證數據同步的同時還原操作可控) mysqldum -uroot -paaaaaa --opt DbName >~/dbname.sql ~~~ ~~~ # 多實例指定sock文件 mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock database > ~/database.sql ~~~ ### 多實例指定sock文件備份數據庫test并使用gzip壓縮 ~~~ mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock test|gzip > ~/test.gz.sql ~~~ ### 使用egrep查看備份的文件的sql內容 `egrep -v "#|\*|--|^$" ~/test.sql` ## **-B參數** 備份多個庫 ~~~ [root@localhost ~]# mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock -B test mysql|gzip >~/mutil_db.sql.gz ~~~ ### 備份庫下的表 #### 備份庫下的某個表 `[root@localhost ~]# mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock dbNAME tableName>~/dbName.sql` #### 備份庫下的多個表 `[root@localhost ~]# mysqldump -uroot -paaaaaa -S /data/3306/mysql.sock dbName tableName tableName ..>~/dbName.sql` * * * * * ## **-d參數** 備份表結構 `[root@localhost ~]# mysqldump -uroot -paaaaaa -d -S /data/3306/mysql.sock dbName tableName` ## **-t參數** 備份表數據 `[root@localhost ~]# mysqldump -uroot -paaaaaa -t -S /data/3306/mysql.sock dbName tableName` ## **-F參數** 刷新二進制日志 ## **`--master-data參數`** 備份記錄點 |參數|值|例子| |-|-|-| |`--master-data`|1|`CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107`| |`--master-data`|2|`-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107`**(注釋change master語句)**| * * * * * # 實例 ## 多實例分庫備份 >[info] 思路: 使用for循環查詢出數據庫中存在的庫,然后分別使用mysqldump進行備份 ### 命令行實現備份 ~~~ # 多實例情況(單實例把sock去掉即可) [root@localhost /test]# mysql -uroot -p'aaaaaa' -S /data/3306/mysql.sock -e 'show databases;'|grep -Evi 'database|information_schema|mysql'|sed -r 's#^([a-z].+$)#mysqldump -uroot -p"aaaaaa" -S /data/3306/mysql.sock --events -B \1|gzip>/test/\1.sql.gz #g'|/bin/bash ~~~ ### 腳本實現(多實例情況) `[root@localhost ~] # vim mysql_bakdb.sql` ~~~ #!/bin/bash #filename mysql_bakdb.sh MYUSER=root # 數據庫用戶 MYPASS=aaaaaa # 用戶密碼 SOCKET=/data/3306/mysql.sock # 多實例sock文件地址 BAKDIR=~/mysqldatabak/`date +"%Y%m%d"`/ # 保存的文件地址 MYCMD="mysql -u${MYUSER} -p${MYPASS} -S ${SOCKET}" # mysql連接 MYDUMP="mysqldump -u${MYUSER} -p${MYPASS} -S ${SOCKET}" # mysqldump連接 mkdir -p $BAKDIR # 創建文件夾 for database in `${MYCMD} -e "show databases;"|grep -Eiv 'mysql|database|information_schema'` do ${MYDUMP} ${database}|gzip > ${BAKDIR}${database}.sql.gz done ~~~ >[danger] **分庫備份的意義** 有時候一個數據庫實例中會有多個庫,例如(blog,bbs..)但是出問題的時候可能是其中的某一個庫，如果在備份時，把所有的庫都備份成一個數據文件的話，恢復某一個數據庫就比較麻煩 ## 多實例分表備份 **典型備份語句(備份制定庫下的表)** ~~~ [root@curder.localhost ~] # mysqldump -uroot -paaaaaa rose student > ~/student.sql ~~~ >[info] 思路：在分庫備份下再循環庫下`show tables from dbName`找到所有表對表進行備份(**無-B參數**) 使用`mysqldump`拼接庫、表進行備份 `[root@localhost ~] # vim mysql_baktable.sh` ~~~ #!/bin/bash #filename mysql_baktable.sh MYUSER=root MYPASS=aaaaaa SOCKET=/data/3306/mysql.sock BAKDIR=~/`date +"%Y%m%d"` MYCMD="mysql -u${MYUSER} -p${MYPASS} -S ${SOCKET}" MYDUMP="mysqldump -u${MYUSER} -p${MYPASS} -S ${SOCKET}" for database in `${MYCMD} -e "show databases;"|grep -Eiv 'mysql|database|information_schema'` do mkdir -p ${BAKDIR}/${database} for table in `${MYCMD} -e "show tables from ${database};"|sed '1d'` # 循環獲取當前庫下的所有表 do ${MYDUMP} ${database} ${table}|gzip > ${BAKDIR}/${database}/${table}.sql.gz done done ~~~ >[danger] 分表備份缺點:文件多，碎。 1. 備份一個完整全備，再做一個分庫分表備份。 2. 腳本批量恢復多個SQL文件。