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

生產(chǎn)mysqldump參數(shù)

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 備份參數(shù)

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

參考o(jì)ldbody

全庫備份

#!/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 結(jié)束：$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 結(jié)束：$Last $GZDumpFile succ >> $LogFile

sleep 1

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

還原

單個(gè)還原  

mysqldump備份中恢復(fù)單張表

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工具檢測(cè)

pt-table-checksum是一個(gè)在線驗(yàn)證主從數(shù)據(jù)一致性的工具,主要用于以下場(chǎng)景:

1. 數(shù)據(jù)遷移前后,進(jìn)行數(shù)據(jù)一致性檢查

2. 當(dāng)主從復(fù)制出現(xiàn)問題,待修復(fù)完成后,對(duì)主從數(shù)據(jù)進(jìn)行一致性檢查

3. 把從庫當(dāng)成主庫,進(jìn)行數(shù)據(jù)更新,產(chǎn)生了"臟數(shù)據(jù)"

4. 定期校驗(yàn)

pt-table-checksum 使用注意

默認(rèn)當(dāng)數(shù)據(jù)庫有25個(gè)以上的并發(fā)查詢時(shí)，pt-table-checksum會(huì)暫停?？梢栽O(shè)置 --max-load 選項(xiàng)來設(shè)置這個(gè)閥值

當(dāng)用 Ctrl+C 停止任務(wù)后，工具會(huì)正常的完成當(dāng)前 chunk 檢測(cè)，下次使用 --resume 選項(xiàng)啟動(dòng)可以恢復(fù)繼續(xù)下一個(gè) 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，在恢復(fù)數(shù)據(jù)的時(shí)候有出來過只能恢復(fù)部份從庫的情況，我的操作方法是把輸出的語句保存在一個(gè)文本里面，然后直接貼到?jīng)]有正常恢復(fù)的從庫去執(zhí)行。

2，--chunk-size-limit默認(rèn)設(shè)置為2，當(dāng)遇到行數(shù)多的大表時(shí)pt-table-checksum可能會(huì)跳過不檢測(cè)，提示：

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).

此時(shí)可以根據(jù)輸出的提示將--chunk-size-limit適當(dāng)調(diào)大一點(diǎn)。

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

2.主從數(shù)據(jù)不一致需要通過replace into來修復(fù)，該sql語句必須是語句級(jí)。pt-table-sync會(huì)把它發(fā)起的所有sql語句都設(shè)置為statement格式，而不管全局的binlog_format值。這在級(jí)聯(lián)A-B-C結(jié)構(gòu)中，也會(huì)遇到pt-table-checksum曾經(jīng)遇到的問題，引起行格式的中繼庫的從庫卡庫是必然。不過pt-table-sync默認(rèn)會(huì)無限遞歸的對(duì)從庫的binlog格式進(jìn)行檢查并警告。

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

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

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

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點(diǎn)開始進(jìn)行checksum

2、不會(huì)，我們嚴(yán)格控制了每個(gè)chunk的大小，鎖粒度及時(shí)間相當(dāng)短，并且我們也二次開發(fā)了pt-table-checksum，使得風(fēng)險(xiǎn)更可控

故障恢復(fù)

mysqldump全備配合binlog做增量備份  通過mysqlbinlog還原數(shù)據(jù)

mysqldump常用

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

Mysqldump導(dǎo)入數(shù)據(jù)庫很慢的解決辦法

--max_allowed_packet=*****           客戶端/服務(wù)器之間通信的緩存區(qū)的最大大小;

--net_buffer_length=****                 TCP/IP和套接字通信緩沖區(qū)大小,創(chuàng)建長(zhǎng)度達(dá)net_buffer_length的行

參照查詢到的目標(biāo)數(shù)據(jù)參數(shù)，導(dǎo)出數(shù)據(jù)；

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

只備份表結(jié)構(gòu)

mysqldump --opt -d 數(shù)據(jù)庫名 -u root -p > xxx.sql 

導(dǎo)出數(shù)據(jù)庫為dbname某張表(test)結(jié)構(gòu)及表數(shù)據(jù)（不加-d）

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

導(dǎo)出整個(gè)數(shù)據(jù)庫結(jié)構(gòu)（不包含數(shù)據(jù)）

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

導(dǎo)出單個(gè)數(shù)據(jù)表結(jié)構(gòu)（不包含數(shù)據(jù)）

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

mysqldump注意事項(xiàng)

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

mysqldump -uroot -p123456 xxx > /opt/xxx.sql   #備份數(shù)據(jù)庫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   對(duì)比沒有加 -B選項(xiàng)  和加 -B選項(xiàng)時(shí)候的區(qū)別

說明：直觀看 加了 -B 參數(shù)的作用是在導(dǎo)出數(shù)據(jù)庫的時(shí)候增加了 創(chuàng)建數(shù)據(jù)庫和連接數(shù)據(jù)庫的命令了，即如下兩條語句

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

總結(jié)：

1、導(dǎo)出數(shù)據(jù)用-B參數(shù)

2、用gzip對(duì)備份的數(shù)據(jù)壓縮

mysqldump 的工作原理

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

cat mysql.sh   #備份數(shù)據(jù)庫多個(gè)庫的腳本

#!/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 

備份單個(gè)表

mysqldump -uroot -p123456 martin student > one.sql  

備份多個(gè)表

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

mysqldump -uroot -p123456 -d martin student1 

只備份student1  表的結(jié)構(gòu)  martin代表數(shù)據(jù)庫

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

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

mysqldump -uroot -p123456 --master-data=1 --compact martin       #--master-data=1 該參數(shù)會(huì)找bin-log位置

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

mysqldump的關(guān)鍵參數(shù)說明

1、-B 指定多個(gè)庫，會(huì)增加建庫語句和use語句

2、--compact 去掉注釋，適合調(diào)試輸出 生產(chǎn)環(huán)境不用

3、-A 備份所有庫

4、-F 刷新binlog日志

5、--master-data=1 增加binglog日志文件名及對(duì)應(yīng)的位置點(diǎn)

6、-x 鎖表

7、-l  只讀鎖表

8、-d  只備份表結(jié)構(gòu)

9、-t  只備份數(shù)據(jù)

10、--single-transaction  適合innodb事務(wù)數(shù)據(jù)庫備份

--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

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

生產(chǎn)場(chǎng)景myisam備份：

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

生產(chǎn)場(chǎng)景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參數(shù)--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.如果你只需要文件備份，不需要經(jīng)常建立從庫，那么可以去掉--master-data。

2.如果你的數(shù)據(jù)量很大 or 備份時(shí)的master信息非常需要，那么可以調(diào)整備份周期，避開兩次備份出現(xiàn)重疊的情況