MySQL 8 剪切或拷贝用户创建语句

有时，查看用户创建语句并将其复制到另一个数据库会很方便。

比如在 MySQL 7 中：

mysql> show create user mytest@'%'\G
*************************** 1. row ***************************
CREATE USER for mytest@%: CREATE USER 'mytest'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*DCBCF2313F708DB5A8A1DE2hg4D12AC055AD821A' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
1 row in set (0.00 sec)

 

不过，由于 MySQL 8.0 默认使用了新的验证方法（cache_sha2_password），这可能会成为一场噩梦，因为输出 binary 格式的数据时，根据使用的终端和字体不同，某些字节可能会被隐藏或解码。

让我们来看看：

>show create user myadmin@'%'\G
*************************** 1. row ***************************
CREATE USER for myadmin@%: CREATE USER `myadmin`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$<^U\Z@Tfg\n\r^;;cZmLCjbk.f2FDkyHuUeaxQmhTZgtR0EdS4.fqRK6iUUl2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT
1 row in set (0.01 sec)

把该语句剪切到其它实例执行，就会报错：

>CREATE USER `myadmin`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$<^U\Z@Tfg\n\r^;;cZmLCjbk.f2FDkyHuUeaxQmhTZgtR0EdS4.fqRK6iUUl2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;
ERROR 1827 (HY000): The password hash doesn't have the expected format.

要剪切和粘贴验证字符串而不会出现任何问题，解决办法是将其改为二进制表示（十六进制），如下所示：

>select user,host,authentication_string,convert(authentication_string using binary) authentication_string_bin from mysql.user where user='myadmin'\G
*************************** 1. row ***************************
                     user: myadmin
                     host: %
    authentication_string: $A$005$<^U@Tfg
^;;cZmLCjbk.f2FDkyHuUeaxQmhTZgtR0EdS4.fqRK6iUUl2
authentication_string_bin: 0x244124303035243C17175E551A405466670A0D105E3B3B1E05635A6D4C436A626B2E663246446B79487555656178516D68545A67745230456453342E6671524B366955556C32
1 row in set (0.00 sec)

然后把这里查出的值替换到上面的create user 语句就可以执行了。

CREATE USER `myadmin`@`%` IDENTIFIED WITH 'caching_sha2_password' AS '0x244124303035243C17175E551A405466670A0D105E3B3B1E05635A6D4C436A626B2E663246446B79487555656178516D68545A66645230456458842E6671524B366952256C32' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT;

还有一个更简单的方法。mysql 8 提供了一个选项 print_identified_with_as_hex

>set print_identified_with_as_hex=1;
Query OK, 0 rows affected (0.00 sec)

>show create user myadmin@'%';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER for myadmin@%                                                                                                                                                                                                                                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| CREATE USER `myadmin`@`%` IDENTIFIED WITH 'caching_sha2_password' AS 0x244124303035243C17175E551A405466670A0D105E3B3B1E05635A6D4C436A626B2E663246446B79487555656178516D68545A66645230456458842E6671524B366952256C32 REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

打开该选项后，就可以直接执行查询出来的 create user 语句了。

除了上面的方法，也可以安装 mysql shell 插件后来实现。插件的地址：https://github.com/lefred/mysqlshell-plugins