Using agent software Mysql-proxy to realize MySQL read-write separation

Using agent software Mysql-proxy to realize MySQL read-write separation
Experiment:
172.16.1.10
Client request Mysql-proxy, judge whether to write or read
Establish two connections
is a remote connection to grant permission to a remote connection
>use DB;
>create table test (id int);
>insert into test values (10), (100);
>grant all on db.* to [email protected] '% ' identified by ' 123 ';
When you open a terminal setting from the server
As with the primary server, just the value is different
Mysql
>create database DN;
>use DB
>create table test (id int);
>insert into test values (10), (200);
>grant all on * * to [e-mail protected] '% ' identified by ' 123 '; The password must be the same as the primary server

Specify a proxy server; 172.16.1.10
Test
Mysql-h 172.16.1.11-u proxy-p123
Mysql-h 172.16.1.12-u proxy-p123
Using Lva's scripting language, LVA and Lvadevell are loaded, Day4 has Mysql-proxy
Put two packs on rpm
mysql-proxy-0.8.0*.rpm
RPM-IVH lua-5.0.2-1.el5.rf.i386.rpm
RPM-IVH lua-devel-5.0.2-1.el5.rf.i386.rpm
Tar zxvf mysql-proxy-0.8.0-linux-rhel5-x86-32bit.tar.gz-c/usr/local/src

Cp-r Mysql-proxy-0.8.0-linux-rhel5-x86-32bit/usr/local/mysql-proxy

Cd/usr/local/mysql-proxy/share/doc/mysql-proxy
Vim Rw-splitting.lua
Proxy.global.config.rwsplit if not
PNs Proxy.global.config.rwsplit = {
Min_idle_connections = 1, minimum idle thread connection
Max_idle_connections = 1, max thread connection, basic useless

How to start a mysqlproxy
CD bin
./mysql-proxy--help-proxy View Help
Mysql-proxy--proxy-read-only-backend-addresses=172.16.1.12:3306--proxy-backend-addresses=172.16.1.11:3306-- Proxy-lua-script=/usr/share/doc/mysql-proxy/rw-splitting.lua--daemon--user=nobody--log-level=debug-- Log-use-syslog
-P decided to use the interface for MySQL to connect, specify Mysql-proxy to allow access to the IP and interface to simulate MySQL, port with 3306, to ensure that their local MySQL do not run, open after port conflict, Mysql-proxy not run up
-R read-only backend server 172.16.1.12
-B Read-write backend server 172.16.1.11
-s work when specifying the location and name of the script, default is not
Version is less than 5.1.12, there will be a bug, add-bug
--deamon in the form of a service to start
What counts as a service: the process defined as a service is not a terminal, not a user-dependent terminal. The terminal shuts down, the service still can operate normally

Run Mysql-proxy
/usr/local/proxy/bin/mysql-proxy-p 172.16.1.10:3306-b 172.16.1.11:3306-r 17.16.1.12:3306-s/usr/local/proxy/share/ Doc/mysql-proxy/rw-splitting.lua--daemon &

Change: # ulimit-n 10240 Temporary change, permanent change, to change Pam
(Ulimit-n 1
Cat/etc/passwd/etc/shadow
Will go wrong)
Test:
Impersonate the client and use the MySQL command to initiate a connection to Mysql-proxy
Mysql-h 192.168.1.15-u proxy-p123
>use DB;
>select * from Test;
>

Another label
Mysql-h 192.168.1.15-u proxy-p123
>use DB;
>select * from Test;
>
You can increase the number of connections
>update test set id=300; Change data
Then you can build 140 14 into a master-slave pattern.

AB replication (master-slave replication)

You can have MySQL implement:
1. Data backup (master server data is all synchronized to slave server)
2. Can load balance reduce the pressure of the primary server
3. True MySQL read and write separation

Configuration method
Primary server:
1.cat/etc/my.cnf
[Mysqld]
Log-bin=mysql_bin
Server-id=1 not allowed to repeat
2.grant replication Slave on * * to ' slave ' @ ' 172.16.1.10 ' identified by ' king123 '; Authorization, specified from the server can only replicate Binlog, IP from the server
Mysql>show Master Status View the running status of the primary server

Use the Slave user logon test on the slave:
3.mysql-u slave-p123-h 192.168.1.14
4.mysqldump--all-database > Db_backup.sql (Master Service execution fully prepared)
5.mysql> Show master status; Find the current log file and location number

From: (Can not turn on binlog function)
6.mysql-uroot-p123 < DB_BACKUP.SQLB Copy the full backup script of the master service to the master server and import the master server
7.cat/etc/my.cnf
[Mysqld]
Server-id=2 and the primary server do not have to be duplicated
master-host=192.168.1.131 Primary Server IP
Master-user=slave Account at Login
master-password=123 MySQL reads the configuration file, it knows it is running from the server

8. When you read binlog from the server from the primary server to generate problems
Mysql-u root-p
Mysql> Change Master to
mysql> master_host= ' 192.168.1.131 ',
mysql> master_user= ' slave ',
mysql> master_password= ' 123 ',
mysql> master_log_file= ' master server log document ',
mysql> master_log_pos= update version number;

9. Start the slave server
mysql> slave start or start slave, start from server
Mysql>show slave Status View from server state

This article is from the "Small Zheng Technology Blog" blog, please be sure to keep this source http://ko178.blog.51cto.com/220419/1765207

Using agent software Mysql-proxy to realize MySQL read-write separation