如何从MySQL导出特权，然后导入到新服务器？

我知道如何使用mysqldump导出/导入数据库，这很好，但是如何将特权获取到新服务器中。

为了增加一点，新的数据库上已经有几个现有的数据库，我如何导入旧的服务器特权而又不破坏已有的两个数据库。服务器：5.0.67-community

新服务器：5.0.51a-24 + lenny1

编辑：我从旧数据库中转储了数据库“ mysql”服务器＆现在想知道与新服务器上的'mysql'db合并的正确方法。

我尝试使用phpMyAdmin直接进行'Import'并最终导致关于重复项的错误（一个我已经手动迁移过了。）

有人能很好地合并两个'mysql'数据库吗？

1.是否需要使用PHPMyAdmin？如果是这样，我将为您编写一些PHPMyAdmin特定说明。 2.如果尝试“从mysql.user限制1选择*，请从PHPMyAdmin”；您得到结果还是错误。

正如我在下面提到的，我认为Richard的mygrants脚本是获取资助信息的好方法。但是，您也可以尝试编辑转储文件，以便为已经存在的用户在用户表中注释掉INSERT。从旧服务器还原的数据库的特权随后将被复制。如果您已经为还原到新框的某些数据库手动分配了特权，请在特权文件中查找这些表名，并将它们注释掉。之后别忘了flush_privileges。有关mysql数据库的详细说明，位于：grahamwideman.com/gw/tech/mysql/perms/index.htm

#1 楼

不要搞乱mysql db。除了users表之外，还有更多的事情要做。最好的选择是“ SHOW GRANTS FOR”命令。我的.bashrc（实际上是我的.bashrc中的.bash_aliases）中有很多CLI维护别名和功能。此函数：

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/;/;s/^\(Grants for .*\)/##  ##/;/##/{x;p;x;}'
}

第一个mysql命令使用SQL生成有效的SQL，该管道通过管道传输到第二个mysql命令。然后将输出通过sed通过管道添加以添加漂亮的注释。

命令中的$ @允许您将其称为：
mygrants --host = prod-db1 --user = admin --password = secret

您可以像这样使用完整的Unix工具套件：

mygrants --host=prod-db1 --user=admin --password=secret | grep rails_admin | mysql --host=staging-db1 --user=admin --password=secret

这是正确的方法移动用户。您的MySQL ACL已使用纯SQL进行了修改。

这实际上是一个不错的bash辅助函数，效果很好。从中获取输出并能够在新服务器上运行，特权将被正确而准确地输入。

– Jeremy Bouse
09年6月11日14:12

我喜欢您的功能，今天它为我节省了很多工作。谢谢你，谢谢你，谢谢你...

–法比奥
2011-2-15在22:32

爱它。这节省了我至少一个小时的工作！

–wolfgangsz
2011年7月1日，12：50

这很好，但有一个大缺陷。在数据库名称中的下划线处会添加一个额外的“ \”，因此，例如，如果您有一个名为foo_bar的数据库具有特定特权的用户，它将被呈现为foo \ _bar而不是foo_bar，因此将无法正确导入。至少，如果将脚本的输出保存到SQL文件中，然后将其导入到新服务器中，则会发生这种情况。我还没有尝试在单一行中直接进行进出口管道。

– Matteo
13-10-15在9:41

_是不是特殊字符（通配符），所以必须转义？但是我认为它必须是\ _而不是\\ _。我在运行命令时得到\\ _。看到这个问题。

–米塔尔
2015年6月26日4:36

#2 楼

有两种方法可以从MySQL实例中提取SQL Grants

METHOD＃1

可以从Percona Toolkit中使用pt-show-grants

MYSQL_CONN="-uroot -ppassword"
pt-show-grants ${MYSQL_CONN} > MySQLUserGrants.sql

方法＃2

可以使用以下方法模拟pt-show-grants

MYSQL_CONN="-uroot -ppassword"
mysql ${MYSQL_CONN} --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql ${MYSQL_CONN} --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

两种方法都会产生纯MySQL授权的SQL转储。剩下要做的就是在新服务器上执行脚本：

mysql -uroot -p -A < MySQLUserGrants.sql

试试看！

pt-show-grants正是您想要的，它很棒。

– Glen Plas
2014年12月7日在22:14

Percona只是纯粹的令人敬畏。

– sjas
2015年10月14日13:02

但是答案2同样好，不需要其他软件就可以工作！

– sjas
2015年10月14日13:05

#3 楼

理查德·布罗诺斯基（Richard Bronosky）的回答对我非常有用。非常感谢！!!!

这里有一个很小的变化，对我很有用。这对于转移用户很有帮助，例如运行phpmyadmin的两个Ubuntu安装之间。只需为除root，phpmyadmin和debian-sys-maint之外的所有用户转储特权即可。然后，代码为

mygrants()
{
mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
) AS query FROM mysql.user WHERE user NOT IN ('root','phpmyadmin','debian-sys-maint')"  | \
mysql $@ | \
sed 's/\(GRANT .*\)/;/;s/^\(Grants for .*\)/##  ##/;/##/{x;p;x;}'
}

如果看我在grep rails_admin的示例，则可以推断出如何执行此操作而无需为每种情况创建特殊功能。使用grep的“反向匹配”选项，如下所示：mygrants --host = prod-db1 --user = admin --password = secret | grep -Ev'root | phpmyadmin | debian-sys-maint'| mysql --host = staging-db1 --user = admin --password = secret

–布鲁诺·布鲁诺斯基（Bruno Bronosky）
17年11月8日在1:25

#4 楼

或者，使用percona-toolkit（以前的maatkit）并为此使用pt-show-grants（或mk-show-grants）。无需繁琐的脚本和/或存储过程。

#5 楼

您可以mysqldump'mysql'数据库并导入到新数据库；必须使用flush_privileges或重新启动，并且您肯定要先备份现有的mysq数据库。

为避免删除现有特权，请确保在特权表中添加而不是替换行（ db，columns_priv，host，func等）。

谢谢@nedm。似乎烦人的cPanel服务器，我正在尝试关闭dbs，但未显示db'mysql'。否则，我会测试并确认您的答案。一旦弄清楚了，我会再检查一次。谢谢。

–加雷斯
09年5月16日在8:49

不幸的是，这是可以理解的，可能会阻止您访问任何数据库，但不能访问用户在共享数据库上直接拥有的数据库。

–戴夫·切尼
09年5月17日下午6:05

是的，如果不能访问“ mysql”，则将很难执行与用户或权限相关的任何操作。您有命令行访问权限吗？可以从终端或命令行运行mysqldump吗（不能从mysql shell运行）？ mysqldump -u用户名-ppassword mysql> mysqldump.sql

–nedm
09年5月18日在17:08

如果我以“ root”身份登录，则可以从Cpanel WHM访问db“ mysql”。从那里我可以访问phpmyadmin版本，该版本具有包含权限的“ mysql”数据库

–加雷斯
09年6月10日在7:53

合并到现有的mysql模式中，几乎可以肯定的是，通过mysqldump从mysql模式中提取的数据是有问题的。您正在处理具有强制关系等的复杂模式。实际上，这种模式是如此复杂，以至于他们创建了专用的SQL语法（GRANT，REVOKE，DROP USER等）来处理它。但是，您的提取仅包含INSERT语句。我的字符用完了。我需要继续吗？

–布鲁诺·布鲁诺斯基（Bruno Bronosky）
09年6月13日在3:52

#6 楼

PHP脚本怎么样？ :)

查看此脚本的源代码，您将获得列出的所有特权：

//connect
mysql_select_db("mysql", mysql_connect("localhost","root",""));

//create grants select statements
$rs = mysql_query("SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM user");

//iterate through grants
while ($row=mysql_fetch_array($rs)) {
    //run grant query
    $rs2 = mysql_query($row['query']);
    //iterate through results
    while($row2 = mysql_fetch_array($rs2)){
        //print results
        echo $row2[0] . ";\n\n";
    }
}

#7 楼

您也可以将其作为存储过程进行操作：

CREATE PROCEDURE spShowGrants()
    READS SQL DATA
    COMMENT 'Show GRANT statements for users'
BEGIN
    DECLARE v VARCHAR(64) CHARACTER SET utf8;
    DECLARE c CURSOR FOR
    SELECT DISTINCT CONCAT(
        'SHOW GRANTS FOR ', user, '@', host, ';'
    ) AS query FROM mysql.user;
    DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;  
    OPEN c;
    WHILE TRUE DO
        FETCH c INTO v;
        SET @v = v;
        PREPARE stmt FROM @v;
        EXECUTE stmt;
    END WHILE;
    CLOSE c;
END

并用

$ mysql -p -e "CALL spShowGrants" mysql

调用，然后用管道通过Richards sed命令输出以获取特权的备份。

#8 楼

尽管@Richard Bronosky的答案似乎是正确的，但在尝试将一组数据库从一台服务器迁移到另一台服务器后，我遇到了这个问题，解决方案要简单得多：

server1$ mysqldump -u root -p --all-databases > dbdump.sql

server2$ mysql -u root -p < dbdump.sql

此时，如果我以root身份登录，则mysql.user表中包含了我期望的所有内容，那么我的所有数据都可见，但是我无法以其他任何用户身份登录，发现这个问题并假设必须重新发出GRANT语句。

但是，事实证明，只需重新启动mysql服务器，即可使mysql.*表中的更新特权生效：

server2$ sudo restart mysql

希望能帮助其他人完成应该是一个简单的任务！

哦，我的情况与OP的情况略有不同，因为我没有尝试将转储合并到具有现有数据库/用户的服务器中。

–汤姆
2011年9月3日，1：17

您实际上不必重新启动MySQLd即可“更新特权”。可以使用MySQL命令“ flush privileges;”来完成。

– CloudWeavers
2012年5月3日18:16

这种方法的问题在于它仅在源MySQL版本和目标MySQL版本相同时才有效。否则，您可能会遇到问题，因为例如源mysql.user表的列与目标mysql.user表的列不同。

–米塔尔
15年6月26日在5:30

#9 楼

使用以下代码创建Shell脚本文件：

################################ ############### 3

echo "SELECT DISTINCT CONCAT (\"show grants for '\", user, \"'@'\", host, \"';\") AS query FROM mysql.user; " >   script.sql    
echo "*** You will be asked to enter the root password twice ******"    
mysql -u root -p  < script.sql > output.sql ;    
cat output.sql | grep show > output1.sql  ; rm output.sql -f ; 
mysql -u root -p  < output1.sql > output.sql ;
clear
echo "-----Exported Grants-----"    
cat  output.sql ; rm  output.sql   output1.sql -f    
echo "-------------------------"
rm  script.sql -f

＃

****，然后在这样的shell：
，系统将要求您输入两次根密码，然后在屏幕上显示GRANTS SQL。****

#10 楼

单行代码与出色的pt-show-grants几乎相同：

mysql --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql --skip-column-names -A | sed 's/$/;/g'

仅基于UNIX工具，不需要其他软件。从bash外壳中假设您有一个正常工作的.my.cnf，可以在其中读取mysql root用户的密码。

半年后回来后，我复制了@rolandomysqldba的帖子的一半，我才意识到。

– sjas
16年4月13日在18:36

编程黑洞网