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

Http://heylinux.com/archives/1004.html

As the most widely used free database in the world, MySQL believes that all engineers engaged in system operations must have contacted. However, in the actual production environment, the single MySQL as a separate database is completely unable to meet the actual needs, whether in security, high availability and high concurrency and other aspects.

Therefore, in general, it is through the master-slave Replication (Master-slave) to synchronize the data, and then through the read-write separation (mysql-proxy) to improve the database of the concurrency load of such a scenario for deployment and implementation.

As shown in the following:

Here are my notes in the process of actual work, shared here, for your reference.

First, the installation and configuration of MySQL
Specific installation process, it is recommended to refer to my 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
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 '

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

Querying the primary database state
Mysql> Show master status;
+------------------+----------+--------------+------------------+
| File | Position | binlog_do_db | binlog_ignore_db |
+------------------+----------+--------------+------------------+
| mysql-bin.000005 | 261 | | |
+------------------+----------+--------------+------------------+

Records the values of FILE and Position, which need to be used in the subsequent operations from 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;

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.

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) Copying data files
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.

(4) Canceling the primary database lock
Mysql> UNLOCK TABLES;

2.3 Verifying the master-slave copy effect

Operations on the primary server
Create a database on the primary server first_db
mysql> CREATE DATABASE first_db;
Query Ok, 1 row affected (0.01 sec)

Create a table on the primary server FIRST_TB
Mysql> CREATE TABLE FIRST_TB (ID int (3), name char (10));
Query Ok, 1 row Affected (0.00 sec)

Insert a record in table FIRST_TB on the primary server
mysql> INSERT INTO FIRST_TB values (001, ' myself ');
Query Ok, 1 row Affected (0.00 sec)

Viewing from a server
mysql> show databases;
=============================
+--------------------+
| Database |
+--------------------+
| Information_schema |
| first_db |
| MySQL |
| Performance_schema |
| Test |
+--------------------+
5 rows in Set (0.01 sec)
=============================
Database first_db has been automatically generated

Mysql> Use first_db
Database chaged

Mysql> Show tables;
=============================
+--------------------+
| tables_in_first_db |
+--------------------+
| FIRST_TB |
+--------------------+
1 row in Set (0.02 sec)
=============================
database table FIRST_TB has also been created automatically

Mysql> select * from FIRST_TB;
=============================
+------+------+
| ID | name |
+------+------+
| 1 | Myself |
+------+------+
1 rows in Set (0.00 sec)
=============================
The record is already there.

Thus, the entire MySQL master-slave replication process is complete, next, we do MySQL read and write separation of the installation and configuration.

Third, MySQL read and write separation
Scenario Description:
Database Master master server: 192.168.10.130
Database slave from server: 192.168.10.131
Mysql-proxy Dispatch server: 192.168.10.132

The following operations are performed on 192.168.10.132, the Mysql-proxy dispatch server.

3.1 MySQL Installation and configuration
The exact installation process is the same as above.

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

Search related RPM packages from rpm.pbone.net
download.fedora.redhat.com/pub/fedora/epel/5/i386/lua-5.1.4-4.el5.i386.rpm
download.fedora.redhat.com/pub/fedora/epel/5/x86_64/lua-5.1.4-4.el5.x86_64.rpm

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

#!/bin/sh## mysql-proxy This script starts and stops the Mysql-proxy daemon## chkconfig:-30# Processname:mysql-proxy # Description:mysql-proxy is a proxy daemon to mysql# Source function library: /etc/rc.d/init.d/functions#proxy_path=/usr/local/binproxy_path=/opt/mysql-proxy/binprog= "Mysql-proxy" # Source Networking configuration. /etc/sysconfig/network# Check that networking are up. [${networking} = "No"] && exit 0# Set default Mysql-proxy configuration. #PROXY_OPTIONS = "--daemon" proxy_options= "--admin-username=root--admin-password=password--proxy-read-only-backend-addresses=192.168.10.131:3306-- proxy-backend-addresses=192.168.10.130:3306--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua-- Proxy-lua-script=/opt/mysql-proxy/scripts/rw-splitting.lua "proxy_pid=/opt/mysql-proxy/run/mysql-proxy.pid# Source mysql-proxy Configuration.if [-f/etc/sysconfig/mysql-proxy]; Then. /etc/sysconfig/mysql-proxyfipath= $PATH:/usr/bin:/usr/local/bin: $PROXY _path# By default it's all goodretval=0# see how we were Called.case "$" in start) # Start daemon. Echo-n $ "Starting $prog:" $NICELEVEL $PROXY _path/mysql-proxy $PROXY _options--daemon--pid-file= $PROXY _pid--user        =mysql--log-level=warning--log-file=/opt/mysql-proxy/log/mysql-proxy.log retval=$? echo If [$RETVAL = 0];  then Touch/var/lock/subsys/mysql-proxy fi;;        Stop) # Stop daemons.        Echo-n $ "Stopping $prog:" Killproc $prog retval=$? echo If [$RETVAL = 0];  Then rm-f/var/lock/subsys/mysql-proxy rm-f $PROXY _pid fi;;  Restart) $ Stop Sleep 3 $ start;  Condrestart) [-e/var/lock/subsys/mysql-proxy] && $ restart;;       Status) status Mysql-proxy retval=$?  ;; *) echo "Usage: $ Start|stop|restart|status|condrestart}" retval=1;; Esacexit $REtval 

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/
chmod +x/opt/mysql-proxy/init.d/mysql-proxy

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

3.5 Test Read-Write separation effect
Create a database connection user for read-write detach
Log in to the primary database server 192.168.10.130, manage the MySQL server via the command line
/opt/mysql/bin/mysql-uroot-p ' New-password '
Mysql> GRANT All on * * to ' proxy1 ' @ ' 192.168.10.132 ' identified by ' password ';
Since we have configured the master-slave replication feature, this operation has been synchronized from the database server 192.168.10.131.

To clearly see the effect of read and write separation, you need to temporarily turn off MySQL master and slave copy function
Log on from the database server 192.168.10.131, manage MySQL server via command line login
/opt/mysql/bin/mysql-uroot-p ' New-password '

To close the slave synchronization process
mysql> stop Slave;
Query OK, 0 rows Affected (0.00 sec)

Connection Mysql-proxy
/opt/mysql/bin/mysql-uproxy1-p ' Password '-p4040-h192.168.10.132

After successful login, insert two records in the FIRST_TB table of first_db data
mysql> use first_db;
Database changed
mysql> INSERT into FIRST_TB values (007, ' first ');
Query Ok, 1 row Affected (0.00 sec)
mysql> INSERT into FIRST_TB values (, ' second ');
Query Ok, 1 row Affected (0.00 sec)

Query records
Mysql> select * from FIRST_TB;
=============================
+------+------+
| ID | name |
+------+------+
| 1 | Myself |
+------+------+
1 rows in Set (0.00 sec)
=============================
The read operation did not see the new record

Mysql> quit
Exit Mysql-proxy

Below, log in to the master-slave database server Separately, compare record information
First, check the primary database server
Mysql> select * from FIRST_TB;
=============================
+------+------+
| ID | name |
+------+------+
| 1 | Myself |
+------+------+
| 007 | First |
+------+------+
| 110 | Second |
+------+------+
3 Rows in Set (0.00 sec)
=============================
Two new records already exist.

Then, check the database server from the
Mysql> select * from FIRST_TB;
=============================
+------+------+
| ID | name |
+------+------+
| 1 | Myself |
+------+------+
1 rows in Set (0.00 sec)
=============================
No new records exist

This verifies that we have realized the MySQL read-write separation, all of the current write operations on the Master master server, to avoid the data of different steps;
In addition, all read operations are distributed to other slave from the server to share the database pressure.

Experience Sharing:
1. When MySQL master-slave replication occurs at show slave status\g when the value of slave_io_running or slave_sql_running is not yes, you need to stop the slave server by stopping slave first and then perform this article again The steps in section 2.1 and 2.2 are recoverable, but if you want to synchronize as much data as possible, you can increase the value of the Master_log_pos node on slave based on the value of the previous synchronization failure, and then test again and again 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 and write separation, load balancing and other functions, and stability to much more than mysql-proxy, we recommend that you use to replace Mysql-proxy, or even mysql-cluster.

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