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