Building Mysql-proxy to realize master-slave synchronous reading and writing separation

Proxy Server Wg61 192.168.0.180 (mysql-proxy)
Primary server Wg62 192.168.0.142 (responsible for writing data)
Wg63 192.168.0.156 from server (responsible for read-only data)

Experimental ideas:

2. Download Mysql-proxy, install the Lua language on the proxy server Wg61
4. Wg61 install proxy, add/etc/profile environment variable parameter
6. Modify proxy configuration file parameters, test read/write separation
8. Build a master-slave server, create test database tables, and authorize user access
10. Start Mysql-proxy test read-Write separation
11. Test hangs from the server, the master server hangs out how
    The steps are as follows:
    1. The Lua language is installed on the WG61 server, Mysql-proxy requires the Lua language call
    [Email protected] ~]# yum-y Insall Lua
    2. Download Mysql-proxy installation package to Wg61 and unzip to/usr/local/
    [Email protected] ~]# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/ Mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz--no-check-certificate
    [Email protected] ~]# TAR-XF mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz-c/usr/local/
    [Email protected] ~]# cd/usr/local/
    [[email protected] local]# ls
    Bin Games Lib Libexec sbin src
    etc include lib64 Mysql-proxy-0.8.5-linux-el6-x86-64bit share
    [[email protected] local] #mv mysql-proxy-0.8.5-linux-el6-x86-64bit/mysql-proxy
    3. Modify environment variable parameters
    [[email protected] local] #vim/etc/profile
    Last added Exportpath=/usr/local/mysql-proxy/bin/:/usr/local/mysql/bin: $PATH
    [Email protected] local]# source!$
    Source/etc/profile the command takes effect
    4, modify the Mysql-proxy configuration file parameters, test read and write separation
    [[email protected] local] #vim/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
    Min_idle_connections = 1, the minimum number of links in 41 rows and 42 lines is changed to 1
    Max_idle_connections = 1,
    5. Create test files on Wg62 master server and authorize users to User1 access rights
    Mysql>show databases;
    +--------------------+
    | Database |
    +--------------------+
    |information_schema |
    |mysql |
    |test |
    +--------------------+
    3rows in Set (0.00 sec)

Mysql>create database HK;
Queryok, 1 Row Affected (0.00 sec)

Mysql>use HK;
Databasechanged
Mysql>create table city (id int);
Queryok, 0 rows affected (0.02 sec)

Mysql>insert into the city values (123);
Queryok, 1 Row Affected (0.00 sec)

Mysql>select * from the city;
+------+
|id |
+------+
| 123 |
+------+
1row in Set (0.00 sec)

Mysql>grant all on hk.* to [email protected] '% ' identified by ' 123456 ';

6. Wg63 Create test files from the server and authorize users to User1 access rights
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Test |
+--------------------+
3 Rows in Set (0.00 sec)

mysql> CREATE DATABASE HK;
Query OK, 1 row Affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| HK |
| MySQL |
| Test |
+--------------------+
4 rows in Set (0.00 sec)

Mysql> CREATE TABLE city (id int);
ERROR 1046 (3d000): No Database selected
mysql> use HK;
Database changed
Mysql> CREATE TABLE city (id int);
Query OK, 0 rows affected (0.06 sec)

Mysql> INSERT into city values (456);
Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * from the city;
+------+
| ID |
+------+
| 456 |
+------+
1 row in Set (0.00 sec)

Mysql> Grant Allon hk.* to [e-mail protected] '% ' identified by ' 123456 ';

7. Start the Mysql-proxy service on the WG1 server
[[email protected] local] #mysql-proxy
--proxy-read-only-backend-addresses=192.168.0.156:3306
--proxy-backend-addresses=192.168.0.142:3306
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua&
[1] 2044
[[email protected] local] #2018 -01-04 05:25:31: (critical) plugin proxy 0.8.5 started

Open a different window to see if the service started successfully:
[[email protected] ~]# lsof-i: 4040
COMMAND piduser FD TYPE DEVICE size/off NODE NAME
Mysql-pro 2044root 9u IPv4 12940 0t0 TCP *:yo-main (LISTEN)
Parameter description:
--proxy-read-only-backend-addresses=192.168.0.111:3306 # Defining back-end read-only servers
--proxy-backend-addresses=192.168.0.112:3306 #定义后端mysql主服务器地址, specify the port of the MySQL write master server
--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua& #指定lua脚本, here, The rw-splitting script is used for read-write separation
When there are multiple read-only servers, you can write more than one of the following parameters:
--proxy-read-only-backend-addresses=192.168.0.111:3306 # Defining back-end read-only servers
--proxy-read-only-backend-addresses=192.168.0.112:3306 # Defining back-end read-only servers
#--proxy-address=192.168.0.110:3307 Specifies the listener port for MySQL proxy, which defaults to: 4040

8. Test reading and writing function
(1) Test write operation: can view the Wg62 data information, can also write data, cannot see the Wg63 data
Mysql>select user ();
+---------------------+
|user () |
+---------------------+
| [Email protected] |
+---------------------+
1row in Set (0.00 sec)

Mysql>show databases;
+--------------------+
| Database |
+--------------------+
| information_schema|
| HK |
|test |
+--------------------+
3rows in Set (0.00 sec)

Mysql>use HK;
Readingtable information for completion of table and column names
Youcan turn off this feature to get a quicker startup with-a

Databasechanged
Mysql>use HK;
Databasechanged
Mysql>show tables;
+--------------+
| TABLES_IN_HK |
+--------------+
|city |
+--------------+
1row in Set (0.00 sec)

Mysql>select * from the city;
+------+
|id |
+------+
| 123 |
+------+
1row in Set (0.00 sec)

Mysql>insert into the city values (1313);
Queryok, 1 Row Affected (0.00 sec)

Mysql>select * from the city;
+------+
|id |
+------+
| 123 |
| 1313 |
+------+
2rows in Set (0.00 sec)
(2) Test reading data
Log in from the WG2 demo client displays data from the server, and can see the corresponding data
[Email protected] ~]# mysql-uuser1-p123456-p4040-h192.168.0.180
[Email protected] ~]# mysql-uuser1-p123456-p4040-h192.168.0.180
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 10
Server version:5.1.73 Source Distribution

Copyright (c), and/or Itsaffiliates, Oracle. All rights reserved.

Oracle is a registered trademark of Oracle corporationand/or it
Affiliates. Other names trademarks of Theirrespective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear thecurrent input statement.

Mysql> select * from Hk.city;
+------+
| ID |
+------+
| 456 |
+------+
1 row in Set (0.00 sec)

Mysql> Ctrl-c--exit!
Aborted
[Email protected] ~]# mysql-uuser1-p123456-p4040-h192.168.0.180
Welcome to the MySQL Monitor. Commands End With; or \g.
Your MySQL Connection ID is 11
Server version:5.1.73 Source Distribution

Copyright (c), and/or Itsaffiliates, Oracle. All rights reserved.

Oracle is a registered trademark of Oracle corporationand/or it
Affiliates. Other names trademarks of Theirrespective
Owners.

Type ' help ', ' or ' \h ' for help. Type ' \c ' to clear thecurrent input statement.

Mysql> select * from Hk.city;
+------+
| ID |
+------+
| 456 |
+------+
1 row in Set (0.01 sec)

Mysql> Select User ();
+---------------------+
| User () |
+---------------------+
| [Email protected] |
+---------------------+
1 row in Set (0.00SEC)
Insert data displayed successfully, but query found no data inserted
mysql> INSERT into hk.city values (4545);
Query OK, 1 row affected (0.01 sec)

Mysql> select * from Hk.city;
+------+
| ID |
+------+
| 456 |
+------+
1 row in Set (0.00 sec)

mysql> INSERT into hk.city values (232323);
Query OK, 1 row Affected (0.00 sec)

Mysql> Select from hk.city;
+------+
| id |
+------+
| 456 |
+------+
1 row in Set (0.00SEC)
9, configure MySQL master-slave and implement read-write separation
(1) Synchronize Wg2 and WG3 two server data Information
[[email protected] ~]# Mysqldump-uroot-p-A > All.sql
[[email protected] ~]# scpall.sql 192.168.0.156:/root/
[[email  Protected] ~]# mysql-uroot-p123456
mysql> source/root/all.sql
(2) Wg2 The primary server is configured as master and authorizes the User2 user to act as a synchronous user
[[ Email protected] ~]# vim/etc/my.cnf
Log-bin=mysql-binlog
binlog-do-db=hk
Binlog_format=row
Server-id=1
Mysql> Grant all on . *to [email protected] '% ' identified by ' 123456 ';
Restart database

(3) Wg3 from server configuration to Slave
Method one: [[email protected] ~] #vim/etc/my.cnf
Master_host= ' 192.168.0.142 '
Master_user= ' User2 '
Master_password= ' 123456 '
Method Two: Log in to the database
Mysql>changemaster to master_host= ' 192.168.0.142 ', master_user= ' user2 ', master_password= ' 123456 ';
Restart database
10, test whether the master-slave synchronous read-write separation through Mysql-proxy
(1) Check whether the master and slave are synchronized
Mysql> Select User ();
+---------------------+
| User () |
+---------------------+
| [Email protected] |
+---------------------+
1 row in Set (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| HK |
| Test |
+--------------------+
3 Rows in Set (0.00 sec)

mysql> use HK;
Reading table information for completion of table Andcolumn names
Can turn off this feature to get a quicker startupwith-a

Database changed
mysql> use HK;
Database changed
Mysql> SELECT * from the city;
+--------+
| ID |
+--------+
| 123 |
| 1313 |
| 4545 |
| 232323 |
+--------+
4 rows in Set (0.00 sec)

Mysql> INSERT INTO city values (789);
Query OK, 1 row Affected (0.00 sec)

Mysql> SELECT * from the city;
+--------+
| ID |
+--------+
| 123 |
| 1313 |
| 4545 |
| 232323 |
| 789 |
+--------+
5 rows in Set (0.00 sec)
Login to the server to view, display the successful insertion
Log in from the server to display the synchronization success
Mysql> Select User ();
+----------------+
| User () |
+----------------+
| [Email protected] |
+----------------+
1 row in Set (0.00 sec)

Mysql> select * from Hk.city;
+--------+
| ID |
+--------+
| 123 |
| 1313 |
| 4545 |
| 232323 |
| 789 |
+--------+
5 rows in Set (0.00SEC)
(2) test downtime from server (writable view data)
mysql> INSERT INTO hk.city values (888);
Query OK, 1 row affected (0.01 sec)

Mysql> select * from Hk.city;
+--------+
| ID |
+--------+
| 123 |
| 1313 |
| 4545 |
| 232323 |
| 789 |
| 0 |
| 888 |
+--------+

7 rows in Set (0.00SEC)

Summary: 1. When the slave database is stopped, proxy query will be transferred to master, when the slave is started, Proxy is still reading master, when there is a new link in the time will be re-read slave data. Sometimes you may need to restart the Mysql-proxy

2. After recovering from the server, synchronize the data that you just inserted again
   (3) Test master server outage
   Mysql> SELECT * from hk.city
   ;
   +--------+
   | ID |
   +--------+
   | 123 |
   | 1313 |
   | 4545 |
   | 232323 |
   | 789 |
   | 0 |
   | 888 |
   +--------+
   7 Rows in Set (0.00 sec)

mysql> use HK;
ERROR (HY000): Lost connection to MySQL serverduring query
mysql> INSERT into hk.city values (999);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect ...
Connection Id:6
Current database: NONE

ERROR (HY000): Lost connection to MySQL server during query
Show only read data cannot write data

Building Mysql-proxy to realize master-slave synchronous reading and writing separation