[root@pld1fmk0112 etc]# cat proxysql-admin.cnf # proxysql admin interface credentials. export PROXYSQL_DATADIR='/var/lib/proxysql' export PROXYSQL_USERNAME='admin' export PROXYSQL_PASSWORD='admin' export PROXYSQL_HOSTNAME='localhost' export PROXYSQL_PORT='6032' # PXC admin credentials for connecting to pxc-cluster-node. export CLUSTER_USERNAME='admin' export CLUSTER_PASSWORD='admin' export CLUSTER_HOSTNAME='localhost' 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='monit0r' # Application user to connect to pxc-node through proxysql export CLUSTER_APP_USERNAME='proxysql_user' export CLUSTER_APP_PASSWORD='passw0rd' # ProxySQL read/write hostgroup export WRITE_HOSTGROUP_ID='10' export READ_HOSTGROUP_ID='11' # ProxySQL read/write configuration mode. export MODE="singlewrite" # ProxySQL Cluster Node Priority File export HOST_PRIORITY_FILE=$PROXYSQL_DATADIR/host_priority.conf [root@pld1fmk0112 etc]# [root@pld1fmk0112 etc]# [root@pld1fmk0112 etc]# cat /etc/hosts 127.0.0.1 localhost.localdomain localhost.localdomain localhost ::1 localhost.localdomain localhost.localdomain localhost 172.19.52.107 pwbak0101.jud.hksarg pwbak0101 172.19.52.108 pwbak0102.jud.hksarg pwbak0102 172.20.52.167 pwbak0401.jud.hksarg pwbak0401 172.20.52.168 pwbak0402.jud.hksarg pwbak0402 10.33.179.109 pwf1bak0103.jud.hksarg pwf1bak0103 172.19.101.104 pld1fmk0112.jud.hksarg pld1fmk0112 [root@pld1fmk0112 etc]# hostname pld1fmk0112 ====================================================================================================================== [root@pld1fmk0112 proxysql]# cat proxysql.log | more 2018-12-03 15:10:07 [INFO] ProxySQL version 1.4.8-1.1 2018-12-03 15:10:07 [INFO] Detected OS: Linux pld1fmk0112 2.6.32-696.30.1.el6.x86_64 #1 SMP Fri May 18 11:50:44 EDT 2018 x86_64 2018-12-03 15:10:07 [INFO] Starting ProxySQL 2018-12-03 15:10:07 [INFO] Sucessfully started 2018-12-03 15:10:07 [INFO] Angel process started ProxySQL process 77221 Standard ProxySQL Cluster rev. 0.1.0702 -- ProxySQL_Cluster.cpp -- Tue May 22 10:05:10 2018 Standard ProxySQL Statistics rev. 1.4.1027 -- ProxySQL_Statistics.cpp -- Tue May 22 10:05:10 2018 Standard ProxySQL HTTP Server Handler rev. 1.4.1031 -- ProxySQL_HTTP_Server.cpp -- Tue May 22 10:05:10 2018 Standard ProxySQL Admin rev. 0.2.0902 -- ProxySQL_Admin.cpp -- Tue May 22 10:05:11 2018 Standard MySQL Threads Handler rev. 0.2.0902 -- MySQL_Thread.cpp -- Tue May 22 10:05:11 2018 Standard MySQL Authentication rev. 0.2.0902 -- MySQL_Authentication.cpp -- Tue May 22 10:05:11 2018 2018-12-03 15:10:07 [INFO] Dumping current MySQL Servers structures for hostgroup ALL 2018-12-03 15:10:07 [INFO] Dumping mysql_servers +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+---- ---------+ | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem _pointer | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+---- ---------+ +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+---- ---------+ 2018-12-03 15:10:07 [INFO] Dumping mysql_servers_incoming +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+ 2018-12-03 15:10:07 [INFO] New mysql_replication_hostgroups table 2018-12-03 15:10:07 [INFO] New mysql_group_replication_hostgroups table 2018-12-03 15:10:07 [INFO] Dumping current MySQL Servers structures for hostgroup ALL 2018-12-03 15:10:07 [INFO] Dumping mysql_servers +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+---- ---------+ | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem _pointer | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+---- ---------+ +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+---- ---------+ Standard Query Processor rev. 0.2.0902 -- Query_Processor.cpp -- Tue May 22 10:05:10 2018 In memory Standard Query Cache (SQC) rev. 1.2.0905 -- Query_Cache.cpp -- Tue May 22 10:05:10 2018 Standard MySQL Monitor (StdMyMon) rev. 1.2.0723 -- MySQL_Monitor.cpp -- Tue May 22 10:05:10 2018 2018-12-03 16:20:58 [INFO] Received command set mysql-default_charset='utf8mb4' 2018-12-03 16:20:58 [INFO] Received command set mysql-query_retries_on_failure=0 2018-12-03 16:20:58 [INFO] Received command set mysql-ping_timeout_server=500 2018-12-03 16:20:58 [INFO] Received command set mysql-monitor_connect_timeout=1000 2018-12-03 16:20:58 [INFO] Received command set mysql-default_max_latency_ms=2000 2018-12-03 16:20:58 [INFO] Received command set mysql-monitor_replication_lag_interval=500 2018-12-03 16:20:58 [INFO] Received command set mysql-ping_interval_server_msec=3000 2018-12-03 16:20:58 [INFO] Received command set mysql-monitor_ping_interval=5000 2018-12-03 16:20:58 [INFO] Received command set mysql-connect_timeout_server_max=3000 2018-12-03 16:59:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 16:59:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.157:3306 is returning "Access denied" for monitoring user 2018-12-03 16:59:48 [INFO] Received command set mysql-monitor_username='proxysql' 2018-12-03 17:00:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:00:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.157:3306 is returning "Access denied" for monitoring user 2018-12-03 17:01:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:01:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.157:3306 is returning "Access denied" for monitoring user 2018-12-03 17:02:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:02:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.157:3306 is returning "Access denied" for monitoring user 2018-12-03 17:03:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:03:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.157:3306 is returning "Access denied" for monitoring user 2018-12-03 17:04:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:04:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.157:3306 is returning "Access denied" for monitoring user 2018-12-03 17:05:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:05:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.157:3306 is returning "Access denied" for monitoring user 2018-12-03 17:06:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:06:07 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.157:3306 is returning "Access denied" for monitoring user 2018-12-03 17:06:23 [INFO] Received load mysql servers to runtime command 2018-12-03 17:06:23 [INFO] Dumping current MySQL Servers structures for hostgroup ALL 2018-12-03 17:06:23 [INFO] Dumping mysql_servers +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+---- ---------+ | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem _pointer | +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+---- ---------+ +--------------+----------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+---- ---------+ 2018-12-03 17:06:23 [INFO] Dumping mysql_servers_incoming +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+ | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+ | 2 | 172.19.100.156 | 3306 | 1 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | | 2 | 172.19.100.157 | 3306 | 1000 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+ 2018-12-03 17:06:23 [INFO] New mysql_replication_hostgroups table 2018-12-03 17:06:23 [INFO] New mysql_group_replication_hostgroups table 2018-12-03 17:06:23 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 2 , address: 172.19.100.156 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 10 , use_ssl: 0 , max_lat ency_ms: 0 , comment: Main Group HID: 2 , address: 172.19.100.157 , port: 3306 , weight: 1000 , status: ONLINE , max_connections: 1000 , max_replication_lag: 10 , use_ssl: 0 , max_ latency_ms: 0 , comment: Main Group 2018-12-03 17:06:23 [INFO] Dumping mysql_servers +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+-----------------+ | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+-----------------+ | 2 | 172.19.100.156 | 3306 | 1 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | 140031188696192 | | 2 | 172.19.100.157 | 3306 | 1000 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | 140031188696448 | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+-----------------+ 2018-12-03 17:06:23 [INFO] Received load mysql users to runtime command 2018-12-03 17:06:23 [INFO] Received load mysql variables to runtime command 2018-12-03 17:06:23 [INFO] Received save mysql servers to disk command 2018-12-03 17:06:23 [INFO] Received save mysql users to disk command 2018-12-03 17:06:23 [INFO] Received save mysql variables to disk command 2018-12-03 17:06:23 [INFO] Received load mysql query rules to runtime command 2018-12-03 17:06:23 [INFO] Received save mysql query rules to disk command 2018-12-03 17:06:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:07:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:08:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:09:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:10:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:11:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:12:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:13:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:14:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:15:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:16:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:17:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:18:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 2018-12-03 17:19:23 MySQL_Monitor.cpp:408:monitor_connect_thread(): [ERROR] Server 172.19.100.156:3306 is returning "Access denied" for monitoring user 44: Access denied for user 'fmkuser'@'172.19.101.104' to database 'fmkchk'. 2018-12-04 15:25:00 mysql_connection.cpp:628:handler(): [ERROR] Failed to mysql_real_connect() on 172.19.100.157:3306 , FD (Conn:22 , MyDS:22) , 10 44: Access denied for user 'fmkuser'@'172.19.101.104' to database 'fmkchk'. 2018-12-04 15:25:00 mysql_connection.cpp:628:handler(): [ERROR] Failed to mysql_real_connect() on 172.19.100.157:3306 , FD (Conn:22 , MyDS:22) , 10 44: Access denied for user 'fmkuser'@'172.19.101.104' to database 'fmkchk'. 2018-12-04 16:55:13 [INFO] Received LOAD MYSQL USERS TO RUNTIME command 2018-12-04 16:55:13 [INFO] Received SAVE MYSQL USERS TO DISK command 2018-12-04 16:55:13 [INFO] Received LOAD SCHEDULER TO RUNTIME command 2018-12-04 16:55:13 [INFO] Received SAVE SCHEDULER TO DISK command 2018-12-04 16:55:13 [INFO] Received LOAD MYSQL SERVERS TO RUNTIME command 2018-12-04 16:55:13 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 2 , address: 172.19.100.156 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 10 , use_ssl: 0 , max_lat ency_ms: 0 , comment: Main Group HID: 2 , address: 172.19.100.157 , port: 3306 , weight: 1000 , status: ONLINE , max_connections: 1000 , max_replication_lag: 10 , use_ssl: 0 , max_ latency_ms: 0 , comment: Main Group 2018-12-04 16:55:13 [INFO] Dumping mysql_servers +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+-----------------+ | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+-----------------+ | 2 | 172.19.100.156 | 3306 | 1 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | 140031188696192 | | 2 | 172.19.100.157 | 3306 | 1000 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | 140031188696448 | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+-----------------+ 2018-12-04 16:55:13 [INFO] Dumping mysql_servers_incoming +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+ | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+ | 2 | 172.19.100.156 | 3306 | 1 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | | 2 | 172.19.100.157 | 3306 | 1000 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+ 2018-12-04 16:55:13 [INFO] New mysql_replication_hostgroups table 2018-12-04 16:55:13 [INFO] New mysql_group_replication_hostgroups table 2018-12-04 16:55:13 [INFO] Dumping current MySQL Servers structures for hostgroup ALL HID: 2 , address: 172.19.100.156 , port: 3306 , weight: 1 , status: ONLINE , max_connections: 1000 , max_replication_lag: 10 , use_ssl: 0 , max_lat ency_ms: 0 , comment: Main Group HID: 2 , address: 172.19.100.157 , port: 3306 , weight: 1000 , status: ONLINE , max_connections: 1000 , max_replication_lag: 10 , use_ssl: 0 , max_ latency_ms: 0 , comment: Main Group 2018-12-04 16:55:13 [INFO] Dumping mysql_servers +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+-----------------+ | hostgroup_id | hostname | port | weight | status | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment | mem_pointer | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+-----------------+ | 2 | 172.19.100.156 | 3306 | 1 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | 140031188696192 | | 2 | 172.19.100.157 | 3306 | 1000 | 0 | 0 | 1000 | 10 | 0 | 0 | Main Gr oup | 140031188696448 | +--------------+----------------+------+--------+--------+-------------+-----------------+---------------------+---------+----------------+-------- ----+-----------------+ 2018-12-04 16:55:13 [INFO] Received SAVE MYSQL SERVERS TO DISK command 2018-12-04 16:55:13 [INFO] Received LOAD MYSQL QUERY RULES TO RUNTIME command 2018-12-04 16:55:13 [INFO] Received SAVE MYSQL QUERY RULES TO DISK command 2018-12-04 17:19:28 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_statements_current' 2018-12-04 17:19:28 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_stages_history_long' 2018-12-04 17:19:28 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_waits_history_long' 2018-12-04 17:19:50 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_statements_current' 2018-12-04 17:19:50 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_stages_history_long' 2018-12-04 17:19:50 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_waits_history_long' 2018-12-04 17:21:22 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_statements_current' 2018-12-04 17:21:22 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_stages_history_long' 2018-12-04 17:21:22 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_waits_history_long' 2018-12-04 17:24:32 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1044, Access denied for user 'fmkuse r'@'172.19.101.104' to database 'information_schema' 2018-12-04 17:25:02 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_statements_current' 2018-12-04 17:25:02 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_stages_history_long' 2018-12-04 17:25:02 MySQL_Session.cpp:2863:handler(): [WARNING] Error during query on (2,172.19.100.157,3306): 1142, SELECT command denied to user 'fmkuser'@'172.19.101.104' for table 'events_waits_history_long'