Build MySQL proxy server for read-write separation + master-Slave synchronization

Lab Requirements:

1. Configure 2 MySQL server (192.168.100.2,192.168.100.3) +1 Proxy Server (192.168.100.1), realize the read and write separation of MySQL agent.

2. Users only need to access the MySQL proxy server, the actual SQL query, write operations to the background of the 2 MySQL server to complete.

3.2 MySQL servers implement master-slave synchronization, where the master server allows SQL queries, writes, and slave servers to allow only SQL queries.

One. Configuration of the master database server (192.168.100.2)

1. Install the package (this experiment uses MySQL5.6 version)

# Rpm-qa | Grep-i MySQL

Mysql-shared-5.6.15-1.el6.x86_64

Mysql-devel-5.6.15-1.el6.x86_64

Mysql-embedded-5.6.15-1.el6.x86_64

Mysql-test-5.6.15-1.el6.x86_64

Mysql-server-5.6.15-1.el6.x86_64

Mysql-client-5.6.15-1.el6.x86_64

Mysql-shared-compat-5.6.15-1.el6.x86_64

2. Start the service and log in to the database native to proxy Server user authorization

# service MySQL start && chkconfig MySQL on

Mysql> Grant All on * * to [e-mail protected] "192.168.100.1" identified by "123456";

3. Creating a test database and tables

mysql> CREATE DATABASE Proxydb;

Mysql> CREATE TABLE Proxydb.proxytab (id int);

mysql> INSERT INTO Proxydb.proxytab values (2);

Two. Configuration of the Slave database server (192.168.100.3)

1. Install the Package

2. Start the service and log in to the database native to proxy Server user authorization

# service MySQL start && chkconfig MySQL on

Mysql> Grant All on * * to [e-mail protected] "192.168.100.1" identified by "123456"; Same authorization as the master database server

3. Creating a test database and tables

mysql> CREATE DATABASE Proxydb; Create the same libraries and tables

Mysql> CREATE TABLE Proxydb.proxytab (id int);

mysql> INSERT into Proxydb.proxytab values (3); To be able to test differentiate between 2 different database servers, insert different data first

Three. mysql Proxy server configuration

1. Test whether an authorized user can log on to 2 database servers on a proxy server

# mysql-h192.168.100.2-uproxyuser-p123456//Login master server

Mysql> select * from Proxydb.proxytab;

+------+

| ID |

+------+

| 2 |

+------+

1 row in Set (0.00 sec)

# mysql-h192.168.100.3-uproxyuser-p123456//Login Slave server

Mysql> select * from Proxydb.proxytab;

+------+

| ID |

+------+

| 3 |

+------+

1 row in Set (0.00 sec)

2. Release 3306 ports

# Service Mysqld Stop

# chkconfig Mysqld off

# NETSTAT-TULNP | grep:3306

3. Install the package that provides the proxy server

# tar ZXVF mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz

# MV Mysql-proxy-0.8.3-linux-rhel5-x86-64bit/usr/local/mysql-proxy//This package is green free installation

# yum-y Install LUA//This software uses the LUA scripting language, so the LUA package needs to be installed

When the agent service starts, it sets the database responsible for writing to the Read database server

Invoking a script to differentiate the user's access type

Specify the port you are listening on

4. Start the agent service

cd/usr/local/mproxy/bin/

Use the Mysql-proxy script in the bin directory to start the proxy service

#./mysql-proxy-p 192.168.100.1:3306-r 192.168.100.3:3306-b 192.168.100.2:3306-s/usr/local/mysql-proxy/share/doc/m Ysql-proxy/rw-splitting.lua &

Main command options:

-P: Specify IP Address, port for agent listening

-r: Specifies the IP address, port of the read server

-B: Specifies the IP address, port of the write server

-S: Specify LUA script files

--keepalive: If the service process crashes, try restarting this process

5. See if the agent process is running

# jobs

# NETSTAT-TULNP | grep:3306

TCP 0 0 192.168.100.1:3306 0.0.0.0:* LISTEN 3314/mysql-proxy

The agent process must be in a running state for clients to access the backend database server through the proxy service.

6. View read-write detach script

# Vim/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua

......

Min_idle_connections = 4,//default concurrent request greater than 4 to implement read-write separation

Max_idle_connections = 8,

Four. The client accesses data on the database server through a proxy.

# mysql-h192.168.100.1-uproxyuser-p123456//test Client First Connection

Mysql> select * from Proxydb.proxytab; Test which database server is accessed

+------+

| ID |

+------+

| 2 |

+------+//assigned to the written database server

1 row in Set (0.00 sec)

# mysql-h192.168.100.1-uproxyuser-p123456//test client second to nth connection

Mysql> select * from Proxydb.proxytab;

Test results:

Concurrent traffic is greater than the default minimum concurrent connection 4 o'clock to differentiate read-write requests

The request is sent to the write server when concurrent traffic is less than the number of concurrent connections

The concurrent traffic is reduced to less than the threshold and still distinguishes between read and write requests

Four. Configure the master-slave synchronization architecture for 2 MySQL database servers

1.MASTER database Server (192.168.100.2) configuration

1.1 The MySQL master-slave synchronization must be guaranteed to have libraries and tables on the primary database server from the database server and the same table structure. By backing up the existing libraries on the primary server, and then importing them to the slave server.

When the existing libraries, tables are used MyISAM engine, can perform offline backup, recovery, so more efficient, otherwise, through the mysqldump and other tools to implement the library export, import.

# mysqldump-uroot-p123456--all-databases >/root/all.sql

# Scp/root/all.sql 192.168.100.3:/root/all.sql//via SCP to Slave server

1.2 Modify the/ETC/MY.CNF configuration and restart the MySQL service program

# VIM/ETC/MY.CNF

[Mysqld]

Log_bin//master server Be sure to enable Binlog logging

SERVER_ID = 2//Specify server ID number

Innodb_flush_log_at_trx_commit=1//Optimization settings

Sync-binlog=1//Allow log synchronization

# service MySQL Restart

1.3 Administrator Logon database grant slave server replication permissions

mysql> grant replication Slave on * * to [e-mail protected] "192.168.100.3" identified by "123456";

1.4 Checking the synchronization status of the master server

Mysql> Show master status;

+----------------------+----------+--------------+------------------+-------------------+

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

+----------------------+----------+--------------+------------------+-------------------+

|      localhost-bin.000004 |              766 |                  |                   | |

+----------------------+----------+--------------+------------------+-------------------+

1 row in Set (0.00 sec)

2.MASTER database Server (192.168.100.3) configuration

2.1 Importing backed-up libraries on slave servers

# mysql-uroot-p123456 </root/all.sql

2.2 Modify the/ETC/MY.CNF configuration and restart the MySQL service program

# VIM/ETC/MY.CNF

[Mysqld]

Log_bin//Enable Binlog logs, from the server can not be turned on

server_id = 3//Specify the server ID number and cannot be the same as the master

Innodb_flush_log_at_trx_commit=1//Optimization settings

Sync-binlog=1//Allow log synchronization

Log_slave_updates=1//records are updated from the library for easy "master-slave-from" chained replication

# service MySQL Restart

2.3 Administrator log in to the native database service and initiate a synchronous operation

Mysql> Change Master to

-master_host= "192.168.100.2",//IP address corresponding to master

-Master_user= "Slaveuser",//user name at sync

-master_password= "123456",//password at sync

-master_log_file= "localhost-bin.000004",//log file corresponding to master

master_log_pos=766; The log offset location corresponding to master

Query OK, 0 rows affected, 2 warnings (0.10 sec)

mysql> start slave; Once slave replication is enabled, you should perform a stop slave when you need to modify the master information, stop replication, and then modify and start replication again.

2.4 Viewing the status from the database server

Mysql> show Slave status\g;

......

Slave_io_running:yes

Slave_sql_running:yes

If the start slave direct error failure, please check the change master related settings is wrong, correct and then try again; if the IO thread or SQL thread has a "No", then the server's error log should be checked to analyze and troubleshoot the master-slave replication after troubleshooting.

2.5 Files and roles related to database server

# ls/var/lib/mysql/

Master.info//Save information for the primary database server you are connected to

localhost-relay-bin.000001//relaybinlog Log

Localhost-relay-bin.index//Save existing Relaybinlog log file name

Relay-log.info//relaybinlog Log Information

Five. Verify that data from the database server can be synchronized from the database server

Write data on the master server and view it on the slave server

mysql-uroot-p123456//Sign in on master server

mysql> CREATE DATABASE TestDB;

Mysql> CREATE TABLE A (id int);

INSERT into TESTDB.A values (100);

INSERT into TESTDB.A values (200);

INSERT into TESTDB.A values (300);

mysql-uroot-p123456//Log on to the slave server

Mysql> select * from TESTDB.A; You can see that the 3 records in the table are synchronized successfully.