Shulou(Shulou.com)06/03 Report--

Mysql master-slave replication read-write separation

I. Overview of MYSQL read-write separation

Mysql, as the most widely used free database in the world, is believed to have been contacted by all engineers engaged in system operation and maintenance. But in the actual production environment, a single Mysql as an independent database can not meet the actual needs, whether in security, high availability, high concurrency and other aspects.

Therefore, generally speaking, it is deployed and implemented by master-slave replication (Master-Slave) to synchronize data, and then through read-write separation (MySQL-Proxy/Amoeba) to improve the concurrent load capacity of the database.

How read-write separation works:

The basic principle is to let the master database handle transactional add, modify, and delete operations (INSERT, UPDATE, DELETE), while the slave database handles SELECT query operations. Database replication is used to synchronize changes caused by transactional operations to slave databases in the cluster.

Internal data exchange process:

Why read-write separation:

In the face of increasing access pressure, the performance of a single server has become a bottleneck and needs to share the load.

1. Master and slave are only responsible for their own writing and reading, which greatly alleviates the contention between X lock and S lock.

2. The slave library can be configured with myisam engine to improve query performance and save system overhead.

3. Increase redundancy and improve availability

The way to achieve read-write separation:

There are generally two ways to implement it.

Application layer implementation, website program implementation

Application layer implementation refers to the separation of read and write within the application and in the connector.

Advantages:

A: the separation of read and write is implemented inside the application, and the installation can use the

B: reduce the difficulty of deployment

C: the access pressure is below a certain level and the performance is good.

Disadvantages:

A: once the architecture is adjusted, the code will change accordingly

B: it is difficult to implement advanced applications, such as automatic database division and table division.

C: not suitable for large-scale application scenarios

Middleware layer implementation

Middleware layer implementation refers to the separation of reading and writing in external middleware programs.

Common middleware programs:

Mysql-proxy amoeba Atlas (360) Cobar (Alibaba) TDDL (Taobao)

Advantages:

A: architecture design is more flexible

B: some advanced controls can be implemented programmatically, such as transparent horizontal split, failover, monitoring

C: we can rely on some technical means to improve the performance of mysql

D: small impact on business code and security at the same time

Disadvantages:

Need the support of a certain development, operation and maintenance team

Overview of MYSQL-PROXY

MySQL Proxy is a simple program between your client and MySQL server that can monitor, analyze, or change their communications. It is flexible and unlimited, and its common uses include: load balancing, failure, query analysis, query filtering and modification, and so on.

MySQL Proxy is such a middle-tier agent. To put it simply, MySQL Proxy is a connection pool, which is responsible for forwarding connection requests from foreground applications to the background database. Through the use of lua scripts, complex connection control and filtering can be achieved to achieve read-write separation and load balancing. For applications, MySQL Proxy is completely transparent, and applications only need to connect to the listening port of MySQL Proxy. Of course, the proxy machine may become a single point of failure, but you can use multiple proxy machines as redundancy and configure the connection parameters of multiple proxy in the connection pool configuration of the application server.

One of the more powerful features of MySQL Proxy is to achieve "read-write separation". The basic principle is to let the master database handle transactional queries and the slave libraries to handle SELECT queries. Database replication is used to synchronize changes caused by transactional queries to slave libraries in the cluster

Lua Overview:

Lua is a small scripting language.

Lua is the fastest. All this determines that Lua is the best choice as an embedded script.

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 location. So Lua is not suitable as a language for developing stand-alone applications.

Lua official website: http://www.lua.org/

Download: mysql-proxy

Http://dev.mysql.com/downloads/mysql-proxy/

II. Installation environment

System environment: CentOS release 6.9 64bit

Software name: mysql-5.7.18

Software user: mysql

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

Data storage location: / var/lib/mysql

Log location: / var/log/mysqld.log

III. Master-slave design

First of all, design the master-slave installation and allocation method, which requires a total of 3 servers, and 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. Install mysql online on three servers

Root@centos-6 mysql] # rpm-Uvh https://repo.mysql.com//mysql57-community-release-el6-11.noarch.rpm

Root@centos-6 mysql] # yum-y install mysql-community-server mysql-community-client mysql-community-common mysql-community-libs

If the network speed is slow, you can download the rpm package and install the local rpm or yum first.

[root@centos-6 home] # mkdir / home/mysql/

[root@centos-6 home] # cd / home/mysql/

[root@centos-6 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

Root@centos-6 mysql] # yum-y install mysql-community-server mysql-community-client mysql-community-common mysql-community-libs

2. Modify password policy and configure simple password to facilitate testing

Obtain the temporary password of MySQL for the first time through the # grep "password" / var/log/mysqld.log command

After logging in to the server with this password, you must change the password immediately, otherwise you will make an error in querying the Times.

The password you just 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

[root@centos-6 mysql] # grep "password" / var/log/mysqld.log

2017-05-28T23:15:52.739913Z 1 [Note] A temporary password is generated for root@localhost: xnzb:ff-h2G_

Mysql > set global validate_password_policy=0; # define complexity

Mysql > set global validate_password_length=1; # defines a length of 8 by default

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

Set the level of password policy through the my.cnf profile

Validate_password_policy=2

The last line validate_password_policy sets the password policy level when mysql starts. If set to 3, you need to specify a dictionary file.

Of course, you can also turn off the validate_password plugin through the my.cnf configuration file. Just add a row

Validate_password = off

The new version of MySQL listens by default on the address family of IPv6.

Change to monitor the IPv4 address family, modify the my.cnf and add a line of configuration:

Bind-address = 0.0.0.0

3. Install lua on 99.36

[root@Centos-6-99] # yum-y install lua

4. Install mysql-proxy on 99.36

It is recommended to use the compiled binary version, because when compiling with the source package, the latest version of MySQL-Proxy has high requirements for the versions of automake,glib and libevent, and these packages are the basic packages of the system, so it is not recommended to force updates. And these compiled binary versions are all in a unified directory after being decompressed, so it is recommended to choose the following versions:

1) download and install mysql-proxy

[root@Centos-6-99 home] # wget http://dev.mysql.com/get/Downloads/MySQL-Proxy/mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz

[root@Centos-6-99 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 system environment variables

Vim / etc/profile

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

[root@xuegod62 local] # source! $/ / make the system environment variable effective

Source / etc/profile

3) modify the mysql-proxy configuration file to achieve read-write separation

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

Set the default configuration

Min_idle_connections = 4

Max_idle_connections = 8

Modify to

Min_idle_connections = 1

Max_idle_connections = 8

Modify the default connection, conduct a quick test, the default minimum of more than 4 client connections will achieve read-write separation, and the maximum number of links is 8.

Note: in order to verify the effect of the test, he was changed to 1. That is, when there is a link, the function of separation of read and write is realized.

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

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 user1@'%' 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 user1@'%' identified by '123456'

Mysql > flush privileges

5) start the MYSQL-PROXY service on 99.36th

[root@Centos-6-99 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

[root@Centos-6-99 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 # defines a backend read-only server

-- proxy-backend-addresses=192.168.99.35:3306 # defines the backend mysql master server address and specifies the port on which mysql writes to the master server

-- proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua & # specifies the lua script, in this case, 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 # defines a backend read-only server

-- proxy-read-only-backend-addresses=192.168.1.65:3306 # defines a backend read-only server

#-proxy-address=192.168.1.62:3307 specifies the listening port of mysql proxy. Default is: 4040

The complete parameters can be viewed by running the following command

Mysql-proxy-help-all

7) check whether mysql-proxy is started

[root@Centos-6-99 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 the separation of read and write

The first server logs in to 99.36 queries

[root@localhost] # mysql-uuser1-p123456-P4040-h292.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 >

The second server logs in to 99.36 queries

[root@localhost] # mysql-uuser1-p123456-P4040-h292.168.99.36

Mysql > use db

Database changed

Mysql > select * from test

+-+

| | id |

+-+

| | 37 |

+-+

1 row in set (0.00 sec)

Mysql >

It indicates that the read-write separation has been successful.

9) check the client understanding status

Mysql > show processlist

+-+ +

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

+-+ +

| | 5 | user1 | 192.168.99.36 Sleep 45314 | db | Sleep | 2 | | NULL |

| | 6 | user1 | 192.168.99.36 Query 45316 | db | Query | 0 | starting | show processlist |

+-+ +

2 rows in set (0.00 sec)

Mysql >

10) description of status parameters

Description of each column of parameters:

The first column, id, is an ID

The user column shows the current user, and if it is not root, this command displays only the sql statements within your permissions.

The host column, which shows which port of the ip the statement is issued from. Can be used to track the user with the problem statement.

The db column that shows which database the process is currently connected to.

The command column, which shows the commands executed by the current connection, which are generally sleep, query, connect.

Time column, the duration of this state, in seconds.

State column, showing the status of the sql statement using the current connection, very important column, state is only a certain state in the execution of the statement, a sql statement, for example, a query may need to go through copying to tmp table, Sorting result, Sending data and other states to complete.

The info column shows the sql statement. Because of the limited length, the long sql statement is not fully displayed, but it is an important basis for judging the problem statement.

5. Configure mysql master at 99.35and mysql slave at 99.37to realize master-slave replication.

1) configure the master server profile to enable the log-bin feature

[root@centos-6 mysql] # vim / etc/my.cnf

Log-bin=mysql-bin-master # enable binary logging

Server-id=1 # Native database ID marking

Binlog-do-db=db # libraries that can be copied from the server. Binary database name that needs to be synchronized

Binlog-ignore-db=mysql # libraries that cannot be copied from the server

2) Authorization allows slave 37 to access 35

Mysql > grant replication slave on *. * to slave@192.168.99.37 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 as slave

[root@Centos-6-99] # vim / etc/my.cnf

Server-id=2 # Native database ID marking

4) configuration 37 changes according to master

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 check the 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

[root@Centos-6-99] # 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 the past

8) external login 99.36 query can also see that the respective master and standby data have been synchronized

[root@localhost] # mysql-uuser1-p123456-P4040-h292.168.99.36

Mysql > use db

Mysql > select * from test

+-+

| | id |

+-+

| | 35 |

| | 36 |

+-+

2 rows in set (0.00 sec)

Mysql >

The query of 99.36 login on another server

[root@localhost] # mysql-uuser1-p123456-P4040-h292.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

[root@localhost] # mysql-uuser1-p123456-P4040-h292.168.99.36

Mysql > use db

Database changed

Mysql > insert into test values

Query OK, 1 row affected (0.07 sec)

Mysql > insert into test values

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 >

Query on 99.35 to confirm that the data has been inserted and synchronized

Mysql > select * from test

+-+

| | id |

+-+

| | 35 |

| | 36 |

| | 100 |

| | 101 |

+-+

4 rows in set (0.00 sec)

Mysql >

10) resolve inconsistencies between 35 and 37 insertions

Disable synchronization on 99.35

[root@centos-6 mysql] # vim / etc/my.cnf

# log-bin=mysql-bin-master # do not enable binary logging

Restart mysql

[root@centos-6 mysql] # / etc/init.d/mysqld restart

Stopping mysqld: [OK]

Starting mysqld: [OK]

Delete 35

[root@centos-6 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 delete 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)

Just restart the synchronization function

Enable synchronization on 99.35

[root@centos-6 mysql] # vim / etc/my.cnf

Log-bin=mysql-bin-master # enable binary logging

Restart mysql

[root@centos-6 mysql] # / etc/init.d/mysqld restart

Stopping mysqld: [OK]

Starting mysqld: [OK]

Just start slave on 99.37.

Mysql > stop slave

External insertion and query can be normal.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.