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

Introduction to the experiment of MySQL master-slave replication and read-write separation

In the actual production environment, if the read and write of the database operate on the same database, it can not meet the actual needs in terms of security, high availability or high concurrency, so generally speaking, the scheme is to synchronize data through master-slave replication, and then through read-write separation to improve the concurrent load capacity of the database.

Principle of MySQL master-slave replication

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

2. The Slave server copies the Binary log of the Master to the relay log (Relay log). First Slave starts a worker thread-- the I _ peg O thread, which opens a normal connection on the Master, and then starts Binary log dump process. Binary log dump process reads the time 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 events to the relay log.

3. SQL slave thread (SQL from the thread) handles the last step. 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 thread, the relay log is usually in the system's cache, so the relay log overhead is small.

MySQL principle of separation of reading and writing

Read-write separation means writing 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 databases in the cluster.

Implementation based on the intermediate proxy layer: the proxy is generally located between the client and the server, and the proxy server receives the client request and forwards it to the back-end database through judgment, which is realized by Amoeba.

Case environment

As shown in the following figure

Set up MySQL master-slave replication in the experimental process

1. Establish a time synchronization environment and build a time source server on the master node.

[root@promote ~] # yum install ntp-y [root@promote ~] # vim / etc/ntp.conf # add two lines at the end of the configuration file server 127.127.58.0 # set the local clock source fudge 127.127.58.0 stratum 8 # set the time level to 8 (limited to 15) [root@ Promote ~] # service ntpd restart # restart service [root@promote ~] # systemctl stop firewalld.service # turn off firewall [root@promote ~] # setenforce 0 # turn off enhanced security features

2. Time synchronization is carried out on two slave nodes respectively.

[root@localhost ~] # yum install ntp-y [root@localhost ~] # / usr/sbin/ntpdate 192.168.58.131 # and time source server synchronization 10 Jul 11:07:16 ntpdate [28695]: the NTP socket is in use, exiting [root@loaclhost ~] # systemctl stop firewalld.service # turn off firewall [root@localhost ~] # setenforce 0 # turn off enhanced security

3. Install MySQL, which was mentioned earlier and omitted.

4. Configure the MySQL Master master server.

[root@promote ~] # vim / etc/my.cnfserver-id = 11 # modify server-id Note that three servers id cannot repeat log-bin=master-bin # modify master server log file log-slave-updates=true # add master-slave synchronization function [root@promote ~] # systemctl restart mysqld.service # restart MySQL server

5. Log in to the MySQL service and authorize the slave server.

[root@promote ~] # mysql-u root-pEnter password: mysql > GRANT REPLICATION SLAVE ON *. * TO 'myslave'@'192.168.58.%' IDENTIFIED BY' 123456permission; # Grant the user REPLICATION SLAVE permission of the user name and password of the host in network segment 192.168.58.0 with user name and password, respectively. Mysql > FLUSH PRIVILEGES; # refresh permission settings mysql > show master status; # to check the status of the primary server, the two values of file,position are very important, which will be used later. +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | master-bin.000002 | 2614 | +- -+

6. Configure the MySQL Slave slave server to perform the same operation on both slave servers, except for server-id.

[root@localhost ~] # vim / etc/my.cnfserver-id = 22 # set server-id Three servers cannot be the same relay-log=relay-log-bin # synchronize log files from the master server to the local relay-log-index=slave-relay-bin.index # define the location and name of the relay-log [root@localhost ~] # service mysqld restart [root@localhost ~] # mysql-u root-pEnter password: mysql > change master to master_host='192.168.58.131',master_user='myslave',master_password='123456'' Master_log_file='master-bin.000002',master_log_pos=2614 # this command is used to specify the master server, and the master_log_file and master_log_pos parameters correspond to the above. Mysql > start slave; # enable synchronization mysql > show slave status\ G # check slave status to make sure Slave_IO_Running Slave_SQL_Running are all yex** 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.58.131 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000002 Read_Master_Log_Pos: 2614 Relay_Log_File: relay-log-bin.000005 Relay_Log_Pos: 1955 Relay_Master_Log_File: master-bin.000002 Slave_IO_Running: Yes Slave _ SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master _ Log_Pos: 2614 Relay_Log_Space: 2535 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_ Cipher: Master_SSL_Key: Seconds_Behind_Master: 0

7. Verify the effect of master-slave synchronization. Determine by creating a new database on the primary server and checking to see if the synchronization was successful.

Database in the primary server

Mysql > show databases;+-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | sys | | test1 | +-+ 6 rows in set (0.23 sec)

From the database in the server

Mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | sys | | test1 | | wq | | yxxx | +- -+ 8 rows in set (0.31 sec)

Next, create a new database test02 on the primary server to see if the synchronization is successful.

Mysql > create database test02;Query OK, 1 row affected (3.86 sec) mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | sys | | test02 | | test1 | +-+ 7 rows in set (0.01 sec)

View the database from the server. Synchronization is successful!

Mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | sys | | test02 | | test1 | | wq | | yxxx | +- -+ 9 rows in set (0.15 sec) build MySQL read-write separation

1. Install the Java environment on the Amoeba proxy server because the Amoeba service is developed based on Java1.5.

Systemctl stop firewalld.service # turn off the firewall setenforce 0 # turn off the enhanced security feature cp jdk-6u14-linux-x64.bin / usr/local/ # copy the package to the specified directory. / jdk-6u14-linux-x64.bin # execute the installation script mv jdk1.6.0_14 / / usr/local/jdk1.6 # for convenience Modify the file name vim / etc/profile # to add Java to the environment variable export JAVA_HOME=/usr/local/jdk1.6 # insert the following lines into the file export 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/binsource / etc / profile # refresh the file Make the changes effective

2. Install and configure Amoeba software.

[root@promote ~] # mkdir / usr/local/amoeba # create a working directory for Amoeba [root@promote ~] # tar zxvf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/ # extract [root@promote ~] # chmod-R 755 / usr/local/amoeba/ # modify file permissions [root@promote ~] # / usr/local/amoeba/ Bin/amoeba # execute Amoeba service amoeba start | stop indicates that amoeba is installed successfully

3. Configure Amoeba read-write separation and two Slave read load balancers.

Mysql > grant all on *. * to test@'192.168.58.%' identified by '123.composter # add permissions on three mysql servers to open to amoeba access

4. Go back to the Amoeba server and configure the amoeba.xml configuration file first.

[root@promote ~] # vim / usr/local/amoeba/conf/amoeba.xml amoeba # user name used by the client to log in to the Amoeba server 123456 # password used by the client to log in to the Amoeba server ${amoeba.home} / conf/access_list.conf master# default server pool master#master server for writing slaves#slaves server for reading

Configure the dbServers.xml file

[root@promote ~] # vim / usr/local/amoeba/conf/dbServers.xml test # modified to user name 123.com # for login server pool to password for login server pool # configure master server 192.168.58.13 IP of master server # mysql server of slave server 1 192.168.58.144 # mysql server IP of slave server 1 # from server 2's mysql server 192.168.58.145 # from server 2's mysql server IP # define slave server pool 1 slave1 Slave2# defines that there are two servers from the server pool

5. After configuration, start the Amoeba software, whose default port is tcp 8066.

[root@promote ~] # / usr/local/amoeba/bin/amoeba start & [1] 69919 [root@promote ~] # log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml2018-07-10 16pur53 INFO context.MysqlRuntimeContext-Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf2018-07-10 16:53: 55925 INFO net.ServerableConnectionManager-Amoeba for Mysql listening on 0.0.0.0 INFO net.ServerableConnectionManager-Amoeba Monitor Server listening on / 127.0.0.1: 54818.^ C [root@promote ~] # netstat-ntap | grep java # 8066 port has been opened tcp6 0 0127.0.0.1 54818:: * LISTEN 69919/java tcp6 0 0: 8066:: * LISTEN 69919/java tcp6 0 0 192.168.58.136 ESTABLISHED 69919/java tcp6 41992 192.168.58.145 ESTABLISHED 69919/java tcp6 3306 192.168.58.136 ESTABLISHED 33236 192.168.58.144 ESTABLISHED 69919 / Java tcp6 0 0 192.168.58.136:48134 192.168.58.131:3306 ESTABLISHED 69919/java

6. Test on the client host.

[root@yx Desktop] # mysql-u amoeba-p123456-h 192.168.58.136-P8066mysql > show databases +-+ | Database | +-+ | information_schema | | mysql | | performance_schema | | school | | sys | | test02 | | test1 | +-+ 7 rows in set (0.22 sec) mysql >

Create a table on Master and synchronize it to each slave server.

Mysql > use test1;Database changedmysql > create table zang (id int (10), name varchar (10), address varchar (20))

Turn off the synchronization function of the two slave servers.

Mysql > stop slave;---- on the master server-the content will not synchronize the use test1insert into zang values ('1th slave mastery);-slave server 1----use test1;insert into zang values (' 2slave recorder 2----use test1;insert into zang values');-slave server 2----use test1;insert into zang values ('3slave division 2') -testing on the client-the first time it reads data to slave server 1; the second time it reads mysql > select * from test1.zang to slave server 2 -+ | id | name | address | +-+ | 3 | zhang | this_is_slave2 | +-+ 3 rows in set (0.03 sec) mysql > select * from test1.zang +-+ | id | name | address | +-+ | 2 | zhang | this_is_slave1 | +-+ 3 rows in Set (0.25 sec)-only the primary server records mysql > insert into zang values ('5') for data written after connecting to the database through the client 'zhang','write_test') If you look at the table in the master server, you will find that there are two records, while there is only one record in the slave server Description: read-write separation +-+ | id | name | address | +-+ | 1 | zhang | this_is_master | | 5 | zhang | write_test | +-- +-+ 3 rows in set (0.01sec)

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.