使用 mysqldump 和数据库用户

Question

我试图编写一个脚本，将我应用程序所使用的数据库的所有需要的mysqldump选项整合到一个脚本中。这包括数据库本身和所有数据库用户/密码/权限。

除了用户部分外，我已经全部搞定了...下面是我目前正在使用的内容：

mysqldump -h host -u root -p \
    --add-drop-database --routines -B database_name > backup.sql

那么，我错过了什么？

- Bobby B

---

MySQL用户不会存储在database_name中。 - ceejayoz

我想链接到DBA stackexchange中的此解决方案。 - knocte

Answer 1

数据库用户/密码/权限保存在 mysql 数据库中，不会随着您的转储命令而被转储。您需要将该数据库添加到要转储的数据库列表中:

mysqldump ... --routines --databases database_name mysql > backup.sql

或者仅仅倾倒所有内容：

mysqldump ... --routines --all-databases > backup.sql

- Marc B

---

完美 - 这就是我刚刚发现的。我建议我们只是警告人们，倾倒/恢复mysql数据库与使用我链接的脚本相比可能会产生一些意想不到的影响，特别是如果两个服务器不是彼此的镜像（它们上面没有相同的数据库）。 - Bobby B

请记住，mysql数据库中包含有关您可能不会转储的数据库的信息。我不知道将该元数据导入新的数据库服务器时会发生什么。 - Halfgaar

我实际上想要相反的结果。我想要所有“标准”的数据库被转储，但不包括用户信息等。因此，我使用了一个显式的数据库列表，而不是所有数据库，以避免转储“mysql”数据库。 - BuvinJ

Answer 2

在转储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.

- Markus T

Answer 3

所以，我有一个根本性的误解。用户并不是特定于数据库，而是在服务器级别创建。

您可以使用以下查询查看所有现有用户：

SELECT * FROM mysql.user;

由此可见，mysqldump不应该处理用户。但是，如果您需要导出/导入用户和权限的答案，建议您查看以下文章——它对我很有帮助。

http://pento.net/2009/03/12/backing-up-permissions-for-individual-databases/

对于在论坛上发表的冗余帖子，我表示歉意，但我认为我应该保留这篇文章，以防其他人产生同样的误解。

- Bobby B

---

不，这只是应该做的事情。此外，如果这篇帖子实际上让你解决了问题，那么你应该将这个答案标记为“答案”，而不是其他的答案。我只是想分享一下。 - jcolebrand

参考链接中展示的 SQL 语句存在错误，可能会出现“UNION 和 INTO 的使用不正确”的错误。解决方法是将 INTO 移至最后一个 SELECT 语句。 - knocte

是的，这就是答案。它回答了你的问题，但你需要动动脑筋，而不仅仅是复制粘贴某个人的命令。 - Johny19

Answer 4

当我从一个Mac OS X开发环境迁移到另一个新的开发环境时，整个流程如下：

0）在新的MacBook上卸载较新版本的MySQL

我不小心安装了MySQL 8，因此必须将其删除，因为它比我旧的MacBook上的版本更高。

# Remove binaries
$ brew uninstall mysql

# Remove data/config that is leftover
$ rm -r /usr/local/var/mysql/

1）在新的 MacBook 上安装相同版本的 MySQL

# Install version that matched old MacBook
$ brew install mysql@5.7

# Because it is an old version, you have to do a special configuration step
$ echo 'export PATH="/usr/local/opt/mysql@5.7/bin:$PATH"' >> ~/.bash_profile
$ source ~/.bash_profile

# Start server
$ mysql.server start

2) 在旧 MacBook 上转储数据

$ mysqldump -uroot --flush-privileges --routines --all-databases > complete_dump.sql

3) 在新 MacBook 上还原数据

$ mysql -p -uroot < complete_dump.sql

- PaulMest

Answer 5

您可能想查看mysqlpump，它可以转储所有数据库，并可以将用户导出为CREATE USER和GRANT语句，而不是依赖于mysql系统数据库+mysql_upgrade。

mysqlpump # Dumps (almost) all databases (see [2])
mysqlpump --exclude-databases=% --users # Dumps all user accounts

从文档中不清楚是否可以执行 mysqlpump --users 命令来获取所有数据库和用户。

某些系统表默认会被省略，请参见限制。

- Jxtps

---

这是唯一安全的答案。这是正确的答案。 - Christopher McGowan

在mysql 8.1中：警告：mysqlpump已被弃用，并将在将来的版本中移除。请改用mysqldump。 我在mysqldump选项中找不到类似的命令，你有什么建议我们现在该如何继续？ - undefined

Answer 6

mysqldump版本10.19包含选项--system=users，它将CREATE USER与GRANT命令添加到转储中。

mysqldump -h host -u root -p \
    --add-drop-database --routines -B database_name \
    --system=users > backup.sql

来源：https://mariadb.com/kb/en/mysqldump/

- Boba Fit

---

什么适用于旧版本？ - Gábor

@Gábor，你能详细说明一下你的情况吗？我认为你可以安装新的mariadb-dump并保留旧的数据库。 - Boba Fit

在没有--system选项的mysql 5上，我该如何转储用户？ - Gábor

除非我漏掉了什么，否则这只适用于MariaDB的mysqldump版本，而不是MySQL的。 - bfontaine

我查看了文档，似乎这是一个Mariadb函数。你是正确的。 - Boba Fit

Answer 7

连接到您的数据库服务器并执行：

select concat('show grants for ','\'',user,'\'@\'',host,'\'') from mysql.user;

你会得到类似这样的东西：

+--------------------------------------------------------+
| concat('show grants for ','\'',user,'\'@\'',host,'\'') |
+--------------------------------------------------------+
| show grants for 'mariadb.sys'@'localhost'              |
| show grants for 'mysql'@'localhost'                    |
| show grants for 'root'@'localhost'                     |
+--------------------------------------------------------+

将输出捕获到某个临时文件中。

然后循环遍历该临时文件中的每一行，将其发送到您的mysql服务器，并捕获输出。

输出将是您可以用来在另一个服务器上重建用户的内容：

GRANT ALL PRIVILEGES ON *.* TO `root`@`localhost` IDENTIFIED VIA mysql_native_password USING 'invalid' OR unix_socket WITH GRANT OPTION
GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION    

这是我目前正在使用的脚本：

mysql -u${BACKUP_DB_USER} -p${BACKUP_DB_PASSWORD} -e"select concat('show grants for ','\'',user,'\'@\'',host,'\'') from mysql.user" > user_list_with_header.txt
sed '1d' user_list_with_header.txt > ./user.txt
while read user; do  mysql -u${BACKUP_DB_USER} -p${BACKUP_DB_PASSWORD} -e"$user" > user_grant.txt; echo "-- ${user}" >> user_privileges.txt; sed '1d' user_grant.txt >> user_privileges.txt; done < user.txt
echo "flush privileges" >> user_privileges.txt;
awk '{print $0";"}'  user_privileges.txt > all_user_privileges_final.sql
rm user.txt user_list_with_header.txt user_grant.txt user_privileges.txt

您可以在文件all_user_privileges_final.sql中找到所有授权语句。

当然，您可以限制初始查询，仅列出您想要的用户。

- vitro

Answer 8

如果 mysql client is for MariaDB，那么你只能使用选项--system=users从mysql/MariaDB中获取用户dump。

例如：

1. 检查 mysql --version。如果输出类似于 mysql Ver 15.1 Distrib 10.6.8-MariaDB 那么就是

2. mysqldump -h <hostname> -u <usename> -p --system=users > mysqldb_users.sql

- HIRA K