MaxScale2.2 How to load MySQL users automatically

之前在[《MySQL性能管理及架构设计》请添加链接描述](https://coding.imooc.com/class/49.html)课程中为大家讲解了如何使用Maxscale来实现数据库的读写分离的方案。近期在课程交流群中有同学提出在使用Maxscale2.2版本时，无法自动加载MySQL5.7中的用户信息。 所以我就研究了一下新版本的Maxscale，发现确实如此，这是什么原因呢？由如何解决这个问题呢？

Let's start by saying why, let's take a look at the following SQL
SELECT U.user, U.host, D.db, U.select_priv, u.authentication_string
From Mysql.user as U-left JOIN
Mysql.db as D on (U.user = d.user and U.host =
D.host)
WHERE u.plugin = ' and U.user not in (' root ')
UNION
SELECT U.user, U.host, T.db, U.select_priv, u.authentication_string
From Mysql.user as U-left JOIN
Mysql.tables_priv as T on (U.user = t.user and u.host = t.host)
WHERE u.plugin = ' and U.user not in (' root ')
This is maxscale. In order to load the MySQL user, the executed SQL, everybody notice u.plugin= ' This filter condition, This plugin column is from the Mysql.user user, the way to represent the MySQL authenticated user, the default is "in the previous version of Mysql5.6, and the default value after MySQL5.6 is Mysql_native_ Password. So executing this SQL in a new version of MySQL is a query that doesn't have any data, which is why Maxscale can't load the MySQL user. However, it is interesting to know that in MARIADB plugin this column is still in the default "state, so if the end of Maxscale is connected to the MARIADB there will be no problem.
The reason is found, then how can we solve it?
Of course is to modify the Maxscale in this SQL, fortunately we can easily get Maxscale source code. We can obtain the source code of Maxscale by the following way
git clone https://github.com/mariadb-corporation/MaxScale.git
and then to
The SERVER/MODULES/AUTHENTICATOR/MYSQLAUTH/DBUSERS.C file is modified to change the SQL from line 45th to line 51 to:

Define New_load_dbusers_query "
SELECT U.user, U.host, D.db, U.select_priv, u.%s \
From Mysql.user as u left JOIN mysql.db as D \
On (U.user = d.user and u.host = d.host) WHERE u.plugin = ' Mysql_native_password '%s \
UNION \
SELECT U.user, U.host, T.db, U.select_priv, u.%s \
From Mysql.user as u left JOIN mysql.tables_priv as t \
On (U.user = t.user and u.host = t.host) WHERE u.plugin = ' Mysql_native_password '%s '
And then the revised source code to compile and install it. Finally, if you are interested in MySQL, you can focus on my previous MySQL two combat courses.
"MySQL performance management and architecture design"
"High-performance extensible MySQL Database design and Architecture Optimization e-commerce project"

MaxScale2.2 How to load MySQL users automatically