题 如何从MySQL导出权限然后导入到新服务器?


我知道如何使用mysqldump导出/导入数据库,这很好,但我如何获得新服务器的权限。

对于额外的点,新的已经存在几个现有的数据库,如何导入旧的服务器权限,而不会破坏现有的数据库。

旧服务器:5.0.67-社区

新服务器:5.0.51a-24 + lenny1

编辑:我从旧服务器转储了数据库'mysql',现在想知道在新服务器上合并'mysql'数据库的正确方法。

我尝试使用phpMyAdmin直接'导入',最后出现了关于重复的错误(我已经手动迁移了一个)。

任何人都有一种优雅的方式来合并两个'mysql'数据库?


80
2018-05-16 06:08




1.您是否要求使用PHPMyAdmin?如果是的话,我会为您编写一些PHPMyAdmin特定说明。 2.如果您尝试“从mysql.user限制1中选择*”,则从PHPMyAdmin开始;你得到结果或错误吗? - Bruno Bronosky
正如我在下面提到的,我认为Richard的mygrants脚本是获得授权信息的好方法。但是,您也可以尝试编辑转储文件,以便为已存在的用户将INSERT注释到用户表。然后将复制从旧服务器恢复的dbs的权限。如果您已为已恢复到新框的某些dBS手动分配了权限,请在权限文件中查找这些表名并对其进行注释。之后不要忘记flush_privileges。很好的描述了mysql db: grahamwideman.com/gw/tech/mysql/perms/index.htm - nedm
这个链接的好消息,谢谢。书签。 - Bruno Bronosky


答案:


不要乱用mysql db。除了用户表之外,还有很多其他内容。你最好的选择是“显示资助 FOR“command。我的.bashrc中有很多CLI维护别名和函数(实际上是我在.bashrc中提供的.bash_aliases)。这个函数:

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

第一个mysql命令使用SQL生成有效的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进行修改。


165
2018-05-27 15:51



这实际上是一个很好的bash辅助函数。从中获取输出并能够在新服务器上运行,并且可以正确且准确地输入权限。 - Jeremy Bouse
我喜欢你的功能,今天它为我节省了大量的工作。谢谢你,谢谢你,谢谢你... - Fabio
这很棒,但它有一个很大的缺陷。在数据库名称的下划线中添加了一个额外的“\”,因此如果您拥有一个名为foo_bar的数据库具有特定权限的用户,它将被渲染为foo \ _bar而不是foo_bar,因此将无法正确导入。至少,如果将脚本的输出保存到SQL文件中,然后将其导入新服务器,则会发生这种情况。我没有尝试过一次直接输出和导入管道。 - matteo
小心这个命令。如果你有 user 使用主机表示用户 %,但后来 db 表你有条目在哪里 host 是一个显式值,那些条目 db使用此方法不检索表。 - Mitar
@matteo添加 -r 到函数中的第二个mysql命令,并且不会输出双转义 mysql。例如: mysql -r $@ - lifo


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

方法#1

您可以使用 PT-显示赠款 来自Percona Toolkit

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

试试看 !!!


40
2018-06-18 18:24



pt-show-grants正是你想要的,它很棒。 - Glenn Plas
Percona只是纯粹的超棒。 - sjas
但答案#2同样好,无需额外的软件即可使用! - sjas


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 .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

14
2017-09-02 20:27



感谢这种“毫不掩饰”的改进。 :) - ThorstenS
如果你看看我的例子 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


或者,使用percona-toolkit(以前的maatkit)并使用 pt-show-grants (要么 mk-show-grants) 为了这个目的。无需繁琐的脚本和/或存储过程。


6
2018-05-01 12:08





你可以mysqldump'mysql'数据库并导入到新的数据库;需要flush_privileges或restart,你肯定想先备份现有的mysq db。

要避免删除现有权限,请确保在权限表(db,columns_priv,host,func等)中追加而不是替换行。


5
2018-05-16 06:24



谢谢@nedm。看起来像恼人的cPanel服务器我试图让dbs关闭不显示db'mysql'。否则我会测试并确认你的答案。一旦我弄明白,我会回来查看。谢谢。 - Gareth
这是不幸但可以理解的,您可能无法访问任何数据库,而是访问共享数据库上您的用户直接拥有的数据库。 - Dave Cheney
哎呀,是的,如果没有访问'mysql',将很难做与用户或权限相关的任何事情。你有命令行访问权限吗?你可以从终端或命令行运行mysqldump(不是从mysql shell)? mysqldump -u username -ppassword mysql> mysqldump.sql - nedm
如果我以'root'身份登录,则可以从Cpanel WHM访问db'mysql'。从那里我可以访问一个版本的phpmyadmin,它有'mysql'数据库包含权限 - Gareth
合并到现有的mysql架构中,通过mysqldump从mysql架构中提取的数据几乎肯定是有问题的。您正在使用强制关系等操纵复杂模式。事实上,这种模式非常复杂,它们创建了专用的SQL语法(GRANT,REVOKE,DROP USER等)来处理它。但是,您的提取仅包含INSERT语句。我这里的人物已经不多了。需要我继续? - Bruno Bronosky


您也可以将其作为存储过程来执行:

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命令管道输出以获取权限的备份。


4
2018-05-09 22:05





虽然看起来@Richard Bronosky的答案是正确的,但在尝试将一组数据库从一台服务器迁移到另一台服务器后,我遇到了这个问题,解决方案更加简单:

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

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

此时,如果我以登录身份登录,我的所有数据都可见 rootmysql.user table有我所期待的一切,但我无法像其他任何用户一样登录并发现这个问题,假设我必须重新发布 GRANT 声明。

然而,事实证明,只需要重新启动mysql服务器以获得更新的权限 mysql.* 表格生效:

server2$ sudo restart mysql

希望这有助于其他人实现应该是一项简单的任务!


3
2017-09-03 01:16



哦,我的情况与OP略有不同,因为我没有尝试将转储合并到具有现有数据库/用户的服务器中。 - Tom
您实际上不必重新启动MySQLd来“更新权限”。可以使用MySQL命令'flush privileges;'来完成 - CloudWeavers
这种方法的问题是只有当源版本和目标MySQL版本相同时它才有效。否则,您可能会遇到问题,因为源mysql.user表具有与目标mysql.user表不同的列。 - Mitar


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";
    }
}

3
2018-05-03 18:11





使用以下代码创建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上运行它,如下所示: 系统会要求您输入两次root密码,然后屏幕上会显示GRANTS SQL。****


2
2017-09-06 23:03





One-liner与令人敬畏的几乎完全相同 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 shell中执行,假设你有一个工作 .my.cnf 你的密码在哪里 mysql root 用户可以阅读。


0
2018-04-13 18:33



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