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

Mysql master-slave replication and read-write separation

Table of contents:

1. The working principle of mysql master-slave replication

2. The principle of mysql read-write separation, using Amoeba intermediate proxy layer to achieve read-write separation.

3. Achieve mysql master-slave replication + read-write separation in vmware.

1. The working principle of Mysql master-slave replication:

1. Replication types supported by mysql:

1), statement-based replication. Execute the sql statement on the server and execute the same statement on the slave server. Mysql defaults to statement-based replication, which is efficient.

2), row-based replication. Copy the changes instead of executing the command on the slave server.

3), mixed type of replication. Statement-based replication is used by default, and row-based replication is used when it is found that statement-based replication cannot be accurately replicated.

2. The principle of master-slave replication.

The master-slave replication principle of mysql is illustrated according to the figure above:

Update the data on mysql_master, write to the binary log (Binary log), and notify the storage engine to commit the transaction

(2) mysql_slave enables I / O threads to copy the binary log of master to the relay log of slave (Relay log)

(3) mysql_slave enables the slave thread to read events from the Relay log and replay the events in order to update the SQL data and make it consistent with the data in the master. As long as the thread is consistent with the os O thread, the relay log is usually located in the SQL cache, so the overhead of the relay log is very small.

3. The principle of mysql read-write separation.

On the basis of master-slave replication, master update data is synchronized to the slave server, master is only responsible for writing data, and slave only provides reading data, thus realizing the optimization of database performance and improving the security of the server.

The method to achieve the separation of mysql read and write:

1) based on the internal implementation of program code

In the code according to select, insert route classification, this kind of method is also the most widely used in the production environment. The advantage is that the performance is better, because the program is implemented in the code, there is no need for additional hardware expenses, the disadvantage is that developers are needed to implement it, and the operation and maintenance personnel have no way to start.

2) implementation based on intermediate agent layer

The agent is generally between the application server and the database server. After receiving the request from the application server, the proxy database server forwards it to the back-end database according to the judgment. There are following representative programs.

(1) mysql_proxy. Mysql_proxy is an open source project of Mysql that makes sql judgments through its own lua script.

(2) Atlas. It is a data middle tier project based on MySQL protocol developed and maintained by Qihoo 360, the infrastructure team of Web platform Department. It optimizes and adds some new features on the basis of mysql-proxy version 0.8.2. The mysql business run by Atlas internally carries billions of read and write requests every day. Support things and stored procedures.

(3) Amoeba. Developed by Alibaba Group employee Chen Silu using preface java language, Alibaba Group puts its users in the production environment, but he does not support things and storage processes.

Not all applications can achieve read-write separation based on program code, like some large-scale java applications, if you achieve read-write separation in program code, the code will be changed greatly, so applications like this will generally consider using the proxy layer to achieve, then use Amoeba as an example today to complete master-slave replication and read-write separation.

Second, experimental cases

1. Lab topology:

2. Experimental environment:

OS:centos7

Mysql-5.7.13

Ip planning such as topological environment

3. Experimental steps:

First deploy the master-slave replication architecture, then achieve read-write separation, and test the read-write separation using client

3-1:

1), master-slave replication:

Configure ntp and build a time synchronization server on master to ensure time synchronization between master and slave servers.

Configure the local yum:

Install ntp:

Yum-y install ntp

2) configure NTP time server:

Vim / etc/ntp.conf

Add the following configuration:

3) restart the ntpd service:

4) add an exception to the firewall:

5) execution time synchronization of slave1 and slave2 respectively:

Install ntp ntpdate on slave1 slave2 respectively

Execute command synchronization time on salve1 and slave2:

3-2:

1) install Mysql in master and slave1,slave2, respectively, with brief installation steps:

The mysql version is as follows:

2), mysql_master configuration:

Modify the / etc/my.cnf configuration file:

Note:

Server-id=1 # # Note that the master and slave server-id cannot be the same

Log-slave-updates=true: commonly used in online replication

Log-bin=master-bin: specify the binlog log name

Restart the mysqld service

Create a user in master to replicate data and authorize to view master's binlog log information.

4. Configure the slave server (slave1 and slave2 configuration are the same)

1), modify / etc/my.cnf main configuration file:

Note:

Server-id=2: server-id modified to 2

Log-bin=mysql-bin: binary log of slave

Relay-log=relay-log-bin: relay log file name

Relay-log-index=slave-relay-bin.index: the index file name of the relay log

Restart the mysqld service

Systemctl restart mysqld

Note:

Slave2 also performs the above operations.

3), perform change master to operations in salve1 and salve2 respectively to synchronize data:

Port 3306 on which the firewall is turned on on master is an exception:

Note that to remember the binary name and location information of the binlog that executes show master status on master, the following needs to be specified:

Slave2 does the same thing:

4), start slave (both slave1 and slave2 do this)

Mysql > start slave

5) check the slave status and make sure the following two values are YES

The current Slave_IO_Running: value is No. View the error message down:

Based on the above information, we can judge that my environment is in a clone state, so the UUID value of mysql is the same, and use the following methods to solve the problem:

Modify the UUID value in auto.cnf and change a value at will. (same with slave2)

Restart the mysqld service:

Systemctl restart mysqld

Log in to mysql again to view the status of master-slave replication:

According to whether the Slave_SQL_Running and Slave_IO_ running values are Yes status

Salve1:

Slave2:

5. Verify whether the data is synchronized from:

1) create a new database "IT" on the master server

Create the test table and insert the data:

Check on slave1 and slave2 to see if the data is updated:

Slave1:

Slave2:

After the master-slave replication configuration is completed, configure read-write separation:

4. Separation of reading and writing:

1. Install the java environment on Amoeba:

Because Amoeba is based on jdk1.5 development, jdk1.5 or version 1.6 is officially recommended, but not in higher versions.

1) download the execution program of jdk:

2), add x permissions, and execute

Chmod + x jdk-6u14-linux-x64.bin

Cp jdk-6u14-linux-x64.bin / usr/local/

. / jdk-6u14-linux-x64.bin # # execute this program

Prompted during installation (yes/no), select yes to install

Mv jdk1.6.0_14/ / usr/local/jdk1.6

3) modify the / etc/profile configuration file to add the following configuration:

Export JAVA_HOME=/usr/local/jdk1.6 / / set the root directory of jdk

Export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib / / assign the program file of jdk to the CLASSPATH variable

Export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME

/ bin / / assign the jdk program file to the PATH variable

Export AMOEBA_HOME=/usr/local/amoeba / / defines the root directory of AMOEBA

Export PATH=$PATH:$AMOEBA_HOME/bin copies the program file of amoeba to the PATH variable

Such as:

4) effective immediately

2. Install and configure Amoeba software:

# mkdir / usr/local/amoeba / / create an amoeba directory

# tar zxf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/

# chmod-R 755 / usr/local/amoeba/

# / usr/local/amoeba/bin/amoeba

Displaying the above indicates that amoeba is installed successfully.

3. Configure amoeba to achieve read-write separation, master is responsible for writing, slave is responsible for reading, and load balancing

1) Log in to mysql on the three master and slave servers, create amoeba users and authorize them

User amoeba, password amoeba (because master-slave replication was previously configured, there will also be authorized users in slave1 slave2, which can be viewed)

2) modify the amoeba.xml file:

Define two configurations, the first is what user connection amoeba the application uses to access the back-end mysql database, and the second is to define the default write pool and read pool.

Operate on Amoeba:

Vim / usr/local/conf/amoeba.xml

1), modify the user and password to connect to amoeba

2), define read and write pool

3), edit dbServers.xml configuration file

1). Modify the user and password for connecting to mysql:

User amoeba password amoeba # Note to delete comments->

Specify the master slave1 slave2 host address:

Define the load balancer for slave:

4) start amoeba:

4. Test the separation of read and write:

1) install mysql on client for remote database connection testing (installation process is slight, you can use yum-y install mysql)

2) Open port 3306 in master salve1 slave2, and add exceptions to the firewall

Open port 8066 on Amoeba:

Firewall-cmd-permanent-add-port=8066/tcp

Firewall-cmd-reload

3) execute the mysql command on client to log in to amoeba

1), create a database test

2), create table student

Check the master-slave synchronization:

Master:

Slave1:

Slave2:

Insert data into the student table on client to see if the slave is synchronized:

Slave1:

Slave2

2) stop slave the slave1 slave2 and stop the master-slave replication

Insert different data into the student table in slave1 and salve2 respectively to test the read load balancer on client:

Slave1:

Slave2:

Query on client:

As you can see, the first query data is the slave2 database, and the second query is the slave1 database, which realizes the load balancing of the polling algorithm.

3) write data on client, and test write data only on master:

As you can see from the above, data is written to the student table on client, and there is no such data when querying, because the write operation only works on the master, and the read is on the slave.

Check master to see if there is any data that has just been written:

Through the above tests, the master-slave replication and read-write separation deployment of mysql has been completed.

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.