MySQL master-slave replication and read-write separation based on mysql-proxy (bottom)
Yesterday talked about MySQL to achieve master-slave replication, but because of time is not said about the implementation of read and write separation, the reason is read and write separation, is to make the database has dual-machine hot standby function, as for the dual-machine hot standby, especially based on the high-availability system of two servers in warm-up (or high availability), because the Therefore, the name of the dual-machine hot standby, dual-machine high-availability according to the work of the switching mode is divided into: master-Standby mode (Active-standby mode) and dual-host mode (active-active mode), the main-standby means that a server is in a certain kind of business activation state (that is, the active state), The other server is in the standby state of the business (that is, the standby state). The dual-host approach means that two different services are in the primary standby state (that is, the Active-standby and Standby-active states) on two servers respectively. The next step is to use Mysql-proxy for read-write separation.
Environment
System: CentOS6.5
master:172.16.19.2
slave:172.16.19.24
mysql-proxy:172.16.19.14
Installation is not said, basically is the Yum install installation. The same as yesterday (in fact, it was yesterday's coming ~).
Implementation of read/write separation
First, install Mysql-proxy:yum installed Mysql-proxy on the third server.
Then modify the configuration file:
1[mysql-Proxy]2Daemon =true#start in daemon mode3Pid-file =/var/run/mysql-Proxy.pid4Log-file =/var/log/mysql-Proxy.log5Log-level = Debug #set the log level to debug and change to info after debugging is complete6Max-open-files =1024x768 7Plugins =Admin,proxy8user = mysql-Proxy9#Ten#Proxy Configuration OneProxy-address =0.0.0.0:3307#指定mysql-Proxy's listening address A#proxy-address =172.16.19.24:4040 -#proxy-backend-addresses = localhost:3306 -Proxy-backend-addresses =172.16.19.2:3306#setting up the background master server: master server, Master readable and writable theProxy-read-only-backend-addresses =172.16.19.24:3306#set background from server: from server, slave, read only -Proxy-lua-script =/usr/lib64/mysql-proxy/lua/rw-splitting.lua #设置读写分离脚本路径, can be downloaded on the official website mysql-Proxy package, after decompression -#proxy-skip-profiling =true -# +#Admin Configuration -#admin-address =0.0.0.0:4041 +Admin-lua-script =/usr/lib64/mysql-proxy/lua/admin.lua #set the management background Lua script path, the script is not automatically defined by default AAdmin-username = root #Set the login admin address user atAdmin-password =123456#设置管理用户密码
once setup is complete, you can perform: service Mysql-proxy start services.
Use mysql-uroot-p-h172.16.19.14--port=3307 to enter the database to view or change the database.
Use the Mysql-uroot-p-h172.16.19.14--port=4041 to enter the database management database.
If no user is connected to the backend via mysql-proxy, the status is unknown, otherwise it is up. At this point, MySQL's master-slave replication and read/write separation is implemented.
PS: This blog Welcome to forward, but please specify the blog address and author, because I level limited, if there is wrong, welcome point, Thank you ~
Blog Address: http://www.cnblogs.com/voidy/
Blog: http://voidy.net
<. ))) ≦
MySQL master-slave replication and read-write separation based on mysql-proxy (bottom)