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

32ijhgf

全库备份
#!/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 {} \;


还原

单个还原  

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工具检测
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