MySQL master-slave copy read/write separation

MySQL master-slave copy read/write separation

I. Overview of MySQL read and write separation

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/amoeba) to improve the database of the concurrency load of such a scenario for deployment and implementation.
How the Read and write separation works:
The basic principle is to have the primary database handle the transactional increment, change, delete (INSERT, UPDATE, delete), and the Select query operation from the database. Database replication is used to synchronize changes caused by transactional operations to the slave database in the cluster.

Data internal Exchange process:

Why to read and write separation:

Faced with increasing access pressure, the performance of a single server becomes a bottleneck and requires load sharing

1, master and slave only responsible for their own write and read, the maximum degree of relief x lock and S lock contention

2. MyISAM engine can be configured from library, improve query performance and save system overhead

3. Increase redundancy and increase availability

How to implement read-write separation:

Generally there are two ways to achieve

Application layer implementation, Web site program implementation

Application-tier implementations refer to the implementation of read-write separation within the application and in the connector

Advantages:
A: The application is internally read/write separated, the installation can use both
B: Reduce the difficulty of a certain deployment
C: Access pressure Below a certain level, the performance is very good
Disadvantages:
A: Once the architecture is adjusted, the code will change
B: Difficult to implement advanced applications, such as automatic sub-Library, sub-table
C: Not suitable for large-scale application scenarios
Middleware Layer Implementation
Middleware layer implementation refers to the implementation of the external middleware program read and write separation
Common Middleware Programs:
Mysql-proxy Amoeba Atlas (Cobar) (Alibaba) Tddl (Taobao)
Advantages:
A: More flexible architecture design
B: Some advanced controls can be implemented on the program, such as: transparent horizontal splitting, failover, monitoring
C: Can rely on some technical means to improve MySQL performance,
D: Small impact on business code, but also safe
Disadvantages:
Requires the support of a certain DevOps team

Mysql-proxy Overview

MySQL Proxy is a simple program that is located between your client side and the MySQL server to monitor, analyze, or change their communications. It is flexible, unlimited, and common uses include: load balancing, failure, query analysis, query filtering and modification, and so on.
MySQL Proxy is such a middle-tier agent, simply said, MySQL proxy is a connection pool, responsible for the front-end application of connection requests forwarded to the background database, and through the use of Lua script, can achieve complex connection control and filtering, so as to achieve read-write separation and load balancing. For the application, the MySQL proxy is completely transparent, and the application only needs to be connected to the listening port of MySQL proxy. Of course, this proxy machine may become a single point of failure, but can use multiple proxy machine as redundancy, in the Application Server connection pool configuration to multiple proxy connection parameters can be configured.

One of the more powerful features of MySQL Proxy is the realization of "read and write Separation", the basic principle is to let the primary database process transactional queries, let the library handle select queries. Database replication is used to synchronize changes caused by transactional queries to the slave libraries in the cluster


LUA Overview:
Lua is a small scripting language.
The speed of Lua is the fastest. It all determines that Lua is the best choice for embedded scripting.
LUA is written in standard C and can be compiled and run on almost all operating systems and platforms. LUA does not provide a powerful library, which is determined by its positioning. So Lua is not suitable as a language for developing standalone applications.
LUA website: http://www.lua.org/
Download: Mysql-proxy
http://dev.mysql.com/downloads/mysql-proxy/

Second, installation environment

System environment: CentOS Release 6.9 64bit
Software Name: mysql-5.7.18

Software users: MySQL

Software Installation location:/usr/bin/,/usr/share/,/usr/local/mysql-proxy

Data storage location:/var/lib/mysql

Log storage location:/var/log/mysqld.log

Third, master-slave design
First Design Master-Slave installation allocation method, a total of 3 servers, the server allocation is as follows:

Master node: 192.168.99.35
Mysql-proxy node: 192.168.99.36
Slave node: 192.168.99.37



Iv. Installation and Deployment

1. Three servers to install MySQL online

[Email protected] mysql]# RPM–UVH https://repo.mysql.com//mysql57-community-release-el6-11.noarch.rpm

[Email protected] mysql]# yum-y install mysql-community-server mysql-community-client Mysql-community-common Mysql-community-libs

Slow speed can be downloaded first rpm package local RPM or Yum installation

[Email protected] home]# mkdir/home/mysql/

[Email protected] home]# cd/home/mysql/

[[email protected] mysql]# ls

mysql-community-client-5.7.18-1.el6.x86_64.rpm

mysql-community-common-5.7.18-1.el6.x86_64.rpm

mysql-community-libs-5.7.18-1.el6.x86_64.rpm

mysql-community-server-5.7.18-1.el6.x86_64.rpm

[Email protected] mysql]# yum-y install mysql-community-server mysql-community-client Mysql-community-common Mysql-community-libs

2, Change password policy configuration simple password easy to test

Get the temporary password for MySQL for the first time by #grep "password"/var/log/mysqld.log command

Log on to the server with this password, you must change the password immediately, or the operation query times error

The password you initially set must match the length and must contain numbers, lowercase or uppercase letters, and special characters.

If you want to set a simple password, do the following:

First, modify the value of the Validate_password_policy parameter

[[email protected] mysql]# grep "Password"/var/log/mysqld.log

2017-05-28t23:15:52.739913z 1 [Note] A temporary password is generated for [email protected]: xnzb:ff-h1g_

mysql> set global validate_password_policy=0; #定义复杂度

mysql> set global validate_password_policy=0; #定义复杂度

mysql> set global validate_password_length=1; #定义长度 Default is 8

Mysql>set password for ' root ' @ ' localhost ' =password (' 123456 ');

3. Install LUA on 99.36

[[email protected] ~]# yum-y install LUA

4. Install Mysql-proxy on 99.36

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:

1) Download and install Mysql-proxy

[Email protected] home]# wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

[Email protected] mysql-proxy]# tar-xvf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz-c/usr/local/&& cd/usr /local/&& mv mysql-proxy-0.8.5-linux-el6-x86-64bit/./mysql-proxy && Cd/usr/local/mysql-proxy

2) Modify the system environment variables

Vim/etc/profile

Export Path=/usr/local/mysql-proxy/bin/:/usr/local/mysql/bin: $PATH//Add this variable

[[email protected] local]# source!$//Make system environment variable effective

Source/etc/profile

3) Modify the Mysql-proxy configuration file for read-write separation

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

The default configuration

Min_idle_connections = 4,

Max_idle_connections = 8,

Revision changed to

Min_idle_connections = 1,

Max_idle_connections = 8,

Modify the default connection for quick testing, the default minimum of 4 client connections will be read and write separated, the maximum number of links is 8.
Note: In order to verify the effect of the experiment, he was changed to 1. When there is a link, the function of read and write separation is realized.

4) Create databases and tables on 99.35 and 99.37, respectively, for write and read operations

mysql-uroot-p123456

mysql> CREATE database db;

mysql> use DB;

Mysql> CREATE TABLE test (id int);

mysql> INSERT INTO test values (35);

Mysql> Grant all on db.* to [e-mail protected] '% ' identified by ' 123456 ';

Mysql>flush privileges;

mysql-uroot-p123456

mysql> CREATE database db;

mysql> use DB;

Mysql> CREATE TABLE test (id int);

mysql> INSERT into test values (37);

Mysql> Grant all on db.* to [e-mail protected] '% ' identified by ' 123456 ';

Mysql>flush privileges;

5) Start the Mysql-proxy service in 99.36

[Email protected] local]# Mysql-proxy--proxy-read-only-backend-addresses=192.168.99.37:3306-- proxy-backend-addresses=192.168.99.35:3306--proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/ Rw-splitting.lua &

[1] 2551

[Email protected] local]# 2017-06-11 10:34:09: (critical) plugin proxy 0.8.5 started

6) Parameter Description

--proxy-read-only-backend-addresses=192.168.99.37:3306 # Defining back-end read-only servers
--proxy-backend-addresses=192.168.99.35: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:

When there are multiple read-only servers, you can write more than one of the following parameters:
--proxy-read-only-backend-addresses=192.168.1.64:3306 # Defining back-end read-only servers
--proxy-read-only-backend-addresses=192.168.1.65:3306 # Defining back-end read-only servers
#--proxy-address=192.168.1.62:3307 Specifies the listener port for MySQL proxy, which defaults to: 4040

Full parameters can be run with the following command to view

Mysql-proxy--help-all

7) Check to see if the Mysql-proxy is started

[[email protected] local]# lsof-i: 4040

COMMAND PID USER FD TYPE DEVICE size/off NODE NAME

Mysql-pro 3144 root 9u IPv4 19597 0t0 TCP *:yo-main (LISTEN)

8) test read/write separation

First Server login 99.36 query

[Email protected] ~]# mysql-uuser1-p123456-p4040-h192.168.99.36

mysql> use DB;

Database changed

Mysql> select * from test;

+------+

| ID |

+------+

| 35 |

+------+

1 row in Set (0.00 sec)

Mysql>

Insert and then query

mysql> INSERT into test values (36);

Query OK, 1 row affected (0.04 sec)

Mysql> select * from test;

+------+

| ID |

+------+

| 35 |

| 36 |

+------+

1 row in Set (0.00 sec)

Mysql>

Second Server login 99.36 query

[Email protected] ~]# mysql-uuser1-p123456-p4040-h192.168.99.36

mysql> use DB;

Database changed

Mysql> select * from test;

+------+

| ID |

+------+

| 37 |

+------+

1 row in Set (0.00 sec)

Mysql>

Indicates that the read/write separation is successful;

9) View Client understanding Status

Mysql> show Processlist;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 5 | User1 | 192.168.99.36:45314 | db |    Sleep |          2 | | NULL |

| 6 | User1 | 192.168.99.36:45316 | db |    Query | 0 | Starting | Show Processlist |

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

2 rows in Set (0.00 sec)

Mysql>

10) state parameter description;

Description of each column parameter:
First column, ID, an identity
The user column, which displays the current user, if not root, displays only the SQL statements within the scope of your permission.
The host column that shows which IP port this statement was issued from. A user that can be used to track a problem statement.
The DB column that shows which database the process is currently connected to.
Command column, which displays the execution commands for the current connection, typically sleep (sleep), query, connection (connect).
Time column, which is the duration of this state, in seconds.
The State column, which shows the status of the SQL statement using the current connection, is an important column, state is just one of the states in the statement execution, an SQL statement, as an example, may need to go through copying to TMP table, sorting result, Sendi The state of NG data can be completed.

The info column shows this SQL statement because the length of the SQL statement is not complete, but it is an important basis for judging the problem statement.

5. Configure MySQL Master in 99.35 and configure MySQL slave in 99.37 to implement master-slave replication

1) Configure the master server configuration file to enable the Log-bin feature

[[email protected] MySQL] #vim/etc/my.cnf

Log-bin=mysql-bin-master #启用二进制日志

Server-id=1 #本机数据库ID Mark

Binlog-do-db=db #可以被从服务器复制的库. The name of the database in which the binary needs to be synchronized

Binlog-ignore-db=mysql #不可以被从服务器复制的库

2) authorization allows slave 37 access 35

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

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> flush Privileges;

Query OK, 0 rows Affected (0.00 sec)

View Mster Status

Mysql> Show master status;

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

| File | Position | binlog_do_db | binlog_ignore_db | Executed_gtid_set |

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

|      mysql-bin-master.000001 | 154 | db |                   MySQL | |

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

1 row in Set (0.00 sec)

3) Configure Server 37 for slave

[Email protected] ~]# VIM/ETC/MY.CNF

server-id=2 #本机数据库ID Mark

4) Configuration 37 According to master changes

mysql-uroot-p123456

mysql> Change Master to master_host= ' 192.168.99.35 ', master_user= ' slave ', master_password= ' 123456 ';

mysql> flush Privileges;

5) Start slave and view status

mysql> start slave;

Query OK, 0 rows affected (0.01 sec)

Mysql> Show Slave status \g

6) Insert data on 35

mysql-uroot-p123456

mysql> use DB;

Database changed

mysql> INSERT into test values (36);

Query OK, 1 row affected (0.08 sec)

Mysql> select * from test;

+------+

| ID |

+------+

| 35 |

| 36 |

+------+

2 rows in Set (0.00 sec)

Mysql>

7) View on 37

[Email protected] ~]# mysql-u root-p123456

mysql> use DB;

Database changed

Mysql> select * from test;

+------+

| ID |

+------+

| 37 |

| 36 |

+------+

2 rows in Set (0.00 sec)

Mysql>

You can see that the newly inserted 36 has synchronized past;

8) External Login 99.36 query can also see that the separate primary and standby data is synchronized

[Email protected] ~]# mysql-uuser1-p123456-p4040-h192.168.99.36

Mysql>use DB;

Mysql> select * from test;

+------+

| ID |

+------+

| 35 |

| 36 |

+------+

2 rows in Set (0.00 sec)

Mysql>

Another server login 99.36 query situation

[Email protected] ~]# mysql-uuser1-p123456-p4040-h192.168.99.36

mysql> use DB;

Database changed

Mysql> select * from test;

+------+

| ID |

+------+

| 37 |

| 36 |

+------+

2 rows in Set (0.00 sec)

9) External login 99.36 insert and query status

[Email protected] ~]# mysql-uuser1-p123456-p4040-h192.168.99.36

mysql> use DB;

Database changed

mysql> INSERT into test values (100);

Query OK, 1 row affected (0.07 sec)

mysql> INSERT INTO test values (101);

Query OK, 1 row affected (0.08 sec)

Mysql> select * from test;

+------+

| ID |

+------+

| 37 |

| 36 |

| 100 |

| 101 |

+------+

4 rows in Set (0.00 sec)

Mysql>

On 99.35, the query confirms that the data is inserted and synchronized;

Mysql> select * from test;

+------+

| ID |

+------+

| 35 |

| 36 |

| 100 |

| 101 |

+------+

4 rows in Set (0.00 sec)

Mysql>

10) Resolve the 35 and 37 inconsistencies in the insert

Disable the sync feature on 99.35

[Email protected] mysql]# VIM/ETC/MY.CNF

#log-bin=mysql-bin-master #不启用二进制日志

Restart MySQL

[Email protected] mysql]#/etc/init.d/mysqld restart

Stopping mysqld: [OK]

Starting mysqld: [OK]

Delete 35

[Email protected] mysql]# mysql-u root-p123456

mysql> Delete from test where id=35;

Query OK, 1 row affected (0.06 sec)

Mysql> select * from test;

+------+

| ID |

+------+

| 36 |

| 100 |

| 101 |

+------+

3 Rows in Set (0.00 sec)

Disable salve on 99.37 and remove 37

mysql> stop Slave;

Query OK, 0 rows affected (0.02 sec)

mysql> Delete from test where id=37;

Query OK, 1 row affected (0.04 sec)

Mysql> select * from test;

+------+

| ID |

+------+

| 36 |

| 100 |

| 101 |

+------+

3 Rows in Set (0.00 sec)

Restart the sync function to

Enable the sync feature on 99.35

[Email protected] mysql]# VIM/ETC/MY.CNF

Log-bin=mysql-bin-master #启用二进制日志

Restart MySQL

[Email protected] mysql]#/etc/init.d/mysqld restart

Stopping mysqld: [OK]

Starting mysqld: [OK]

Start slave on 99.37

mysql> stop Slave;

External insert and query OK

This article is from the "Tomorrow Inspiration" blog, make sure to keep this source http://coolner.blog.51cto.com/957576/1934221

MySQL master-slave copy read/write separation