“Mysqldump”的版本间的差异
跳到导航
跳到搜索
| （未显示同一用户的7个中间版本） | |||
| 第65行： | 第65行： | ||
导入不用写表名,只指定库名就行了 | 导入不用写表名,只指定库名就行了 | ||
| − | =Reference | + | =see also = |
| + | [[Mysql study之常用操作]] | ||
| + | |||
| + | =Reference = | ||
[https://www.cnblogs.com/chuanzhang053/p/8710434.html mysqldump备份时，--master-data选项的作用是什么] | [https://www.cnblogs.com/chuanzhang053/p/8710434.html mysqldump备份时，--master-data选项的作用是什么] | ||
| 第71行： | 第74行： | ||
[https://www.cnblogs.com/stache/p/5836261.html mysqldump数据库备份还原详解] | [https://www.cnblogs.com/stache/p/5836261.html mysqldump数据库备份还原详解] | ||
| + | |||
| + | [https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-option-examples dev.mysql.com 4.5.4 mysqldump — A Database Backup Program] | ||
[https://blog.csdn.net/yzf279533105/article/details/125162013 mysqldump导出部分数据和其它选项] | [https://blog.csdn.net/yzf279533105/article/details/125162013 mysqldump导出部分数据和其它选项] | ||
| 第76行： | 第81行： | ||
[https://blog.51cto.com/jschu/1727786 mysqldump导出指定表和部分指定数据] | [https://blog.51cto.com/jschu/1727786 mysqldump导出指定表和部分指定数据] | ||
| + | [https://www.cnblogs.com/chuanzhang053/p/8866618.html mysqldump工具,通过--where选项,导出指定表中指定数据?] | ||
| + | |||
| + | [https://blog.csdn.net/qq_44336650/article/details/121853803 mysqldump where子句使用] | ||
| + | |||
| + | [https://blog.csdn.net/airfish20000/article/details/77677824 mysqldump的where条件的妙用] | ||
[https://my.oschina.net/huzorro/blog/74132 MySQL mysqldump排除指定表] | [https://my.oschina.net/huzorro/blog/74132 MySQL mysqldump排除指定表] | ||
| 第87行： | 第97行： | ||
[https://blog.51cto.com/u_15127526/4379949 MySQL 8.MySQL数据库导入、导出和授权] | [https://blog.51cto.com/u_15127526/4379949 MySQL 8.MySQL数据库导入、导出和授权] | ||
| + | [https://blog.51cto.com/u_15067247/4037586 mysqldump导出数据时，某些表不导出，排除某些表，不导出某些表] | ||
| + | |||
| + | |||
| + | [https://www.runoob.com/mysql/mysql-database-export.html MySQL 导出数据] | ||
| + | [https://www.computerhope.com/unix/mysqldum.htm Linux mysqldump command] | ||
==mysql导出导入大量数据 == | ==mysql导出导入大量数据 == | ||
2022年7月28日 (四) 02:53的最新版本
目录
忽略多个表
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(表名)
导入
导入不用写表名,只指定库名就行了
see also
Reference
mysqldump备份时，--master-data选项的作用是什么
dev.mysql.com 4.5.4 mysqldump — A Database Backup Program
mysqldump工具,通过--where选项,导出指定表中指定数据?
mysqldump 使用 --set-gtid-purged
mysqldump提示Warning: A partial dump from a server that has GTIDs
mysqldump导出数据时，某些表不导出，排除某些表，不导出某些表
