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

In the actual production environment, if the read and write of the database are all operated in the same database server, it can not meet the actual demand in terms of security, high availability or high concurrency. So how to solve this problem in the actual production environment? This requires mentioning two technologies that are particularly important to mysql databases-master-slave replication and read-write separation.

In the actual environment, a MySQL master server replicates data with two MySQL slave servers, and the front-end application operates on the master server during database write operations; during database read operations, it operates on two slave servers, which greatly reduces the pressure on the master server. As shown in the figure:

First, the principle of MySQL master-slave replication

There is an inevitable relationship between the master-slave replication of MySQL and the read-write separation of MySQL. First of all, the master-slave replication must be deployed. Only when the master-slave replication is completed, can the data read-write separation be carried out.

1) replication types supported by MySQL

(1) statement-based replication: a SQL statement executed on the master server and the same statement executed on the slave server. MySQL uses statement-based replication by default, which is more efficient. But the binary logging function must be enabled.

(2) Line-based replication: copy the changes instead of executing the command from the server.

(3) mixed type replication: statement-based replication is used by default, and row-based replication is adopted once it is found that statement-based replication cannot be accurately replicated.

2) the working process of replication

The working process of MySQL replication is shown in the figure:

The working process of MySQL replication is described in detail:

1. Before each thing updates the data, Master records these changes in the binary log. After writing to the binary log, Master notifies the storage engine to commit the transaction

2.Slave copies Master's Binary log (binary log) to its Relay log (relay log). First Slave starts a working process-- the Icano thread, which opens a normal connection on the Master, and then starts the Binlog dump process (binary log dump process). Binlog dump process reads events from Master's binary log, and if it has caught up with Master, it sleeps and waits for Master to generate new events. The Icano thread writes these times to the relay log.

3.SQL slave thread (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log and replays the events in it to update the data of Slave to make it consistent with the data in Master. As long as the thread is consistent with the IBO thread, the relay log is usually in the OS cache, so the overhead of the relay log is very small.

A very important limitation of the replication process is that replication is serialized on Slave, that is, parallel update operations on Master cannot be operated in parallel on Slave.

Second, the principle of MySQL separation of reading and writing.

Simply put, read-write separation means writing only on the master server and reading only on the slave server. The basic principle is to have the master database handle transactional queries and the slave database to handle select queries. Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster. As shown in the figure:

At present, there are two common types of MySQL read-write separation:

1) based on the internal implementation of program code

Route classification is carried out according to select and insert in the code, which is also the most widely used in production environment at present. The advantage is that the performance is better, because it is implemented in the program code, there is no need to add additional equipment as hardware development, the disadvantage is that developers are needed to implement it, and the operators do not know how to start.

2) implementation based on intermediate agent layer

The agent is generally located between the client and the server. After receiving the request from the client, the proxy server forwards it to the back-end database through judgment. There are two representative programs:

(1) MySQL-Proxy: for the MySQL open source project, SQL judgment is made through its own lua script, although it is the official product of MySQL. But MySQL officials do not recommend using MySQL-Proxy in production environments.

(2) Amoeba (amoeba): developed by Chen Silu, whose layer works in Alibaba. The program is developed in Java language and used in production environment by Alibaba. It does not support transactions and stored procedures

After the above simple comparison, it is a good choice to achieve MySQL read-write separation through program code, but not all applications are suitable to achieve read-write separation in program code. For example: some large and complex Java applications, if you implement separation in the program code, the code will change greatly. Therefore, complex applications like this will generally consider using the proxy layer to implement.

Third, set up MySQL master-slave replication

The experimental extension is as follows:

The general steps are as follows:

(1) MySQL master server establishes time synchronization

(2) MySQL synchronizes time from the server

(3) turn off the firewall

(4) install MySQL database

(5) configure MySQL master server

(6) configure MySQL slave server

(7) verify the effect of master-slave replication.

(1) MySQL master server establishment time synchronization [root@localhost ~] # yum-y install ntp / / install NTP [root @ localhost ~] # vim / etc/ntp.conf / / write the configuration file of the ntp service. / / omit part of the content Fill in the following content: server 127.127.1.0 / need to synchronize the upper server (itself) fudge 127.127.1.0 stratum 8 / / synchronize the number of layers of the upper server (size cannot exceed or equal to 16) [root@localhost ~] # systemctl restart ntpd / / restart the ntp service (2) MySQL performs time synchronization from the server

Both servers need to synchronize their time!

[root@localhost ~] # yum-y install ntpdate [root@localhost ~] # ntpdate 192.168.1.1 ntpdate / install the ntpdate tool and specify the ntp server to achieve synchronization (3) turn off the firewall (or open the corresponding port)

Because of the experimental environment, the forced shutdown of the firewall is used here. Turn off the firewall on every server!

[root@localhost ~] # systemctl stop firewalld// turn off firewall (4) install MySQL database

To install MySQL database, please refer to the blog article: compile and install MySQL database system

After the installation is complete, start the MySQL database

[root@localhost ~] # systemctl start mysqld// starts the MySQL database [root@localhost ~] # netstat-anpt | grep mysqldtcp6 00: 3306: * the listening port of the LISTEN 3263/mysqld / / mysql database is TCP/3306, check it! [root@localhost ~] # mysqladmin-u root password '123456 / set password for MySQL database administrator "root"

Both master and slave servers need to install MySQL database, start and set password!

(5) configure MySQL master server [root@localhost ~] # vim / etc/my.cnf// to write the configuration file of MySQL database. / / omit part of the content and fill in the following content: server_id = 11Universe / server ID number Customized but cannot conflict with other MySQL servers log_bin = master-bin// defines the file name of the bin-log (stored in the data directory by default) log-slave-updates = true// enables automatic backup from the server [root@localhost ~] # systemctl restart mysqld// restart MySQL database [root@localhost ~] # mysql-u root-pEnter password: / / log in to MySQL database mysql > grant replication slave on *. * to 'myslave'@ '192.168.1% 'identified by' 123456' Query OK, 0 rows affected (0.00 sec) / / authorized user myslave, password is 123456, have replication permissions for all databases and tables on all MySQL servers on the 192.168.1.0 network segment mysql > flush privileges;Query OK, 0 rows affected (0.00 sec) / / refresh permission mysql > show master status / / View the status of the master server +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed _ Gtid_Set | +-+ | master-bin.000001 | 410 | | | +-+ 1 row in set (0.00 sec) / / File column displays the log name | Position column display offset (6) configure MySQL slave server

The configuration of the two slave servers is almost the same!

[root@localhost ~] # vim / etc/my.cnf// write the configuration file of MySQL database. / / omit part of the content and fill in the following content: server_id = 22 MySQL / server ID number, customized but not conflicting with other MySQL servers. Relay-log = relay-log-bin// defines the relay log file name and path (saved in the data directory by default) relay-log-index = slave-relay-bin.index// defines the relay log index file name (many relay log files are recorded) / / default The relay log uses a file name in the form of host_name-relay-bin.nnnnnn, where host_name is the hostname of the slave server and nnnnnn is the sequence number. / / create a continuous relay log file with a consecutive serial number, starting at 000001. Track the relay log currently in use in the index file from the server. The default relay log index file name is host_name-relay-bin.index. By default, these files are created in the data directory of the slave server. [root@localhost ~] # systemctl restart mysqld// restart MySQL database [root@localhost ~] # mysql-u root-pEnter password: / / Log in to MySQL database mysql > change master to master_host='192.168.1.1',master_user='myslave',- > master_password='123456',master_log_file='master-bin.000001',master_log_pos=410 Query OK, 0 rows affected, 2 warnings (0.02 sec) / / specify the IP address of the primary service, login user name, password, specified synchronization log, offset mysql > start slave;Query OK, 0 rows affected (0.00 sec) / / start synchronization mysql > show slave status\ Gramp / check Slave status, make sure the following two items are "Yes" … / / omit some of the contents and fill in the following content: Slave_IO_Running: Yes / / Yes O thread startup Slave_SQL_Running: Yes / / SQL thread startup … / / omit some of the contents and fill in the following

After the configuration of the two slave servers! You can test it!

(7) verify the effect of master-slave replication.

① logs in to the MySQL database on the master and slave servers to view the database

[root@localhost ~] # mysql-u root-pEnter password: mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | test | +-+ 4 rows in set (0.10 sec)

② creates a new database on the primary server

Mysql > create database qq;Query OK, 1 row affected (0.00 sec)

③ views the database on the master server and the slave server respectively.

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | qq | | test | +-+ 5 rows in set (0.00 sec)

If the database is the same, the master-slave replication is successful!

Fourth, set up MySQL to separate read and write

The foundation of MySQL read-write separation is MySQL master-slave replication. All the following operations are based on mysql master-slave replication.

The software used to build MySQL read-write separation is Amoeba software. A brief introduction:

The open source framework for the Amoeba project released an Amoeba for MySQL software in 2008. This software is dedicated to the front-end agent of MySQL's distributed database. It mainly acts as a SQL routing function for the application layer to access MySQL, and has load balancing, high availability, SQL filtering, read-write separation, routing related to the target database, and can request multiple databases concurrently. The functions of high availability, load balancing and data slicing of multiple data sources can be achieved through Amoeba. At present, Amoeba has been used in the production lines of many enterprises, and its version can be downloaded on the official website.

(1) install the Java environment on the Amoeba server

Note: Amoeba software should never be installed on any mysql server!

Because Amoeba is based on jdk1.5, jdk1.5 or version 1.6 is officially recommended. The higher version is not recommended! View the default Java environment for the Centos 7 system:

[root@localhost ~] # java-versionopenjdk version "1.8.0x102" OpenJDK Runtime Environment (build 1.8.0_102-b14) OpenJDK 64-Bit Server VM (build 25.102-b14, mixed mode)

Therefore, you also need to redeploy the java environment, using the package network disk link: https://pan.baidu.com/s/1sHwbvlIZ2VDLb_qw-b_z4w

Extraction code: rs1x

Do the following:

[root@localhost ~] # cp jdk-6u14-linux-x64.bin / usr/local [root@localhost ~] # chmod + x / usr/local/jdk-6u14-linux-x64.bin [root@localhost ~] # cd / usr/local [root@localhost local] #. / jdk-6u14-linux-x64.bin / / enter yes and enter [root@localhost local] # mv jdk1.6.0_14/ jdk1.6 when prompted / / rename the jdk installation directory [root@localhost ~] # vim / etc/profile / / write a global environment variable configuration file. / / omit part of the content Fill in the following export JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoeba/export PATH=$PATH:$AMOEBA_HOME/ [root @ localhost ~] # source / etc/profile [root@localhost ~] # java-versionjava version "1.6.0o14" Java (TM) SE Runtime Environment (build 1.6.0_14-b08) Java HotSpot (TM) 64-Bit Server VM (build 14.0-b16 Mixed mode) / / the current Java environment of the system is version 1.6 Can be used! (2) install and configure Amoeba software

Link to the network disk of Amoeba software: https://pan.baidu.com/s/1m-gBbCW4SxTt4b5NvQngQQ

Extraction code: w93m

Do the following:

[root@localhost ~] # mkdir / usr/local/amoeba [root@localhost ~] # tar zxf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/ [root@localhost ~] # chmod-R 755 / usr/local/amoeba/ [root@localhost ~] # / usr/local/amoeba/bin/amoebaamoeba start | if amoeba is installed successfully, Amoeba read / write separation is configured and read load balancing is realized between two Slave

Open permissions to Amoeba access in ① Master, Slave1 and Slave2. The commands are as follows:

Mysql > grant all on *. * to 'test'@'192.168.1.%' identified by' 123.composit query OK, 0 rows affected (0.00 sec)

② edits the amoeba.xml configuration file. This profile mainly defines the master-slave server address pool.

[root@localhost] # vim / usr/local/amoeba/conf/amoeba.xml... / / because there is so much content, each item edited adds the actual line number 30 amoeba31 32 123456 / specify the account and password when the client accesses the amoeba server! 115 master116 117 master118 slaves// pay attention to cancel the original comment message

③ Editing dbServer.xml configuration File

[root@localhost] # vim / usr/local/amoeba/conf/dbServers.xml... / / because there are so many contents Each item edited adds the actual line number 26 test 27 28 123.com / / specifies the account password used by the amoeba server to access the mysql database 43 44 45 46 i192.168.1.1 47 48 49 50 51 52 53 192.168.1.2 54 55 56 57 58 59 192.168. 1.360 61 / / slave2 configuration requires copying the configuration information of salve1 (line number may not match the actual number) 62 63 64 65 66 1 67 68 69 slave1 Slave2 70 71

After the ④ confirms that it is correct, start the Amoeba software, whose default port is TCP 8066

[root@localhost ~] # / usr/local/amoeba/bin/amoeba start & [root@localhost ~] # netstat-anpt | grep 8066tcp6 0 0:: 8066: * LISTEN 3799/java (5) Test

Due to the experimental environment, we test the effect of read-write separation, the actual environment, easy not to test!

① installs the mysql tool on the client side and accesses mysql through the agent with the following command:

[root@localhost ~] # yum-y install mariadb// Note: since the Centos 7 system, the built-in database is called mariadb, and it is no longer mysql [root@localhost ~] # mysql-u amoeba-h 192.168.1.4-P 8066-pEnter password: MySQL [(none)] > / indicates that the access is successful

② creates a table synchronization on the MySQL master server to the slave server

Mysql > use qq;Database changedmysql > create table Q1 (id int); Query OK, 0 rows affected (0.10 sec)

Stop synchronization on both slave servers and execute the following command:

Mysql > stop slave;Query OK, 0 rows affected (0.01 sec)

Insert data on the primary server:

Mysql > insert into Q1 values ('1'); Query OK, 1 row affected (0.00 sec)

The two ③ synchronized tables from the server and manually inserted a piece of data.

The first slave server

Mysql > use qq;Database changedmysql > insert into Q1 values ('2'); Query OK, 1 row affected (0.00 sec)

The second slave server

Mysql > use qq;Database changedmysql > insert into Q1 values ('3'); Query OK, 1 row affected (0.00 sec)

④ test read operation

Query the result for the first time on the client:

MySQL [(none)] > select * from qq.q1;+-+ | id | +-+ | 3 | +-+ 1 row in set (0.00 sec)

The result of the second query on the client:

MySQL [(none)] > select * from qq.q1;+-+ | id | +-+ | 2 | +-+ 1 row in set (0.00 sec)

It will always be these two results!

In this way, the load balance of read data is realized, and the effect is obvious!

⑤ test write operation

Insert a piece of data on the client, but cannot query it on the client!

MySQL [qq] > insert into Q1 values ('4'); Query OK, 1 row affected (0.00 sec)

But it can be queried on the main server!

Mysql > select * from Q1 + | id | +-+ | 1 | | 4 | +-+ 2 rows in set (0.00 sec)

It is verified that the separation of MySQL read and write has been realized. At present, all the writes are written on the master server to avoid data asynchronism; all read operations are allocated to the slave server to share the pressure on the database!

-this is the end of this article. Thank you for reading-

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.