mysql常用操作 （包括mysqldump,pt-table）

生產mysqldump參數

mysqldump -uroot '-pxx' -q --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs --socket=mysql.sock --set-gtid-purged=OFF

mysqldump 備份參數

接下來就是具體的解決步驟，首先備份數據。備份時不加 –master-data 參數和 –single-transaction。究其原因，–master-data 禁用 –lock-tables 參數，在和 –single-transaction 一起使用時會禁用 –lock-all-tables。在備份開始時，會獲取全局 read lock。 –single-transaction 參數設置默認級別為 REPEATABLE READ，并且在開始備份時執行 START TRANSACTION。在備份期間， 其他連接不能執行如下語句：ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE。MySQL 同步夯住，如果加了上述參數，mysqldump 也會夯住。mysqldump 會 FLUSH TABLES、LOCK TABLES，如果有 –master-data 參數，會導致 Waiting for table flush。同樣，有 –single-transaction 參數，仍然會導致 Waiting for table flush。另外，還可以看到 Waiting for table metadata lock，此時做了 DROP TABLE 的操作。此時可以停掉 MySQL 同步來避免這個問題。

參考oldbody

全庫備份

#!/bin/bash

#mysqldump to fully backup mysql data

if [ -f /root/.bash_profile ];then

source /root/.bash_profile

fi 

BakDir=/opt/mysqlbak/full

LogFile=/opt/mysqlbak/full/bak.log

Date=`date +%Y%m%d`

Begin=`date +"%Y年%m月%d日 %H:%M:%S"`

cd $BakDir

DumpFile=$Date.sql

GZDumpFile=$Date.sql.tgz

mysqldump -uroot -p'xxxxxx' --all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs --socket=/opt/3306/mysql.sock --set-gtid-purged=OFF > $DumpFile

tar zcvf $GZDumpFile $DumpFile

if [ -f $DumpFile ];then

rm -rf $DumpFile

fi

Last=`date +"%Y年%m月%d日 %H:%M:%S"`

echo 開始：$Begin 結束：$Last $GZDumpFile succ >> $LogFile

sleep 1

find /opt/mysqlbak/full -name "*.tgz" -mtime +3 -exec rm -rf {} \;

分庫備份

#!/bin/bash

if [ -f /root/.bash_profile ];then

source /root/.bash_profile

fi 

MysqlUser=root

PassWord='xxxxxx'

Port=3306

Socket="/opt/$Port/mysql.sock"

MysqlCmd="mysql -u$MysqlUser -p$PassWord -S $Socket"

Database=`$MysqlCmd -e "show databases;"|egrep -v "Database|_schema|mysql"`

MysqlDump="mysqldump -u$MysqlUser -p$PassWord -S $Socket"

#IP=`ifconfig eth0|awk -F "[:]+" 'NR==2 {print $4}'`

BackupDir=/opt/mysqlbak/fenku

LogFile=/opt/mysqlbak/fenku/bak.log

Begin=`date +"%Y年%m月%d日 %H:%M:%S"`

[ -d $BackupDir ] || mkdir -p$BackupDir

for dbname in $Database

  do

     $MysqlDump --events --set-gtid-purged=OFF -B $dbname|gzip>/$BackupDir/${dbname}_$(date +%F)_bak.sql.gz

done

Last=`date +"%Y年%m月%d日 %H:%M:%S"`

echo 開始：$Begin 結束：$Last $GZDumpFile succ >> $LogFile

sleep 1

find /opt/mysqlbak/fenku -name "*.gz" -mtime +3 -exec rm -rf {} \;

還原

單個還原  

mysqldump備份中恢復單張表

mysql -uroot -pMANAGER erp --one-database <dump.sql

SELECT TABLE_NAME,TABLE_ROWS,DATA_LENGTH/1024/1024 "DATA_LENGTH",CREATE_TIME,TABLE_COLLATION FROM  INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'xx' ORDER BY TABLE_ROWS DESC;

看DATA_LENGTH大小是否一致

pt工具檢測

pt-table-checksum是一個在線驗證主從數據一致性的工具,主要用于以下場景:

1. 數據遷移前后,進行數據一致性檢查

2. 當主從復制出現問題,待修復完成后,對主從數據進行一致性檢查

3. 把從庫當成主庫,進行數據更新,產生了"臟數據"

4. 定期校驗

pt-table-checksum 使用注意

默認當數據庫有25個以上的并發查詢時，pt-table-checksum會暫停。可以設置 --max-load 選項來設置這個閥值

當用 Ctrl+C 停止任務后，工具會正常的完成當前 chunk 檢測，下次使用 --resume 選項啟動可以恢復繼續下一個 chunk

utf8

 # pt-table-sync  --execute  --replicate \

 test.checksums   --charset=utf8 \

 --sync-to-master h=192.168.1.207,P=3306,u=root,p=123456

1，在恢復數據的時候有出來過只能恢復部份從庫的情況，我的操作方法是把輸出的語句保存在一個文本里面，然后直接貼到沒有正常恢復的從庫去執行。

2，--chunk-size-limit默認設置為2，當遇到行數多的大表時pt-table-checksum可能會跳過不檢測，提示：

Skipping table db.table because on the master it would be checksummed in one chunk but on these replicas it has too many rows:

  355085 rows on asddb.xxx

The current chunk size limit is 239358 rows (chunk size=119679 * chunk size limit=2.0).

此時可以根據輸出的提示將--chunk-size-limit適當調大一點。

slave  

show slave status\G;

master

show slave hosts;

show variables like 'ENFORCE_GTID_CONSISTENCY';

show global variables like '%gtid_mode%';

set @@global.gtid_mode = off_permissive;

set @@global.enforce_gtid_consistency = on;

autocommit=1

yum -y  install perl-Time-HiRes perl-DBI perl-DBD-MySQL

percona-toolkit-2.2.18.tar.gz

make && make install

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'USER'@'MASTER_HOST' identified  by 'PASSWORD';

SELECT concat('DROP TABLE IF EXISTS ', table_name, ';') FROM information_schema.tables WHERE table_schema='xx'

GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'checksums'@'masterip' IDENTIFIED BY 'xx';

grant all on test.* to 'checksums'@'masterip' IDENTIFIED BY 'xx';

PTDEBUG=1 /usr/local/bin/pt-table-sync --replicate=test.checksums --recursion-method=processlist -d xx --tables=pub_dditem --port=3306 h='172.29.12.197',u='checksums',p='MANAGER' --print --execute

pt-table-sync 使用注意

1.采用replace into來修復主從不一致，必須保證被replace的表上有主鍵或唯一鍵，否則replace into退化成insert into，起不到修復的效果。這種情況下pt-table-sync會采用其他校驗和修復算法，但是效率非常低，例如對所有列的group by然后求count(*)(表一定要有主鍵！)。

2.主從數據不一致需要通過replace into來修復，該sql語句必須是語句級。pt-table-sync會把它發起的所有sql語句都設置為statement格式，而不管全局的binlog_format值。這在級聯A-B-C結構中，也會遇到pt-table-checksum曾經遇到的問題，引起行格式的中繼庫的從庫卡庫是必然。不過pt-table-sync默認會無限遞歸的對從庫的binlog格式進行檢查并警告。

3.由于pt-table-sync每次只能修復一個表，所以如果修復的是父表，則可能導致子表數據連帶被修復，這可能會修復一個不一致而引入另一個不一致；如果表上有觸發器，也可能遇到同樣問題。所以在有觸發器和主外鍵約束的情況下要慎用。pt-table-sync工具同樣也不歡迎主從異構的結構。pt-table-sync工具默認會進行先決條件的檢查。

4.pt-table-sync在修復過程中不能容忍從庫延遲，這正好與pt-table-checksum相反。如果從庫延遲太多，pt-table-sync會長期持有對chunk的for update鎖，然后等待從庫的master_pos_wait執行完畢或超時。從庫延遲越大，等待過程就越長，主庫加鎖的時間就越長，對線上影響就越大。因此要嚴格設置max-lag。

5.對從庫數據的修復通常是在主庫執行sql來同步到從庫。因此，在有多個從庫時，修復某個從庫的數據實際會把修復語句同步到所有從庫。數據修復的代價取決于從庫與主庫不一致的程度，如果某從庫數據與主庫非常不一致，舉例說，這個從庫只有表結構，那么需要把主庫的所有數據重新灌一遍，然后通過binlog同步，同時會傳遞到所有從庫。這會給線上帶來很大壓力，甚至拖垮集群。正確的做法是，先用pt-table-checksum校驗一遍，確定不一致的程度：如果不同步的很少，用pt-table-sync直接修復；否則，用備份先替換它，然后用pt-table-sync修復。 說明： 這實際提供了一種對myisam備份的思路：如果僅有一個myisam的主庫，要為其增加從庫，則可以：先mysqldump出表結構到從庫上，然后啟動同步，然后用pt-table-sync來修復數據。

1.http://blog.itpub.net/29733787/viewspace-1462550/

show master status ;

show slave status \G；

SET @@SESSION.GTID_NEXT= '5882bfb0-c936-11e4-a843-000c292dc103:15';

2.

pt 如何更好的使用pt工具

1、是的，在凌晨2點開始進行checksum

2、不會，我們嚴格控制了每個chunk的大小，鎖粒度及時間相當短，并且我們也二次開發了pt-table-checksum，使得風險更可控

故障恢復

mysqldump全備配合binlog做增量備份  通過mysqlbinlog還原數據

mysqldump常用

grep -i "change master to" master-data.sql  mysql5.5主從能用到

Mysqldump導入數據庫很慢的解決辦法

--max_allowed_packet=*****           客戶端/服務器之間通信的緩存區的最大大小;

--net_buffer_length=****                 TCP/IP和套接字通信緩沖區大小,創建長度達net_buffer_length的行

參照查詢到的目標數據參數，導出數據；

# mysqldump -uroot -p*** 原數據庫 -e --max_allowed_packet=4194304 --net_buffer_length=16384 > file.sql

只備份表結構

mysqldump --opt -d 數據庫名 -u root -p > xxx.sql 

導出數據庫為dbname某張表(test)結構及表數據（不加-d）

mysqldump -uroot -pdbpasswd dbname test>db.sql;

導出整個數據庫結構（不包含數據）

mysqldump -h localhost -uroot -p123456  -d database > dump.sql

導出單個數據表結構（不包含數據）

mysqldump -h localhost -uroot -p123456  -d database table > dump.sql

mysqldump注意事項

參考 http://huaxin.blog.51cto.com/903026/1846224

mysqldump -uroot -p123456 xxx > /opt/xxx.sql   #備份數據庫xxx

egrep -v "#|\*|--|^$" /opt/xxx.sql

mysqldump -uroot -p123456 xxx --default-character-set=latin1 > /opt/xxx1.sql

egrep -v "#|\*|--|^$" /opt/xxx1.sql

mysqldump -uroot -p123456 -B xxx --default-character-set=latin1 > /opt/xxx1_B.sql

diff xxx1.sql xxx1_B.sql   對比沒有加 -B選項  和加 -B選項時候的區別

說明：直觀看 加了 -B 參數的作用是在導出數據庫的時候增加了 創建數據庫和連接數據庫的命令了，即如下兩條語句

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `xxx` /*!40100 DEFAULT CHARACTER SET latin1 */;

總結：

1、導出數據用-B參數

2、用gzip對備份的數據壓縮

mysqldump 的工作原理

利用mysqldump命令備份數據的過程，實際上就是把數據從mysql庫里面以邏輯的sql語句的形式輸出

cat mysql.sh   #備份數據庫多個庫的腳本

#!/bin/bash

for dbname in `mysql -uroot -p123456 -e "show databases;"|grep -Evi "database|info|perf"`

do

    mysqldump  -uroot -p123456  --events -B ${dbname}|gzip > /opt/${dbname}.sql.gz

done 

備份單個表

mysqldump -uroot -p123456 martin student > one.sql  

備份多個表

mysqldump -uroot -p123456 martin student student1 > two.sql

mysqldump -uroot -p123456 -d martin student1 

只備份student1  表的結構  martin代表數據庫

mysqldump -uroot -p123456 -A -B --events|gzip > /opt/all.sql.gz   -A代表所有數據庫

mysqldump -uroot -p123456 -A -B -F --events|gzip > /opt/all.sql.gz   -F 會刷新bin-log

mysqldump -uroot -p123456 --master-data=1 --compact martin       #--master-data=1 該參數會找bin-log位置

mysqldump -uroot -p123456 --master-data=2 --compact martin       #--master-data=2 該參數會找bin-log位置，但是語句被注釋，實際并不執行

mysqldump的關鍵參數說明

1、-B 指定多個庫，會增加建庫語句和use語句

2、--compact 去掉注釋，適合調試輸出 生產環境不用

3、-A 備份所有庫

4、-F 刷新binlog日志

5、--master-data=1 增加binglog日志文件名及對應的位置點

6、-x 鎖表

7、-l  只讀鎖表

8、-d  只備份表結構

9、-t  只備份數據

10、--single-transaction  適合innodb事務數據庫備份

--master-data[=#] 

If equal to 1, will print it as a CHANGE MASTER command; if equal to 2, that command will 

be prefixed with a comment symbol

這個參數會運行--lock-all-tables，將master的binlog和postion信息寫入SQL文件的頭部，除非結合--single-transaction（但并不是說就完全的不會鎖表了，執行的時候也會添加短暫的全局讀鎖）

生產場景myisam備份：

mysqldump -uroot -p123456 -A -B --master-data=1 -x --events|gzip > /opt/all.sql.gz

生產場景innodb備份：

mysqldump -uroot -p123456 -A -B --master-data=1 --events --single-transaction|gzip > /opt/all.sql.gz

system ls /opt

rh  xxx1_B.sql  xxx1_B.sql.gz  xxx1.sql  xxx.sql

source /opt/xxx1_B.sql 

mysql5.7 mysqldump參數--all-databases --lock-all-tables --routines --triggers --events --master-data=2 --flush-logs --socket=/opt/3306/mysql.sock --set-gtid-purged=OFF

mysqldump重疊備份帶來的鎖表問題  2013

解決方法：

1.如果你只需要文件備份，不需要經常建立從庫，那么可以去掉--master-data。

2.如果你的數據量很大 or 備份時的master信息非常需要，那么可以調整備份周期，避開兩次備份出現重疊的情況