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

This blog introduces how to synchronize data through master-slave replication (Master-Slave), and then improve the concurrent load capacity of the database through read-write separation (MySQL-Proxy) to achieve mysql high availability.

First of all, let's learn about AB master-slave replication. AB replication is to do the addition or deletion of create, update, insert, delete and other databases, tables and records on host A, and host B will automatically synchronize and update databases, tables and records.

AB master-slave replication

Working principle and process

(the picture is from the Internet)

Description: 1. Record data changes to the binary log on the main library

two。 The standby library copies the logs on the primary database to its own relay log.

3. The standby database reads the events in the relay log and replays them on the standby database

4. The standby database makes the corresponding operation of the main database according to the contents, and updates the data.

Process:

Host A: create database db-> write this command to the local binary log

Host B: the Imax O thread monitors and reads the additions to the binary log file on host A, and writes the updated contents to host B's own relay log file.

SQL thread: read the SQL statements in the center of the relay log file on host B, and execute these SQL statements automatically, eventually creating the db library on host B.

Separation of reading and writing

Principle and working process

There is a close relationship between the master-slave replication of MySQL and the read-write separation of MySQL. First, the master-slave replication is deployed. Only after the master-slave replication is finished, can the data read-write separation be carried out.

To put it simply, read-write separation is written on the master server and read only on the slave server. The basic principle is to let the master database handle transactional queries, while the slave database handles select queries. Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster.

For example, the master database is equivalent to the bank's all-in-one deposit and withdrawal machine, and the slave is equivalent to the ATM. When the user wants to deposit (write data), it operates on the ATM, and when the user wants to withdraw (read), it operates on the ATM.

Read-write separation can be based on either internal program code or middleware (mysql-proxy, amoeba, Atlas) proxy layer.

AB Master-Slave replication and read-write Separation to achieve High availability deployment of Database

Experimental topology diagram:

Environment: centos7

Master database server (master): 192.168.11.11

From the database server (slave): 192.168.11.12

Proxy server: 192.168.11.13

One. AB master-slave replication deployment

Deployment ideas:

1. Turn on the binlog binary log file function in the / etc/my.cnf main configuration file on host A, set the server-id unique identification number to the host, and restart the Mariadb service.

two。 Create a user account for AB master-slave replication on host A, and view the position location value of the binlog log file in the master status

3. Set the server-id unique identification code in the / etc/my.cnf main configuration file on host B, and restart the mariadb service

4. Use the help change master to search command on host B to help, and use the change master command to tell host B about the correct happiness of its master owner.

5. Start the mariadb attendant service with start slave on host B, and check the data synchronization status of Ab master-slave replication with show slave status, and make sure that the state of the two threads is yes.

-Operation on host A (192.168.11.11)-

1. Install the database and start the database service

The deployment process can be referred to (without too much explanation): https://blog.51cto.com/14181896/2361492

2. Edit the main configuration file / etc/my.cnf and add the following line 234

3. Restart the service and create a user account for AB master / slave replication (account password is rep), as shown below:

4. View the position location value of the binlog log file in the master status (the position value will change according to the number of restarts)

-Operation on host B (192.168.11.12)-

1. Install and deploy the database service with host A

2. Modify the main configuration file / etc/my.cnf by adding the following:

3. Restart the service to test whether the account rep user can remotely access the database service of the master host

4. In the database, use the change master command to tell host B the correct information of his host master.

CHANGE MASTER TOMASTER_HOST='192.168.11.11', # A host IPMASTER_USER='rep', # user for master-slave replication MASTER_PASSWORD='rep', # password MASTER_PORT=3306 for master-slave replication # the port number is 3306MASTERLOGREGROGFILECTOUBIN.000001 master, # the value MASTER_LOG_POS=403 corresponding to the master status of host A, and # the position value of host A, MASTER_CONNECT_RETRY=10 # the value cannot be connected with the master and the retry time is 10s

5. Use start slave to start the Mariadb attendant service, and use show slave status to check the data synchronization status of AB master-slave replication. Make sure that the status of IO and SQL threads is yes.

6. Test whether the AB master-slave replication is configured successfully, create a library on A (192.168.11.11), and see if host B (192.168.11.12) synchronizes the database

Host A:

Host B:

You can see that the synchronization to the newly created database testrep ends with the master-slave replication configuration!

Two. Using middleware to realize read-write separation operation

There are many kinds of read-write separation middleware. In this experiment, Atlas software is used to achieve read-write separation.

Note: proxy server (192.168.11.13) does not need to install mysql service

Atlas introduction:

Developed by 360, its advantages are:

(1) modify based on mysql-proxy-0.8.2, and the code is completely open source.

(2) it is lightweight and easy to deploy and configure.

(3) support DB read-write separation

(4) support reading load balancer from DB and automatically eliminating faults from DB

(5) support smooth uplink and downlink DB

(6) have better security mechanism (IP filtering, account authentication)

(7), version update, problem follow-up, communication circle are all more active.

Proxy server deployment process:

1. Install and configure the atlas software on 192.168.11.13

Rpm-ivh Atlas-2.2.1.el6.x86_64.rpm (the source code package needs to be downloaded from the Internet. Baidu searches the Atlas rpm source code package)

Echo "PATH=$PATH:/usr/local/mysql-proxy/bin/" > > / etc/profile / / set variable environment

Source / etc/profile / / enable variable environment

Ll / usr/local/mysql-proxy/

Description:

The executable files are all distributed in the bin directory.

1. "encrypt" is used to generate MySQL password encryption, which is used in configuration.

2. "mysql-proxy" is MySQL's own read-write separation agent.

3. "mysql-proxyd" is created by 360. there is a "d" behind it to start, restart and stop the service. It's all carried out by him.

Under the conf directory is the configuration file.

1. "test.cnf" has only one file, which is used to configure the agent and can be edited using vim.

2. Under the lib directory are some packages, as well as the dependencies of Atlas

3. In the log directory, there are logs, such as error messages and other records.

Enter the bin directory and use encrypt to encrypt the password of the database, where the user and password are rep

Cd / usr/local/mysql-proxy/bin/

. / encrypt 01 generates an encrypted password and copies it (above)

Cd / usr/local/mysql-proxy/conf/

Cp-v test.cnf test.cnf.bak / / backup test.cnf configuration file

Complete configuration file content of read-write sharing modified by vi test.conf

Test.cnf read-write separation profile feature description:

1: [mysql-proxy] / / read-write separation agent configuration

6:admin-username = user name of the user / / management interface

9:admin-password = password of the pwd / / administrative interface

12:proxy-backend-addresses = 192.168.100.25 12:proxy-backend-addresses 3306 / / IP address and port number of the master database (readable and writable)

15:proxy-read-only-backend-addresses = 192.168.100.26 virtual 3306 server 1192.168.100.27 3306room2 / / ip, port and weight of the read server

18:pwds = admin:VFnEp9P4Vu4=, rep:VFnEp9P4Vu4= / / username of the backend MYSQL and encrypted password generated by the encrypt command

21:daemon = true / / set to daemon mode (running in the background)

24:keepalive = true / / allow keepalive

27:event-threads = 8 / / number of worker threads is 8

30:log-level = message / / Log level is message message

33:log-path = / usr/local/mysql-proxy/log / / Log file path

45:proxy-address = 0.0.0.0 IP 3306 / / Management interface and port for Atlas snooping

48:admin-address = 0.0.0.0 IP 2345 / / Management interface and port for Atlas snooping

2. Configure Atlas and start the atlas service

/ usr/local/mysql-proxy/bin/mysql-proxyd test start

(restart atlas service: / usr/local/mysql-proxy/bin/mysql-proxyd test restart)

Set mysql-proxyd to boot:

Echo "/ usr/local/mysql-proxy/bin/mysql-proxyd test start" > > / etc/profile

Source / etc/profile

Grab packet verification:

Tcpdump grab package: tcpdump-I ens33-nn tcp port 3306

Create a table on host An and write the data:

B host synchronization

Grab the bag result:

At this point, AB master-slave replication and read-write separation deployment to this end!

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.