Set up mysql proxy with mmm2 and mysql master-slave replication environment for zabbix read/write splitting

Jephe Wu - http://linuxtechres.blogspot.com

Environment: 2 mysql database configured with master-slave replication. mysql-proxy 0.8.1 from EPEL running on CentOS 6 x86_64, as well as mmm_monitor. Zabbix server connecting to mysql-proxy ip address as database. separated web server for Zabbix running on another machine connecting to mysql-proxy ip address


Diagram:

 Zabbix Web -> mysql-proxy 0.8.1/mmm2  (CentOS 6 64bit)
 Zabbix Server -> mysql-proxy 0.8.1/mmm2 -> writer mysql db
                                                                        -> reader mysql db


Objective: Make Zabbix to send non-transactional queries to reader mysql db only, and DML to writer mysql db, in short, read/write splitting.

Steps: We are only talking about mysql-proxy configuration part in this article, you can refer other blogs on this website for Zabbix and master-slave database replication.

1. use mysql-proxy from EPEL on CentOS 6 64bit
configuring EPEL first, then install mysql-proxy

2. use read-write splitting script from 0.8.2 alpha source code instead of tutorial-keep-alive.lua from 0.8.1 yum repository which is not working.

3. configuration file:
[root@mysql-proxy ~]# more /etc/sysconfig/mysql-proxy
# Options for mysql-proxy
#LUA_PATH=/usr/share/doc/mysql-proxy-0.8.1/examples/?.lua
ADMIN_USER="admin"
ADMIN_PASSWORD="password"
ADMIN_LUA_SCRIPT="/usr/lib64/mysql-proxy/lua/admin.lua"
PROXY_USER="mysql-proxy"
PROXY_OPTIONS="--daemon --keepalive  --proxy-backend-addresses=192.168.12.233:3306 --proxy-read-only-backend-addresses=192.168.12.234:3306 --proxy-address=:3306 --proxy-lua-script=/usr/share/doc/mysql-proxy-0.8.1/examples/rw-splitting-got-from-0.8.2alpha-source-tar-file.lua --log-level=message --log-file=/var/log/mysql-proxy.log"


netstat -ntpl  , you will find mysql proxy admin port listening at 4041, you can connect to it from another host to admin port:
mysql -uadmin -ppassword -P4041 -h mysql-proxy-host
===============
or with the following configuration:
root@mon01:/etc/mysql-proxy/ # more /etc/sysconfig/mysql-proxy
# Options to mysql-proxy
# do not remove --daemon
LUA_PATH="/srv/mysql-proxy/lib/?.lua"
PROXY_OPTIONS="--daemon --defaults-file=/etc/mysql-proxy/my.cnf --keepalive"
---------
root@mon01:/etc/mysql-proxy/ # more my.cnf
[mysql-proxy]
admin-username=root
admin-password=password
proxy-address=0.0.0.0:3306

proxy-read-only-backend-addresses=172.17.1.6:3306
proxy-read-only-backend-addresses=172.17.1.7:3306
proxy-read-only-backend-addresses=172.17.1.8:3306
proxy-backend-addresses=172.17.1.5:3306

proxy-pool-no-change-user=true
proxy-skip-profiling=true

log-file=/srv/mysql-proxy/log/mysql-proxy.log
#log-level=warning
#log-level=message
#log-level=debug
user=mysql
admin-lua-script=/srv/mysql-proxy/lib/mysql-proxy/lua/admin.lua
pid-file=/srv/mysql-proxy/run/mysql-proxy.pid
proxy-lua-script=/srv/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua  (this rw-splitting.lua is from mysql-proxy 0.8.2 tar file)

4. /etc/init.d/mysql-proxy restart

5. monitor slave mysql db logfile -> tail -f /var/lib/mysql/mysql_query.log (see /etc/my.cnf)

6. you can also see some DML statements on slave database, that's because master-slave replication, slave gets syncing with master operations.

7. once you finished configuring above, when clicking zabbix web monitoring - dashboard, most of select statements will go to slave database.


FAQ:
1.  Zabbix web interface gives error "Lock wait timeout exceeded; try restarting transaction"
=> Uncommen innodb_lock_wait_timeout = 50, change it to 500 seconds. restart mysql

2. Error in query: Deadlock found when trying to get lock; try restarting transaction
=> Just try again and refer to http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html  - how to cope with deadlock

3. For read/write splitting, tutorial-keep-alive.lua which comes with EPEL mysql-proxy rpm doesn't work.

4. how to check if rw-spliting is working?
while true ;do mysql -uroot -ppassword -e 'show processlist' ;sleep 1;done
to check if you  can find any select statement.




References:
1. Mysql master-slave and mysql-proxy read/write splitting - http://www.linuxso.com/linuxrumen/10236.html
2. http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html - how to cope with Deadlocks

Commands:
1.  show slave status\G
2.  show create table history;  to display DDL statement for table creation
3. show table status
4. show processlist;
5. show full processlist; to find out slow queries.


==============
Use mysql proxy to balance load between  2 readonly mysql database.


root@mon02:/etc/mysql-proxy/ # more my.cnf
[mysql-proxy]
admin-username=root
admin-password=password
proxy-address=0.0.0.0:3306
#proxy-read-only-backend-addresses=172.16.2.1:3306
#proxy-read-only-backend-addresses=172.16.2.2:3306
proxy-backend-addresses=172.16.2.2:3306,172.16.2.2:3306
log-file=/srv/mysql-proxy/log/mysql-proxy.log
log-level=message
user=mysql
admin-lua-script=/srv/mysql-proxy/lib/mysql-proxy/lua/admin.lua
pid-file=/srv/mysql-proxy/run/mysql-proxy.pid


root@mon02:/etc/mysql-proxy/ # more /etc/sysconfig/mysql-proxy
# Options to mysql-proxy
# do not remove --daemon
PROXY_OPTIONS="--daemon --defaults-file=/etc/mysql-proxy/my.cnf --keepalive"

Note: if you separate proxy-backend-addresse to two lines, it might not work, it will only add one server as backend, check log file /srv/mysql-proxy/log/mysql-proxy.log to confirm.