Mysql master-slave replication-mysql-proxy for read/write splitting

System Centos6.0

The MySQL proxy is a simple program between the MySQL client and the MySQL server. It can be used to monitor, analyze, or transmit communication between them. Its flexibility allows you to use it to the maximum extent, including load balancing, fault tolerance, analysis query, and correction.

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2221425513-0.jpg "title =" 3.jpg"/>

Master: 192.168.182.128

Slave: 192.168.182.129

Mysql-proxy: 192.168.182.130

1. Install the database on the Master/Slave server;

Master and slave operations:

[root@master ~]# yum install mysql mysql-devel -y

Configure master-slave replication;

Master:

Vim/etc/my. cnflog-bin = mysql-bin # Add server-id = 1 # Add

Slave:

Vim/etc/my. cnflog-bin = mysql-bin # Add server-id = 2 # Add

Master: Add authorized users;

mysql> grant replication slave on *.* to 'leo'@'%' identified by 'leo123';

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2221424348-1.jpg "title =" 4.jpg"/>

Slave:

mysql> change master to master_host='192.168.182.128',master_user='leo',master_password='leo123',master_log_file='mysql-bin.000001',master_log_pos=247;mysql> start slave;

View status:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/222142E31-2.jpg "title =" 5.jpg"/>

Master-slave replication OK;

The following describes how to configure read/write splitting on mysql-proxy;

[root@mysql-proxy ~]# wget http://cdn.mysql.com/Downloads/MySQL-Proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz[root@mysql-proxy ~]# useradd -r mysql-proxy[root@mysql-proxy ~]# tar zxvf mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit.tar.gz -C /usr/local[root@mysql-proxy ~]# mv /usr/local/mysql-proxy-0.8.3-linux-glibc2.3-x86-32bit /usr/local/mysql-proxy[root@mysql-proxy ~]# export PATH=$PATH:/usr/local/mysql-proxy/bin/[root@mysql-proxy ~]# echo "PATH=$PATH:/usr/local/mysql-proxy/bin" >> /etc/profile

Here we will first introduce the related mysql-proxy parameters;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/222142N34-3.jpg "title =" 6.jpg"/>

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2221421641-4.jpg "title =" 7.jpg"/>

-- Help-all: Get all help information; -- proxy-address = host: port: address and port of the proxy service listener; -- admin-address = host: port: management Module listening address and port; -- proxy-backend-addresses = host: port: the address and port of the backend mysql server; -- proxy-read-only-backend-addresses = host: port: the address and port of the backend read-only mysql server; -- proxy-lua-script = file_name: The Lua script that completes the mysql proxy function; -- daemon: Start mysql-proxy in daemon mode; -- keepalive: restart mysql-proxy upon crash; -- log-file =/path/to/log_file_name: log file Name; -- log-level = level: log level; -- log-use-syslog: syslog-based logging; -- plugins = plugin: the plug-in loaded when mysql-proxy is started; -- user = user_name: the user who runs the mysql-proxy process; -- defaults-file =/path/to/conf_file_name: the default configuration file path. The configuration section uses the [mysql-proxy] ID; -- proxy-skip-profiling: Disable profile; -- pid-file =/path/to/pid_file_name: Process file name;

Next we start:

First, create a remote test user on master \ slave.

Master:

mysql> grant all on *.* to root@'%' identified by 'root123';

No operation will be performed on the slave; I configure the mysql database to synchronize; so no additional operations are required;

Start:

[root@mysql-proxy ~]# mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.182.128:3306" --proxy-read-only-backend-addresses="192.168.182.129:3306" --proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" --plugins=admin --admin-username="admin" --admin-password="admin" --admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

View related information:

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22214264A-5.jpg "title =" 8.jpg"/>

495 process is mysql-proxy to start this process

Root indicates that keepalive is at work; tries to restart when the process crashes; this is also a new feature of mysql-proxy;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2221425526-6.jpg "title =" 9.jpg"/>

4040 is the proxy port; 4041 is the admin port; that is, Management

The following is a test;

Open several terminals;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/22214225U-7.jpg "title =" 4.jpg"/>

Log on to admin to view

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/2221422021-8.jpg "title =" 5.jpg"/>

Becauserw-splitting.luBy default, script a has four links to enable separation. Therefore, you can enable multiple terminals and test multiple connections. You can also modify the values in the connections;

650) this. width = 650; "src =" http://www.bkjia.com/uploads/allimg/131228/222142L46-9.jpg "title =" 2.jpg"/>

The state is displayed. The separation is successful;

This article is from the Coffee _ Blue Mountains blog, please be sure to keep this source http://lansgg.blog.51cto.com/5675165/1242674