catalogue
1. MySQL read-write separation and master-slave replication principle
2. Why do we do read-write separation
3. When should I separate reading from writing
4. Master-slave replication and read-write separation
5. Replication types supported by mysql
6. Working process of master-slave replication
8. Common MySQL read / write separation can be divided into the following two types:
Set up mysql master-slave replication
Build Amoeba to realize read-write separation
1. MySQL read-write separation and master-slave replication principle
Principle of master-slave replication
1. Generate two threads from the library, one I/O thread and one SQL thread;
2. The I / O thread requests the binlog of the main database and writes the binlog log to the relay log file;
3. The master library will generate a log dump thread to transfer binlog to the slave library i/o thread;
4.xSQL thread will read the logs in the relay log file and parse them into specific operations to achieve consistent master-slave operations and consistent final data;
Read write separation principle
The basic principle of read-write separation is to let the main database handle transactional add, change and DELETE operations (INSERT, UPDATE and DELETE), while the SELECT query operation is processed from the database. Database replication is used to synchronize changes caused by transactional operations to slave databases in the cluster.
2. Why do we do read-write separation
Because the "write" (writing 10000 pieces of data may take 3 minutes) operation of the database is time-consuming.
However, the "read" of the database (it may take only 5 seconds to read 10000 pieces of data).
Therefore, the separation of reading and writing solves the problem that the writing of the database affects the efficiency of query
3. When should I separate reading from writing
The database does not have to be read-write separated. If the program uses more databases, less updates and more queries, it will be considered. The use of database master-slave synchronization and read-write separation can share the pressure of the database and improve the performance.
4. Master-slave replication and read-write separation
In the actual production environment, the reading and writing of the database are in the same database server, which can not meet the actual needs. Whether in security, high availability or high concurrency, it can not meet the actual needs. Therefore, master-slave replication is used to synchronize data, and then read-write separation is used to improve the concurrent load capacity of the database. It is somewhat similar to rsync, but the difference is that rsync backs up disk files, while mysql master-slave replication backs up data and statements in the database.
5. Replication types supported by mysql
(1) STATEMENT: STATEMENT based replication. Execute sql statements on the server and the same statements on the slave server. mysql adopts STATEMENT based replication by default, with high execution efficiency.
(2) ROW: ROW based replication. Copy the changed content instead of executing the command from the server.
(3) MIXED: MIXED type replication. By default, statement based replication is adopted. Once it is found that statement based replication cannot be accurately copied, row based replication will be adopted.
6. Working process of master-slave replication
(1) Before each transaction updates data, the Master records these changes in the Binary log. After writing the Binary log, the Master notifies the storage engine to commit the transaction.
(2) Slave copies the of the Master to the Relay log. First, slave starts a worker thread (I/O). The I/O thread opens a common connection on the Master, and then starts the binlog dump process. Binlog dump process reads events from the binary log of the Master. If it has kept up with the Master, it will sleep and wait for the Master to generate new events. The I/O thread writes these events to the Relay log.
(3) SQL slave thread is the last step of the process. The SQL thread reads events from the relay log, replays the events and updates the Slave data to make it consistent with the data in the Master. As long as the thread is consistent with the I/O thread, the relay log is usually located in the OS cache, so the overhead of relay log is very small.
There is a very important limitation in the replication process, that is, the replication is serialized on the Slave, that is, the parallel update operation on the Master cannot be operated in parallel on the Slave.
Copy frame
8. Common MySQL read / write separation can be divided into the following two types:
1) Internal implementation based on program code
In the code, routing is classified according to select and insert. This kind of method is also the most widely used in production environment.
The advantage is better performance, because it is implemented in program code, and there is no need to add additional equipment for hardware expenditure; The disadvantage is that it needs developers to implement it, and the operation and maintenance personnel have no way to start.
However, not all applications are suitable for realizing read-write separation in program code. For example, some large and complex Java applications, if reading-write separation is realized in program code, the code will be greatly changed.
2) . implementation based on intermediate agent layer
The proxy is generally located between the client and the server. After receiving the client request, the proxy server forwards it to the back-end database through judgment. There are the following representative programs.
(1)MySQL-Proxy. MySQL proxy is an open source MySQL project, and SQL judgment is performed through its own lua script.
(2)Atlas. It is a data middle tier project based on MySQL protocol developed and maintained by the infrastructure team of Qihoo 360's Web Platform Department. Based on MySQL proxy version 0.8.2, it optimizes it and adds some new features. The MySQL service run by atlas in 360 carries billions of read and write requests every day. Support things and stored procedures.
(3)Amoeba. Developed by Chen Siru, the author once worked for Alibaba. The program is developed by the Java language and Alibaba uses it in the production environment. However, it does not support transactions and stored procedures.
Because a large number of Lua scripts need to be written to use MySQL Proxy, these Luas are not ready-made, but need to be written by themselves. This is very difficult for people who are not familiar with MySQL Proxy built-in variables and MySQL Protocol.
Amoeba is a very easy to use and portable software. Therefore, it is widely used in the agent layer of database in production environment.
9. amoeba profile
Master profile: $AMOEBA_HOME/conf/amoeba.xml is used to configure the basic parameters of Amoeba service, such as Amoeba host address, port, authentication method, user name, password, number of threads, timeout, location of other configuration files, etc.
Database service profile: $AMOEBA_HOME/conf/dbServers.xml is used to store and configure the information of the database server represented by Amoeba, such as host IP, port, user name, password, etc.
Segmentation rule profile: $AMOEBA_HOME/conf/rule.xml, used to configure segmentation rules.
Database function configuration file: $AMOEBA_HOME/conf/functionMap.xml is used to configure the processing methods of database functions. Amoeba will use the methods in the configuration file to parse database functions.
Segmentation rule function configuration file: $AMOEBA_HOME/conf/ruleFunctionMap.xml is used to configure the processing method of user-defined functions used in segmentation rules.
Access rule profile: $AMOEBA_HOME/conf/access_list.conf, which is used to authorize or prohibit some server IP S from accessing Amoeba.
Log specification profile: $AMOEBA_HOME/conf/log4j.xml is used to configure the level and mode of Amoeba's output log.
Actual operation
1. Environment configuration
master Server: 192.168.241.129 mysql5.7 slave1 Server: 192.168.241.133 mysql5.7 slave2 Server: 192.168.241.134 mysql5.7 Amoeba Server: 192.168.241.136 jdk1.6,Amoeba Client server: 192.168.241.128 mysql
2. Initial environmental preparation
systemctl stop firewalld setenforce 0
Set up mysql master-slave replication
Setup time synchronization:
[root@localhost ~]#yum install ntp -y
#Install the time synchronization server. After the master and slave are installed
Configure master server [root@localhost ~]#vim /etc/ntp.conf #Modify profile server 127.127.241.0 #Set local clock source fudge 127.127.241.0 stratum 8 #Set the time level to 8 and limit it to 15 server 127.127.241.0 fudge 127.127.241.0 stratum 8 [root@localhost ~]#service ntpd start #Open service
Configure slave server [root@localhost ~]#yum install ntpdate -y #Install synchronization service [root@localhost ~]#service ntpd start #Open service Redirecting to /bin/systemctl start ntpd.service [root@localhost ~]#/usr/sbin/ntpdate 192.168.241.129 #Perform synchronization 4 Dec 13:21:17 ntpdate[70994]: the NTP socket is in use, exiting [root@localhost ~]#crontab -e */30 * * * * /usr/sbin/ntpdate 192.168.241.129
Configure master-slave
######Open binary log#### ###master server##### [root@localhost ~]#vim /etc/my.cnf [mysqld] server-id = 1 log-bin=master-bin #Open binary log binlog_format=MIXED #Binary log format log-slave-updates=true #Turn on synchronization from server log-bin=master-bin binlog_format=MIXED log-slave-updates=true
[root@localhost ~]#systemctl restart mysqld.service [root@localhost ~]#mysql -uroot -pWx12345@ grant replication slave on *.* to 'myslave'@'192.168.241.%' identified by 'Wx12345@'; flush privileges; show master status;
####from server###### [root@localhost ~]#vim /etc/my.cnf [mysqld] server-id = 2 #Modify. Note that the id is different from that of the Master, and the IDs of the two Slave should also be different relay-log=relay-log-bin #Add, enable the relay log, and synchronize the log file records from the primary server to the local server relay-log-index=slave-relay-bin.index #Add and define the location and name of relay log files, which are generally in the same directory as relay log [root@localhost ~]# systemctl restart mysqld.service [root@localhost ~]#mysql -uroot -pWx12345@ help change master to change master to master_host='192.168.241.129',master_user='myslave',master_password='Wx12345@',master_log_file='master-bin.000002',master_log_pos=604; start slave; show slave status\G
Verify master-slave synchronization. The master server creates a database, and the slave server also has a database create database ky15; show databases;
Build Amoeba to realize read-write separation
##install Java environment## because Amoeba Based on jdk1.5 Developed, so it is officially recommended jdk1.5 Or 1.6 Version, higher version is not recommended. [root@localhost local]#cd /opt/ [root@localhost local]#cp jdk-6u14-linux-x64.bin /usr/local/ [root@localhost local]#chmod +x /usr/local/jdk-6u14-linux-x64.bin [root@localhost local]#cd /usr/local/ [root@localhost local]#./jdk-6u14-linux-x64.bin #Press enter all the way to the end, and finally enter yes for automatic installation
[root@localhost local]#mv jdk1.6.0_14/ jdk1.6 #Change your name [root@localhost local]#vim /etc/profile export JAVA_HOME=/usr/local/jdk1.6 export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin export AMOEBA_HOME=/usr/local/amoeba export PATH=$PATH:$AMOEBA_HOME/bin [root@localhost local]#source /etc/profile #Refresh next [root@localhost local]# java -version Check whether the installation is successful
############## install amoeba ########### 1.#Create the amoeba directory in the / usr/local directory mkdir /usr/local/amoeba 2.#Switch to opt to decompress amoeba cd /opt/ tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba cd /usr/local/ Switch to directory view ls
