备份 MySQL 用户
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/597732/
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
Backup MySQL users
提问by Cherian
How do I backup MySQL users and their privileges?
如何备份 MySQL 用户及其权限？
Anything like mysqldump?
像mysqldump这样的东西吗？
I am looking for something like:
我正在寻找类似的东西：
mysqldump -d -u root -p MyTable > Schema.sql
回答by spirit
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
回答by jsist
You can backup mysql database using
您可以使用备份mysql数据库
mysqldump -u root -p mysql > mysql.sql
and restore mysql database by executing
并通过执行恢复mysql数据库
mysql -uroot -p mysql < mysql.sql
Dont forget to
不要忘记
FLUSH PRIVILEGES
FLUSH PRIVILEGES
after restoring dump.
恢复转储后。
Hope it helps...
希望能帮助到你...
回答by simplyharsh
So far my experience with MySQL i didn't see anything to backup user and their privileges through a command line.
到目前为止，我使用 MySQL 的经验我没有看到任何可以通过命令行备份用户及其权限的内容。
But i can backup those critical data by backing up mysql
但是我可以通过备份mysql来备份那些关键数据
mysqldump -u root -p mysql > mysql.sql
回答by Zoredache
The users and privileges are stored in the databased named 'mysql'. You can use mysqldump to backup the tables in the databased named 'mysql'.
用户和权限存储在名为“mysql”的数据库中。您可以使用 mysqldump 来备份名为“mysql”的数据库中的表。
回答by Josh Wilson
Percona has a great tool for this. pt-show-grantswill dump users and their permissions so you can easily reload them.
Percona 有一个很好的工具。pt-show-grants将转储用户及其权限，以便您可以轻松地重新加载它们。
https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html
https://www.percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html
回答by Oleh Vasylyev
Good practice is using script for daily backup MySQL users and their privileges. Take take a look on a one:
好的做法是使用脚本进行日常备份 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
Result of this script will be mysqldump file with users and privileges.
此脚本的结果将是具有用户和权限的 mysqldump 文件。
P.S. If your MySQL requires password - put -por -u username -pafter mysql -h $HOSTNAMEin two places.
PS 如果您的 MySQL 需要密码 -在两个地方放置-p或-u username -p之后mysql -h $HOSTNAME。
回答by cdbunch
The scripts given above give the general idea, but they're inefficient. They're forking/execing mysql n+1 times. It can be done in only two calls to mysql
上面给出的脚本给出了总体思路，但它们效率低下。他们分叉/执行 mysql n+1 次。只需两次调用mysql即可完成
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}
If there are users other than root that you don't want to backup use or and specify user != 'whatever' in the where clause of the first mysql call.
如果您不想备份 root 以外的用户，请使用或指定 user != 'whatever' 在第一个 mysql 调用的 where 子句中。
回答by stee
probably pretty obvious for mysql command liners but for spirit's answer above had to add -u root -ppassword after both mysql commands
对于 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;
mysql -u root -ppassword -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | 一边读，嗯；do mysql -u root -ppassword -BNe "show grants for $uh" | sed 's/$/;/; s/\\/\/g'; 完成> grants.sql;
