突破阿里限制实现"RDS For MySQL 5.7到自建MySQL主从"

摘要: 阿里云的RDS For MySQL 5.7 到线下IDC机房的主从搭建相信看官方文档就可以完成,但是搭建之后却发现无法在本地进行认证权限的管理,这是为何?而阿里官方推荐使用DTS同步工具去实现,这个费用着实不少,又是否能够破解呢?本文详解如何暴力破解user表实现从库用户权限更改功能

故事背景

数据库明细

说在前面:

  • 云上数据库: RDS For MySQL 5.7.20 普通用户权限
  • IDC数据库: MySQL 5.7.20

故事情节

现需要搭建RDS For MySQL 到线下IDC机房的主从,问题如下:

同步系统表失败

故障原因

  • RDS For MySQL 5.7.20 到自建MySQL 5.7.20主从同步异常的原因为RDS与MySQL官方版本使用的系统库不同
  • 自建MySQL 5.7.20 无法同步主库RDS的系统表
  • 自建MySQL 5.7.20 恢复RDS的全备份数据后无法执行授权语句,报错如下:
ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.
  • 自建MySQL 5.7.20 恢复RDS的全备份数据后无法对系统表执行更新操作,报错如下:
ERROR 1064 (42000): Unknown trigger has an error in its body: 'Unknown system variable 'maintain_user_list''

解决方法

  • 跳过系统表的同步

从库添加认证授权失败

RDS For MySQL 5.7.20 到自建MySQL 5.7.20 搭建主从同步架构:

  • 配置从库不同步RDS主库的系统表
# skip rep
replicate_wild_ignore_table=mysql.%
replicate_wild_ignore_table=sys.%
replicate_wild_ignore_table=information_schema.%
  • 非系统库数据同步正常
  • 从库无法执行grant命令,即无法添加授权信息
  • 从库无法对系统表mysql.user表执行insert操作
  • 从库无法对系统表mysql.user表执行updat操作

报错如下:

ERROR 1064 (42000): Unknown trigger has an error in its body: 'Unknown system variable 'maintain_user_list''

失败原因

阿里工单答复:RDS 目前已经不支持 MYISAM 引擎创建了。所以如果是通过自建的replication 同步 就会有这个问题的,RDS 统一INNOB 引擎。
如果需求是从RDS 到自建数据库的同步关系,建议您使用DTS 做业务数据的同步。

探索解决方法

从库尝试使用MySQL8.0.11

RDS For MySQL 5.7的备份文件 到线下自建MYSQL 8.0.11 无法恢复数据。因此该方法不可行。

尝试临时关闭GTID模式
  1. 临时停止slave同步
  2. 修改配置关闭gtid模式
  3. 重启服务

对系统表mysql.user测试明细:

No. 测试项目 结果
1 是否能够执行grant命令 ×
2 是否能够执行update命令 ×
3 是否能够执行insert命令 ×

该方法同样无法解决。

解决方案

思路

# 权限相关的一些表:
SCHEMA_PRIVILEGES:提供了数据库的相关权限,这个表是内存表是从mysql.db中拉去出来的。
TABLE_PRIVILEGES:提供的是表权限相关信息,信息是从 mysql.tables_priv 表中加载的
COLUMN_PRIVILEGES :这个表可以清楚就能看到表授权的用户的对象,那张表那个库以及授予的是什么权限,如果授权的时候加上with grant option的话,我们可以看得到PRIVILEGE_TYPE这个值必须是YES。
USER_PRIVILEGES:提供的是表权限相关信息,信息是从 mysql.user 表中加载的
通过表我们可以很清晰看得到MySQL授权的层次,SCHEMA,TABLE,COLUMN级别,当然这些都是基于用户来授予的

从数据库用户权限管理的原理可以了解到管理用户的是user表,如果要更细致的权限还需要db表和tables_priv表。(本文只从user表着手)

步骤概览

  1. 创建新的user_1表
  2. 对user_1表添加新的用户
  3. 停服务
  4. 将user_1替换user表
  5. 启动该服务
  6. 可以通过update、insert、delete操作user表来添加权限(grant无法操作)

测试环境

root@MySQL-01 10:35:  [mysql]> create table user_1 like user;
Query OK, 0 rows affected (0.01 sec)

root@MySQL-01 10:36: [mysql]> select * from user_1;
Empty set (0.00 sec)

root@MySQL-01 10:36: [mysql]> show table status like user;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'user' at line 1
root@MySQL-01 10:36: [mysql]> show table status like 'user_1';
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
| user_1 | MyISAM | 10 | Dynamic | 0 | 0 | 0 | 281474976710655 | 1024 | 0 | NULL | 2019-01-04 10:36:00 | 2019-01-04 10:36:00 | NULL | utf8_bin | NULL | | Users and global privileges |
+--------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+-----------------------------+
1 row in set (0.00 sec)

root@MySQL-01 10:36: [mysql]> show table status like 'user';
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------+----------+----------------+-----------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------+----------+----------------+-----------------------------+
| user | MyISAM | 10 | Dynamic | 3 | 53 | 160 | 281474976710655 | 2048 | 0 | NULL | 2015-05-22 15:24:42 | 2019-01-04 09:27:49 | 2015-05-22 15:33:22 | utf8_bin | NULL | | Users and global privileges |
+------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------+----------+----------------+-----------------------------+
1 row in set (0.00 sec)

root@MySQL-01 10:36: [mysql]> insert into user_1 select * from user;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

root@MySQL-01 10:36: [mysql]> select * from user_1;
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | References_priv | Index_priv | Alter_priv | Show_db_priv | Super_priv | Create_tmp_table_priv | Lock_tables_priv | Execute_priv | Repl_slave_priv | Repl_client_priv | Create_view_priv | Show_view_priv | Create_routine_priv | Alter_routine_priv | Create_user_priv | Event_priv | Trigger_priv | Create_tablespace_priv | ssl_type | ssl_cipher | x509_issuer | x509_subject | max_questions | max_updates | max_connections | max_user_connections | plugin | authentication_string |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
| localhost | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| 127.0.0.1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
| ::1 | root | | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | | | | | 0 | 0 | 0 | 0 | | |
+-----------+------+----------+-------------+-------------+-------------+-------------+-------------+-----------+-------------+---------------+--------------+-----------+------------+-----------------+------------+------------+--------------+------------+-----------------------+------------------+--------------+-----------------+------------------+------------------+----------------+---------------------+--------------------+------------------+------------+--------------+------------------------+----------+------------+-------------+--------------+---------------+-------------+-----------------+----------------------+--------+-----------------------+
3 rows in set (0.00 sec)

root@MySQL-01 10:36: [mysql]> select user,host,authentication_string from user_1;
+------+-----------+-----------------------+
| user | host | authentication_string |
+------+-----------+-----------------------+
| root | localhost | |
| root | 127.0.0.1 | |
| root | ::1 | |
+------+-----------+-----------------------+
3 rows in set (0.00 sec)

root@MySQL-01 10:36: [mysql]> update user_1 set authentication_string=password('(Uploo00king)') where user='root';
Query OK, 3 rows affected, 1 warning (0.01 sec)
Rows matched: 3 Changed: 3 Warnings: 1

root@MySQL-01 10:37: [mysql]> select user,host,authentication_string from user_1;
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
| root | 127.0.0.1 | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
| root | ::1 | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

root@MySQL-01 10:37: [mysql]> exit
Bye
[root@sh_02 data]# /etc/init.d/mysqld stop
Shutting down MySQL.. [ OK ]
[root@sh_02 mysql]# ll user_1*
-rw-r-----. 1 mysql mysql 10630 Jan 4 10:36 user_1.frm
-rw-r-----. 1 mysql mysql 328 Jan 4 10:37 user_1.MYD
-rw-r-----. 1 mysql mysql 2048 Jan 4 10:37 user_1.MYI
[root@sh_02 mysql]# mv user_1.frm user.frm
[root@sh_02 mysql]# mv user_1.MYD user.MYD
[root@sh_02 mysql]# mv user_1.MYI user.MYI
[root@sh_02 mysql]# ll user*
-rw-r-----. 1 mysql mysql 10630 Jan 4 10:36 user.frm
-rw-r-----. 1 mysql mysql 328 Jan 4 10:37 user.MYD
-rw-r-----. 1 mysql mysql 2048 Jan 4 10:37 user.MYI
-rw-rw----. 1 mysql mysql 3989 Aug 14 09:14 user_view.frm
[root@sh_02 mysql]# /etc/init.d/mysqld start
Starting MySQL. [ OK ]


[root@sh_02 mysql]# mysql -uroot -p'(Uploo00king)'
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@MySQL-01 10:39: [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

root@MySQL-01 10:39: [(none)]> use mysql
Database changed
root@MySQL-01 10:39: [mysql]> select user,host from mysql.user;
+------+-----------+
| user | host |
+------+-----------+
| root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+------+-----------+
3 rows in set (0.00 sec)

root@MySQL-01 10:39: [mysql]> grant all on *.* to booboo@'%' identified by '(Uploo00king)';
ERROR 1785 (HY000): Statement violates GTID consistency: Updates to non-transactional tables can only be done in either autocommitted statements or single-statement transactions, and never in the same statement as updates to transactional tables.

root@MySQL-01 11:39: [mysql]> update mysql.user_1 set user='aliyun_root' where host='127.0.0.1';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

root@MySQL-01 11:40: [mysql]> select user,host from mysql.user_1;
+-------------+-----------+
| user | host |
+-------------+-----------+
| aliyun_root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+-------------+-----------+
3 rows in set (0.00 sec)

aliyun_root@MySQL-01 11:51: [mysql]> insert into mysql.user values ('%','jowing', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, '', password('Joowing@2017'));
Query OK, 1 row affected, 1 warning (0.01 sec)

aliyun_root@MySQL-01 11:51: [mysql]> select user,host from mysql.user;
+-------------+-----------+
| user | host |
+-------------+-----------+
| jowing | % |
| aliyun_root | 127.0.0.1 |
| root | ::1 |
| root | localhost |
+-------------+-----------+
4 rows in set (0.00 sec)

生产环境

用户权限

目标:IDC机房从库支持通过insert、update、delete命令来修改用户权限,权限比较简单,分为只读和写。

需要设置以下权限:权限作用于所有的库多有的表

用户名 密码 权限
root 123 读写
joowingbuz 123 只读
ottersync 123 只读
syncdw 123 只读
datasis 123 只读
joowingv 123 只读
datadev 123 只读
readonly 123 只读

步骤概览

# 1. 登陆数据库后操作如下:
use mysql;
create table user_1 like user;
insert into user_1 select * from user;
delete from mysql.user_1 where user='root';
insert into mysql.user_1 values ('%','root', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, '', password('123'));
insert into mysql.user_1 values ('%','joowingbuz', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
insert into mysql.user_1 values ('%','ottersync', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
insert into mysql.user_1 values ('%','syncdw', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
insert into mysql.user_1 values ('%','datasis', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
insert into mysql.user_1 values ('%','joowingv', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
insert into mysql.user_1 values ('%','datadev', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
insert into mysql.user_1 values ('%','readonly', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
# 2. 退出数据库并停止服务
/data/mysql/support-files/mysql.server stop
# 3. 将user_1表的物理文件覆盖user表
cd /data/xtrabackup_data/mysql/
mv user.frm user.ibd user.MYD user.MYI user.TRG /tmp
mv user_1.frm user.frm
mv user_1.MYI user.MYI
mv user_1.MYD user.MYD
# 4. 启动数据库
/data/mysql/support-files/mysql.server start

操作明细

root@MySQL-01 15:42:  [(none)]> use mysql
Database changed
root@MySQL-01 15:42: [mysql]> create table user_1 like user;
Query OK, 0 rows affected (0.01 sec)

root@MySQL-01 15:42: [mysql]> insert into user_1 select * from user;
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0

root@MySQL-01 15:42: [mysql]> select user,host,authentication_string from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
| root | 127.0.0.1 | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
| root | ::1 | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

root@MySQL-01 15:42: [mysql]> select user,host,authentication_string from mysql.user_1;
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
| root | 127.0.0.1 | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
| root | ::1 | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

root@MySQL-01 15:42: [mysql]> desc mysql.user_1;
+------------------------+-----------------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+---------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(16) | NO | PRI | | |
| Password | char(41) | NO | | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | YES | | | |
| authentication_string | text | YES | | NULL | |
+------------------------+-----------------------------------+------+-----+---------+-------+
42 rows in set (0.00 sec)

root@MySQL-01 15:47: [mysql]> delete from mysql.user_1 where user='root';
Query OK, 3 rows affected (0.00 sec)

root@MySQL-01 15:51: [mysql]> insert into mysql.user_1 values ('%','root', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, '', password('123'));
Query OK, 1 row affected, 1 warning (0.00 sec)

root@MySQL-01 15:52: [mysql]> insert into mysql.user_1 values ('%','joowingbuz', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
Query OK, 1 row affected, 1 warning (0.00 sec)

root@MySQL-01 15:52: [mysql]> insert into mysql.user_1 values ('%','ottersync', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
Query OK, 1 row affected, 1 warning (0.00 sec)

root@MySQL-01 15:52: [mysql]> insert into mysql.user_1 values ('%','syncdw', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));

root@MySQL-01 15:52: [mysql]> insert into mysql.user_1 values ('%','datasis', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
Query OK, 1 row affected, 1 warning (0.00 sec)

root@MySQL-01 15:52: [mysql]> insert into mysql.user_1 values ('%','joowingv', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
Query OK, 1 row affected, 1 warning (0.01 sec)

root@MySQL-01 15:52: [mysql]> insert into mysql.user_1 values ('%','datadev', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
Query OK, 1 row affected, 1 warning (0.00 sec)

root@MySQL-01 15:52: [mysql]> insert into mysql.user_1 values ('%','readonly', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('123'));
Query OK, 1 row affected, 1 warning (0.00 sec)

root@MySQL-01 15:52: [mysql]> select user,host,authentication_string from mysql.user;
+------+-----------+-------------------------------------------+
| user | host | authentication_string |
+------+-----------+-------------------------------------------+
| root | localhost | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
| root | 127.0.0.1 | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
| root | ::1 | *D4DF57DFB7019B3D8C4294CC413AF1D650A275E4 |
+------+-----------+-------------------------------------------+
3 rows in set (0.00 sec)

root@MySQL-01 15:52: [mysql]> select user,host,authentication_string from mysql.user_1;
+------------+------+-------------------------------------------+
| user | host | authentication_string |
+------------+------+-------------------------------------------+
| ottersync | % | *9443FA914A2D69FE8832F8294E7422CC1B02A492 |
| joowingbuz | % | *DFFDA1CA6135E355EF468AB13A465BB5D4FE2B11 |
| root | % | *89BE852E4EECFD217F0C5463FB30AD25BD0751E0 |
| syncdw | % | *3DD7B4B4F6EE968FF3452B607BDEE6294B6A425A |
| datasis | % | *011D511C71990F832C531A0F9CFB34CF7BB4E485 |
| joowingv | % | *56B364074270DF7F6D670A6B4F5A4AD13322397A |
| datadev | % | *D3D73E0F6BFC3159B024EF31484B6F9CC2963C5B |
| readonly | % | *E2BA196C0C7F409990FDB3FAB5F9C7CE95F7C449 |
+------------+------+-------------------------------------------+
8 rows in set (0.00 sec)


root@joowing-server-06:~# /data/mysql/support-files/mysql.server stop
Shutting down MySQL
...... *


root@joowing-server-06:~# cd /data/xtrabackup_data/
root@joowing-server-06:/data/xtrabackup_data# cd mysql
root@joowing-server-06:/data/xtrabackup_data/mysql# ll user*
-rw-r----- 1 mysql mysql 10630 8月 14 15:42 user_1.frm
-rw-r----- 1 mysql mysql 744 8月 14 15:52 user_1.MYD
-rw-r----- 1 mysql mysql 2048 8月 14 15:53 user_1.MYI
-rw-r----- 1 mysql mysql 10630 8月 9 20:14 user.frm
-rw-r----- 1 mysql mysql 98304 8月 9 20:14 user.ibd
-rw-r--r-- 1 mysql mysql 328 8月 9 20:14 user.MYD
-rw-r--r-- 1 mysql mysql 2048 8月 9 20:14 user.MYI
-rw-r----- 1 mysql mysql 3569 8月 9 20:14 user.TRG
-rw-r----- 1 mysql mysql 3982 8月 9 20:14 user_view.frm
root@joowing-server-06:/data/xtrabackup_data/mysql# mv user.frm user.ibd user.MYD user.MYI user.TRG /data
root@joowing-server-06:/data/xtrabackup_data/mysql# ll user*
-rw-r----- 1 mysql mysql 10630 8月 14 15:42 user_1.frm
-rw-r----- 1 mysql mysql 744 8月 14 15:52 user_1.MYD
-rw-r----- 1 mysql mysql 2048 8月 14 15:53 user_1.MYI
-rw-r----- 1 mysql mysql 3982 8月 9 20:14 user_view.frm
root@joowing-server-06:/data/xtrabackup_data/mysql# mv user_1.frm user.frm
root@joowing-server-06:/data/xtrabackup_data/mysql# mv user_1.MYI user.MYI
root@joowing-server-06:/data/xtrabackup_data/mysql# mv user_1.MYD user.MYD
root@joowing-server-06:/data/xtrabackup_data/mysql# ll user*
-rw-r----- 1 mysql mysql 10630 8月 14 15:42 user.frm
-rw-r----- 1 mysql mysql 744 8月 14 15:52 user.MYD
-rw-r----- 1 mysql mysql 2048 8月 14 15:53 user.MYI
-rw-r----- 1 mysql mysql 3982 8月 9 20:14 user_view.frm
root@joowing-server-06:/data/xtrabackup_data/mysql# /data/mysql/support-files/mysql.server start
Starting MySQL
...... *

root@joowing-server-06:/data/xtrabackup_data/mysql# mysql -uroot -p'123'
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.20-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

root@MySQL-01 15:55: [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: rm-uf6f05k2rg95s23bp.mysql.rds.aliyuncs.com
Master_User: idc_slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001641
Read_Master_Log_Pos: 447207506
Relay_Log_File: joowing-server-06-relay-bin.000225
Relay_Log_Pos: 35529076
Relay_Master_Log_File: mysql-bin.001641
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: mysql.%,sys.%,information_schema.%
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 447207506
Relay_Log_Space: 35529295
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1095052097
Master_UUID: b3e1de69-5daa-11e8-bed2-7cd30ab8a9fc
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: b3e1de69-5daa-11e8-bed2-7cd30ab8a9fc:97478646-97483368
Executed_Gtid_Set: b3e1de69-5daa-11e8-bed2-7cd30ab8a9fc:1-97483368,
c39ecf19-5daa-11e8-aa9c-7cd30ac4764a:1-178658794,
c69289d7-9bc9-11e8-b922-44a842431b62:1-12
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

ERROR:
No query specified


# 验证只读账号
mysql -uroot -p'123' -e "create database dbzyadmin;"
mysql -ujoowingbuz -p'123' -e "create database dbzyadmin;"
mysql -uottersync -p'123' -e "create database dbzyadmin;"
mysql -usyncdw -p'123' -e "create database dbzyadmin;"
mysql -udatasis -p'123' -e "create database dbzyadmin;"
mysql -ujoowingv -p'123' -e "create database dbzyadmin;"
mysql -udatadev -p'123' -e "create database dbzyadmin;"
mysql -ureadonly -p'123' -e "create database dbzyadmin;"

# 只读账号无法执行写操作,验证成功
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1044 (42000) at line 1: Access denied for user 'readonly'@'%' to database 'dbzyadmin'

后续用户权限变更操作指南

后续新增用户、删除用户、更改密码命令如下:

新增读写用户
insert into mysql.user_1 values ('%','【用户名】', '', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', 'Y', '', '', '', '', 0, 0, 0, 0, '', password('【密码】'));
新增只读用户
insert into mysql.user_1 values ('%','【用户名】', '', 'Y', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', 0, 0, 0, 0, '', password('【密码】'));
删除用户命令
delete from mysql.user where user='【用户名】';
修改密码
update mysql.user set authentication_string=password('【密码】') where user='【用户名】';

后记

RDS目前使用MySQL版本和官方在系统库上差异还是很大的,若需要搭建RDS到线下自建MySQL5.7的主从时,可以通过此法去实现。

本文中对user表的破解,同样适适用于mysql.dbmysql.tables_priv表,都破解则可以将权限从用户拓展到库表列。读者可自行实验测试。