1, Master slave replication
There is a close relationship between master-slave replication of MySQL and read-write separation of MySQL. First, deploy master-slave replication, only master-slave replication
Only after the replication is completed can the data be read and written separately on this basis.
1. Principle
Core of master-slave replication: 2 logs and 3 threads
master thread: bin log, binary log, dump thread
The dump thread on the master monitors the update of the bin log binary log. If there is any update, it will notify the I/O thread of the slave
slave: relay log, relay log, I/O thread, SQL thread
Thread 1:
The I/O thread on the slave will apply to the master to synchronize the update contents of the bin log binary log, and the I/O thread of the slave will update the binary log
Write the content to your own relay log
Thread 2:
The SQL thread of slave synchronously executes the update statements in the log to the content to achieve consistency with the master database
2. Replication types supported by mysql
Statement based replication: SQL statements executed on the master server and the same statements executed on the slave server; MySQL
Statement based replication is adopted by default, which is more efficient.
Line based replication: copy the changed content instead of executing the command from the server.
Mixed type replication: statement based replication is adopted by default. Once it is found that statement based replication cannot be accurately copied, it will be adopted
Row based replication.
3. Working process of master-slave replication
(1) The Master node records the data changes into a bin log. When the data on the Master changes, the
Write its changes to the binary log.
(2) The Slave node will detect whether the binary log of the Master has changed within a certain time interval. If it has changed
If it changes, start an I/O thread to request the binary event of the Master.
(3) At the same time, the Master node starts a dump thread for each I/O thread, which is used to send binary events to it and save them to
In the local Relay log of the Slave node, the Slave node will start the SQL thread to read binary from the Relay log
The log is replayed locally, that is, it is parsed into sql statements and executed one by one to make its data consistent with that of the Master node. Finally, I/O
The thread and SQL thread will go to sleep and wait for the next wake-up.
Note:
● the relay log is usually located in the OS cache, so the overhead of the relay log is very small.
● the replication process ② has a very important limitation, that is, the replication is serialized on the Slave, that is, the parallel update on the Master
Operations cannot operate in parallel on Slave.
4. MySQL master-slave replication delay
① . the master server is highly concurrent, forming a large number of transactions
② . network delay
③ The main and slave hardware devices cause: cpu main frequency, memory io, hard disk io
④ , is not synchronous replication, but asynchronous replication
Optimize Mysql parameters from the library. For example, increase innodb_buffer_pool_size, so that more operations can be completed in Mysql memory,
Reduce disk operations.
Use high-performance hosts from the library. Including strong cpu and increased memory. Avoid using virtual virtual hosts and use physical hosts
Improved i/o.
Using SSD disks from the library
Network optimization to avoid synchronization across machine rooms
2, MySQL master-slave replication architecture
1. Preliminary preparation
① Confirm the host name and IP address. The following are configured in CentOS 7 environment.
| host name | IP address | Main software |
| Master | 192.168.110.128 | mysql-5.7 |
| Slave1 (slave) | 192.168.110.129 | mysql-5.7 |
| Slave2 (slave) | 192.168.110.130 | mysql-5.7 |
| Amoeba (trunk) | 192.168.110.131 | jdk1.6,Amoeba |
| client | 192.168.110.132 | mysql-5.7 |
② Confirm the environment and close the firewall and security enhancement system
systemctl stop firewalld systemctl disable firewalld setenforce 0
③ Time synchronization between master and slave servers
##############master############# [root@master ~]# yum install -y ntp [root@master ~]# vim /etc/ntp.conf #Add the following ...... server 127.127.80.0 #Set the local clock source. Pay attention to modifying your own network segment fudge 127.127.80.0 stratum 8 #Set the time level to 8 (limited to 15) ...... [root@slave2 ~]# systemctl start ntpd ##############selave1 and selave2############# [root@slave1 ~]# yum install -y ntp ntpdate [root@slave1 ~]# systemctl start ntpd [root@slave1 ~]# /usr/sbin/ntpdate 192.168.110.128 #Set time synchronization with primary server 2 Nov 00:28:31 ntpdate[18122]: the NTP socket is in use, exiting [root@slave1 ~]# crontab -e #Set scheduled tasks */30 * * * * /usr/sbin/ntpdate 192.168.110.128 #The time synchronization steps of the two slave servers are the same
2. File configuration
① . mysql configuration of master server
[root@master ~]# vim /etc/my.cnf ...... [mysqld] server-id = 1 #Define the server ID. each host cannot be the same log-bin=master-bin #The primary server turns on the binary log binlog_format = MIXED #MIXED mode is used this time log-slave-updates=true #Allow binary logs to be updated from the server [root@master ~]# systemctl restart mysqld.service #Restart service [root@master ~]# mysql -uroot -p #Enter mysql environment Enter password: #Authorize slave servers mysql> GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.110.%' IDENTIFIED BY '1234556'; mysql> flush privileges; mysql> show master status; +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 154 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql> #The File column displays the log name and the Position column displays the offset
② mysql configuration of slave server
##############selave1 ############# [root@slave1 ~]# vim /etc/my.cnf #Add the following ...... server-id = 2 #Note that the id cannot be the same as other hosts relay-log=relay-log-bin #Turn on the relay log and synchronize the log file records from the primary server to the local server relay-log-index=slave-relay-bin.index #Define the location and name of the relay log file relay_log_recovery = 1 #Options #When the slave goes down from the database, if the relay log is damaged and some relay logs are not processed, all unexecuted logs will be automatically discarded relay-log，And re from master Get logs on the, which ensures relay-log Integrity of. This feature is off by default Yes, will relay_log_recovery When the value of is set to 1, you can slave It is recommended to enable this function from the library. #To configure synchronization, pay attention to the master_log_file and Master_ log_ The value of POS should be consistent with that of Master query mysql> CHANGE master to master_host='192.168.110.128',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603; mysql> start slave; #Start synchronization, and execute reset slave in case of error; ############## selave2############# [root@slave1 ~]# vim /etc/my.cnf #Add the following ...... server-id = 3 relay-log=relay-log-bin relay-log-index=slave-relay-bin.index relay_log_recovery = 1 mysql> CHANGE master to master_host='192.168.110.128',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603; mysql> start slave; ############## see Slave state ############# mysql> show slave status\G; #Ensure that both IO and SQL threads are Yes, which means the synchronization is normal.
Note: General slave_ IO_ Running: possibility of no
① . the network is not connected
② There is a problem with my.cnf configuration
③ , password, file name and pos offset are incorrect
④ . the firewall is not closed
3. Verifying master-slave replication
3, Introduction to separation of reading and writing
1. Basic concepts
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
of Whether in security, high availability or high concurrency, it can not meet the actual needs. Therefore, through
Master-slave replication is used to synchronize data, and then read-write separation is used to improve the concurrent load capacity of the database. Kind of like rsync,
However, the difference is that rsync backs up disk files, while mysql master-slave replication backs up data and statements in the database.
① The basic principle of read-write separation is to let the main database handle transactional add, change and delete operations (INSERT, UPDATE
DELETE), that is, write only on the master server and read only on the slave server. The SELECT query operation is processed from the database. data
Library replication is used to synchronize changes caused by transactional operations to slave databases in the cluster.
② Why should we separate reading from writing?
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.
③ Separation of reading and 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.
2. Implementation mode
There are two ways to implement read-write separation: internal implementation based on program code and Implementation Based on intermediate agent layer.
① Internal implementation based on program code
Routing is classified according to select and insert in the code. This kind of method is also the most widely used in production environment at present.
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 developers are needed
The operation and maintenance personnel have no way to start.
However, not all applications are suitable for reading and writing separation in program code, such as some large and complex Java applications
The separation of reading and writing in the program code makes great changes to the code.
② . 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 data through judgment
Library, with the following representative programs:
◆ MySQL proxy: MySQL proxy is an open source project of MySQL, and the SOL is judged through its own lua script.
◆ Atlas: it is a data middle tier item based on MySQL protocol developed and maintained by the infrastructure team of Qihoo 360's Web Platform Department
Purpose. It optimizes mysql-proxy0.8.2 and adds some new features. Within 360
The department uses the mysql service run by Atlas to carry billions of read and write requests every day. Support things and stored procedures.
◆ Amoeba: developed by Chen Siru, the author once worked for Alibaba. The program is developed by Jaya language and Alibaba will use it
For production environments. 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 highly portable software. Therefore, it is widely used in the agent layer of database in production environment.
4, Construction of MySQL read-write separation architecture
1. Preliminary preparation
① . install the Java environment. Because amoeba is developed based on jdk1.5, it is officially recommended to use jdk1.5 or 1.6,
Higher versions are not recommended.
[root@amoeba opt]# ls /opt/ #Prepare amoeba and jdk installation packages in the opt directory amoeba-mysql-binary-2.2.0.tar.gz jdk-6u14-linux-x64.bin mysql-5.7.17 rh [root@amoeba opt]# cp jdk-6u14-linux-x64.bin /usr/local/ [root@amoeba opt]# cd /usr/local/ [root@amoeba opt]# chmod +x jdk-6u14-linux-x64.bin #Grant Execution Authority [root@amoeba opt]# ./jdk-6u14-linux-x64.bin #implement #Press and hold Enter, prompt for YES, wait for completion and Enter [root@amoeba local]# mv jdk1.6.0_14/ /usr/local/jdk1.6 #Edit the global configuration file and add the following configuration on the last line export JAVA_HOME=/usr/local/jdk1.6 #Output defines the working directory of Java export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib #Output the specified java type export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin#Add java path environment variable export AMOEBA_HOME=/usr/local/amoeba #Output definition amoeba working directory export PATH=$PATH:$AMOEBA_HOME/bin #Add path environment variable [root@amoeba ~]# source /etc/profile #Refresh file [root@amoeba ~]# java -version #Check the java version information to see if the installation was successful java version "1.6.0_14" Java(TM) SE Runtime Environment (build 1.6.0_14-b08) Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode) [root@amoeba ~]# ②,install Amoeba [root@amoeba ~]# mkdir /usr/local/amoeba [root@amoeba ~]# tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ [root@amoeba ~]# chmod -R 755 /usr/local/amoeba/ #Give permissions to the directory [root@amoeba ~]# /usr/local/amoeba/bin/amoeba #Open amoeba amoeba start|stop [root@amoeba ~]# #If amoeba start|stop is displayed, the installation is successful
2. Configure amoeba read / write separation, and the first two slave do read load balancing
#First, open permissions to amoeba on mysql of master, slave1 and slave2 mysql> grant all on *.* to test@'192.168.110.%' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> #Next, go back to the amoeba server and configure the amoeba service [root@amoeba ~]# cd /usr/local/amoeba/conf/ [root@amoeba conf]# cp amoeba.xml amoeba.xml.bak #Back up the configuration file first [root@amoeba conf]# cp dbServers.xml dbServers.xml.bak [root@amoeba conf]# cd [root@amoeba ~]# vim /usr/local/amoeba/conf/amoeba.xml #Modify amoeba configuration file ...... #--30 lines-- <property name="user">amoeba</property> #--32 lines-- <property name="password">123456</property> #--115 lines-- <property name="defaultPool">master</property> #--Lines 117 - 120, uncomment and modify <property name="writePool">master</property> <property name="readPool">slaves</property> ...... [root@amoeba ~]# vim /usr/local/amoeba/conf/dbServers.xml #Modify database configuration file ...... #--Line 23 -- comment out the function: enter the test library by default to prevent an error when there is no test library in mysql <!-- <property name="schema">test</property> --> #--26 -- modify and use the previously created authorized user <property name="user">test</property> #--29 -- remove the comment, and the password is the authorized user password created previously <property name="password">123456</property> #--45 -- modify and set the master name of the master server <dbServer name="master" parent="abstractServer"> #--48 -- modify and set the address of the master server <property name="ipAddress">192.168.10.20</property> #--52 -- modify and set slave server name slave1 <dbServer name="slave1" parent="abstractServer"> #--55 -- modify and set the address of slave server 1 <property name="ipAddress">192.168.10.30</property> #--58 -- copy and paste the above six lines, and set the slave 2 name and address from server 2 <dbServer name="slave2" parent="abstractServer"> <property name="ipAddress">192.168.10.40</property> #--Line 65 -- modify <dbServer name="slaves" virtual="true"> #--Line 71 -- modify <property name="poolNames">slave1,slave2</property> ...... [root@amoeba ~]# /usr/local/amoeba/bin/amoeba start & # #Start amoeba software and press ctrl+c to return [root@amoeba ~]# netstat -anpt | grep java #Check whether port 8066 is enabled. The default port is TCP 8066