Replication process setup
Asynchronous replication
After the primary database executes Commit, the primary database writes Binlog logs to the client without waiting for Binlog logs to be sent to the secondary database.
Steps:
1. Ensure that the primary and secondary libraries have the same version of the database installed.
2. On the master database, set an account for REPLICATION and grant REPLICATION SLAVE privileges. Create a replicate user repl that can connect from the host whose IP is 192.169.56.103:
GRANT REPLICATION SLAVE on. To ‘rep1’@’192.168.56.103’ IDENTIFIED BY ‘1234test’;
3. Modify the configuration file my. CNF of the primary database server, enable BINLOG, and set the value of server-id. The modification of these two parameters takes effect only after the database service is restarted
Modify my CNF as follows:
[mysqld]
log-bin=/home/ mysql/log/mysql-bin. log
server-id= 1
Note: If the mysql directory does not have a log directory, create a log directory first
4. Then get the current binary log name and offset value on the main library. The purpose of this operation is to recover the data from this point after starting from the database.
Run show master status:
5. Modify the configuration file my. CNF of the secondary database and add the server-id parameter. Note The value of server-ID must be unique and different from that of the primary database. If there are multiple secondary database servers, each secondary database server must have its own unique server-ID.
Modify mycnf as follows:
[mysqld]
server-id=2
6. On the slave database, use the -skip-slave-start option to start the slave database, so that the replication process on the slave database service will not be started immediately, which is convenient for us to further configure the slave database service:
Operation command: / bin/mysqld_safe – defaults – file = / home/mysql/mysql3307 / my CNF – skip – slave – start
7. Set the secondary database server, specify the user for replication, IP address and port of the primary database server
And the log file and location to start the replication, the reference code is as follows:
mysql> CHANGE MASTER TO
->MASTER_HOST=master host name
->MASTER_USER=replication_user_name
-> MASTER PASSWORD=replication_password
->MASTER_LOG_FILE=’recorded_log_file_name
->MASTER_LOG_POS=recorded _log_position
Examples are as follows:
CHANGE the MASTER TO MASTER_HOST = ‘192.168.56.103, MASTER_PORT = 3306, MASTER_USER =’ TAB ‘, MASTER_PASSWORD = ‘1234 test’ ,MASTER_LOG_FILE=’mysql-bin.000001′,MASTER_LOG_POS=428;
On the slave library, start the slave thread:
9. Run the show processList command on the slave to display a process similar to the following:
Run show slave status. Will display:
11. Run the show processList command on the master to display a process similar to the following:
12. Test it out:
Create a new database and table on the master library and insert data to see if the associated database and table and insert data are automatically created in the slave library.
Before creating a new database and table:
Create the database Orders and table order_exp in the main library and insert the data
Check from the library
Semi-synchronous replication
Before MySQL5.5, MySQL replication is asynchronous operations, the main library and the library of data between a certain delay, so there is a hidden trouble: when the person to write on the main library and submit a transaction is successful, and from the library has not been the main library push Binlog log, the main library goes down, such as the main library may due to disk damage, memory failure such as the main library on this transaction The Binlog is lost, at which point the slave library may lose the transaction, resulting in master/slave inconsistencies.
Semi-synchronous replication, however, waits for one of the slave libraries to also receive the Binlog transaction and write the Relay Log successfully before returning the Commit operation to the client. In this way, semi-synchronization ensures that there are at least two Log records after the transaction is successfully committed, one in the master Binlog and the other in the slave Relay Log, thus further ensuring data integrity. Semisync_master/Semisync_slave semi-synchronous replication is largely determined by the RTT (round-trip latency) of the master and slave networks.
The installation is relatively simple. In the asynchronous replication environment described in the previous section, you can install the semi-synchronous replication plug-in.
(1) Check whether MySQL server supports dynamic add-ons:
mysql> select @@have_dynamic_loading;
2) Check whether plug-ins exist in the MySQL installation directory.
Install plug-in:
Install the semisync_master.so plugin on the master library:
mysql> install plugin rpl_semi_sync_master SONAME ‘semisync_master.so’
Semisync_slave. so:
mysql> install plugin rpl_semi_sync_slave SONAME ‘semisync_slave.so’;
After the installation is complete, you can see the plug-in you just installed in the Plugin table
mysql> select * from mysql.plugin;
Semi-sync is not enabled by default on the primary and secondary libraries. Configure global parameters on the primary library:
mysql> set global rpl_semi_sync_master_enabled=1;
mysql> set global rpl_semi_sync_master timeout 30000;
Configure global parameters as from the library:
mysql> set global rpl_semi_sync_slave_enabled=1;
4) Perform other steps for asynchronous replication
Read and write separation practice
SpringBoot+MyBatis Combined with MySQL read-write separation
Read/write separation is all about choosing which database to execute a SQL query, and who to choose the database. Either the middleware does it for us, or the program does it itself. Therefore, in general, read/write separation can be implemented in two ways. The first is to rely on middleware to do SQL separation for us; The second is for the application to do the separation itself. We chose to do it ourselves, primarily using the routing data source provided by Spring, as well as AOP
However, the biggest disadvantage of using read-write separation at the application level is that you cannot dynamically add database nodes because the data source configuration is written into the configuration. Adding a new database means adding a new data source, which must change the configuration and restart the application. The advantage, of course, is that it’s relatively simple.
Main ideas:
1. Choose the key problem of database execution and examine the packaging system of Spring
AbstractRoutingDataSource internal maintains a set of target data sources, and make the routing between the key and the target data source mapping, provides the method based on the key lookup the data source. So we need to declare a MyRoutingDataSource to responsible for their own data sources, and should be inherited from AbstractRoutingDataSource, covering determineCurrentLookupKey method, The result of this method is our choice of data source.
2, review AbstractRoutingDataSource internal, there are two container:
Where targetDataSources refers to the method we pass
Set the target data source, and resolvedDataSources is the actual data source that Spring passes the method
The transformation took place.
But in actual operation, the Spring through the getConnection method to obtain a database connection, eventually in the getConnection method call determineTargetDataSource method to locate the actual data source, In determineTargetDataSource method with determineCurrentLookupKey covered in front of us come from a container resolvedDataSources get the actual data source
3. So, let’s define an enumerated class
To indicate how many data sources there are and use this class as the key for both data source containers.
4. To ensure thread-safety, we use a ThreadLocal to store data source objects currently held by each thread that operates on the database
5, in our own data sources MyRoutingDataSource determineCurrentLookupKey method, you just need to.
6. After configuring the native data source in application.yml, load it by configuring the DataSourceConfig class.
MyRoutingDataSource = myRoutingDataSource = myRoutingDataSource = myRoutingDataSource
The application transaction manager and MyBatis both use this virtual data source myRoutingDataSource
In order to reduce the intrusion of business code, we define a SpringAop class DataSourceAop, which defines two pointcuts, slavePointcut and masterPointcut, according to the method name to switch data sources, such as query method requirements start with query and so on.
By default, all queries go to the secondary library, and inserts/changes/deletes go to the primary library
10, the special case is some cases we need to force read master library, in this case, we define an annotation, with the annotation annotation read master library, corresponding, modify the next DataSourceAop pointcut definition.
11. Just test our code, which is under the module RW-separation.