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

Source: Internet
Author: User
Tags lua reserved

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:

  1. Download Mysql-proxy, install the Lua language on the proxy server Wg61
  2. Wg61 install proxy, add/etc/profile environment variable parameter
  3. Modify proxy configuration file parameters, test read/write separation
  4. Build a master-slave server, create test database tables, and authorize user access
  5. Start Mysql-proxy test read-Write separation
  6. 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

    1. 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

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.