MySQL proxy read-write separation

The following are excerpted from the Internet. Original site: http://blog.csdn.net/edwzhang/article/details/8475624

MySQL Master-slave Replication (master-slave) and read-write separation (mysql-proxy) practices

In general, it is through master-slave replication (Master-slave) to synchronize the data, and then through the read-write separation (mysql-proxy) to improve the database's concurrency load of such a scenario to deploy and implement.

As shown in the following:

Deploy the following:

First, the installation and configuration of MySQL

Refer to this article: http://heylinux.com/archives/993.html

It is worth mentioning that my installation process is the source package compiled and installed, and all the configuration and data are unified planning to the/opt/mysql directory, so after the installation on one server, you can package the entire MySQL directory, and then upload to other servers to unpack, you can immediately use.

Second, MySQL master-slave replication

Scenario Description:
Primary database server: 192.168.10.130,mysql is already installed and has no application data.
From the database server: 192.168.10.131,mysql is installed and no application data is applied.

2.1 Operations on the primary server

Managing MySQL server via command line login
/opt/mysql/bin/mysql-uroot-p ' New-password '

Authorization to 192.168.10.131 from the database server
Mysql> GRANT REPLICATION SLAVE on * * to ' rep1 ' @ ' 192.168.10.131 ' identified by ' password ';

If the primary server already has application data, the following processing is required for master-slave replication:

(1) The primary database for the lock table operation, do not let the data to write again action
Mysql> FLUSH TABLES with READ LOCK;

(2) View Primary database status
Mysql> Show master status;

(3) record the value of FILE and Position.
Copy the data file of the master server (the entire/opt/mysql/data directory) to the slave server, it is recommended to compress the tar archive and then upload it to the server.

2.2 Configuration from the server
Modify the configuration file from the server/opt/mysql/etc/my.cnf
Modify Server-id = 1 to Server-id = 10, and make sure that the ID is not used by another MySQL service.

Start the MySQL service
/opt/mysql/init.d/mysql start

Managing MySQL server via command line login
/opt/mysql/bin/mysql-uroot-p ' New-password '

Executing a synchronous SQL statement
Mysql> Change Master to
Master_host= ' 192.168.10.130 ',
Master_user= ' Rep1 ',
master_password= ' Password ',
Master_log_file= ' mysql-bin.000005 ',
master_log_pos=261;

Start slave synchronization process after correct execution
mysql> start slave;

2.3 Canceling the primary database lock
Mysql> UNLOCK TABLES;

Master-Slave synchronization check
Mysql> Show Slave Status\g

==============================================
1. Row *******************
Slave_io_state:
master_host:192.168.10.130
Master_user:rep1
master_port:3306
Connect_retry:60
master_log_file:mysql-bin.000005
read_master_log_pos:415
relay_log_file:localhost-relay-bin.000008
relay_log_pos:561
relay_master_log_file:mysql-bin.000005
Slave_io_running:yes
Slave_sql_running:yes
replicate_do_db:
............... To omit a number of ......
Master_server_id:1
1 row in Set (0.01 sec)
==============================================

The value of slave_io_running and slave_sql_running must be yes to indicate that the status is normal.

After you have finished, insert the record in the main database, then see if there is data from the database, and if there is data, it indicates that the insert was successful.

Third, MySQL read and write separation

3.2 Checking the system for required packages
by Rpm-qa | grep name verifies that the following packages are all installed.

gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig*
libevent* glib*

If the relevant software package is missing, it can be installed online via yum-y install or directly from the system installation CD and installed by RPM-IVH mode.

3.3 Compiling and installing LUA
Mysql-proxy's read-write separation is primarily implemented through the Rw-splitting.lua script, so it is necessary to install LUA.

Lua can be obtained in the following ways
Download the source package from http://www.lua.org/download.html

Here we recommend the use of the source package for installation
Cd/opt/install
wget http://www.lua.org/ftp/lua-5.1.4.tar.gz
Tar zvfx lua-5.1.4.tar.gz
CD lua-5.1.4

VI Src/makefile
Add-fpic to the cflags=-o2-wall $ (mycflags) record and change to cflags=-o2-wall-fpic $ (mycflags) to avoid errors during compilation.

Make Linux
Make install

CP etc/lua.pc/usr/lib/pkgconfig/
Export Pkg_config_path= $PKG _config_path:/usr/lib/pkgconfig

3.4 Installation Configuration Mysql-proxy
Mysql-proxy can be obtained from the following URLs:
http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/

It is recommended to use a compiled binary version, because the latest version of the Mysql-proxy has high requirements for automake,glib and libevent versions when compiling with the source package, which are the basic packages of the system and are not recommended for forced updates.
And these compiled binaries are all in a unified directory after decompression, so it is recommended to select the following versions:
32-bit RHEL5 platform:
Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
64-bit RHEL5 platform:
Http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-64bit.tar.gz

The test platform is RHEL5 32-bit, so select a 32-bit package
wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz

Tar xzvf mysql-proxy-0.8.1-linux-rhel5-x86-32bit.tar.gz
MV Mysql-proxy-0.8.1-linux-rhel5-x86-32bit/opt/mysql-proxy

Create a Mysql-proxy service management script
mkdir/opt/mysql-proxy/init.d/

Vim Mysql-proxy

Detailed Script parameters:
==============================================
Proxy_path=/opt/mysql-proxy/bin//define MYSQL-PROXY service binary file path

proxy_options= "--admin-username=root \//define internal Management Server account
--admin-password=password \//define internal Management Server password

--proxy-read-only-backend-addresses=192.168.10.131:3306 \//define backend read-only from server address
--proxy-backend-addresses=192.168.10.130:3306 \//define back-end primary server address
--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua \//define LUA Management script path

--proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua "\//define LUA read-write detach script path

Proxy_pid=/opt/mysql-proxy/run/mysql-proxy.pid//define Mysql-proxy PID file path

$NICELEVEL $PROXY _path/mysql-proxy $PROXY _options \
--daemon \//definition starts in daemon mode
--keepalive \//causes the process to automatically recover after an abnormal shutdown
--pid-file= $PROXY _pid \//define Mysql-proxy PID file path
--user=mysql \//Start the service as a MySQL user
--log-level=warning \//define log log level, from high to Low (Error|warning|info|message|debug)
--log-file=/opt/mysql-proxy/log/mysql-proxy.log//define log log file path
==============================================

CP mysql-proxy/opt/mysql-proxy/init.d///Put the configured files under the directory

chmod +x/opt/mysql-proxy/init.d/mysql-proxy//Modify Permissions

Mkdir/opt/mysql-proxy/run
Mkdir/opt/mysql-proxy/log

Mkdir/opt/mysql-proxy/scripts

Configuring and using the Rw-splitting.lua read-write detach script
The latest script we can get from the latest Mysql-proxy source package
Cd/opt/install

wget http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.1.tar.gz

Tar xzvf mysql-proxy-0.8.1.tar.gz
CD mysql-proxy-0.8.1
CP lib/rw-splitting.lua/opt/mysql-proxy/scripts

Modify read-write detach script Rw-splitting.lua
Modify the default connection, do a quick test, do not change the number of connections to reach 4 o'clock to enable read-write separation
Vim/opt/mysql-proxy/scripts/rw-splitting.lua
=============================
--Connection pool
If not Proxy.global.config.rwsplit then
Proxy.global.config.rwsplit = {
Min_idle_connections = 1,//default is 4
Max_idle_connections = 1,//default is 8
Is_debug = False
}
End
=============================

After the modification is complete, start mysql-proxy
/opt/mysql-proxy/init.d/mysql-proxy start

Experience Sharing:
1. When MySQL master-slave replication appears in show slave status\g when the value of slave_io_running or slave_sql_running is not yes,

You need to stop from the server first through the stop slave and then perform the steps in this article 2.1 and 2.2 to recover, but if you want to synchronize more data as much as possible, you can master_log_ on slave The value of the POS node increases based on the value of the previous synchronization failure, and then tests repeatedly until the synchronization is OK. Because the principle of MySQL master-slave replication is to read the master server from the server Binlog, and then according to Binlog Records to update the database.

2.mysql-proxy's Rw-splitting.lua script has many versions on the Web, but the most accurate version is still included in the source package lib/ Rw-splitting.lua script, if there is a LUA script programming basis, can be optimized on the basis of this script;

3.mysql-proxy is actually very unstable, in the case of high concurrency or faulty connection, the process is easy to automatically shut down, so it is a good idea to turn on the--keepalive parameter so that the process automatically recovers, but still can't solve the problem fundamentally, Therefore, it is usually the most prudent to install a mysql-proxy on each slave server for its own use, although relatively inefficient but can guarantee stability;

4. A master multi-slave architecture is not the best architecture, usually the better way is through the program code and middleware, such as planning, such as setting the table data to increase the increment of the ID value of the growth and other ways to implement two or more primary servers, but must pay attention to ensure that the integrity of these primary server data, Otherwise, the effect will be worse than a plurality of master-slave architectures;

The stability of 5.mysql-cluster is not too good;

6.Amoeba for MySQL is a good middleware software, the same can be read-write separation, load balancing and other functions, and stability to much more than mysql-proxy, we recommend that you use to replace Mysql-proxy, Even Mysql-cluster.

MySQL proxy read-write separation