[TOC] ### 1. mysqldump 參考：http://www.cnblogs.com/chenmh/p/5300370.html * * * * * **可以直接被分成壓縮文件，省空間** ~~~ mysqldump --single-transaction -hlocalhost --all-databases --triggers --routines --events -P3306 -uroot -pxxxx |gzip >test.tar.gz # 備份 gunzip < test.tar.gz |mysql -hlocalhost -uroot -pxxxxx # 還原 ~~~ > * 在日常維護工作當中經常會需要對數據進行導出操作，而mysqldump是導出數據過程中使用非常頻繁的一個工具； 1. 導出所有數據庫，該命令會導出包括系統數據庫在內的所有數據庫 ~~~ mysqldump -uroot -proot --all-databases >/tmp/all.sql ~~~ 2. 導出db1、db2兩個數據庫的所有數據 ~~~ mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql ~~~ 3. 導出db1中的a1、a2表 > 注意導出指定表只能針對一個數據庫進行導出，且導出的內容中和導出數據庫也不一樣，導出指定表的導出文本中沒有創建數據庫的判斷語句，只有刪除表-創建表-導入數據 ~~~ mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql ~~~ 4. 條件導出，導出db1表a1中id=1的數據 > 如果多個表的條件相同可以一次性導出多個表字段是整形 ~~~ mysqldump -uroot -proot --databases db1 --tables a1 --where='id=1' >/tmp/a1.sql ~~~ 5. 生成新的binlog文件,-F > 有時候會希望導出數據之后生成一個新的binlog文件,只需要加上-F參數即可 ~~~ mysqldump -uroot -proot --databases db1 -F >/tmp/db1.sql ~~~ 6. 只導出表結構不導出數據，--no-data ~~~ mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql ~~~ 7. 跨服務器導出導入數據 > 將h1服務器中的db1數據庫的所有數據導入到h2中的db2數據庫中，db2的數據庫必須存在否則會報錯, 加上-C參數可以啟用壓縮傳遞 ~~~ # 全量復制 mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2 # 增量復制 mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test ~~~ 8. 將主庫的binlog位置和文件名追加到導出數據的文件中，--dump-slave > 注意：--dump-slave命令如果當前服務器是從服務器那么使用該命令會執行stop slave來獲取master binlog的文件和位置，等備份完后會自動執行start slave啟動從服務器。但是如果是大的數據量備份會給從和主的延時變的更大，使用--dump-slave獲取到的只是當前的從服務器的數據執行到的主的binglog的位置是（relay_mater_log_file,exec_master_log_pos),而不是主服務器當前的binlog執行的位置，主要是取決于主從的數據延時。 > 該參數在在從服務器上執行，相當于執行show slave status。當設置為1時，將會以CHANGE MASTER命令輸出到數據文件；設置為2時，會在change前加上注釋,該選項將會打開--lock-all-tables，除非--single-transaction被指定。在執行完后會自動關閉--lock-tables選項。--dump-slave默認是1 ~~~ mysqldump -uroot -proot --dump-slave=1 --databases db1 >/tmp/db1.sql ~~~ 9. 將當前服務器的binlog的位置和文件名追加到輸出文件，--master-data > 該參數和--dump-slave方法一樣，只是它是記錄的是當前服務器的binlog，相當于執行show master status，狀態（file,position)的值。注意：--master-data不會停止當前服務器的主從服務 10. --opt 等同于--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, --disable-keys 該選項默認開啟, 可以用--skip-opt禁用. mysqldump -uroot -p --host=localhost --all-databases --opt 11. 保證導出的一致性狀態--single-transaction > 該選項在導出數據之前提交一個BEGIN SQL語句，BEGIN 不會阻塞任何應用程序且能保證導出時數據庫的一致性狀態。它只適用于多版本存儲引擎（它不顯示加鎖通過判斷版本來對比數據），僅InnoDB。本選項和--lock-tables 選項是互斥的，因為LOCK TABLES 會使任何掛起的事務隱含提交。要想導出大表的話，應結合使用--quick 選項。 > --quick, -q > 不緩沖查詢，直接導出到標準輸出。默認為打開狀態，使用--skip-quick取消該選項。 12. --lock-tables, -l > 開始導出前，鎖定所有表。用READ LOCAL鎖定表以允許MyISAM表并行插入。對于支持事務的表例如InnoDB和BDB，--single-transaction是一個更好的選擇，因為它根本不需要鎖定表。 > 請注意當導出多個數據庫時，--lock-tables分別為每個數據庫鎖定表。因此，該選項不能保證導出文件中的表在數據庫之間的邏輯一致性。不同數據庫表的導出狀態可以完全不同。 13. 導出存儲過程和自定義函數--routines, -R ~~~ mysqldump -uroot -p --host=localhost --all-databases --routines ~~~ 14. 壓縮備份 ~~~ mysqldump -uroot -proot --databases abc 2>/dev/null |gzip >/abc.sql.gz ~~~ 壓縮備份 還原 ~~~ gunzip -c abc.sql.gz |mysql -uroot -proot abc ~~~ > 1 mysqldump導出數據主要有兩種控制：一種是導出的全過程都加鎖 lock-all-tables, 另一種則是不加。前者會在導出開始時執行 FLUSH TABLES WITH READ LOCK; 也就是加全局讀鎖，會阻塞其它寫操作，以保證導出是一致性的；因此只有在導出測試數據時或導出時沒有業務連接操作時可不加 lock-all-tables . > 至于說一致性導出的另一種方式 single-transaction, 則是有適用范圍的，見下邊。 > 2 single-transaction 選項和 lock-all-tables 選項是二選一的，前者是在導出開始時設置事務隔離狀態并使用一致性快照開始事務,而后馬上unlock tables，然后執行導出,導出過程不影響其它事務或業務連接，但只支持類似innodb多版本特性的引擎，因為必須保證即使導出期間其它操作(事務點t2)改變了數據，而導出時仍能取出導出開始的事務點t1時的數據。而lock-all-tables則一開始就 FLUSH TABLES WITH READ LOCK; 加全局讀鎖，直到dump完畢。 > -- 關于一致性快照，簡單地說，就是通過回滾段能記錄不同的事務點的各版本數據 > -- single-transaction 的流程如下： > SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ > START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ > SHOW MASTER STATUS -- 這一步就是取出 binlog index and position > UNLOCK TABLES > ...dump... > 3 master_data 選項開啟時默認會打開lock-all-tables，因此同時實現了兩個功能，一個是加鎖，一個是取得log信息。 > master_data取1和取2的區別，只是后者把 change master ... 命令注釋起來了，沒多大實際區別； > 4 當master_data和 single_transaction 同時使用時，先加全局讀鎖，然后設置事務一致性和使用一致性快照開始事務，然后馬上就取消鎖，然后執行導出。過程如下 > FLUSH TABLES WITH READ LOCK > SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ > START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */ > SHOW MASTER STATUS -- 這一步就是取出 binlog index and position > UNLOCK TABLES > ...dump... > 5 總結，了解了這些選項作用后，使用起來就明確了. > 如果需要binlog信息則使用 master_data; > 如果不想阻塞同時表是innodb引擎可使用 single_transaction 取得一致性快照(取出的數據是導出開始時刻事務點的狀態) > 如果表不支持多版本特性，則只能使用 lock-all-tables 阻塞方式來保證一致性的導出數據。 > 當然，如果能保證導出期間沒有任何寫操作，可不加或關閉 lock-all-tables > 追問 > ok,謝謝,我已經找到答案了... > 做一下測試..然后看一下日志..我就大概明白了...一般情況下..兩個參數用在一起是最好的...即可以保證數據的一致性,又可以記錄日志點....對于增量備份,主從搭建都很好... > 不過,還是謝謝你的詳細解答... * * * * * ### 2. 新建定時任務 1. 備份腳本 ~~~ #!/bin/bash set -x source $1 DATE=`date +'%Y-%m-%d-%H:%M'` #p2="$3" result=`echo "$3" |grep "day"` if [ "$result" != "" ];then date=`date -d $3 +%Y%m%d` else date=`date -d $3 +%Y%m` fi echo "date:$date" backUpOneParam(){ mysqldump --host=$FROM_IP -u$FROM_USER -p$FROM_PASSWD --databases $FROM_DATABASE --table $1 > /tmp/$DATE-$1.sql \ && mysql --host=$TO_IP -u$TO_USER -p$TO_PASSWD -D$TO_DATABASE < /tmp/$DATE-$1.sql } backUpTwoParam(){ mysqldump --host=$FROM_IP -u$FROM_USER -p$FROM_PASSWD --databases $FROM_DATABASE --table "$1_$date" > /tmp/$DATE-$1_$date.sql \ && mysql --host=$TO_IP -u$TO_USER -p$TO_PASSWD -D$TO_DATABASE < /tmp/$DATE-$1_$date.sql } if [ $# -eq 2 ];then backUpOneParam $2 elif [ $# -eq 3 ];then backUpTwoParam $2 $3 else echo "USAGE: $0 <config_file> <TABLENAME> <interval>day|month" echo "e.g.: $0 <transfer.properties> device_training_record -1day" exit 1; fi ~~~ 2. 配置文件 ~~~ #源數據庫 FROM_IP='192.168.2.21' FROM_USER='timing' FROM_PASSWD='**' FROM_DATABASE='****' #目標數據庫 TO_IP='192.168.2.21' TO_USER='manage' TO_PASSWD='*****' TO_DATABASE='managedb' #定時任務 #*/1 * * * * /home/tuna/shelles/mysql/backup.sh /home/tuna/shelles/mysql/backup.properties #測試 #./transfer.sh ./transfer.properties device_training_record +0day ~~~ 2. 建立定時任務 * source或者點去加載配置文件，會讀取不到配置！！！！！ * * * * * ### 3. 完全卸載mysql 1.刪除客戶端服務器 sudo apt-get autoremove --purge MySQL-server-5.6(5.6是你的版本號） sudo apt-get autoremove --purge mysql-client-5.6(5.6是你的版本號） sudo apt-get remove mysql-common (非常重要) 2.清理殘留數據 dpkg -l |grep ^rc|awk '{print $2}' |sudo xargs dpkg -P 3.清除數據庫舊數據 mysql的安裝文件夾刪除 4.重新安裝 sudo apt-get install mysql-server-5.6(5.6是你的版本號） * * * * * ### 4. mysql 備份、還原 > mysqldump+binlo：周一到周六增量備份，周日全量備份 #### 4.1 開啟binlog 1. 修改mysqld.cnf ~~~ root@docker02:/etc/mysql/mysql.conf.d# pwd /etc/mysql/mysql.conf.d root@docker02:/etc/mysql/mysql.conf.d# vim mysqld.cnf # 做以下修改 log-bin=mysql-bin # 這個隨便起名 ~~~ 2. 查看binlog信息  3. 查看binlog文件（在mysql的datadir下，從mysql.cnf獲取）  多了兩個這個文件 mysql-bin.000001 沒mysql重啟一次，會增加一個這個文件，如mysql-bin.000002 4. 查看binlog文件 SHOW BINLOG EVENTS IN 'mysql-bin.000005' \G  #### 4.2 增量與全量備份腳本 1. 全量備份腳本 ~~~ #!/bin/bash set -x set -e D_USER=root D_PASSWD=tuna BAK_DIR=/data/mysql/backup/full LOGFILE=/data/mysql/backup/bak.log OLDBINLOG=/data/mysql/backup/daily deleteLogs(){ if [ -d $OLDBINLOG ];then rm -f $OLDBINLOG/* fi } if [ ! -d $BAKDIR ];then mkdir -p $BAKDIR elif [ ! -f $LOGFILE ];then touch $LOGFILE fi DATE=`date +'%Y%m%d-%H:%M'` BEGIN=`date +"%Y年%m月%d日 %H:%M"` mysqldump -uroot -ptuna --quick --events --all-databases --flush-logs --delete-master-logs --single-transaction > $BAK_DIR/$DATE.sql deleteLogs LAST=`date +"%Y年%m月%d日 %H:%M:%S"` echo "開始:$BEGIN 結束:$LAST $DATE.sql.tgz succ" >> $LOGFILE ~~~ 2. 增量備份腳本 ~~~ #!/bin/bash BakDir=/data/mysql/backup/daily #增量備份時復制mysql-bin.00000*的目標目錄，提前手動創建這個目錄 BinDir=/var/lib/mysql #mysql的數據目錄 LogFile=/data/mysql/backup/bak.log BinFile=$BinDir/mysql-bin.index #mysql的index文件路徑，放在數據目錄下的 set -x set -e NextNum=0 # flush the log to the disk and generate a new mysql-bin.00000* file if [ ! -d $BakDir ];then mkdir -p $BakDir fi mysqladmin -uroot -ptuna flush-logs Counter=`wc -l $BinFile |awk '{print $1}'` for file in `cat $BinFile` do base=`basename $file` #basename用于截取mysql-bin.00000*文件名，去掉./mysql-bin.000005前面的./ NextNum=`expr $NextNum + 1` if [ $NextNum -eq $Counter ] then echo $base skip! >> $LogFile else dest=$BakDir/$base if(test -e $dest) #test -e用于檢測目標文件是否存在，存在就寫exist!到$LogFile去 then echo $base exist! >> $LogFile else `mysqlbinlog $BinDir/$base >$BakDir/$base` echo $base copying >> $LogFile fi fi done echo `date +"%Y年%m月%d日 %H:%M:%S"` $Next Bakup succ! >> $LogFile ~~~ ### 5. xtrabackup ~~~ # 全量復制 innobackupex --user=root --password=tuna /data/mysql/backup/full/ # 在上一次全量或者增量的基礎上進行增量復制 innobackupex --user=root --password=tuna --incremental /data/mysql/backup/incr/ --incremental-basedir=/data/mysql/backup/full/2017-08-14_10-54-45 ~~~ 創建好的完整備份不能直接用來還原數據庫，需要對數據做一些準備工作，其中包括一些已經提交的事務的重放，未提交事務的回滾。 –apply-log 準備還原備份的選項 –use-memory=4G 設置準備還原數據時使用的內存，可以提高準備所花費的時間 增量備份的恢復需要有3個步驟 恢復完全備份 恢復增量備份到完全備份(開始恢復的增量備份要添加--redo-only參數，到最后一次增量備份要去掉--redo-only) 對整體的完全備份進行恢復，回滾未提交的數據 ~~~ # 準備一個全備 innobackupex --apply-log --redo-only /data/mysql/backup/full/2017-08-14_10-54-45 # 將增量應用到完全備份，注意最后一個增量不加 --redo-only 參數了，之前的增量一定要加 # 增量1 innobackupex --apply-log --redo-only /data/mysql/backup/full/2017-08-14_17-05-53 --incremental-dir=/data/mysql/backup/incr/2017-08-14_17-06-54 # 增量2，可不加--read-only innobackupex --apply-log --redo-only /data/mysql/backup/full/2017-08-14_17-05-53 --incremental-dir=/data/mysql/backup/incr/2017-08-14_17-06-54 # 把所有合在一起的完全備份整體進行一次apply操作，回滾未提交的數據## innobackupex --apply-log /data/mysql/backup/full/2017-08-14_10-54-45/ innobackupex --copy-back --rsync /data/mysql/backup/full/2017-08-14_10-54-45 ~~~ * 加上時間點還原具體的數據，我們通常也要備份binlog，在最后一次增量備份的目錄中，xtrabackup_binlog_info文件記載了最后一次增量備份在binlog的開啟位置，從這個位置開始還原全部數據。  ~~~ # 從mysql-bin.000001 的552位置開始還原 mysqlbinlog /tmp/mysql-bin.000001 --start-position=552 | mysql -uroot -ptuna ~~~ * 改數據權限 ~~~ cd /var/lib/mysql chown -R mysql.mysql * service mysql start ~~~ #### 6. 問題 問題1. ~~~ ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2) ~~~ 解決辦法： ~~~ service mysql stop service mysql start ~~~