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

Source: Internet
Author: User
Tags lua

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.