catalogue
2, Principle of mysql master-slave replication
2. Working principle of mysql master-slave replication
3, mysql read-write separation principle
4, Deploy master-slave synchronization
1. Set time synchronization between master and slave servers
5, Deploy read write separation
2. Configure read write separation
Test the write of master server
1, Case overview
- In enterprise applications, mature businesses usually have a large amount of data
- A single Mysql cannot meet the actual needs in terms of security, high availability and high concurrency
- Configure multiple master-slave database servers to realize read-write separation
2, Principle of mysql master-slave replication
1. Replication type of mysql
- Statement based replication
- Row based replication
- Mixed type replication
2. Working principle of mysql master-slave replication
- The master database of the master server records the operations to the Binary log through the dump thread
- Start the I/O thread from the server and send the synchronization log request to the master server
- The master server sends the binary log content to the slave server
- Synchronize binary logging operations from the server to the relay log (stored in the cache of the slave server)
- The sql thread in the slave server writes the operation recorded in the relay log to the slave server database after it is executed from the server.
3, mysql read-write separation principle
1. Principle
Read write separation is to write only on the master server and read only on the slave server. The basic principle is to let the master database handle transactional queries and the slave database handle select queries. Database replication is used to synchronize the changes caused by transactional queries on the master database to the slave database 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.
But 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
PS: when to use read-write separation
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.
3. Implementation mode
Common MySQL read-write separation can be divided into the following two types:
① 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 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 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.
② 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 project of MySQL. SQL judgment is made through its own lua script.
(2) Atlas 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.
Amoeba: it is a proxy with MySQL as the underlying data storage and corresponding MySQL protocol interface, nicknamed amoeba
When the read request is sent to the slave server, the polling scheduling algorithm is adopted
amoeba is written in java language, and the configuration file is xml
amoeba is mainly responsible for an external proxy IP
When accessing this IP, if the request sent is a "write" request, it will be forwarded to the primary server
When the sent request is "read", it will be forwarded to the slave server through scheduling, and the polling algorithm will be used to allocate it to two slave servers in turn
amoeba can be regarded as a scheduler. If the main server hangs (single point of failure), MHA will solve this problem
4, Deploy master-slave synchronization
1. Set time synchronization between master and slave servers
① . main server (192.168.174.10)
yum -y install ntp vim /etc/ntp.conf Last line add fudge 127.127.184.0 stratum 10 #Set time limit to 15 in level server 127.127.184.0 #Set the local clock source. Pay attention to modifying the network segment systemctl start ntpd
②. Slave server (192.168.174.7192.168.184.27)
yum -y install ntp ntpdate systemctl start ntpd /usr/sbin/ntpdate 192.168.184.10 #Time synchronization, pointing to the Master server IP systemctl stop firewalld setenforce 0
Two slave servers operate in the same way
2. Server installation mysql
establish mysql user useradd -s /sbin/nologin mysql #Install dependent packages yum -y install \ gcc \ gcc-c++ \ make \ pcre-devel \ expat-devel \ perl \ ncurses-devel \ autoconf \ cmake #compile cd /opt/mysql-5.7.20 cmake \ -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DEXTRA_CHARSETS=all \ -DSYSCONFIDIR=/etc \ -DMYSQL_DATADIR=/home/mysql/ \ -DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock make && make install #Support environment variables (mysql command) echo export PATH=$PATH:/usr/local/mysql/bin/ >>/etc/profile export PATH=$PATH:/usr/local/mysql/bin/ source /etc/profile cd /opt/mysql-5.6.26 cp support-files/my-default.cnf /etc/my.cnf In order to ensure safety, according to the principle of minimum authority myql directory right chown -R mysql.mysql /usr/local/mysql/ cp support-files/mysql.server /etc/init.d/mysqld chmod 755 /etc/init.d/mysqld chkconfig --add /etc/init.d/mysqld chkconfig mysqld --level 35 on vi /etc/init.d/mysqld #Modify data file directory and working directory basedir=/usr/local/mysql datadir=/home/mysql /usr/local/mysql/scripts/mysql_install_db \ --user=mysql \ --ldata=/var/lib/mysql \ --basedir=/usr/local/mysql \ --datadir=/home/mysql service mysqld start
3. Configure database rules
① . main server
vim /etc/my.cnf server_id = 1 log_bin=master-bin #Add, and the primary server opens the binary log log_slave=updates=true #Added to allow binary logs to be updated from the server systemctl restart mysqld mysql -u root -p #Authorize the slave server to copy all tables of all databases (the first account) grant replication slave on *.* to 'myslave'@'192.168.174.%' identified by '123456'; flush privileges; see master Database status show master status\G
②. Slave server
vim /etc/my.cnf 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 the relay log file systemctl restart mysqld mysql -u root -p change master to master_host='192.168.174.10' , master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=2385; #Configure synchronization, pay attention to master_log_file and Master_ log_ The value of POS should be consistent with that of the Master query. Here is an example. Everyone's is different start slave; #Start synchronization, and execute reset slave in case of error; show slave status\G #View Slave status ensure IO and SQL Threads are Yes,It means the synchronization is normal. Slave_IO_Running: Yes #Responsible for io communication with host Slave_SQL_Running: Yes #Be responsible for your own slave mysql process
Summary:
MySQL master-slave replication and read-write separation are closely related. This paper introduces MySQL master-slave replication (synchronization), and then the next article will introduce MySQL read-write separation
Here is a brief introduction to binary logs:
MySQL binary log is a binary file, which is mainly used to record modified data or SQL statements that may cause data changes
The binary log records all operations that change the MySQL database, and records other additional information such as statement occurrence time, execution time, operation data, etc., but it does not record SQL statements that do not modify data such as SELECT and SHOW.
Binary logs are mainly used for database recovery( ⭐⭐) And master-slave copy and audit operations.
PS: if synchronization fails, you can try to solve it by using the following methods
#Ignore the current error and perform the next synchronization
stop slave first;
① slave database: SET GLOBAL sql_slave_skip_counter=1;
START SLAVE;
Problem: the I/O thread is always connecting
First, look at the last error. The content of the error report is that the fulcrum cannot be found
stop slave first;
② slave database: CHANGE MASTER TO master_log_file=‘mysql-bin.000001’,master_log_pos=0;
First of all, this is because of the problem at the binlog location index. In the production environment, do not directly reset slave (delete the change master operation);
reset slave will restore the master-slave synchronized files and locations to the initial state. It's ok if there is no data at first. If there is data, it's equivalent to restarting synchronization, and some problems may occur;
Generally, master-slave synchronization is required for subsequent data to realize master-slave synchronization. For old data, you can use the database synchronization tool to synchronize the database first, and then conduct master-slave synchronization after completion;
OK, the correct way to solve the above problems is:
1. Open the master server and enter mysql
2. Execute flush logs// At this time, the master server will re create a binlog file;
3. Execute show master status\G on the main service; The display is as follows:
4. Go to mysql from the server;
5.stop slave;
6.change master to master_log_file='mysql-bin.000012',master_log_pos=154;// The file and POS here are displayed by the master server above.
7.start slave; // It should be OK at this time
8.show slave status\G;
5, Deploy read write separation
1. Install amoeba
take jdk-6u14-linux-x64.bin and amoeba-mysql-binary-2.2.0.tar.gz.0 Upload to/opt Directory. cd /opt/ cp jdk-6u14-linux-x64.bin /usr/local/ cd /usr/local/ chmod +x jdk-6u14-linux-x64.bin ./jdk-6u14-linux-x64.bin Press space to the last line Press yes,Press enter mv jdk1.6.0_14/ /usr/local/jdk1.6 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 source /etc/profile java -version ##install Amoeba Software## mkdir /usr/local/amoeba tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ chmod -R 755 /usr/local/amoeba/ /usr/local/amoeba/bin/amoeba //If amoeba start|stop is displayed, the installation is successful
2. Configure read write separation
① Configure amoeba docking on the server
grant all on *.* to test@'192.168.174.%' identified by '123456';
② configure server docking on amoeba server
##install Amoeba Software## mkdir /usr/local/amoeba tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ chmod -R 755 /usr/local/amoeba/ /usr/local/amoeba/bin/amoeba //If amoeba start|stop is displayed, the installation is successful ##to configure Amoeba Read write separation, two Slave Read load balancing## #First, open the permission to Amoeba on the mysql of Master, Slave1 and Slave2 grant all on *.* to 'test'@'192.168.174.%' identified by '123456';
② configure server docking on amoeba server
cd /usr/local/amoeba/conf/ cp amoeba.xml{,.bak} vim amoeba.xml #Modify amoeba configuration file 30 modify <property name="user">amoeba</property> 32 modify <property name="password">010230</property> 115 modify <property name="defaultPool">master</property> 117 Remove comments– <property name="writePool">master</property> <property name="readPool">slaves</property> cp dbServers.xml{,.bak} vim dbServers.xml #Modify database configuration file 23 modify <property name="schema">mysql</property> 26 modify <!-- mysql user --> <property name="user">test</property> 28-30 Remove comments <property name="password">010230</property> 45 Modify and set the name of the master server Master <dbServer name="master" parent="abstractServer"> 48 Primary server address, modify settings <property name="ipAddress">192.168.184.10</property> 52 Modify and set the name of the slave server slave1 <dbServer name="slave1" parent="abstractServer"> 55 Modify and set the address of slave server 1 <property name="ipAddress">192.168.184.20</property> 58 Copy and paste the above 6 lines and set the name from server 2 slave2 And address <dbServer name="slave2" parent="abstractServer"> <property name="ipAddress">192.168.184.40</property> 65 modify <dbServer name="slaves" virtual="true"> 71 modify <property name="poolNames">slave1,slave2</property> /usr/local/amoeba/bin/amoeba start & #Start Amoeba software and press ctrl+c to return netstat -anpt | grep java #Check whether port 8066 is enabled. The default port is TCP 8066
3. Test read-write separation
download mariadb client yum -y install mariadb systemcrl stop firewalld setenforce 0 Remote login amoeba The server mysql -u amoeba -p123456-h 192.168.174.27 -P8066
Test on client: yum install -y mysql mysql-server mysql -u amoeba -p123456 -h 192.168.174.10 -P8066 adopt amoeba Server proxy access mysql ,Connecting through the client mysql Only the master service will record the data written later, and then synchronize it to the slave server
Test the write of master server
From server:
Primary server:
From server:
Test the read of slave server
Vi. summary
1. Master slave synchronization principle
- Through amoeba proxy server, it can write only on the master server and read only on the slave service;
- The main database handles transactional queries, and the select query is processed from the database;
- Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster
2. How to check whether master-slave synchronization is successful
- Enter the command show slave status\G in the slave server to view the master-slave information, which contains the status information of IO threads, as well as the IP address, port and transaction start number of the master server,
- When slave_io_running and slave_ sql_ When running is displayed as yes, it indicates that the master-slave synchronization status is successful
3. If I/O and SQL are not yes, how do you troubleshoot them
- First, eliminate the network problem and use the ping command to check whether the slave service can communicate with the master server
- Furthermore, check whether the firewall and core protection are turned off
- Then check whether the slave in the slave server is enabled
- Whether the server IDs of the two slave servers are the same, so that only one can be connected
- master_log_file and Master_ log_ Whether the value of POS is consistent with that of Master query
4. What information can show slave status see (more important)
- Status information of IO thread
- IP address, port and transaction start location of master server
- The latest error information and error location, etc
5. What are the possibilities of slow (delayed) master-slave replication
- The load of the main server is too large, which is occupied by multiple sleep or zombie threads, resulting in excessive system load
- The slave library hardware is worse than the master library, resulting in replication delay
- Master slave replication is a single thread. If the write concurrency of the master library is too large to be transmitted to the slave library, it will lead to delay.
- Too many slow SQL statements
- Network delay