备份MySQL用户

Question

如何备份MySQL用户及其权限？

像mysqldump这样的工具有吗？

我正在寻找类似于：

mysqldump -d -u root -p MyTable > Schema.sql

- Cherian

Answer 1

mysql -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | \
while read uh; do mysql -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql

- spirit

Answer 2

你可以使用以下命令备份MySQL数据库：

mysqldump -u root -p mysql > mysql.sql

通过执行以下命令来恢复MySQL数据库：

 mysql -uroot -p mysql < mysql.sql

在恢复转储后不要忘记执行

FLUSH PRIVILEGES

的操作。

希望能够帮到你...

- jsist

---

谢谢！在执行了FLUSH PRIVILEGES命令之后，一切都开始正常工作了。 - Dzamir

这是更好的答案，因为它包括刷新权限命令。另一种选择是只重启服务器进程而不是刷新权限。 - akahunahi

只有在恢复到相同的MySQL版本时才能保证正常工作，'mysql'数据库可能因版本不同而不同，并且包含的内容不仅仅是用户/权限。如果您的意图是将用户和权限迁移到另一个服务器，则应采用上面@spirit提供的方法。 - kostas

Answer 3

在我使用MySQL的经验中，我没有看到可以通过命令行备份用户及其权限信息的功能。

但是，我可以通过备份MySQL来备份这些关键数据。

mysqldump -u root -p mysql > mysql.sql

- taurean

---

这应该如何恢复？只需在information_schema上执行生成的文件吗？ - Ahmad Hajjar

Answer 4

Percona有一个非常好的工具。 pt-show-grants会导出用户及其权限，以便您可以轻松地重新加载它们。

https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html

- Josh Wilson

---

这个很好地解决了问题，并与其他解决方案不同，它可以转储散列密码。使用“--ignore”来跳过某些用户的授权，或者使用“--only”仅包括某些用户的授权。选项语法与“mysqldump”不同之处在于“--ask-pass”是自己的选项。 - Alastair Irvine

Answer 5

用户和权限存储在名为“mysql”的数据库中。您可以使用mysqldump备份名为“mysql”的数据库中的表格。

- Zoredache

---

你能添加如何恢复它们吗？ - Rafael Herscovici

Answer 6

好的实践是使用脚本每天备份MySQL用户及其权限。看一个例子：

#!/bin/sh

HOSTNAME="localhost"

mysql -h $HOSTNAME -B -N -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'debian-sys-maint' AND user != 'root' AND user != ''" mysql > mysql_all_users_$HOSTNAME.txt

while read line; do mysql -h $HOSTNAME -B -N -e "SHOW GRANTS FOR $line"; done < mysql_all_users_$HOSTNAME.txt > mysql_all_users_$HOSTNAME.sql

sed -i.bak 's/$/;/' mysql_all_users_$HOSTNAME.sql

rm mysql_all_users_$HOSTNAME.txt
rm mysql_all_users_$HOSTNAME.sql.bak

这个脚本的结果将是一个包含用户和权限的mysqldump文件。

附注：如果你的MySQL需要密码，请在mysql -h $HOSTNAME后面的两个位置添加-p或-u username -p。

- Oleh Vasylyev

Answer 7

对于 MySQL 命令行用户来说，这可能非常明显，但是对于 @spirit 上面的答案，需要在两个 mysql 命令后添加 -u root -ppassword。

mysql -u root -ppassword -BNe "select concat(''',user,''@'',host,''') from mysql.user where user != 'root'" | while read uh; do mysql -u root -ppassword -BNe "show grants for $uh" | sed 's/$/;/; s/\\/\/g'; done > grants.sql;

- stee

Answer 8

上述的脚本给出了一般的想法，但它们是低效的。它们需要执行 mysql n+1 次。只需要两个调用就可以完成。

mysql ${logininfo} -B -N -e "SELECT CONCAT('\'',user,'\'@\'',host,'\'') from user where user != 'root'" mysql | \
while read uh
do
   echo "SHOW GRANTS FOR ${uh};"
done | mysql ${logininfo} -B -N | sed -e 's/$/;/' > ${outfile}

如果除了root之外还有其他用户您不想备份使用，可以在第一个mysql调用的where子句中使用user != 'whatever'来指定。

- cdbunch