Mysqldump

忽略多个表

tables=" tempData worldSceneCitys"

mysqldump -u root -p'xx' -h 127.0.0.1 --opt --default-character-set=utf8 --triggers -R --hex-blob --single-transaction --no-autocommit --master-data=2  ftsgqxz_12  ${tables[@]} > 1.sql


#对数据表进行过滤操作#
        tables=`/usr/local/services/mysql/bin/mysql -h ${gamedbip} -P${dbport} -u${users} -p${dbpass} $gamedbname -e "show tables" | grep -Ev '^m_|^Tables_in'`;

#从数据库导出到管理机指定目录
        mysqldump -h ${dbip} -P${dbport} -u${users} -p${dbpass} --opt --default-character-set=utf8 --triggers -R --hex-blob --single-transaction --no-autocommit --master-data=2  ${gamedbname} ${tables[@]} > ${backurl}/${gamedbname}/${gamedbname}_${backdatet}.sql;

--master-data选项

mysqldump --help
--master-data[=#]  This causes the binary log position and filename to be 
                      appended to the output. 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. This option will turn 
                      --lock-all-tables on, unless --single-transaction is 
                      specified too (in which case a global read lock is only 
                      taken a short time at the beginning of the dump; don't 
                      forget to read about --single-transaction below). In all 
                      cases, any action on logs will happen at the exact moment 
                      of the dump. Option automatically turns --lock-tables 
                      off.

--master-data[=#]      在备份导出的文件里追加二进制binlog文件的位置和名称 
                        如果值等于1，就会添加一个CHANGE MASTER语句 
                        如果值等于2，就会在CHANGE MASTER语句前添加注释（不起作用了呗~） 
                        这个参数会--lock-all-tables锁表，除非你指定了--single-transaction 
                        这种情况下，锁表只会在dump开始的时候持续一小段时间,照理说 
                        在dump的时候，任何动作都会影响到binlog文件 
                        dump结束之后，选项会自动关闭锁表功能

　　--master-data选项的作用就是将二进制的信息写入到输出文件中，在这里是写入到备份的sql文件中

If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. 

If the option value is 1, the statement is not written as a comment and takes effect when the dump file is reloaded. 

If no option value is specified, the default value is 1.

如果选项赋值为2，那么CHANGE MASTER TO 语句会被写成一个SQL comment（注释），从而只提供信息; 

如果选项赋值为1，那么语句不会被写成注释并且在dump被载入时生效。

如果没有指定，默认值为1。

troubleshooting

If you don't want to restore GTIDs, pass --set-gtid-purged=OFF.

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events. 

mysqldump -uUsername -h xx.xx.xx.xx -P3306 DBname t_name --set-gtid-purged=off -p >t_name.sql(表名)

mysql导出入关于gtid问题

导入

导入不用写表名,只指定库名就行了

Reference.

mysqldump备份时，--master-data选项的作用是什么

mysqldump详解之--master-data

mysqldump数据库备份还原详解

mysqldump导出部分数据和其它选项

mysqldump导出指定表和部分指定数据

mysqldump工具,通过--where选项,导出指定表中指定数据?

mysqldump where子句使用

mysqldump的where条件的妙用

MySQL mysqldump排除指定表

mysqldump 使用 --set-gtid-purged

mysqldump提示Warning: A partial dump from a server that has GTIDs

MySQL mysqldump数据导出详解

MySQL 8.MySQL数据库导入、导出和授权

mysqldump导出数据时，某些表不导出，排除某些表，不导出某些表

mysql导出导入大量数据

数据仓库：Mysql大量数据快速导出

https://www.jianshu.com/p/6761e10d9fb3