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