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

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

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

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

$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

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

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

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

网站栏目：mysql常用操作（包括mysqldump,pt-table）
网站网址：http://scyanting.com/article/peodsg.html

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

其他资讯