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

First, the principle of MySQL master-slave replication:

The master-slave replication of MySQL and the read-write separation of MySQL are inseparable, and the read-write separation of data can only be realized based on the master-slave replication architecture.

1. Replication types supported by MySQL:

(1) statement-based replication. As the name implies, the SQL statement is executed on the master server and the same statement is executed on the slave server. MySQL uses this way of replication by default, which is more efficient.

(2) Row-based replication. Copy the changes instead of executing the command from the master server again.

(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.

The above three types of replication do not require human intervention, and the MySQL database will automatically control them.

2. The working process of replication, as shown in the following figure:

1. Before everything 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 the Binary log of master to the relay log. First slave starts a worker thread, the I _ Binlog dump process O thread, which opens a normal connection on master, and then starts Binlog dump process (the Binlog dump process). Binlog dump process reads events from the master's binary log, and if it has followed master, it sleeps and waits for master to generate new events. The Icano thread writes these events 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 to update the data in slave to make it consistent with the data in master. As long as the thread is consistent with the IWeiO site, the relay log is usually stored in the system's cache, so the overhead of the relay log is small.

A very important limitation of the replication process is that replication is serialized on slave and parallelized on master. To put it bluntly, it is possible that some data updates are made on multiple SQL statements at the same time on master, but when slave replicates, it can only execute SQL statements one by one for data synchronization.

Second, the principle of MySQL read-write separation:

To put it simply, as shown in the following figure, it is written only on the master server and read only on the slave server. The basic principle is to let the master database handle data write and update operations, while the slave database handles select query operations.

The more common MySQL read-write separation is divided into two types:

1. Based on the internal implementation of the program code: route classification is carried out according to select and insert statements in the code. This kind of method is most widely used in the production environment. 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 expenses; the disadvantage is that developers are needed to implement it, and our operation and maintenance staff are unable to start.

2. Implementation based on intermediate proxy: the agent is located between the client and the MySQL server. After receiving the request from the client, the proxy server transmits it to the back-end database after judging it. There are two representative programs for intermediate agents: MySQL-Proxy and amoeba (amoeba).

The differences between the two are as follows:

MySQL-Proxy is an open source MySQL project. SQL is judged by its own lua script. Although it is an official product of MySQL, MySQL officials do not recommend it to be applied to the production environment.

Amoeba is developed in the Java language, and Alibaba uses it in a production environment, which does not support transactions and stored procedures.

Although it is a good choice to implement MySQL read-write separation through program code, not all applications are suitable to achieve read-write separation in program code. For some large and complex Java applications, the code will be changed greatly if read-write separation is implemented in program code. Therefore, large and complex applications will generally consider using the proxy layer to implement.

3. Set up MySQL master-slave replication and read-write separation:

The environment is as follows:

.

Preparatory work:

1. Five centos 7 servers, and three master-slave servers need to be installed with MySQL.

2. Related software packages:

MySQL software package: https://pan.baidu.com/s/1u-gF81Un0ZE5QIIjGwH1Sg extraction code: 4i6x

Amoeba software package: https://pan.baidu.com/s/1sHcMTKAPX3A_gulhhaonyw extraction code: yzbx

3. Ensure that the network is smooth, intercommunicated and firewalls release traffic.

For MySQL installation, please refer to: https://blog.51cto.com/14227204/2425596

Build master-slave replication (establish time synchronization environment):

Configure the primary server:

[root@mysql /] # yum-y install ntp # install ntp [root@mysql /] # vim / etc/ntp.conf.... / / omit the following two lines: server 127.127.1.0fudge 127.127.1.0 stratum 8 [root@mysql /] # systemctl restart ntpd [root@mysql /] # systemctl enable ntpd [root@mysql /] # vim / etc/my.cnf.. server_id = 11 # modify ID should be distinguished from server log_bin = master-bin # modify log-slave-updates = true # add [root@mysql /] # systemctl restart mysqld # restart service to make configuration effective [root@mysql /] # mysql-u root-p Enter password: # enter password mysql > grant replication slave on *. * to 'myslave'@'192.168.1.%' identified by' 123123' Mysql > flush privileges;mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | master-bin.000001 | 552 | +- -+ 1 row in set (0.00 sec) # file column displays the log name The position column shows the offset, which you need to use when configuring the slave server later

Configure the slave server:

[root@mysql /] # yum-y instal ntpdate # install the ntpdate tool [root@mysql /] # ntpdate 192.168.1.10 # for time synchronization 23 Sep 18:06:22 ntpdate [6959]: no server suitable for synchronization found [root@mysql /] # vim / etc/my.cnf. Server_id = 22 # ID remember to distinguish it from the master server relay-log = relay-log-bin # add relay-log-index = slave-relay-bin.index # add [root@mysql /] # systemctl restart mysqld # restart to make the configuration effective [root@mysql /] # mysql-u root-p Enter password: # Log in to mysql to configure synchronization mysql > change master to master_host='192.168.1.10' Master_user='myslave',master_password='123123',master_Log_file='master-bin.000001',master_log_pos=552 # change the mater_log_file and mater_log_pos parameters mysql > start slave in the above command according to the result of the master server # start synchronization mysql > show slave status\ gateway * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.10 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 552 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 284 Relay_Master_Log_File: master-bin.000001 Slave_ IO_Running: Yes # make sure these two behaviors yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB:

Do the same configuration on another slave server and synchronize to the master server. I won't explain much here.

Set up read-write separation:

Install the Java environment on the host Amoeba:

[root@localhost media] # lsamoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin [root@localhost media] # cp * / usr/src/ [root@localhost media] # cd / usr/src/ [root@localhost src] # chmod + x jdk-6u14-linux-x64.bin [root@localhost src] #. / jdk-6u14-linux-x64.bin # enter yes after yes appears Enter. Do you agree to the above license terms? [yes or no] yesPress Enter to continue. # enter Done for this line. # description: [root@localhost src] # ls # there will be an extra directory under this directory: amoeba-mysql-binary-2.2.0.tar.gz debug jdk1.6.0_14 jdk-6u14-linux-x64.bin kernels [root@localhost src] # mv jdk1.6.0_14/ / usr/local/jdk1.6 [root@localhost src] # vim / etc/profile... # cut to the last line Enter the following five lines of export JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/ binding [root @ localhost src] # source / etc/profile # execute s to make the configuration effective [root@localhost /] # java-version # View Java Version java version "1.6.0y14" Java (TM) SE Runtime Environment (build 1.6.0_14-b08) Java HotSpot (TM) 64-Bit Server VM (build 14.0-b16) Mixed mode)

Open permissions to amoeba access in master, slave1 and slave2:

Mysql > grant all on *. * to test@'192.168.1.%' identified by '123.com'

Install and configure the Amoeba software:

[root@localhost /] # mkdir / usr/local/amoeba [root@localhost /] # cd / usr/src/ [root@localhost src] # tar zxf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/ [root@localhost /] # chmod-R 755 / usr/local/amoeba/ # give amoeba directory permissions [root@localhost /] # / usr/local/amoeba/bin/amoebaamoeba start | stop # displaying this content indicates that [root@localhost /] # vim / usr/local/amoeba/conf/amoeba.xml # edits the configuration file successfully. Amoeba # modifies the user and password for client login (search user is available) 123456.. . ${amoeba.home} / conf/functionMap.xml 1500 master # modified to master master # modified to master slaves # modified to slaves # the above two lines have been commented True needs to be removed from the comments

Edit the dbServers/xml file

[root@localhost /] # vim / usr/local/amoeba/conf/dbServers.xml.. Change test # to test 123.com # to set the password and remove the comments.. # modify the name 192.168.1.10 # set the host IP 192.168.1.20 192.168.1.30 1 slave1 Slave2 [root@localhost /] # / usr/local/amoeba/bin/amoeba start& # start the service

Next, start the test:

Install mysql on the client host:

[root@localhost /] # yum-y install mysql [root@localhost /] # mysql-u amoeba-p123456-h 192.168.1.40-P8066 # Login access # create a table in the master host test library and synchronize it to each slave server mysql > create table zang (id int (10), name varchar (10)); # then turn off the slave function on each slave server and insert the distinguishing statement mysql > insert into zang values ('3 slave parallelism wangwu`) # mysql > stop slave; on the master server # disable the slave function mysql > insert into zang values ('1slave dongzhangsan'); # Slave server 1 MySQL > insert into zang values (' 2slave daidonglisi`); # Slave server 2

Test the read operation:

MySQL [test] > select * from zang; # query +-+-+ for the first time | id | name | +-+-+ | 1 | zhangsan | +-+-+ 1 row in set [test] > select * from zang # second query +-+ | id | name | +-+-+ | 2 | lisi | +-+-+ 1 row in set (0.00 sec) MySQL [test] > select * from zang # third query +-+ | id | name | +-+-+ | 1 | zhangsan | +-+-+ 1 row in set (0.00 sec)

Test the write operation:

Insert a piece of data on the client host:

MySQL [test] > insert into zang values; Query OK, 1 row affected (0.01 sec) MySQL [test] > select * from zang; # No +-+-+ on client | id | name | +-+-+ | 2 | lisi | +-+-+ mysql > select * from zang # only found on the master master service, the slave server cannot synchronize with +-+-+ because the salve function is turned off | id | name | +-+-+ | 3 | wangwu | | 4 | zholiu | +-+-+ 3 rows in set (0.01 sec)

In this way, the read and write separation of MySQL is realized, all the write operations are on the master master server, to avoid data asynchronism, and all read operations are allocated to the slave server to share the pressure on the server.

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.