MySQL master-slave replication and read-write separation-non-original

Original source: http://www.cnblogs.com/luckcs/articles/2543607.html

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

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.

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

first, MySQL installation and configuration of the specific installation process, it is recommended to refer to my 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, the entire MySQL directory can be packaged, and then uploaded to other servers to unpack, it can be used immediately.

Second, MySQL master-slave replication Scenario Description: Primary database server: 192.168.10.130,mysql has been installed, and 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

Manage MySQL server/opt/mysql/bin/mysql-uroot-p ' new-password ' via command line login

Authorized to 192.168.10.131 mysql> grant REPLICATION SLAVE from database server on *. * to ' rep1 ' @ ' 192.168.10.131 ' identified ' password ';

Querying the primary database status 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 Configure the configuration file from the server to modify from the server/opt/mysql/etc/my.cnf Server-id = 1 to Server-id = 10, and make sure that the ID is not used by another MySQL service.

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

Manage MySQL server/opt/mysql/bin/mysql-uroot-p ' new-password ' via command line login

Execute 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 mysql> start slave after correct execution;

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: 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 main database for the lock table operation, do not let the data again write 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.

(4) Cancel the main database lock mysql> UNLOCK TABLES;

2.3 Verifying the master-slave copy effect

operations on the primary server create databases on the primary server first_db mysql> CREATE database first_db; Query Ok, 1 row affected (0.01 sec)

CREATE table FIRST_TB on the primary server 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)

view mysql> show databases from the server; ============================= +--------------------+ | 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) ============================= Record also exists

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

iii. MySQL Read and write separation Scenario Description: Database Master master: 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.

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

3.2 Checking the system for required packages via 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 download source packages from http://www.lua.org/download.html in the following ways

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

Here we recommend using 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 in cflags=-o2-wall $ (mycflags) This line of records with-fpic, 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 at the following URL: 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 choose from 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 the 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
02     #
03     # mysql-proxy This script starts and stops the mysql-proxy daemon
04     #
05     # chkconfig: - 78 30
06     # processname: mysql-proxy
07     # description: mysql-proxy is a proxy daemon to mysql
08       
09     # Source function library.
10     . /etc/rc.d/init.d/functions
11       
12     #PROXY_PATH=/usr/local/bin
13     PROXY_PATH=/opt/mysql-proxy/bin
14       
15     prog="mysql-proxy"
16       
17     # Source networking configuration.
18     . /etc/sysconfig/network
19       
20     # Check that networking is up.
21     [ ${NETWORKING} = "no" ] && exit 0
22       
23     # Set default mysql-proxy configuration.
24     #PROXY_OPTIONS="--daemon"
25     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"
26     PROXY_PID=/opt/mysql-proxy/run/mysql-proxy.pid
27       
28     # Source mysql-proxy configuration.
29     if [ -f /etc/sysconfig/mysql-proxy ]; then
30             . /etc/sysconfig/mysql-proxy
31     fi
32       
33     PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH
34       
35     # By default it‘s all good
36     RETVAL=0
37       
38     # See how we were called.
39     case "$1" in
40       start)
41             # Start daemon.
42             echo -n $"Starting $prog: "
43             $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
44             RETVAL=$?
45             echo
46             if [ $RETVAL = 0 ]; then
47                     touch /var/lock/subsys/mysql-proxy
48             fi
49            ;;
50       stop)
51             # Stop daemons.
52             echo -n $"Stopping $prog: "
53             killproc $prog
54             RETVAL=$?
55             echo
56             if [ $RETVAL = 0 ]; then
57                     rm -f /var/lock/subsys/mysql-proxy
58                     rm -f $PROXY_PID
59             fi
60            ;;
61       restart)
62             $0 stop
63             sleep 3
64             $0 start
65            ;;
66       condrestart)
67            [ -e /var/lock/subsys/mysql-proxy ] && $0 restart
68           ;;
69       status)
70             status mysql-proxy
71             RETVAL=$?
72            ;;
73       *)
74             echo "Usage: $0 {start|stop|restart|status|condrestart}"
75             RETVAL=1
76            ;;
77     esac
78       
79     exit $RETVAL

script Parameters in detail: ============================================== proxy_path=/opt/mysql-proxy/bin// Define the 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 exception is closed--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 respectively (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

Configure and use 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 the read-write detach script Rw-splitting.lua modify the default connection, do a quick test, do not change the number of connections to 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_idl E_connections = 1,//default is 4 Max_idle_connections = 1,//default is 8 Is_debug = false} end =============================

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

3.5 Test Read-Write separation effect Create a database connection for read-write detach users log on to the primary database server 192.168.10.130, manage MySQL server/opt/mysql/bin/mysql-uroot-p ' with command line login 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.

In order to clearly see the effect of read and write separation, you need to temporarily turn off the MySQL master-slave replication function login from the database server 192.168.10.131, through the command line login management MySQL server/opt/mysql/bin/mysql-uroot-p ' New-password '

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

Connect mysql-proxy/opt/mysql/bin/mysql-uproxy1-p ' password '-p4040-h192.168.10.132

After successful landing, 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 record mysql> select * from FIRST_TB; ============================= +------+------+ | ID | name | +------+------+ | 1 | Myself | +------+------+ 1 rows in Set (0.00 sec) ============================= read operation and no new record is seen

Mysql> quit Quit Mysql-proxy

Below, respectively log in to the master-slave database server, compare record information first, check the main 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 from the database server 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 implemented the MySQL read and write separation, all of the current write operations are all on the Master master server, to avoid the data of different steps, in addition, all the 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 and read-write separation-non-original