Connecting to ProxySQL from Asterisk configured with Percona XtraDB (Galera) Cluster

[Mehman Jafarov]

Hi guys,

I have a problem with ProxySQL.

I will migrate from MYSQL to Percona XtraDB Cluster with three nodes, ProxySQL as load balancer and Asterisk as application. I have configured test environment.

ProxySQL successfully configured with proxysql-admin tool. To test exactly, I shutdown the two of Cluster nodes and test with Node1.

My config file for ProxySQL is

[root@cybcallb ~]# cat /etc/proxysql-admin.cnf 

# proxysql admin interface credentials.

export PROXYSQL_USERNAME="admin"

export PROXYSQL_PASSWORD="admin"

export PROXYSQL_HOSTNAME="127.0.0.1"

export PROXYSQL_PORT="6032"

# PXC admin credentials for connecting to pxc-cluster-node.

export CLUSTER_USERNAME="proxy_user"

export CLUSTER_PASSWORD="cp@psql"

export CLUSTER_HOSTNAME="16.14.30.100"

export CLUSTER_PORT="3306"

# proxysql monitoring user. proxysql admin script will create this user in pxc to monitor pxc-nodes.

export MONITOR_USERNAME="monitor"

export MONITOR_PASSWORD="monitor123"

# Application user to connect to pxc-node through proxysql

export CLUSTER_APP_USERNAME="proxysql_user"

export CLUSTER_APP_PASSWORD="cp@pss"

# ProxySQL read/write hostgroup 

export WRITE_HOSTGROUP_ID="10"

export READ_HOSTGROUP_ID="11"

# ProxySQL read/write configuration mode.

export MODE="loadbal"

'asterisk' user and password all are same in each machine - Node1, ProxySQL, Application.

I grant full access on DB to asterisk user.

App user: asterisk

Asterisk IP: 20.255.19.127

Node1 IP: 16.14.30.100    (Database)

ProxySQL IP: 16.14.30.105

Now I will paste some excerpts from each machine .

Outputs from Node1:

mysql> select host,user from mysql.user;

+---------------------+---------------------+

| host                   | user                  |

+---------------------+---------------------+

|16.14.30.105     | asterisk            |

| 20.255.19.127  | asterisk            |

| 16.%                 | proxysql_user |

| 16.%                 | monitor            |

| 16.14.30.105    | proxy_user      |

Outputs from ProxySQL side:

mysql> select * from mysql_servers;

+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+

| hostgroup_id | hostname     | port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment   |

+--------------+--------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-----------+

| 10             | 16.14.30.100     | 3306 | ONLINE | 1000   | 0           | 1000            | 0                   | 0       | 0              | READWRITE |

mysql> select username, password, default_hostgroup  from mysql_users;

+--------------------+--------------------------------------------------------------------------+-------------------------+

| username        | password                                                                          | default_hostgroup |

+--------------------+--------------------------------------------------------------------------+-------------------------+

| proxy_user      | *341950EB3F9BD2343242342342425gfdb48B9A840C | 0                          |

| proxysql_user | *2986g5E955C1FA5B2D9CC2FEF196AA46588A6AA0  | 10                        |

| asterisk           | *7BBBGGG85A37675Y46KJHKA24BC0BB10536AF1    | 10                        |

+-------------------+---------------------------------------------------------------------------+------------------------+

3 rows in set (0.00 sec)

Now I will try 2 connections

1. Connect backend DB through ProxySQL with 'asterisk' user from Application machine. 

2. Connection to DB from ProxySQL machine and check the status from processlist.

1.Output for connection from ProxySQL machine

[root@cybcallb ~]# mysql -u asterisk -p -h 16.14.30.105 -P 6033

Enter password: 

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 387399

Server version: 5.5.30 (ProxySQL)

Copyright (c) 2009-2017 Percona LLC and/or its affiliates

Copyright (c) 2000, 2017, 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.

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| information_schema |

| mysql                        |

| performance_schema |

| shems                       |

| sys                      |

| test                  |

+--------------------+

6 rows in set (0.00 sec)

mysql> 

I also created the 'test' database with 'asterisk' user  successfully and from output we can see full access to all databases.

 (Note paste here about create database 'test' and done some insertion operation to table)

2. Just connection to ProxySQL machine from Application (Asterisk) with asterisk user

Now let's check the process list.

Login with monitor user in ProxySQL 

 

mysql> select * from  stats_mysql_processlist;

+-------------+-------------+-----------+----------------------------+--------------------+------------+-----------+--------------+------------+--------------+----------+---------+-------------+------+

| ThreadID | SessionID | user     | db                            | cli_host             | cli_port   | hostgroup | l_srv_host | l_srv_port | srv_host | srv_port | command | time_ms  | info |

+-------------+-------------+-----------+---------------------------+---------------------+------------+-----------+--------------+------------+--------------+----------+---------+-------------+------+

| 0               | 331471    | asterisk   | shems                        | 20.255.19.127    | 41965     | 10            |                  |                 |                    |              | Sleep   | 10144996  |        |

| 2               | 387399    | asterisk   | information_schema  | 16.14.30.105      | 38616     | 10            |                  |                 |                    |              | Sleep   | 1461          |        |

+-------------+--------------+-------------+----------------------------+-------------------------+------------+-------------+--------------+------------+----------------+------------+----------+---------------+-----+

2 rows in set (0.00 sec)

From output we can see that we have connections to ProxySQL locally and remote. But I can' t see any connection in 'srv_host' column for DB - 16.14.30.100. 

Although I can do any DML operation from local (ProxySQL) connection. 

But when try to do 'insert' operation from remote (Asterisk app) following error occurred and I thin ProxySQL doesn't send the query to DB side.

RROR[8556]: res_odbc.c:498 ast_odbc_find_table: Unable to query database columns on connection 'asteriskdb'.

res_odbc.c:641 ast_odbc_prepare_and_execute: SQL Execute returned an error -1: 42S22: [MySQL][ODBC 3.51 Driver][mysqld-5.5.30]Unknown column 'data' in 'field list' (77)

But when we do connection directly to DB from application with same user then everything is OK.

I can't solve the reason of error and find solution. And also can you check If I configure anything wrongly. 

HELP ME PLEASE!!!

Thanks !!!

Mehman Jafarov