Re: MaxScale question (From Kakao)

[Mark Riddoch]

Hi Chan,

first of all sorry to hear that you had no success with the maxscale google group. Looking back at the archive of the group I can not see your post, I suspect somehow it got blocked before getting to the group. I will CC this response to the google group so that it appears there as well.

As for the problem you are having, I can not see an immediate problem with your setup, from the MaxScale side it all looks right. I suspect the problem is related to the way authentication works with host names.

MySQL uses a triple for the authentication that includes the source of the connection. When MaxScale is put between the client and the MySQL server that MySQL server will see all connections as if they originate from MaxScale rather than the host the client application is running on. So if you have three machines A, B and C, with the client application running on machine A, MaxScale on B and the MySQL server on C and you have a user X that you wish to authenticate. You would need to either have the host part wild carded or have entries for user X for both hosts A and B.

MaxScale will lookup X@A, as this is the user that connects to MaxScale and the MySQL server will lookup X@B as it only sees the connection from MaxScale to MySQL and not the real client application.

The other slight complication in the story is that the wildcard % does not match the localhost entry, so if you want to connect from the localhost or 127.0.0.1 you need to have an explicit X@localhost entry in the MySQL user table.

It would be interesting to see what MaxScale has in the way of database users. You can do this by using the relent interface to connect to MaxScale, with telnet localhost <port> and running the show dbusers command with the name of your service, e.g. show dbusers "RW Split Router"

I hope this helps
Mark

On 25 Jun 2014, at 11:08, 성동찬_Chan <ch...@kakao.com> wrote:

> Hi~!
>
> My name is Chan working as a DBA in Kakao.
> Now, I’m doing functional test about MaxScale, and I have some questions.
> (In fact I asked this question on google group)
>
> I asked to KS Kim in OSS Korea, he introduced you guys :)
>
> I’m testing with below scenario, but I wonder any problem with this?
> (It doesn’t work well)
>
> ------------------------------------------------------------------------------------------------------------
> — I config MaxSale like this. - 1 master, 2 slaves
> ------------------------------------------------------------------------------------------------------------
>
> ######################
> ## MaxScale
> ######################
> [MySQL Monitor]
> type=monitor
> module=mysqlmon
> servers=server1,server2,server3
> user=maxmon
> passwd=maxpwd
>
> [RW Split Router]
> type=service
> router=readwritesplit
> servers=server1,server2,server3
> user=maxmon
> passwd=maxpwd
>
> [RW Split Listener]
> type=listener
> service=RW Split Router
> protocol=MySQLClient
> port=3306
> socket=/tmp/rwsplit.sock
>
> [server1]
> type=server
> address=192.168.0.10
> port=3306
> protocol=MySQLBackend
>
> [server2]
> type=server
> address=192.168.0.11
> port=3306
> protocol=MySQLBackend
>
> [server3]
> type=server
> address=192.168.0.12
> port=3306
> protocol=MySQLBackend
>
>
> And I create users like this.
>
> ######################
> ## Database Servers
> ######################
> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'maxmon'@'%' IDENTIFIED BY 'maxpwd'
> GRANT SELECT ON `mysql`.`user` TO 'maxmon'@'%';
>
> GRANT USAGE ON *.* TO 'test'@'%' IDENTIFIED BY 'test';
> GRANT ALL PRIVILEGES ON `test`.* TO 'test'@'%’;
>
>
> ------------------------------------------------------------------------------------------------------------
> — Start MaxScale.
> — This is the messages.
> ------------------------------------------------------------------------------------------------------------
>
>
> 2014 06/13 19:51:06 Home directory : /usr/local/MaxScale
> 2014 06/13 19:51:06 Data directory : /usr/local/MaxScale/data3582
> 2014 06/13 19:51:06 Configuration file : /usr/local/MaxScale/etc/MaxScale.cnf
> 2014 06/13 19:51:07 Initialise debug CLI router module V1.1.1.
> 2014 06/13 19:51:07 Loaded module debugcli: V1.1.1 from /usr/local/MaxScale/modules/libdebugcli.so
> 2014 06/13 19:51:07 Initialise readconnroute router module V1.0.2.
> 2014 06/13 19:51:07 Loaded module readconnroute: V1.0.2 from /usr/local/MaxScale/modules/libreadconnroute.so
> 2014 06/13 19:51:07 Initializing statemend-based read/write split router module.
> 2014 06/13 19:51:07 Loaded module readwritesplit: V1.0.2 from /usr/local/MaxScale/modules/libreadwritesplit.so
> 2014 06/13 19:51:07 Initialise the MySQL Monitor module V1.2.0.
> 2014 06/13 19:51:07 Loaded module mysqlmon: V1.2.0 from /usr/local/MaxScale/modules/libmysqlmon.so
> 2014 06/13 19:51:07 SkySQL MaxScale 0.7.0 (C) SkySQL Ab 2013,2014
> 2014 06/13 19:51:07 MaxScale is running in process 3582
> 2014 06/13 19:51:07 Loaded 11 MySQL Users.
> 2014 06/13 19:51:07 Loaded module MySQLClient: V1.0.0 from /usr/local/MaxScale/modules/libMySQLClient.so
> 2014 06/13 19:51:07 Loaded 11 MySQL Users.
> 2014 06/13 19:51:07 Backend server 192.168.0.10:3306 state : RUNNING MASTER
> 2014 06/13 19:51:07 Backend server 192.168.0.11:3306 state : RUNNING SLAVE
> 2014 06/13 19:51:07 Backend server 192.168.0.12:3306 state : RUNNING SLAVE
> 2014 06/13 19:51:07 Loaded 11 MySQL Users.
> 2014 06/13 19:51:07 Loaded 11 MySQL Users.
> 2014 06/13 19:51:07 Loaded module telnetd: V1.0.1 from /usr/local/MaxScale/modules/libtelnetd.so
> 2014 06/13 19:51:07 MaxScale started with 1 server threads.
> 2014 06/13 19:51:07 Started MaxScale log flusher.
>
>
> ------------------------------------------------------------------------------------------------------------
> — I checked “netstat”, I can see “ESTABLISHED”.
> ------------------------------------------------------------------------------------------------------------
> tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN
> tcp 0 0 192.168.0.20:41268 192.168.0.10:3306 ESTABLISHED
> tcp 0 0 192.168.0.20:38929 192.168.0.11:3306 ESTABLISHED
> tcp 0 0 192.168.0.20:60517 192.168.0.12:3306 ESTABLISHED
>
>
> ------------------------------------------------------------------------------------------------------------
> — Finally I tried to connect to one MaxScale, but connection refused;
> ------------------------------------------------------------------------------------------------------------
> $ mysql -utest -ptest -h 127.0.0.1 -P 3306
> ERROR 1045 (2800): Authorization failed
>
>
> ------------------------------------------------------------------------------------------------------------
> — Error log says like this, as I try to connect with “test” user.
> ------------------------------------------------------------------------------------------------------------
> 2014 06/16 10:35:23 Error : getaddrinfo failed for [192.168.0.12/255.255.254.252] due [Name or service not known]
> 2014 06/16 10:35:23 140364711581664 [getUsers()] setipaddress failed: user m...@192.168.0.12/255.255.254.252 not added
> 2014 06/16 10:35:23 140364711581664 [mysql_users_add()] Failed adding user ro...@127.0.0.1(127.0.0.1)
> 2014 06/16 10:35:23 140364711581664 [mysql_users_add()] Failed adding user ro...@127.0.0.1(127.0.0.1)
> 2014 06/16 10:35:23 140364711581664 [mysql_users_add()] Failed adding user m...@127.0.0.1(127.0.0.1)
>
>
>
> I have no idea why I can’t to connect through MaxScale.
> I waited the reply on Google Groups, but it still opened.. :(
>
> Thanks.
> Chan.

[성동찬_Chan]

Hi~! Mark.

I connected successfully with your help :)
Thanks.

I have another question.

I created "test@localhost" user and connected with that user.
DB says 192.16.8.0.20 server is connected at that moment. (It’s MaxScale)

+-------+--------+--------------------+------+-------------+---------+-------+------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+-------+--------+--------------------+------+-------------+---------+-------+------+----------+
| 17365 | test | 192.168.0.20:39963 | NULL | Sleep | 2 | | NULL | 0.000 |
+-------+--------+--------------------+------+-------------+---------+-------+------+----------+

Then, I removed "test@localhost” user.
I expected it should not be connected through that account, but it works.

What I want to ask is how prohibit remove that user as soon as remove DB account from database, not to restart MaxScale.
(The account blocked when I restart MaxScale.)

Thanks.
Chan.

2014. 6. 25., 오후 7:34, Mark Riddoch <mark.r...@skysql.com> 작성:

[Mark Riddoch]

Hi Chan,

glad to hear you have this working.

The reason for the restart behaviour is related to the way MaxScale reloads user data format he backend server. It will reload the user table when a connection tries to authenticate that is not in the MaxScale table. At this stage it will reload the contents of the MaxScale internal table. In your case, where you remove the user form the MySQL user table, MaxScale will still have the entry in the internal MaxScale table. When you do the restart it will not be loaded, so hence you get the response you did. One way around this is to force MaxScale to reload the user table by using the command reload dbusers command in the telnet interface. E.g. reload dbusers "RW Split Service"

Ultimately we are thinking of having a much closer link between the internal MaxScale table and the MySQL table, so that when accounts are removed from the MySQL user table they will be removed form the MaxScale internal table as well.


Mark

[성동찬_Chan]

Hi~! Mark.

I start test maxscale, and execute simple query to collect which server I connect at the moment..
But sometimes connection gone away as I execute query.

#####################################################################
MariaDB [(none)]> show variables like '%host%';
+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| hostname | chandb01 |
| report_host | |
+---------------+----------+
2 rows in set (0.00 sec)

MariaDB [(none)]> show variables like '%host%';
ERROR 2013 (HY000): Lost connection to MySQL server during query
MariaDB [(none)]> show variables like '%host%';
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 8706
Current database: *** NONE ***

+---------------+----------+
| Variable_name | Value |
+---------------+----------+
| hostname | chandb03 |
| report_host | |
+---------------+----------+
2 rows in set (0.00 sec)





skygw_err1.log says some error, at the point connection’s gone away.
#####################################################################
2014 06/27 11:38:57 Error : Writing to client socket failed due 32, Broken pipe.
2014 06/27 11:39:03 Error : Writing to client socket failed due 32, Broken pipe.





skygw_msg1.log says like this, when start-up the maxscale. (I think there’s no problem)
#####################################################################
2014 06/27 11:31:26 Home directory : /usr/local/MaxScale
2014 06/27 11:31:26 Data directory : /usr/local/MaxScale/data8686
2014 06/27 11:31:26 Configuration file : /usr/local/MaxScale/etc/MaxScale.cnf
2014 06/27 11:31:26 Initialise debug CLI router module V1.1.1.
2014 06/27 11:31:26 Loaded module debugcli: V1.1.1 from /usr/local/MaxScale/modules/libdebugcli.so
2014 06/27 11:31:26 Initialise readconnroute router module V1.0.2.
2014 06/27 11:31:26 Loaded module readconnroute: V1.0.2 from /usr/local/MaxScale/modules/libreadconnroute.so
2014 06/27 11:31:26 Initializing statemend-based read/write split router module.
2014 06/27 11:31:26 Loaded module readwritesplit: V1.0.2 from /usr/local/MaxScale/modules/libreadwritesplit.so
2014 06/27 11:31:26 Initialise the MySQL Monitor module V1.2.0.
2014 06/27 11:31:26 Loaded module mysqlmon: V1.2.0 from /usr/local/MaxScale/modules/libmysqlmon.so
2014 06/27 11:31:26 SkySQL MaxScale 0.7.0 (C) SkySQL Ab 2013,2014
2014 06/27 11:31:26 MaxScale is running in process 8686
2014 06/27 11:31:26 Backend server 192.168.0.10:3306 state : RUNNING MASTER
2014 06/27 11:31:26 Backend server 192.168.0.11:3306 state : RUNNING SLAVE
2014 06/27 11:31:26 Backend server 192.168.0.12:3306 state : RUNNING SLAVE
2014 06/27 11:31:26 Loaded 12 MySQL Users.
2014 06/27 11:31:26 Loaded module MySQLClient: V1.0.0 from /usr/local/MaxScale/modules/libMySQLClient.so
2014 06/27 11:31:26 Loaded 12 MySQL Users.
2014 06/27 11:31:26 Loaded 12 MySQL Users.
2014 06/27 11:31:26 Loaded 12 MySQL Users.
2014 06/27 11:31:26 Loaded module telnetd: V1.0.1 from /usr/local/MaxScale/modules/libtelnetd.so
2014 06/27 11:31:26 MaxScale started with 1 server threads.
2014 06/27 11:31:26 Started MaxScale log flusher.
2014 06/27 11:31:37 Loaded module MySQLBackend: V2.0.0 from /usr/local/MaxScale/modules/libMySQLBackend.so



My configuration is like this as you see before.
#####################################################################

What’s the problem?
I think maxscale can’t connect to 192.168.0.11(chandb02) server, but database server is still normal status.

Thanks.
Chan.

[andreas...@gmail.com]

Hi Chan,

I hope you do not mind if I join this discussion.
I also try using Maxscale with readwritesplit module.
I saw your log and got curious.
When I use readwritesplit, Maxscale load only libreadwritesplit.so without libreadconnroute.so.
But at you log, I saw both modules are loaded.
Do you use both readwritesplit and readconnroute? Can I know why?

My Maxscale configuration is not that differ with yours. But I do not use monitor module, because Maxscale can not automatically detect master. Plus I use debugcli module to set database server flags (master or slave).
I asked why above because I want to know, wheteher setting readconnroute module will automatically set server flags.

Back to your problem, at debugcli, I use command 'list servers' to check whether Maxscale can detect server status.
This is result example:

MaxScale> list servers
Server | Address | Port | Status | Connections
-------------------------------------------------------------------------------
server1 | master-db | 3306 | Master, Running | 18327
server2 | slave1-db | 3306 | Slave, Running | 0

Does Maxscale says that 192.168.0.12 is running?

Hopefully this helps...

[gyw...@gmail.com]

Hi~!

You asked to me why I use readconnroute and readwritesplit?
In fact, I'm doing on functional test, so I configured like that.
And each module use different port - 3306 and 3307, so it's no problem, in my opinion
(If it could make some problem, I'll choose one of them.)

Thanks.
Chan. :)

2014년 6월 27일 금요일 오후 12시 33분 55초 UTC+9, andreas...@gmail.com 님의 말:

[Mark Riddoch]

Hi,

there should be no problem at all with having both routers installed and running. In fact thesis how we do our testing, we have lots of different services setup with different options all connecting to the same set of servers.

Mark

[pks...@e2enetworks.com]

I am getting a similar kind of error

2017-07-31 22:49:00 error : [MySQLAuth] Client hostname lookup failed, getnameinfo() returned: 'Name or service not known'.
2017-07-31 22:49:00 notice : [MySQLAuth] [Read-Write Service] Loaded 6 MySQL users for listener Read-Write Listener.
2017-07-31 22:49:00 error : [MySQLAuth] Client hostname lookup failed, getnameinfo() returned: 'Name or service not known'.
2017-07-31 22:49:00 warning: [MySQLAuth] Read-Write Service: login attempt for user 'crnews_liverateU'@::ffff:172.16.103.89:63856, authentication failed.

Can you post your fix for the issue.

[Markus Mäkelä]

Hi,

I would suggest turning on the info log level by adding log_info under the [maxscale] section. This will show which users are loaded.

Next step is to check that the user has a grant in both the client and MaxScale IP or a wildcard grant that covers them both.

The error about the hostname lookup could be caused by a grant on a hostname that cannot be resolved.

Markus

--
Markus Mäkelä, Software Engineer MariaDB Corporation
t: +358 40 7740484 | Skype: markus.j.makela

--
You received this message because you are subscribed to the Google Groups "MaxScale" group.
To unsubscribe from this group and stop receiving emails from it, send an email to maxscale+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Markus Mäkelä]

Seems I had a typo in my previous email, I meant to suggest adding log_info=true under the [maxscale] section.

Markus

--
Markus Mäkelä, Software Engineer MariaDB Corporation
t: +358 40 7740484 | Skype: markus.j.makela

[Pk Singh]

Hey Markus,

Thanks for quick response.

I'll get the log_info=true option added to maxscale.cnf of my setup.

I have grant for both client and maxscale IP over my Mysql DB server and also all grants are over IP's not hostname.

Let me see if I can get something in logs post enabling info level logging.

--

Cheers

Pawan Kr. Singh
E2E Networks Pvt. Ltd.
Mobile no. 8220466633, 8510021973

Managed DevOps: https://www.e2enetworks.com/managed-services/managed-devops/
Please consider your environmental responsibility before printing this email.