Mysql-proxy agent plus MySQL master-slave for read and write separation

Construction of experimental Environment:

Mysql-proxy 192.168.1.163

Mysql-master 192.168.1.164 (Master)

Mysql-slave 192.168.1.162 (from)

Configuration: Mysql-proxy 192.168.1.163

#yum Install Lua-y

Lua-5.1.4-4.1.el6.x86_64 (most of which are installed by default)

Download Mysql-proxy Latest Version: MySQL Proxy 0.8.1 Alpha

#yum Install Lrzsz-y

#rz (upload mysql-proxy)

#tar ZXVF mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

#mv Mysql-proxy-0.8.5-linux-el6-x86-64bit/usr/local/mysql-proxy

#ls-L/usr/local/mysql-proxy

Drwxr-xr-x. 2 7161 wheel 4096, bin

Drwxr-xr-x. 2 7161 wheel 4096 include

Drwxr-xr-x. 6 7161 wheel 4096, Lib

Drwxr-xr-x. 2 7161 wheel 4096 libexec

Drwxr-xr-x. 7 7161 wheel 4096 licenses

Drwxr-xr-x. 3 7161 wheel 4096 share

#ehho "path= $PATH:/usr/local/mysql-proxy/bin/" >>/ETC/BASHRC

#export path= $PATH:/usr/local/mysql-proxy/bin/

#source/ETC/BASHRC Refresh

Mysql-master 192.168.1.164 (Master)

#yum install MySQL mysql-server-y

#/etc/init.d/mysqld start

#mysql

mysql> CREATE database db;

Query OK, 1 rowaffected (0.00 sec)

mysql> use db;

Database changed

Mysql> show databases;

+--------------------+

| Database |

+--------------------+

|information_schema |

| db |

| MySQL |

| Test |

+--------------------+

4 rows in Set (0.00 sec)

Mysql> CREATE TABLE test (id int);

Query OK, 0 rowsaffected (0.04 sec)

mysql> INSERT INTO testvalues (6363);

Query OK, 1 rowaffected (0.00 sec)

Mysql> Grant all on db.* to [e-mail protected] '% ' identified by ' 123456 '; login user and access password.

Query OK, 0 rowsaffected (0.00 sec)

Mysql-slave 192.168.1.162 (from) (step above and master server-like configuration)

#yum install MySQL mysql-server-y

#/etc/init.d/mysqld start

#mysql

mysql> CREATE database db;

Query OK, 1 rowaffected (0.00 sec)

mysql> use db;

Database changed

Mysql> show databases;

+--------------------+

| Database |

+--------------------+

|information_schema |

| db |

| MySQL |

| Test |

+--------------------+

4 rows in Set (0.00 sec)

Mysql> CREATE TABLE test (id int);

Query OK, 0 rowsaffected (0.04 sec)

mysql> INSERT INTO testvalues (6363);

Query OK, 1 rowaffected (0.00 sec)

Mysql> Grant all on db.* to [e-mail protected] '% ' identified by ' 123456 '; login user and access password.

Query OK, 0 rowsaffected (0.00 sec)

Configuration: Mysql-proxy 192.168.1.163

#mysql-proxy-r 192.168.0.162:3306-b 192.168.0.164:3306-s/usr/local/mysql-proxy/share/doc/mysql-proxy/ Rw-splitting.lua &

-R Specify Read server

-B Specify some servers

-s specifies to use that Lua (I'm doing this side of the read and write separation so choose Rw-splitting.lua)

Prompt after executing the command:

2016-03-18 00:32:38: (critical) plugin proxy 0.8.5 started

# Netstat-antup|grep Proxy

TCP 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 3323/mysql-proxy

Test:

#mysql-U user1-p123456-p 4040-h 192.168.0.163 (take Mysql-proxy to do PC test. )

mysql> use db;

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

Database changed

mysql> Select *from test;

Server default DB:

Client Default Db:db

Syncronizing

+------+

| ID |

+------+

| 62 |

+------+

1 row in Set (0.00 sec)

mysql> INSERT into test values (n);

Query OK, 1 row Affected (0.00 sec)

mysql> Select *from test;

+------+

| ID |

+------+

| 62 |

+------+

1 row in Set (0.00 sec)

Test write Data no problem, but select *from test; Not seen, indicating that read and write separations have now been implemented.

Detection data login 1.162 from (read-only server)

mysql> use DB;

Database changed

mysql> Select *from test;

+------+

| ID |

+------+

| 62 |

+------+

1 row in Set (0.00 sec)

Detection data login 1.164 from (write server)

mysql> use DB;

Database changed

mysql> Select *from test;

+------+

| ID |

+------+

| 64 |

| 63 |

+------+

2 rows in Set (0.00 sec)

INSERT INTO test values (63) above 1.163 and add 63来 to (1.64 write server).

Read/write separation succeeded.

So below we configure MySQL master and slave:

Mysql-master 192.168.1.164 (Master)

#vim/etc/my.conf

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

Log-bin=mysqllog

Server-id=1 add these three lines.

Binlog-do-db=db

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Login Database: Primary server

Mysql> Grant All on * * to [e-mail protected] '% ' identified by ' 123456 ';

Query OK, 0 rows Affected (0.00 sec)

Create a synced account.

mysql> drop table test; Delete Test table

#service mysqld Restart

Mysql-slave 192.168.1.162 (from)

#vim/etc/my.conf

[Mysqld]

Datadir=/var/lib/mysql

Socket=/var/lib/mysql/mysql.sock

User=mysql

# Disabling Symbolic-links is recommended to prevent assorted security risks

Symbolic-links=0

server-id=2

master-host=192.168.0.164 t add these 4 lines

Master-user=user2

master-password=123456

[Mysqld_safe]

Log-error=/var/log/mysqld.log

Pid-file=/var/run/mysqld/mysqld.pid

Login database: From server

mysql> drop table test; Delete Test table

#service mysqld Restart

Login:mysql-master 192.168.1.164 (Master)

mysql> use DB;

Database changed

Mysql> CREATE TABLE test (id int);

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT into test values (62);

Query OK, 1 row Affected (0.00 sec)

mysql> INSERT INTO test values (63);

Query OK, 1 row Affected (0.00 sec)

Build the Test table.

Login:mysql-slave 192.168.1.162 (from)

mysql> use DB;

Database changed

mysql> Select *from test;

+------+

| ID |

+------+

| 62 |

| 63 |

+------+

2 rows in Set (0.00 sec)

The data has been synced over.

Mysql> show slave status \g;

1. Row ***************************

Slave_io_state:waiting for Master to send event

master_host:192.168.0.164

Master_user:user2

master_port:3306

Connect_retry:60

master_log_file:mysqllog.000002

read_master_log_pos:622

relay_log_file:mysqld-relay-bin.000002

relay_log_pos:514

relay_master_log_file:mysqllog.000002

Slave_io_running:yes

Slave_sql_running:yes

replicate_do_db:

replicate_ignore_db:

Replicate_do_table:

Replicate_ignore_table:

Replicate_wild_do_table:

Replicate_wild_ignore_table:

last_errno:0

Last_error:

skip_counter:0

exec_master_log_pos:622

relay_log_space:670

Until_condition:none

Until_log_file:

until_log_pos:0

Master_ssl_allowed:no

Master_ssl_ca_file:

Master_ssl_ca_path:

Master_ssl_cert:

Master_ssl_cipher:

Master_ssl_key:

seconds_behind_master:0

Master_ssl_verify_server_cert:no

last_io_errno:0

Last_io_error:

last_sql_errno:0

Last_sql_error:

1 row in Set (0.00 sec)

ERROR:

No query specified

Mysql>

This article is from the "Han Yu" blog, please be sure to keep this source http://891023.blog.51cto.com/3984699/1752626

Mysql-proxy agent plus MySQL master-slave for read and write separation