生产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 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信息非常需要，那么可以调整备份周期，避开两次备份出现重叠的情况
分享名称：mysql常用操作（包括mysqldump,pt-table）
分享地址：http://bjjierui.cn/article/peodsg.html
