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信息非常需要，那么可以调整备份周期，避开两次备份出现重叠的情况