I am kite, public number “ancient kite”, a simple programmer to encourage division. Articles will be included in JavaNewBee, and there will be a Java back-end knowledge map, which will cover the path from small white to big cow.
Interviewer: I see on your resume that your company database is MySQL read-write separated?
Xiao Hua: Well, yes.
Interviewer: How do you configure the master and slave database?
Xiaohua: Well, the DBA helped us to do it, so we can use it directly.
Interviewer: Did you encounter any failures in the master-slave structure, such as the failure of the slave library or the master library? How did you solve them?
Flower: This one is also done by the DBA.
Interviewer :(smiling) ok, that’s all for today, go back and wait for the announcement.
Now don’t wait for the DBA, configure your own MySQL master/slave, be your own DBA.
The background that
Assume that you have installed the MySQL database and use MySQL 5.7.16 for this operation. There is only one Mac available, so we can use two physical machines to operate directly. If there is no idle machine, we can operate on the virtual machine.
In the simplest master slave configuration.
Primary server: 192.168.0.101
Secondary server: 192.168.0.108
Several schemas for the database layer
In the system architecture, the database layer is mainly composed of the following modes: single point mode, master/slave mode, master/slave mode.
Single point pattern
The single point mode is the simplest mode, with only one database server and the simplest deployment. But there is a single point of risk. Once this server fails, the whole system fails.
The main standby mode
To address the risks of the single point mode, the master/standby mode was developed. At present, active/standby mode should be the minimum configuration of each online service system. For example, database services purchased on various cloud platforms usually have backup function enabled. If the primary node fails, you can switch to the backup node to prevent the entire system from crashing.
The active and standby nodes are divided into one active and one standby node and one standby node. Multiple backups are for greater security, in case the primary node fails at the same time that the backup node fails.
When a fault occurs on the primary node, you need to switch to the backup node. The switchover mode includes manual switchover and automatic switchover. Manual switchover has a certain delay. When a fault occurs on the primary node, o&M personnel can only discover it or receive a system notification.
A master-slave mode
The primary server writes data, and the secondary server reads data. The primary server synchronizes data from the primary server to the secondary server in a timely manner.
The master/slave mode is divided into one master/slave mode, one master/multiple slave mode, and multiple master/multiple slave mode. The more complex the deployment, the higher the system stability. The master/slave mode can better share the database pressure and separate the insert and update operations from the query operations, improving the overall system performance.
The purpose of this article is to introduce the configuration and principles of a simple master slave architecture.
Principle of master-slave
The master node
1. When insert, UPDATE, and delete operations are performed on the primary node, they are written to binlog in chronological order. 2. When a slave node connects to a master node, the master node creates a thread called binlog dump.
3. Binlog dump threads are created as many slave nodes as a primary node.
4. When the master node’s binlog changes, the binlog dump thread notifies the slave node (Push mode) and sends the corresponding binlog to the slave node.
From the node
When master/slave synchronization is enabled, the slave node creates two threads to complete the data synchronization.
I/O thread: This thread is connected to the master node, and the binlog dump thread on the master node sends the contents of the binlog to this thread. The thread receives the binlog content and writes it to the local relay log.
SQL thread: this thread reads the relay log written by the I/O thread and performs operations on the slave database based on the relay log.
The primary server writes data, and the secondary server reads data. The primary server synchronizes data from the primary server to the secondary server in a timely manner.
Master server configuration
Enabling remote Connection
Run the following command to log in to MySQL:
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.108' identified by 'P@ssw0rd';
FLUSH PRIVILEGES;
Copy the codeAfter the above statement is executed, use the client or command line from the slave server to test whether it takes effect.
Mysql -h 192.168.0.101 -uroot -pCopy the code
Enable bin-log and set the server-id
The default location of the MySQL configuration file is as follows. The priority of the MySQL configuration file decreases from top to bottom:
-
/etc/my.cnf -
/etc/mysql/my.cnf -
/usr/local/etc/my.cnf -
~/.my.cnf
The configuration is as follows:
[mysqld]
log-bin=mysql-bin
server-id=101
Copy the codeThe other parameters
In addition to the two necessary parameters above, there are several other parameters.
binlog_format
The following three formats are supported for bin-log logs. Mixed is recommended. Statement: The system writes SQL statements about database operations to the binlog. Row: Writes each data change to the binlog. Mixed: Mixed statement and row. MySQL decides when to write statement and when to write row.
binlog-do-db
Configure databases to be synchronized. If no database is configured, all databases are synchronized.
binlog-do-db=db1
binlog-do-db=db2
Copy the codebinlog-ignore-db
Configure databases that do not need to be synchronized.
binlog-ignore-db=db3
Copy the codeexpire-logs-days
Bin-log Specifies the number of days for storing logs. The longer the number of days for storing logs, the more space is occupied.
Then restart the MySQL service
mysql.server restart
Copy the codeRun the following command to check whether the configuration takes effect:
show variables like 'log_bin';
show variables like 'server_id';
Copy the codeUse the following statement to check the master state.
show master status;
Copy the code
Slave server configuration
1. Open the configuration file of the secondary server and add the following configuration:
server-id=108
Copy the code2. Restart the MySQL service.
mysql.server restart
Copy the code3. Configure primary/secondary synchronization
change master to Master_host = '192.168.0.101, master_user =' root ', master_password = 'P @ ssw0rd, master_log_file =' 000001 'mysql - bin. ,master_log_pos=154;Copy the codeMaster_host indicates the IP address of the primary server. Master_user and master_password indicate the user name and password of the primary server. Master_log_file and master_log_pos can be found on the primary server using the show master status statement.
4. Start the synchronization process.
start slave
Copy the code5. View the synchronization status.
show slave status;
Copy the codePossible problems
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Since mysql 5.6 introduced the concept of uUID, the server_UUID in each replication master/slave structure must be different. Since MY new Mac was migrated from the old machine using the system’s own Migration Assistant, all the software and configuration were identical.
You can view the server_UUID value and the auto-. CNF directory in the following statements. The auto-.
show variables like '%server_uuid%';
show variables like '%datadir%';
Copy the code/usr/local/var/mysql = /usr/local/var/mysql = /usr/local/var/mysql = /usr/local/var/mysql = /usr/local/var/mysql
Change the value of the server-uuid in the auto-. CNF file in the datadir directory.
Then restart the service and check the synchronization status.
Test the
After the synchronization configuration is complete, I create a database on the primary server, create a table, and then add, modify, and delete data to see if the corresponding synchronization changes are made on the secondary server.
Under normal circumstances, when the operation is completed on the primary server, the secondary server will see the corresponding data immediately.
Primary/secondary status query
Master service status
To view the status of the active service, run the following statement:
show master status\G;
Copy the code
The State of the first thread is “Master has sent all binlog to slave; Waiting for more updates “, indicating that the synchronization thread is always running.
mysql> show processlist\G; * * * * * * * * * * * * * * * * * * * * * * * * * * * 1 row * * * * * * * * * * * * * * * * * * * * * * * * * * * Id: 20 User: root Host: 192.168.0.108:57815 db: NULL Command: Binlog Dump Time: 11702 State: Master has sent all binlog to slave; waiting for more updates Info: NULL *************************** 2. row *************************** Id: 21 User: Root Host: localhost db: NULL Command: Query Time: 0 State: starting Info: show processList 2 rows in set (0.03 SEC)Copy the code
ERROR: No query specified
Slave service state
Run the show slave status command. You can view slave service status to see master service information and Slave_IO and Slave_SQL thread status.
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.101
Master_User: root
Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1393 Relay_Log_File: 192-relay-bin.000005 Relay_Log_Pos: 1284 Relay_Master_Log_File: mysql-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1393 Relay_Log_Space: 1930 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 101 Master_UUID: 220919a2-9690-11e6-9c9b-9d406b577440 Master_Info_File: /usr/local/var/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) ERROR: No query specified Copy the codeYou can also use show ProcessList to see threads from the slave service.
mysql> show processlist\G;
*************************** 1. row ***************************
Id: 1
User: system user
Host: db: NULL Command: Connect Time: 84003 State: Slave has read all relay log; waiting for more updates Info: NULL *************************** 2. row *************************** Id: 2 User: system user Host: db: NULL Command: Connect Time: 123734 State: Waiting for master to send event Info: NULL *************************** 3. row *************************** Id: 9 User: root Host: localhost db: NULL Command: Query Time: 0 State: starting Info: show processlist 3 rows in set (0.00 sec) ERROR: No query specified Copy the codeHow do I recover synchronization when I hang up from the library
There’s no such thing as peace and quiet. It’s just that someone helped you stand up to the pot. Service outages are usually unintentional, and what happens when the service from the library dies.
When the binlog dump thread of the primary server sends specified binlog information to the secondary server, the specified binlog information includes the name and location of the bin-log file on the primary server.
After receiving information from the service’s I/O thread, write the log content to the end of the realy-log file (mysql-relay-bin.xxxxxx), Record the file name and location of the bin-log read from the master server to master-info (you can view the location of master.info by using the Master_Info_File field in show slave status). So that the next read can tell the master service where to start synchronization.
When the SQL thread of the slave server detects the new content of realy-log, it parses the log file to generate the corresponding SQL statements and applies the SQL statements to the database to ensure data consistency between the master and slave servers.
As a result, it hangs from the library in time because master.info records the location of the last synchronization. As soon as the synchronization service starts again, the incremental synchronization can continue from the last synchronization location.
So what if the main library is down, this is another sad story, it is not so simple to hang from the library, if immediately start that is the best solution. If you can’t restart immediately due to hardware or a difficult problem, choose a secondary database that is closest to the primary database and last synchronized. If possible (for example, the master service just failed to start the database, but the machine is still there), pull the latest bin-log from the master service for synchronization. Finally, a series of Settings change the selected slave library to the master library configuration. (I won’t expand it, because if I expand it again, I will not be exposed.)
ShardingSphere-JDBC implements read and write separation
After the master and slave are separated, read and write operations of the system are separated. Write operations go to the master node and read operations go to the slave node. This requires database middleware to help, now the more popular middleware has Atlas, Cobar, Mycat, Sharding-Sphere, the specific choice or research, depending on the standards of each company.
Sharding-sphere includes Shardingsphere-JDBC and Shardingsphere-proxy.
Shardingsphere-jdbc is positioned as a lightweight Java framework that provides additional services in Java’s JDBC layer. It uses the client directly connected to the database, in the form of JAR package to provide services, without additional deployment and dependence, can be understood as an enhanced VERSION of THE JDBC driver, fully compatible with JDBC and various ORM frameworks.
- Works with any JDBC-based ORM framework, such as JPA, Hibernate, Mybatis, Spring JDBC Template or directly using JDBC.
- Support any third party database connection pool, such as DBCP, C3P0, BoneCP, Druid, HikariCP, etc.
- Supports any database that implements the JDBC specification, currently supports MySQL, Oracle, SQLServer, PostgreSQL, and any database that complies with the SQL92 standard.
ShardingSphere the current version is 4 x, website address: https://shardingsphere.apache.org/index_zh.html
Next simple demonstration ShardingSphere-JDBC + Spring Boot + MyBatis to achieve simple read and write separation.
Release notes
The JDK: 1.8
Spring Boot:2.3.2.RELEASE (current latest version)
Mybatis -spring-boot-starter:2.1.3 (current version)
ShardingSphere-JDBC:4.1.1 (current version)
Create a Spring Boot project
1. Go to Spring Initializr (https://start.spring.io/) to create the project structure, select JDK version 8 and Spring Boot 2.3.2 (the latest version), and enter the information related to the project.
2. Introduce Spring boot-related dependency packages, including spring-boot-starter- Web, mybatis-spring-boot-starter, mysql-connector-Java, lombok.
3. Finally, download the created directory structure directly and import it into IDEA. Then maven updates the dependency package.
4. Introduce shardingSphere-JDBC dependency packages specifically for Spring Boot.
<dependency>
<groupId>org.apache.shardingsphere</groupId>
<artifactId>sharding-jdbc-spring-boot-starter</artifactId>
<version>4.1.1</version>
</dependency>
Copy the code5. Configure the master/slave database to ensure that the connection pool for read requests to the slave node and write requests to the master node is Spirng Boot 2.x default Hikari. Note that if you are using another connection pool, such as Druid, the jdbcUrl property is changed to the URL.
spring:
shardingsphere:
datasource:
master:
type: com.zaxxer.hikari.HikariDataSource
driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://localhost:3306/master-slave? useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: password slave0: type: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: JDBC: mysql: / / 192.168.0.108:3306 / master - slave? useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT username: root password: password names: master,slave0 props: sql.show: true masterslave: load-balance-algorithm-type: round_robin sharding: master-slave-rules: master: master-data-source-name: master slave-data-source-names: slave0 Copy the codeIf there are multiple slave nodes, can continue in the spring. The shardingsphere. Add a slave1, slave2 datasource.
Finally, you need to configure master-slave-rules to ensure read/write separation. If you do not configure master-slave-rules, the round_robin routing algorithm is adopted to treat the master and slave nodes identically. Write requests may fall on the slave node, making it impossible to synchronize with the master node.
6, configure MyBatis related mapper. XML, DAO interface, entity, etc., without too much introduction.
7, add Service, Controller, add a query method, corresponding to the database select. Add a new method corresponding to database insert.
@RestController
@RequestMapping("user")
public class UserController {
@Autowired
private IUserService userService; @GetMapping(value = "list") public Object list(a) { List<User> users = userService.list(); return users; } @PostMapping(value = "add") public Object add(@RequestBody UserDto userDto) { User user = new User(); BeanUtils.copyProperties(userDto, user); return userService.addUser(user); } } Copy the codeSelect is routed to the secondary node and INSERT is routed to the primary node.
Source code in github, students who need to go to Github to obtain.
Warehouse address: https://github.com/huzhicheng/play, one of the sharding – JDBC – write – read – the split project is the example source code.
conclusion
Master/slave configuration + Read/write separation ensures high availability and overall system performance to a large extent, and is a basic configuration for Internet applications. MySQL can use bin-log to implement master/slave synchronization. The master node pushes the log generated by the write operation to the slave node in the push mode. The slave node writes the log to the local relay log and then to the database. The master.info file is also updated during this process to record the location of the local synchronization for the next incremental synchronization.
The specific configuration can be independently developed by the company or a relatively stable open source framework. Shardingsphere-jdbc is used in this paper, which is only a proxy client. Its principle is to analyze SQL statements and determine whether to read or write. To distribute to different nodes.
If more complex operations such as cross-library joins are required, Proxy middleware such as ShardingSphere-proxy is required.
Strong man wait, first give a praise bar, always white piao, the body can not bear!
Public account “ancient kite”, Java developer, full stack engineer, known as late prince, bug killer, good at solving problems. A programmer with both depth and breadth of encouragement teacher, originally intended to write poetry but wrote up the code of rural code farmers! Stick to original dry goods output, you can choose to pay attention to me now, or read a historical article and then pay attention to it. Long press the QR code to follow, become excellent with me!
