MySQL common operations (including mysqldump,pt-table)

Reference Oldbody

Full Library Backup

#!/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 year%M month%d day%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 year%M month%d day%h:%m:%s "'

echo Start: $Begin end: $Last $GZDumpFile succ >> $LogFile

Sleep 1

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

Sub-Library Backup

#!/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 year%M month%d day%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 year%M month%d day%h:%m:%s "'

echo Start: $Begin end: $Last $GZDumpFile succ >> $LogFile

Sleep 1

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

Restores

Single Restore

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;

See if the data_length is the same size.

PT Tool Detection

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

This article is from the "people, to have their own ideas" blog, please be sure to keep this source http://szgb2016.blog.51cto.com/340201/1842036

MySQL common operations (including mysqldump,pt-table)