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

In the actual production environment, if the database is read and written in the same database server, it can not meet the actual needs in terms of security, high availability, high concurrency and so on. Therefore, generally speaking, the data is synchronized by master-slave replication (Master-Slave). Then through the separation of read and write to improve the concurrent load capacity of the database such a scheme to deploy and implement.

1. The principle of MySQL master-slave replication

There is a close relationship between the master-slave replication of MySQL and the read-write separation of MySQL. First of all, 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. The 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. 2) Row-based replication. Copy the changes instead of executing the command from the 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) replication process 1) before each transaction 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 its relay log. First, Slave starts a worker thread-- the I _ swap O thread, which opens a normal connection on Master, and then starts Binlog 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 to update the data in Slave to make it consistent with the data in Master. As long as the thread is consistent with the Ibank O thread, the relay log is usually in the cache of OS, so the overhead of the relay log is 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. 2. The principle of MySQL read-write separation.

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.

Based on the implementation of the intermediate proxy layer: the agent 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. There are two representative programs.

(1) MySQL-Proxy. MySQL-Proxy is an open source project for MySQL. SQL is judged by its own lua script. Although it is an official product of MySQL, MySQL officials do not recommend using MySQL in a production environment. (2) Amoeba. Developed by Chen Silu, the author used to work for Alibaba. The program is developed by Java language and used in production environment by Alibaba. It does not support transactions and stored procedures.

-after the above simple comparison, it is naturally 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. Like some large and complex Java applications, if you implement read-write separation in program code, the code will be changed greatly. Therefore, applications like this will generally consider using the proxy layer to implement. Experimental topology diagram

Experimental environment host operating system IP address main software MasterCentOS 7.3192.168.100.137mysql-5.5.24.tar.gzSlave1CentOS 7.3192.168.100.132mysql-5.5.24.tar.gzSlave2CentOS 7.3192.168.100.149mysql-5.5.24.tar.gzAmoebaCentOS 7.3192.168.100.150amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin client CentOS 7.3192.168.100.148mysql -5.5.24.tar.gz experimental deployment 1. Build MySQL master-slave replication (1) establish time synchronization environment Build a time synchronization server on the master node

1) install NTP

# yum-y install ntp

2) configure NTP

# vim / etc/ntp.conf server 127.127.100.0 / / Local clock source fudge 127.127.100.0 stratum 8 / / set the time level to 8 (limited to 15)

3) restart the service and turn off the firewall and enhance security settings

# service ntpd restart#systemctl stop firewalld.service#setenforce 0 (2) perform time synchronization on the slave node and turn off the firewall and enhance security function # yum install ntpd start#systemctl stop firewalld.service#setenforce 0#/usr/sbin/ntpdate 192.168.100.137 / / perform time synchronization (3) install the MySQL database on Master, Slave1, Slave2. The specific process will not be detailed here. (4) configure MySQL master server

1) modify or add the following in / etc/my.cnf and restart the service

# vim / etc/my.cnfserver-id = 11log-bin=master-bin / / Master server log file log-slave-updates=true / / Slave server update binary log # systemctl restart mysqld.service

2) Log in to MySQL to authorize the slave server

# mysql-u root-pmysql > GRANT REPLICATION SLAVE ON *. * TO 'myslave'@'192.168.100.%' IDENTIFIED BY' 123456candidate MySQL > FLUSH PRIVILEGES;mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +- -+-+ | master-bin.000001 | 339 | +- -+ 1 row in set (0.00 sec) (5) configure slave server (Slave1) Slave2)

1) modify or add the following in / etc/my.cnf

# vim / etc/my.cnfserver-id = 22 / / Note here that server-id cannot be the same as the master 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

2) restart the MySQL service

# systemctl restart mysqld.service

3) Log in to MySQL and configure synchronization. Change the parameters of master_log_file and master_log_pos in the following command according to the result of the primary server

# mysql-u root-pmysql > change master to master_host='192.168.100.137',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=339

4) start the synchronization, check the Slave status, and make sure the following two values are YES.

Mysql > start slave

(6) Test the effect of master-slave replication.

1) create a new database db_test on the primary server.

Mysql > create database db_test

2) if you view the database on the master server and the slave server respectively and show that the database is the same, the master-slave replication is successful.

2. Build MySQL read-write separation (1) turn off firewall and enhance security function # systemctl stop firewalld.service#setenforce 0 (2) copy jdk software package and install # cp jdk-6u14-linux-x64.bin / usr/local/#cd / usr/local#./jdk-6u14-linux-x64.bin / / yes after enter (3) modify jdk directory name # mv jdk1.6.0_14/ / usr/local/jdk1.6 (4) add jdk, jre, Amoeba environment variable # vi / etc/profileexport 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/bin (5) refresh environment variable # source / etc/profile (6) create amoeba working directory # mkdir / usr/local/amoeba (7) extract the amoeba package to the working directory The following # tar zxvf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/ (8) modify amoeba directory permissions # chmod-R 755 / usr/local/amoeba/ (9) verify whether amoeba is successfully installed # / usr/local/amoeba/bin/amoeba / / if amoeba start is displayed | stop indicates that the installation is successful (10) in Master, Open permissions in Slave1 and Slave2 to Amoeba to access mysql > grant all on *. * to test@'192.168.100.%' identified by '123.com' (11) go back to the amoeba server and modify the amoeba.xml configuration file # cd / usr/local/amoeba#vim conf/amoeba.xml---30 line-- amoeba / / user name that accesses the amoeba-32 lines-- 123456 / / password-117-uncomment-master / / default primary server master / / write primary server slaves / / read as slaves pool Existing slave1, slave2 server (12) modify dbServers.xml configuration file-- 26-29murf-uncomment-- test 123.commur42-master server address-- 192.168.100.137murmur52-slave server host name-55-slave server address-192.168.100.132mur57-slave server host name-- 60-slave server address-192.168.100.149-end-slave1 Slave2 (13) starts amoeba service # / usr/local/amoeba/bin/amoeba start& (14) View java port opening Its default port is tcp8066# netstat-anpt | grep java (15) install MySQL on the client and log in to the Amoeba server (turn off the firewall and enhance security settings) # yum install-y mysql#mysql-u amoeba-p123456-h 192.168.100.100-P8066#systemctl stop firewalld.service#setenforce 0 (16) Test read-write separation

1) create a table on Master, synchronize it to each slave server, then turn off the Slave function of each slave server, and then insert a distinction statement

# mysql-u root-pmysql > use db_test;mysql > create table zang (id int (10), name varchar (10), address varchar (20)); / / Master server mysql > stop slave; / / Slave server mysql > insert into zang values

2) synchronize the table from the server and insert other contents manually

Mysql > use db_test; / / slave server 1mysql > insert into zang values; mysql > use db_test; / / slave server 2mysql > slave 2')

3) Test read operation

4) Test write operation

Insert a statement on the Client host:

Mysql > insert into zang values ('5ZHANGZHANGZHANGZHANGZHANGJI Writebasket test`)

However, it cannot be queried in Client, and in the end, the content of this statement can only be seen on Master, indicating that the write operation is on the Master 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.