MySQL 使用 mysqldump 和数据库用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3982299/
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
Using mysqldump and database users
提问by Bobby B
I'm attempting to assemble all the options that I need for mysqldump to create everything used by my applications database into a single script. This includes the database itself and all the database users/passwords/privileges.
我正在尝试将 mysqldump 所需的所有选项组合在一起，以将我的应用程序数据库使用的所有内容创建到单个脚本中。这包括数据库本身和所有数据库用户/密码/权限。
I've got it all figured out with the exception of the user piece... here's what I'm currently using:
除了用户部分，我已经弄清楚了……这是我目前正在使用的：
mysqldump -h host -u root -p \
--add-drop-database --routines -B database_name > backup.sql
So, what am I missing?
那么，我错过了什么？
回答by Marc B
The database users/passwords/privileges are kept in the mysqldatabase, and won't get dumped with your dump command. You'll have to add that database as well to the list of DBs to dump:
数据库用户/密码/权限保存在mysql数据库中，不会被您的 dump 命令转储。您还必须将该数据库添加到要转储的数据库列表中：
mysqldump ... --routines --databases database_name mysql > backup.sql
or just dump everything:
或者只是转储所有内容：
mysqldump ... --routines --all-databases > backup.sql
回答by Markus T
When dumping the mysql database, don't forget:
转储mysql数据库时，不要忘记：
--flush-privileges Emit a FLUSH PRIVILEGES statement after dumping the mysql
database. This option should be used any time the dump
contains the mysql database and any other database that
depends on the data in the mysql database for proper
restore.
回答by Bobby B
So, I had a fundamental misunderstanding. Users are not specific to a database, but are rather created at the server level.
所以，我有一个根本的误解。用户不是特定于数据库的，而是在服务器级别创建的。
You can view all existing users with the following query:
您可以使用以下查询查看所有现有用户：
SELECT * FROM mysql.user;
Knowing this, it's pretty obvious that mysqldump shouldn't do anything with users. However, if you need an answer to exporting/importing users and perms I suggest you check the following article - it helped me out.
知道这一点，很明显 mysqldump 不应该对用户做任何事情。但是，如果您需要导出/导入用户和权限的答案，我建议您查看以下文章 - 它帮助了我。
http://pento.net/2009/03/12/backing-up-permissions-for-individual-databases/
http://pento.net/2009/03/12/backing-up-permissions-for-individual-databases/
My apologies for the noise on the board, but I figured I'd leave the post incase anyone else has the same misunderstanding.
我为板上的噪音道歉，但我想我会离开这个帖子，以防其他人有同样的误解。
回答by PaulMest
Full process for me when migrating from one Mac OS X dev environment to a new one.
从一个 Mac OS X 开发环境迁移到新环境时的完整过程。
0) Get rid of newer version of MySQL on new MacBook
0) 在新 MacBook 上摆脱更新版本的 MySQL
I had accidentally installed MySQL 8, so I had to remove it because it was newer than my old MacBook.
我不小心安装了 MySQL 8，所以我不得不删除它，因为它比我的旧 MacBook 新。
# Remove binaries
$ brew uninstall mysql
# Remove data/config that is leftover
$ rm -r /usr/local/var/mysql/
1) Install same version of MySQL on new MacBook
1) 在新 MacBook 上安装相同版本的 MySQL
# Install version that matched old MacBook
$ brew install [email protected]
# Because it is an old version, you have to do a special configuration step
$ echo 'export PATH="/usr/local/opt/[email protected]/bin:$PATH"' >> ~/.bash_profile
$ source ~/.bash_profile
# Start server
$ mysql.server start
2) Dump data on old MacBook
2) 在旧 MacBook 上转储数据
$ mysqldump -uroot --flush-privileges --routines --all-databases > complete_dump.sql
3) Restore data on new MacBook
3) 在新 MacBook 上恢复数据
$ mysql -p -uroot < complete_dump.sql
