MySQL mysqldump - 仅导出结构而没有自动增量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15656463/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
mysqldump - Export structure only without autoincrement
提问by Paris
I have a MySQL database and I am trying to find a way to export its structure only, without the auto increment values. mysqldump --no-datawould almost do the job, but it keeps the auto_increment values. Is there any way to do it without using PHPMyAdmin (that I know it can do it)?
我有一个 MySQL 数据库，我试图找到一种方法来仅导出其结构，而没有自动增量值。mysqldump --no-data几乎可以完成这项工作，但它保留了 auto_increment 值。有没有办法不使用 PHPMyAdmin（我知道它可以做到）？
采纳答案by JoDev
You can do this :
你可以这样做 ：
mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*\b//' > <filename>.sql
As mentioned by others, If you want sedto works properly, add the g(for global replacement) parameter like this :
正如其他人所提到的，如果你想sed以正常工作，添加g（用于摹这样叶形更换）参数：
mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*\b//g' > <filename>.sql
(this only works if you have GUI Tools installed: mysqldump --skip-auto-increment)
（如果您已经安装了GUI工具这仅适用：mysqldump --skip-auto-increment）
New UPDATE thanks to comments.
新的更新感谢评论。
The \bis useless and sometimes will break the command. See this SO topicfor explanations.
So the optimized answer would be :
将\b是无用的，有时候会打破命令。有关解释，请参阅此SO 主题。所以优化的答案是：
mysqldump -u root -p -h <db-host> --opt <db-name> -d --single-transaction | sed 's/ AUTO_INCREMENT=[0-9]*//g' > <filename>.sql
回答by JohnW
JoDev's answer worked perfectly for me with a small adjustment to the sed regular expression:
JoDev 的回答对我来说非常有用，只需对 sed 正则表达式稍作调整：
mysqldump -d -h localhost -u<user> -p<password> <databaseName> | sed 's/ AUTO_INCREMENT=[0-9]*//g' > databaseStructure.sql
回答by Erin Schoonover
It is --create-options, which is included with --opt, by default, which generates the AUTO_INCREMENT table definitions.
默认情况下，它是 --create-options，它包含在 --opt 中，用于生成 AUTO_INCREMENT 表定义。
If you only want the base tables,
如果你只想要基表，
mysql -hlocalhost -uuser -ppass --skip-column-names --batch \
-e "select table_name from tables where table_type = 'BASE TABLE' and table_schema = 'schemaname'" INFORMATION_SCHEMA \
| xargs mysqldump -hlocalhost -uuser -ppass \
--no-data --skip-triggers --skip-opt --no-create-db \
schemaname
If you want views, triggers and routines too,
如果你也想要视图、触发器和例程，
mysqldump -hlocalhost -uuser -ppass \
--skip-opt --events --routines --no-data \
schemaname
回答by Gerardo Rosciano
Thanks to this post, I was able to answer my question:
感谢这篇文章，我能够回答我的问题：
How can I do version control on my db?
如何对我的数据库进行版本控制？
Then I just created this script: db_bkp.sh
然后我刚刚创建了这个脚本： db_bkp.sh
#!/bin/sh
filename="db_structure.sql"
backupfolder="/var/www/"
fpath="$backupfolder/$filename"
usr="DBUSER"
pass="DBPASS"
db="DBNAME"
mysqldump --user=$usr --password=$pass --no-data $db | sed 's/ AUTO_INCREMENT=[0-9]*//g' > "$fpath"
Then I added this to crontab:
然后我将其添加到 crontab 中：
30 5 * * * sh /home/scripts/db_bkp.sh
Then in my repo I added the result, db_structure.sqlto git and before pushing changes to prod I always check if there's any structural changes I forgot to do on all dbs.
然后在我的 repo 中，我将结果添加db_structure.sql到 git 中，在将更改推送到 prod 之前，我总是检查是否有任何我忘记对所有 dbs 进行的结构更改。
回答by Zio Panzu
mysqldump -u [USER] -p [PASSWORD] -d --skip-opt --single-transaction [DB_SCHEMA] > [FILE.ESTENSIONE]
mysqldump -u [用户] -p [密码] -d --skip-opt --single-transaction [DB_SCHEMA] > [FILE.ESTENSIONE]
