MySQL Master-Slave Copy and Read-Write Separation
- In enterprise applications, mature business usually has a large amount of data
- Single MySQL does not meet practical requirements in terms of security, high availability, and high concurrency
- Configure multiple master-slave database servers for read-write separation
1. Principle of master-slave replication
- Generate two threads from the library, one I/O thread and one SQL thread;
- The i/o thread requests the binlog of the primary library and writes the resulting binlog log to the relay log file;
- The Primary Library generates a log dump thread to pass binlog from the library i/o thread;
- The xSQL thread reads the logs in the relay log file and parses them into specific operations to achieve master-slave consistency and final data consistency.
2. Principle of Read-Write Separation
The basic principle of read-write separation is that the primary database handles transactional add, change, delete operations (INSERT, UPDATE, DELETE) and SELECT query operations from the database. Database replication is used to synchronize changes caused by transactional operations to the secondary database in the cluster.
3. Why read-write separation
- Because the database "write" (writing 10,000 pieces of data may take 3 minutes) is time consuming.
- But the database "reads" (it may only take 5 seconds to read 10,000 pieces of data).
- So read-write separation solves the problem that database writing affects the efficiency of query.
4. When do I need to read-write separation
- The database does not necessarily have to be read-write separated. If a program uses more databases, updates are less, and queries are more, it will be considered.
- Using database master-slave synchronization and read-write separation can share database pressure and improve performance.
5. Master-Slave Copy and Read-Write Separation
In the actual production environment, both reading and writing to the database are in the same database server, which cannot meet the actual needs. Whether it is in security, high availability or high concurrency, it is completely unable to meet the actual needs. Therefore, data is synchronized by master-slave replication, and the concurrent load capacity of the database is enhanced by read-write separation. A little like rsync, but the difference is that Rsync backs up disk files, while mysql master-slave replication backs up data and statements in the database.
6. Replication types supported by MySQL
- STATEMENT: Statement-based replication. Execute sql statements on the server and execute the same statements from the server. mysql uses statement-based replication by default, which is efficient.
- ROW: Row-based replication. Copy the changes over instead of executing the command from the server once.
- MIXED: Mixed type of replication. Statement-based replication is used by default, and line-based replication is used whenever it is found that statement-based replication cannot be accurately replicated.
7. The working process of master-slave replication
- Master records these changes in the Binary log before each transaction updates the data. When the Binary log is written, Master notifies the storage engine to commit the transaction.
- Slave copies the Master to its Relay log. First slave starts a worker thread (I/O), the I/O thread opens a normal connection on Master and begins the Binlog dump process. Binlog dump process reads events from Master's binary logs, and if it has followed Master, it sleeps and waits for Master to generate new events, which the I/O thread writes to the relay log.
- The last step of the process is handled by the SQL slave thread, which reads events from the relay log and replays them to update Slave data to match the data in Master. As long as the thread is consistent with the I/O thread, the relay log is usually in the OS cache, so the cost of relay logging is minimal.
An important limitation of the replication process is that replication is serialized on Slave, which means that parallel update operations on Master cannot operate on Slave side by side.
The common MySQL read-write separation is divided into the following two types:
- Internal implementation based on program code
Routing is classified in code by select, insert, which is also the most widely used method in production environments.
The advantage is better performance because it is implemented in program code and does not require additional equipment for hardware costs; The disadvantage is that developers are required to implement it, and operations and maintenance personnel are unable to start.
However, not all applications are suited to achieve read-write separation in program code, such as some large and complex Java applications, which will change the code a lot if read-write separation is implemented in program code.
- Implementation based on intermediate proxy layer
The proxy is generally located between the client and the server. The proxy server forwards to the back-end database after receiving the client's request through judgment, which is the following representative program.
(1) MySQL-Proxy. MySQL-Proxy is an open source MySQL project, which makes SQL judgment through its own lua script.
(2) Atlas. A MySQL protocol-based data intermediate project developed and maintained by the Infrastructure Team of Qihoo 360's Web Platform Department. It is optimized on the basis of mysql-proxy version 0.8.2 and adds some new features. The Mysql business running inside 360 uses Atlas and hosts billions of read and write requests per day. Stored procedure.
- Amoeba. Developed by Chen Siru, the author once worked in Alibaba. The program was developed in the Java language and used in the production environment by Alibaba. However, it does not support transactions and stored procedures.
Since using MySQL Proxy requires writing a large number of Lua scripts, 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 s.
Amoeba is a very easy-to-use, highly portable software. Therefore, it is widely used in the proxy layer of the database in the production environment.
- Main Profile: $AMOEBA_HOME/conf/amoeba.xml, which configures the basic parameters of the Amoeba service, such as Amoeba host address, port, authentication method, user name used for connection, password, number of threads, timeout, location of other configuration files, and so on.
- Database Service Profile: $AMOEBA_HOME/conf/dbServers.xml to store and configure information about the database server proxied by Amoeba, such as host IP, port, user name, password, and so on.
- Sharing Rules Profile: $AMOEBA_HOME/conf/rule.xml to configure the slicing rules.
- Database Function Profile: $AMOEBA_HOME/conf/functionMap.xml, which is used to configure how database functions are handled. Amoeba will use the method in the configuration file to parse database functions.
- Sharing Rule Function Profile: $AMOEBA_HOME/conf/ruleFunctionMap.xml, which configures the handling of user-defined functions used in the slicing rules.
- Access Rule Profile: $AMOEBA_HOME/conf/access_list.conf, used to authorize or prohibit certain server IP access to Amoeba.
- Log Specification Profile: $AMOEBA_HOME/conf/log4j.xml to configure the level and manner of Amoeba output logs.
9. Actual operations
1. Environment Configuration
master Server: 192.168.254.10 mysql5.7 slave1 Server: 192.168.254.20 mysql5.7 slave2 Server: 192.168.254.30 mysql5.7 Amoeba Server: 192.168.254.40 jdk1.6,Amoeba Client Server: 192.168.254.50 mysql
2. Initial environmental preparation
systemctl stop firewalld setenforce 0
Set up mysql master-slave replication
Build Time Synchronization
[root@localhost ~]#yum install ntp -y
#Insert at end of line after opening file vim /etc/ntp.conf server 127.127.254.0 #Set Local Clock Source fudge 127.127.254.0 stratum 8 #Set the time level to 8 and limit to 15
service ntpd start
4. Configure slave servers (192.168.254.20, 192.168.254.30)
Configure Slave Server [root@localhost ~]# yum install ntp -y [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.254.10 #Perform synchronization 23 Dec 11:18:18 ntpdate: the NTP socket is in use, exiting [root@localhost ~]# crontab -e */30 * * * * /usr/sbin/ntpdate 192.168.254.10
slave2:192.168.254.30 Same as above
5. Configure the master server (192.168.254.10)
[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 [root@localhost ~]# mysql -u root -p GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.254.%' IDENTIFIED BY '123123'; #Authorize the slave server, allow all users of the segment, log in with the myslave user 123123 password, and use any tables in the library FLUSH PRIVILEGES; #Save user and permission configurations in memory show master status; #View the binary log being used by the master server and the current execution binary log location #File column shows log name, Fosition column shows offset
6. Configuring mysql from the server (192.168.254.20, 192.168.254.30)
vim /etc/my.cnf server-id = 2 #Modify, note that the id and Master are different, so are the IDs of the two Slave s relay-log=relay-log-bin #Add, turn on relay logging, synchronize log files from primary server to local relay-log-index=slave-relay-bin.index #Add, define the location and name of the relay log file
systemctl restart mysqld mysql -u root -p change master to master_host='192.168.254.10' , master_user='myslave',master_password='123123',master_log_file='master-bin.000003',master_log_pos=604; #Configure synchronization, note master_log_file and master_ Log_ The value of POS should be consistent with the Master query, here is an example, everyone is different start slave; #Start synchronization and reset slave if there is an error; show slave status\G #View Slave Status //Ensure that both IO and SQL threads are Yes, indicating that 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
General Slave_ IO_ Running: Possibility of No:
1. Network is not accessible
2. my.cnf configuration problem
3. The password, file name, pos offset are incorrect
4. Firewall is not closed
Verify master-slave replication
Execution on primary server
create database db_test;
To view from the server
Set up MySQL read-write separation
1. Install Java Environment
because Amoeba Based on Yes jdk1.5 Developed, so officially recommended jdk1.5 Or 1.6 Version, higher version is not recommended. 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/ #Copy executable to / usr/local directory cd /usr/local/ chmod +x jdk-6u14-linux-x64.bin #Use chmod +x to give it weight ./jdk-6u14-linux-x64.bin #Execute the file, press space to the last line, press yes, press enter mv jdk1.6.0_14/ /usr/local/jdk1.6 #Move the JDK directory to the / usr/local directory and rename it jdk1.6 vim /etc/profile #Edit Global Profile #Add at last line 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 #Execute Global Profile java -version #View java version information
2. 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
3. Configure Amoeba read-write separation, two Slave read load balancing
Now open access to Amoeba on mysql for Master, slave1, slave2
grant all on *.* to test@'192.168.254.%' identified by '123.com';
Amoeba server configuration amoeba service
cd /usr/local/amoeba/conf/ cp amoeba.xml amoeba.xml.bak vim amoeba.xml #Modify amoeba profile
117 Remove Comments-
<property name="writePool">master</property> <property name="readPool">slaves</property>
cp dbServers.xml dbServers.xml.bak vim dbServers.xml #Modify Database Configuration File
23 commented out
Role: Enter the test library by default to prevent errors when there is no test library in mysql
<!-- mysql schema <property name="schema">test</property> -->
<!-- mysql user --> <property name="user">test</property>
28-30 Remove comments
45 Modify to set Master as the name of the master server
<dbServer name="master" parent="abstractServer">
48 Modify to set the address of the primary server
52 Modify to set slave1 from the server name
<dbServer name="slave1" parent="abstractServer">
55 Modify to set the address from Server 1
58 Copy the above 6 lines and paste, set slave2 name and address from Server 2
<dbServer name="slave2" parent="abstractServer"> <property name="ipAddress">192.168.184.40</property>
<dbServer name="slaves" virtual="true">
<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 if port 8066 is open, default port is TCP 8066
3. Test Read-Write Separation
Test on the client server:
Quick installation of MySQL virtual client using yum
yum install -y mysql mysql-server mysql -u amoeba -p123123 -h 192.168.184.20 -P8066
Accessing mysql through amoeba server proxy, only the primary service records the data written after connecting mysql through the client, and then synchronizes it to the slave server
On the primary server
use db_test; create table test (id int(10),name varchar(10),address varchar(20));
Two slave servers
stop slave; #Turn off synchronization use db_test;
insert into test values('1','zhangsan','this_is_slave1');
insert into test values('2','lisi','this_is_slave2');
On the primary server
insert into test values('3','wangwu','this_is_master');
On Client Server
use db_test; select * from test; //The client reads data to slave1 and slave2, respectively, and shows only two data added from the server, not the primary server. insert into test values('4','qianqi','this_is_client'); //Only the primary server has this data
Execute the start slave on two more slaves from the server; This synchronizes the data added on the primary server