“This is the 8 days I participated in the Genwen Challenge, the details of the activity: Genwen Challenge”

MySQL Master/slave replication Overview

In actual production, the importance of data is self-evident

If we only one server database, then it is easy to produce a single point of failure problem, such as this server access to pressure and there is no response or collapses, so service is not available, such as this server hard disk is broken again, so the whole database data is lost, this is a major safety accident.

In order to avoid the service is not available and ensure safety and reliability of data, we need to deploy at least two or more than two servers to store database data, is also more than we need to copy the data been deployed on many different servers, even if there is a server fails, another server can still continue to provide services.

MySQL provides the master-slave replication function to improve service availability and data security.

Master /slave replication means that the server is divided into master and slave servers. The master server reads and writes, and the slave server only reads. Master /slave replication is also called master/slave, master is the master, slave is the slave, but it is not mandatory.

Master/slave replication can be used to separate database backup and read/write

MySQL master-slave replication architecture

One master, many slaves architecture

Dual master and slave structure

MySQL master-slave replication principle

  • When data changes on the primary library server, its changes are written to a binary event log file

  • The slave server probes the binary log on the master server for changes at certain intervals, and if it detects that the binary event log on the master server has changed, an I/O Thread requests the master binary event log

  • At the same time, the main library server starts a dump Thread for each I/O Thread to send binary event logs to

  • The library server saves the received binary event log to its own local relaylog file

  • The slave server will start the SQL Thread to read the binary log from the slave log and play it back locally to make its data consistent with the master server.

  • Finally, I/O threads and SQL threads go to sleep, waiting to be awakened the next time

The master-slave replication process has little delay and little impact

MySQL multi-instance setup

Multiple instances of MySQL are used to start multiple MySQL instances (mainly for learning purposes) on a Linux machine after MySQL is installed.

Multi-instance configuration

  • Install mysql, download the mysql installation package, and decompress it.

  • Create mysql users and user groups

    Groupadd mysql useradd -r -g mysql mysql chown -r mysql. Mysql /usr/local/mysql5.7Copy the code

  • Create a data directory for storing mysql multi-instance data.

    The mkdir -p/usr/local/mysql5.7 / data / 3307 mkdir -p/usr/local/mysql5.7 / data / 3308 mkdir -p/usr/local/mysql5.7 / data / 3309 The mkdir -p/usr/local/mysql5.7 / data / 3310Copy the code

  • Run the following command in the bin directory of the mysql decompression directory:

    . / mysqld - the initialize - the console - basedir = / usr/local/mysql5.7 - datadir = / usr/local/mysql5.7 / data / 3307 - user = mysql . / mysqld - the initialize - the console - basedir = / usr/local/mysql5.7 - datadir = / usr/local/mysql5.7 / data / 3308 - user = mysql . / mysqld - the initialize - the console - basedir = / usr/local/mysql5.7 - datadir = / usr/local/mysql5.7 / data / 3309 - user = mysql . / mysqld - the initialize - the console - basedir = / usr/local/mysql5.7 - datadir = / usr/local/mysql5.7 / data / 33010 - user = mysqlCopy the code

    Note: — Initialize generates a random password for the root user. — Console outputs initialization logs. You can see the generated root password. –user Specifies the user running the MySQL instance

  • Create configuration files in their respective data directories

    #= = = = = = = = = = = = = = = = 3307 port = = = = = = = = = = = = = = = =[client] port = 3307 socket = / usr/local/mysql5.7 / data / 3307 / mysql. The sock default - character - set = utf8 [mysqld] port = 3307 The socket = / usr/local/mysql5.7 / data / 3307 / mysql. The sock datadir = / usr/local/mysql5.7 / data/log - error = 3307 / usr/local/mysql5.7 / data / 3307 / error log pid - file = / usr/local/mysql5.7 / data / 3307 / mysql. The pid character - set - server = utf8 lower_case_table_names=1 autocommit = 1
#= = = = = = = = = = = = = = = = 3308 port = = = = = = = = = = = = = = = =[client] port = 3308 socket = / usr/local/mysql5.7 / data / 3308 / mysql. The sock default - character - set = utf8 [mysqld] port = 3308 The socket = / usr/local/mysql5.7 / data / 3308 / mysql. The sock datadir = / usr/local/mysql5.7 / data/log - error = 3308 / usr/local/mysql5.7 / data / 3308 / error log pid - file = / usr/local/mysql5.7 / data / 3308 / mysql. The pid character - set - server = utf8 lower_case_table_names=1 autocommit = 1

#= = = = = = = = = = = = = = = = 3309 port = = = = = = = = = = = = = = = =[client] port = 3309 socket = / usr/local/mysql5.7 / data / 3309 / mysql. The sock default - character - set = utf8 [mysqld] port = 3309 The socket = / usr/local/mysql5.7 / data / 3309 / mysql. The sock datadir = / usr/local/mysql5.7 / data/log - error = 3309 / usr/local/mysql5.7 / data / 3309 / error log pid - file = / usr/local/mysql5.7 / data / 3309 / mysql. The pid character - set - server = utf8 lower_case_table_names=1 autocommit = 1

#= = = = = = = = = = = = = = = = 3310 port = = = = = = = = = = = = = = = =[client] port = 3310 socket = / usr/local/mysql5.7 / data / 3310 / mysql. The sock default - character - set = utf8 [mysqld] port = 3310 The socket = / usr/local/mysql5.7 / data / 3310 / mysql. The sock datadir = / usr/local/mysql5.7 / data/log - error = 3310 / usr/local/mysql5.7 / data / 3310 / error log pid - file = / usr/local/mysql5.7 / data / 3310 / mysql. The pid character - set - server = utf8 lower_case_table_names=1 autocommit = 1Copy the code

Start multi-instance

Run this command in the bin directory of the MySQL installation directory

. / mysqld_safe - defaults - file = / usr/local/mysql5.7 / data / 3307 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql5.7 / data / 3308 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql5.7 / data / 3309 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql5.7 / data / 3310 / my CNF &Copy the code

Login instance

/mysql -uroot -p-p3307-h127.0.0.1./mysql -uroot -p-p3308-h127.0.0.1./mysql -uroot -p-p3309-h127.0.0.1./mysql -uroot -p-p3309-h127.0.0.1 Uroot - p - P3310 - h127.0.0.1Copy the code

Change the password

alter user 'root'@'localhost' identified by '123456; '

Example Enable remote access permission

grant all privileges on *.* to root@'%' identified by '123456';

The first of *.* represents all database names, and the second represents all database tables

Root@ root in ‘%’ indicates the user name

% indicates all IP addresses. % can also specify specific IP addresses, such as root@localhost or [email protected].

Refresh the permissions

flush privileges;

Multi-instance shutdown

/ mysqladmin-uroot -p p3307-h127.0.0.1 shutdown./ mysqladmin-uroot -p p3308-h127.0.0.1 shutdown./mysqladmin -mysqladmin-uroot -p p3308-h127.0.0.1 shutdown -uroot -p P3309 -h127.0.0.1 shutdown./mysqladmin -uroot -p P3310 -h127.0.0.1 shutdownCopy the code

MySQL has one master and many slave architecture

The configuration file

Add it to the my.cnf configuration file of the MySQL primary server (3307)

Log-bin =mysql-bin # indicates that binary logging is enabled

Server-id =3307 # Indicates the server id, which must be unique

Add it to the my.cnf configuration file of the MySQL slave server (3308)

server-id=3308

Add it to the my.cnf configuration file of MySQL slave server (3309)

server-id=3309

Add it to the MySQL slave server (3310) configuration file my.cnf

server-id=3310

The main library configuration

  1. / mysql-uroot -p p3307-h127.0.0.1

  2. Grant replication slave on *.* to ‘copy’@’%’ identified by ‘123456’;

  3. View the status of the primary library

    The default value is File: mysql-bin.000001. Position: 154. The offset changes because authorization statements have been executed. So we need to top up the offset of master.

From library configuration

  1. To check slave status: show slave status \G; The result will be: Empty set (0.00 SEC). The initial state of the library. If you do not need to reset: reset slave;

  2. Set up the listening master and start the replication

    change master to Master_host = '192.168.79.150, master_user =' copy ', master_port = 3307, master_password = '123456', master_log_file = 'mysql - bin. 000 001',master_log_pos=154; start slave;Copy the code

    Master_host: indicates the IP address of the Master

    Master_port: indicates the Master port number, which is the port number of the container

    Master_user: user used for data synchronization

    Master_password: indicates the password used for synchronization

    Master_log_file: specifies the log File from which Slave starts copying data, that is, the value of the File field mentioned above

    Master_log_pos: Which Position to start reading from, that is, the value of the Position field mentioned above

  3. Run the show slave status command to check the slave library status.

    If Slave_IO_Running and Slave_SQL_Running are yes, slave is successfully enabled

    mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Waiting for Master to send event Master_Host: 192.168.79.150 Master_User: copy Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 154 Relay_Log_File: dev1-relay-bin.000002 Relay_Log_Pos: 320 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: 154 Relay_Log_Space: 526 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: 3307 Master_UUID: Be3a5680-8 FDD eb - 897 - a - 000-11 c297b3e36 Master_Info_File: / usr/local/mysql5.7 / data / 3309 / 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)Copy the code

test

  1. Connect to the main library, create a database in the main library, create a table in the library and write data.

  2. You can use software like Navicat for convenience

  3. Check to see if the table database and tables and their data created in the master library exist. The data existence primary/secondary mode is set up successfully

  4. Binlong: show binlog events in ‘mysql-bin.000001’\G;

    mysql> show binlog events in 'mysql-bin.000001'\G;*************************** 1. row *************************** Log_name: mysql-bin.000001 Pos: 4 Event_type: Format_desc Server_id: 3307 End_log_pos: 123 Info: Server ver: 5.7.24-log, Binlog ver: 4 *************************** 2. row *************************** Log_name: mysql-bin.000001 Pos: 123 Event_type: Previous_gtids Server_id: 3307 End_log_pos: 154 Info: *************************** 3. row *************************** Log_name: mysql-bin.000001 Pos: 154 Event_type: Anonymous_Gtid Server_id: 3307 End_log_pos: 219 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS' *************************** 4. row *************************** Log_name: mysql-bin.000001 Pos: 219 Event_type: Query Server_id: 3307 End_log_pos: 368 Info: CREATE DATABASE `shop` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci' *************************** 5. row *************************** Log_name: mysql-bin.000001 Pos: 368 Event_type: Anonymous_Gtid Server_id: 3307 End_log_pos: 433 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS' *************************** 6. row *************************** Log_name: mysql-bin.000001 Pos: 433 Event_type: Query Server_id: 3307 End_log_pos: 628 Info: use `shop`; CREATE TABLE `shop`.`order` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(255) NULL, PRIMARY KEY (`id`) ) *************************** 7. row *************************** Log_name: mysql-bin.000001 Pos: 628 Event_type: Anonymous_Gtid Server_id: 3307 End_log_pos: 693 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS' *************************** 8. row *************************** Log_name: mysql-bin.000001 Pos: 693 Event_type: Query Server_id: 3307 End_log_pos: 765 Info: BEGIN *************************** 9. row *************************** Log_name: mysql-bin.000001 Pos: 765 Event_type: Table_map Server_id: 3307 End_log_pos: 816 Info: table_id: 109 (shop.order) *************************** 10. row *************************** Log_name: mysql-bin.000001 Pos: 816 Event_type: Write_rows Server_id: 3307 End_log_pos: 862 Info: table_id: 109 flags: STMT_END_F *************************** 11. row *************************** Log_name: mysql-bin.000001 Pos: 862 Event_type: Xid Server_id: 3307 End_log_pos: 893 Info: COMMIT /* xid=94 */ *************************** 12. row *************************** Log_name: mysql-bin.000001 Pos: 893 Event_type: Anonymous_Gtid Server_id: 3307 End_log_pos: 958 Info: SET @@SESSION.GTID_NEXT= 'ANONYMOUS' *************************** 13. row *************************** Log_name: mysql-bin.000001 Pos: 958 Event_type: Query Server_id: 3307 End_log_pos: 1030 Info: BEGIN *************************** 14. row *************************** Log_name: mysql-bin.000001 Pos: 1030 Event_type: Table_map Server_id: 3307 End_log_pos: 1081 Info: table_id: 109 (shop.order) *************************** 15. row *************************** Log_name: mysql-bin.000001 Pos: 1081 Event_type: Write_rows Server_id: 3307 End_log_pos: 1126 Info: table_id: 109 flags: STMT_END_F *************************** 16. row *************************** Log_name: mysql-bin.000001 Pos: 1126 Event_type: Xid Server_id: 3307 End_log_pos: 1157 Info: COMMIT /* Xid =97 */ 16 rows in set (0.00 SEC)Copy the code

  5. Check the status of the slave

    mysql> mysql> show slave status \G;*************************** 1. row *************************** Slave_IO_State: Waiting for Master to send event Master_Host: 192.168.79.150 Master_User: copy Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 1157 Relay_Log_File: dev1-relay-bin.000002 Relay_Log_Pos: 1323 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: 1157 Relay_Log_Space: 1529 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: 3307 Master_UUID: Be3a5680-8 FDD eb - 897 - a - 000-11 c297b3e36 Master_Info_File: / usr/local/mysql5.7 / data / 3308 / 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)Copy the code

Matters needing attention

When data is written to the master, it is synchronized to the slave, but when a piece of data is written to the slave, it is not synchronized to the other slave and master libraries. When the primary key of data in a table is incremented. When data is inserted into the master database, the data from the master database will not be displayed in the data from the master database if the primary key of the data from the slave database conflicts with that of the data from the master database. From the state of the library:

Read_Master_Log_Pos: 1157 # this item still changes with writes to the master library, i.e. synchronizing data from the master library. Relay_Log_Pos: 1323 # this entry will stop. This is the operation of the relay log. Slave_IO_Running: Yes Slave_SQL_Running: NO # this item will become NO.Copy the code

View the status of the slave library. The Last_Error field returns an error.

Solutions:

  1. Delete data from the repository that conflicts with the master. Then stopstop slave;, and then restart thestart slave;. At this point, the relay file is regenerated and the data is resynchronized.
  2. Delete the entire table or database on the slave library and add the master library again. However, this operation will copy a lot of data, after all, the entire table and database is deleted.

MySQL multi master multi slave architecture construction

If the primary server is down, the write operation will not complete, but the read operation can be completed (because the general MySQL master-slave architecture is used for read and write separation). There would be a single point of failure in the architecture of a master library.

The MySQL database on port 3307 is the primary database, and the MySQL database on port 3309 is the secondary database

The MySQL database on port 3308 is the primary database, and the MySQL database on port 3310 is the secondary database

The MySQL server of port 3307 and the MySQL guard of port 3309 are primary and secondary

Configuration file configuration

Add the following configuration to the my.cnf file of MySQL on port 3307 of the first primary server

auto_increment_increment=2

auto_increment_offset=1

log-slave-updates

sync_binlog=1
Copy the code

Add the following configuration to the my.cnf file of MySQL on port 3308 of the second primary server

auto_increment_increment=2

auto_increment_offset=2

log-slave-updates

sync_binlog=1
Copy the code

Configuration Item Description

  1. auto_increment_increment

    Auto_increment_increment =n Increment_increment =n Increment_increment =n increment_increment=n

  2. Auto_increment_offset = 1

    Set the starting value, it is set to 1, this Master auto_increment field produced by the numerical value is: 1, 3, 5, 7,… Such an odd number of ID

    Set auto_increment_offset to different master Settings. Otherwise, it is easy to cause primary key conflicts. For example, if the offset of master1 =1, then the offset of master2 =2, and the offset of master3 =3

  3. log-slave-updates

    In dual-master mode, the log-slave-updates configuration item must be configured. Otherwise, data is updated on Master1 (port 3307), master2 (port 3308) and Slave1 (port 3309), but not slave2 (port 3310)

  4. sync_binlog

    MySQL flusher the binlog cache to the log file for every few transaction commits. Default is 0, safest is 1.

    If sync_binlog=0, MySQL does not use fsync to flush data from binlog_cache to disk after a transaction is committed, and Filesystem decides when to synchronize data to disk, or when the cache is full

    Sync_binlog =n, after every n transaction commits, MySQL will perform a disk synchronization command such as fsync to force data from binlog_cache to disk

Matters needing attention

The log-bin function is enabled only for the secondary database, and the log-slave-updates parameter is not added. Data replicated from the secondary database to the master database will not be written to the log-bin log file. After log-slave-updates is enabled, data copied from the slave database to the master database will be written to the log-bin log file. This is also the function of this parameter. When writing data directly to the slave library, the log-bin log is written.

When a primary key is automatically generated, it is generated based on the generated primary key according to the rule, that is, the value is larger than the existing value.

Configuration of master and slave libraries

  1. Start Start MySQL respectively

    . / mysqld_safe - defaults - file = / usr/local/mysql5.7 / data / 3307 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql5.7 / data / 3308 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql5.7 / data / 3309 / my CNF &. / mysqld_safe - defaults - file = / usr/local/mysql5.7 / data / 3310 / my CNF &Copy the code

  2. Grant replication slave on *.* to ‘copy’@’%’ identified by ‘123456’; MySQL has been executed for port 3307.

  3. Reset Master: reset Master; It is not necessary to reset the database, but to specify the name of biglog when listening to the master database from the slave database. After the reset, mysql-bin.000001 is displayed.

  4. Execute on MySQL on port 3308 because ports 3308, 3309, and 3310 have a master/slave configuration set on them.

    stop slave;
reset salve;
Copy the code

  5. The slave library listens on the master library

    #= = = = = = = = = = = = = = in 3307 port and port 3310 MySQL execution = = = = = = = = = = = = = = =change master to Master_host = '192.168.79.150, master_user =' copy ', master_port = 3308, master_password = '123456', master_log_file = 'mysql - bin. 000 001',master_log_pos=154; start slave;
#= = = = = = = = = = = = = = in 3308 port and port 3309 MySQL execution = = = = = = = = = = = = = = =change master to Master_host = '192.168.79.150, master_user =' copy ', master_port = 3307, master_password = '123456', master_log_file = 'mysql - bin. 000 001',master_log_pos=154; start slave;Copy the code

    test

    As with the one master, many slave test in the previous section.

Code practice: multi-data source configuration

Once our MySQL cluster is set up, it will be used in the code. Usually when we write code, we only have one data source set. Now so many data sources, and how to use!

There are two cases: 1, do read and write separation. 2. Only the master library is operated, and the slave library is used as a backup.

Scheme 1: Mapper package isolation of MyBatis

Core principles

Based on Mapper package isolation, each Mapper package operates on a different database, and each Mapper package corresponds to one database.

Code implementation

Pom. XML:


      
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>org.msr</groupId>
    <artifactId>spring-mybatis-mutil-datasource</artifactId>
    <version>1.0</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>

    <dependencies>
        <! --Spring related dependencies -->
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-jdbc</artifactId>
            <version>5.1.4 ensuring. RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-tx</artifactId>
            <version>5.1.4 ensuring. RELEASE</version>
        </dependency>
        <dependency>
            <groupId>org.springframework</groupId>
            <artifactId>spring-context</artifactId>
            <version>5.1.4 ensuring. RELEASE</version>
        </dependency>

        <! --Mybatis framework dependencies -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.6</version>
        </dependency>
        <! --Mybatis with Spring integration dependencies -->
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis-spring</artifactId>
            <version>1.3.2</version>
        </dependency>
        <! MySQL database connection driver version not too high -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.43</version>
        </dependency>
        <! --JDBC connection pool -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.1</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.mybatis.generator</groupId>
                <artifactId>mybatis-generator-maven-plugin</artifactId>
                <version>1.3.6</version>
                <configuration>
                    <! -- Configuration file location -->
                    <configurationFile>GeneratorMapper.xml</configurationFile>
                    <verbose>true</verbose>
                    <overwrite>true</overwrite>
                </configuration>
            </plugin>
        </plugins>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.xml</include>
                </includes>
            </resource>

        </resources>
    </build>
</project>
Copy the code

Configuration file:

datasource.xml


      
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd">

    <! Configure the data source for the master library -->
    <bean id="masterDruidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="JDBC: mysql: / / 192.168.79.150:3307 / shop"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    <! -- Configure the connection factory for the primary library -->
    <bean id="masterSqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="masterDruidDataSource"/>
    </bean>
    <! -- Configure packet scanning for primary library -->
    <bean id="masterMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="masterSqlSessionFactoryBean"/>
        <property name="basePackage" value="org.msr.masterslave.master.mapper"/>
    </bean>


    <! Configure the data source for the slave library
    <bean id="slaveDruidDataSource" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="JDBC: mysql: / / 192.168.79.150:3309 / shop"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    <! -- Configure the slave library connection factory -->
    <bean id="slaveSqlSessionFactoryBean" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="slaveDruidDataSource"/>
    </bean>
    <! -- Configure packet scanning from the slave library -->
    <bean id="slaveMapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="slaveSqlSessionFactoryBean"/>
        <property name="basePackage" value="org.msr.masterslave.slave.mapper"/>
    </bean>
</beans>
Copy the code

application.xml


      
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:context="http://www.springframework.org/schema/context"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd ">
    <import resource="classpath:datasource.xml"/>
    <context:component-scan base-package="org.msr"/>
</beans>
Copy the code

Mapper:

The master library mapper

package org.msr.masterslave.master.mapper;

import org.msr.masterslave.model.UserInfo;


public interface MasterUserInfoMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(UserInfo record);

    int insertSelective(UserInfo record);

    int updateByPrimaryKeySelective(UserInfo record);

    int updateByPrimaryKey(UserInfo record);
}
Copy the code

MasterUserInfoMapper.xml


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.msr.masterslave.master.mapper.MasterOrderMapper">
    <resultMap id="BaseResultMap" type="org.msr.masterslave.model.UserInfo">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
    </resultMap>
    <sql id="Base_Column_List">
        id
        , name
    </sql>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete
        from userInfo
        where id = #{id,jdbcType=INTEGER}
    </delete>
    <insert id="insert" parameterType="org.msr.masterslave.model.UserInfo">
        insert into userInfo (id, name)
        values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR})
    </insert>
    <insert id="insertSelective" parameterType="org.msr.masterslave.model.UserInfo">
        insert into userInfo
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id ! = null">
                id,
            </if>
            <if test="name ! = null">
                name,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id ! = null">
                #{id,jdbcType=INTEGER},
            </if>
            <if test="name ! = null">
                #{name,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="org.msr.masterslave.model.UserInfo">
        update userInfo
        <set>
            <if test="name ! = null">
                name = #{name,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
    <update id="updateByPrimaryKey" parameterType="org.msr.masterslave.model.UserInfo">
        update userInfo
        set name = #{name,jdbcType=VARCHAR}
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>
Copy the code

Slave library mapper

package org.msr.masterslave.slave.mapper;

import org.msr.masterslave.model.UserInfo;

public interface SlavUserInfoMapper {
    UserInfo selectByPrimaryKey(Integer id);
}
Copy the code

SlaveUserInfoMapper.xml


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.msr.masterslave.slave.mapper.SlaveOrderMapper">
    <resultMap id="BaseResultMap" type="org.msr.masterslave.model.UserInfo">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
    </resultMap>
    <sql id="Base_Column_List">
        id
        , name
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from userInfo
        where id = #{id,jdbcType=INTEGER}
    </select>
</mapper>
Copy the code

The model code:

package org.msr.masterslave.model;

import java.io.Serializable;

public class UserInfo implements Serializable {
    private Integer id;

    private String name;

    public Integer getId(a) {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName(a) {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString(a) {
        return "Order{" +
                "id=" + id +
                ", name='" + name + '\' ' +
                '} '; }}Copy the code

The service code:

package org.msr.masterslave.service;

import org.msr.masterslave.master.mapper.MasterOrderMapper;
import org.msr.masterslave.model.UserInfo;
import org.msr.masterslave.slave.mapper.SlaveOrderMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/ * * * *@author MaiShuRen
 * @site http://www.maishuren.top
 * @sinceLet * * / 2021-03-28
@Service("orderService")
public class OrderService {
    @Autowired
    private MasterUserInfoMapper masterUserInfoMapper;

    @Autowired
    private SlaveUserInfoMapper slaveUserInfoMapper;

    public UserInfo read(Integer id) {
        return slaveOrderMapper.selectByPrimaryKey(id);
    }

    public int write(UserInfo userInfo){
        returnmasterOrderMapper.insert(userInfo); }}Copy the code

Testing:

package org.msr.masterslave;

import org.msr.masterslave.model.UserInfo;
import org.msr.masterslave.service.OrderService;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/ * * *@author MaiShuRen
 * @site http://www.maishuren.top
 * @sinceThe 2021-03-28 did * * /
public class Main {
    public static void main(String[] args) {
        ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        // Insert data
        UserInfo userInfo = new UserInfo();
        userInfo.setName("hahaha");
        int write = orderService.write(userInfo);
        System.out.println(write);
        // Query by id
        UserInfoService userInfoService = applicationContext.getBean(UserInfoService.class);
        UserInfo read = userInfoService.read(1); System.out.println(read.toString()); }}Copy the code

disadvantages

1. With a lot of duplicate code such as configuration in Spring and multiple sets of Mapper mapping code, each database should basically have its own configuration

2. When the master and slave are switched for read and write operations, the program needs to manually select the corresponding class, so it may form data written to the slave library and read to the master library

3. If there are many slave nodes, there is no way to carry out load balancing

4. If a node crashes, we cannot switch to another node and fail over

Scenario 2: Dynamic Data Source (based on ThreadLocal)

Core principles

Custom data sources and inheritance AbstractRoutingDataSource achieve its protected Object determineCurrentLookupKey () method. Use ThreadLocal’s thread isolation to dynamically set the data source key to achieve data source switching.

Code implementation

Pom.xml is the same as scheme 1

A set of mapper:

UserInfoMapper

package org.msr.masterslave.mapper;

import org.msr.masterslave.model.UserInfo;

public interface UserInfoMapper {
    int deleteByPrimaryKey(Integer id);

    int insert(UserInfo record);

    int insertSelective(UserInfo record);

    UserInfo selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(UserInfo record);

    int updateByPrimaryKey(UserInfo record);
}
Copy the code

UserInfoMapper.xml


      
<! DOCTYPEmapper PUBLIC "- / / mybatis.org//DTD Mapper / 3.0 / EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.msr.masterslave.mapper.UserInfoMapper">
    <resultMap id="BaseResultMap" type="org.msr.masterslave.model.UserInfo">
        <id column="id" jdbcType="INTEGER" property="id"/>
        <result column="name" jdbcType="VARCHAR" property="name"/>
    </resultMap>
    <sql id="Base_Column_List">
        id
        , name
    </sql>
    <select id="selectByPrimaryKey" parameterType="java.lang.Integer" resultMap="BaseResultMap">
        select
        <include refid="Base_Column_List"/>
        from userInfo
        where id = #{id,jdbcType=INTEGER}
    </select>
    <delete id="deleteByPrimaryKey" parameterType="java.lang.Integer">
        delete
        from userInfo
        where id = #{id,jdbcType=INTEGER}
    </delete>
    <insert id="insert" parameterType="org.msr.masterslave.model.UserInfo">
        insert into userInfo (id, name)
        values (#{id,jdbcType=INTEGER}, #{name,jdbcType=VARCHAR})
    </insert>
    <insert id="insertSelective" parameterType="org.msr.masterslave.model.UserInfo">
        insert into userInfo
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="id ! = null">
                id,
            </if>
            <if test="name ! = null">
                name,
            </if>
        </trim>
        <trim prefix="values (" suffix=")" suffixOverrides=",">
            <if test="id ! = null">
                #{id,jdbcType=INTEGER},
            </if>
            <if test="name ! = null">
                #{name,jdbcType=VARCHAR},
            </if>
        </trim>
    </insert>
    <update id="updateByPrimaryKeySelective" parameterType="org.msr.masterslave.model.UserInfo">
        update userInfo
        <set>
            <if test="name ! = null">
                name = #{name,jdbcType=VARCHAR},
            </if>
        </set>
        where id = #{id,jdbcType=INTEGER}
    </update>
    <update id="updateByPrimaryKey" parameterType="org.msr.masterslave.model.UserInfo">
        update userInfo
        set name = #{name,jdbcType=VARCHAR}
        where id = #{id,jdbcType=INTEGER}
    </update>
</mapper>
Copy the code

Model code: UserInfo same as scheme 1

The service code:

UserInfoService

package org.msr.masterslave.service;

import org.msr.masterslave.config.MyDataSource;
import org.msr.masterslave.mapper.UserInfoMapper;
import org.msr.masterslave.model.UserInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/ * * *@author MaiShuRen
 * @site http://www.maishuren.top
 * @sinceLet * * / 2021-03-28
@Service("orderService")
public class UserInfoService {

    @Autowired
    private UserInfoMapper userInfoMapper;

    public UserInfo read(Integer id) {
        MyDataSource.setDataSource("slave");
        return userInfoMapper.selectByPrimaryKey(id);
    }

    public int write(UserInfo userInfo) {
        MyDataSource.setDataSource("master");
        returnuserInfoMapper.insert(userInfo); }}Copy the code

Testing:

package org.msr.masterslave;

import org.msr.masterslave.model.UserInfo;
import org.msr.masterslave.service.UserInfoService;
import org.springframework.context.support.ClassPathXmlApplicationContext;

/ * * *@author MaiShuRen
 * @site http://www.maishuren.top
 * @sinceThe 2021-03-28 did * * /
public class Main {
    public static void main(String[] args) {
        ClassPathXmlApplicationContext applicationContext = new ClassPathXmlApplicationContext("applicationContext.xml");
        UserInfoService userInfoService = applicationContext.getBean(UserInfoService.class);
        / / read the data
        UserInfo read = userInfoService.read(3);
        System.out.println(read.toString());
        / / write data
        UserInfo userInfo = new UserInfo();
        userInfo.setName("hahaha");
        intwrite = userInfoService.write(userInfo); System.out.println(write); }}Copy the code

Code diagram how it works

disadvantages

Dynamic multi-data sources, this solution we need to define a dynamic data source class, can dynamically switch data sources in the process of running the program

1. There is a lot of repetitive code such as configuration beans in Spring that configure multiple data sources

2. When the primary/secondary read/write operation is performed, the program needs to manually set the data in the ThreadLocal, so that data may be written to the secondary library and read to the primary library

3. If there are many slave nodes, there is no way to carry out load balancing

4. If a node crashes, we cannot switch to another node and fail over

Solution 3: Use Springboot

In fact, the use of Springboot is just a Springboot rewrite of the above two schemes. It can also be said to be the Springboot implementation of the two schemes above.

Renovation Plan 1

Core principles

Package isolation is also used with Mapper, which is configured using SpringBoot(convention over configuration) annotations

Code implementation

pom.xml


      
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.3.9. RELEASE</version>
		<relativePath/> <! -- lookup parent from repository -->
	</parent>
	<groupId>org.msr.masterslave</groupId>
	<artifactId>springboot-multi-datasource-1</artifactId>
	<version>0.0.1 - the SNAPSHOT</version>
	<name>springboot-multi-datasource-1</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter</artifactId>
		</dependency>

		<! Load mybatis with springBoot -->
		<dependency>
			<groupId>org.mybatis.spring.boot</groupId>
			<artifactId>mybatis-spring-boot-starter</artifactId>
			<! -- SpringBoot parent project does not specify version, we need to specify manually -->
			<version>1.3.2</version>
		</dependency>
		<! MySQL JDBC driver package -->
		<dependency>
			<groupId>mysql</groupId>
			<! Springboot's parent project does not need to manually specify the version.
			<artifactId>mysql-connector-java</artifactId>
		</dependency>
		<! --JDBC connection pool -->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>druid</artifactId>
			<version>1.1.1</version>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-configuration-processor</artifactId>
			<optional>true</optional>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>
	<resources>
		<resource>
			<directory>src/main/java</directory>
			<includes>
				<include>**/*.xml</include>
			</includes>
		</resource>
	</resources>
</project>
Copy the code

The code for model, Service, and Mapper is consistent with plan 1. Configuration of XML is done using configuration classes

Configuration file: application.properties

Database connection configuration information
master.datasource.url=JDBC: mysql: / / 192.168.79.150:3307 / test? useUnicode=true&characterEncoding=utf8&useSSL=false
master.datasource.driver=com.mysql.cj.jdbc.Driver
master.datasource.username=root
master.datasource.password=123456
Database connection configuration information
slave.datasource.url=JDBC: mysql: / / 192.168.79.150:3309 / test? useUnicode=true&characterEncoding=utf8&useSSL=false
slave.datasource.driver=com.mysql.cj.jdbc.Driver
slave.datasource.username=root
slave.datasource.password=123456
Copy the code

MasterDataSourceConfig

package org.msr.masterslave.config;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Getter;
import lombok.Setter;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/ * * *@author MaiShuRen
 * @site http://www.maishuren.top
 * @sinceThe 2021-03-28 did * /
@Getter
@Setter
@Configuration
@MapperScan(basePackages = {"org.msr.masterslave.master.mapper"}, sqlSessionFactoryRef = "masterSqlSessionFactoryBean")
public class MasterDataSourceConfig {

    @Value("master.datasource.username")
    private String masterUsername;
    @Value("master.datasource.password")
    private String masterPassword;
    @Value("master.datasource.driver")
    private String masterDriver;
    @Value("master.datasource.url")
    private String masterUrl;

    /** * Configure the master data source **@return* /
    @Bean
    public DruidDataSource masterDataSource(a) {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setUrl(masterUrl);
        druidDataSource.setUsername(masterUsername);
        druidDataSource.setPassword(masterPassword);
        druidDataSource.setDriverClassName(masterDriver);
        return druidDataSource;
    }

    @Bean
    public SqlSessionFactoryBean masterSqlSessionFactoryBean(DruidDataSource masterDataSource) {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(masterDataSource);
        returnsqlSessionFactoryBean; }}Copy the code

SlaveDataSourceConfig

package org.msr.masterslave.config;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Getter;
import lombok.Setter;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

/ * * *@author MaiShuRen
 * @site http://www.maishuren.top
 * @sinceThe 2021-03-28 did * /
@Getter
@Setter
@Configuration
@MapperScan(basePackages = {"org.msr.masterslave.slave.mapper"}, sqlSessionFactoryRef = "slaveSqlSessionFactoryBean")
public class SlaveDataSourceConfig {

    @Value("slave.datasource.username")
    private String slaveUsername;
    @Value("slave.datasource.password")
    private String slavePassword;
    @Value("slave.datasource.driver")
    private String slaveDriver;
    @Value("slave.datasource.url")
    private String slaveUrl;

    /** * Configure slave data source **@return* /
    @Bean
    public DruidDataSource slaveDruidDataSource(a) {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(slaveDriver);
        dataSource.setUrl(slaveUrl);
        dataSource.setUsername(slaveUsername);
        dataSource.setPassword(slavePassword);
        return dataSource;
    }

    @Bean
    public SqlSessionFactoryBean slaveSqlSessionFactoryBean(DruidDataSource slaveDruidDataSource) {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(slaveDruidDataSource);
        returnsqlSessionFactoryBean; }}Copy the code

test

package org.msr.masterslave;

import org.msr.masterslave.model.UserInfo;
import org.msr.masterslave.service.UserInfoService;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;

@SpringBootApplication
public class MultiDatasource1Application {

    public static void main(String[] args) {
        ConfigurableApplicationContext applicationContext = SpringApplication.run(MultiDatasource1Application.class, args);
        UserInfoService userInfoService = applicationContext.getBean(UserInfoService.class);

        UserInfo userInfo = new UserInfo();
        userInfo.setName("springboot");
        int write = userInfoService.write(userInfo);
        System.out.println(write);

        UserInfo read = userInfoService.read(1); System.out.println(read.toString()); }}Copy the code

disadvantages

To plan a

Renovation Plan 2

Core principles

With the second scheme

Code implementation

The maven dependency and configuration file application.properties for POM.xml is the same as in the previous section, and the code for model, service, and mapper is the same.

Dynamic data source Springboot implementation

MyDataSource

package org.msr.masterslave.config;

import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

/** * author: MaiShuRen * site: http://www.maishuren.top * since: 2021-03-29 23:01 **/
public class MyDataSource extends AbstractRoutingDataSource {
    public static final ThreadLocal<String> LOCAL = new ThreadLocal<>();
    @Override
    protected Object determineCurrentLookupKey(a) {
        return getDataSource();
    }

    public static void setDataSource(String dataSourceKey) {
        LOCAL.set(dataSourceKey);
    }

    public static String getDataSource(a) {
        returnLOCAL.get(); }}Copy the code

MyDataSourceConfig

package org.msr.masterslave.config;

import com.alibaba.druid.pool.DruidDataSource;
import lombok.Getter;
import lombok.Setter;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import java.util.HashMap;
import java.util.Map;

/ * * *@author MaiShuRen
 * @site http://www.maishuren.top
 * @sinceThe 2021-03-29 23:01 * * /
@Getter
@Setter
@Configuration
@MapperScan(basePackages = {"org.msr.masterslave.mapper"}, sqlSessionFactoryRef = "sqlSessionFactoryBean")
public class MyDataSourceConfig {

    @Value("master.datasource.username")
    private String masterUsername;
    @Value("master.datasource.password")
    private String masterPassword;
    @Value("master.datasource.driver")
    private String masterDriver;
    @Value("master.datasource.url")
    private String masterUrl;

    @Value("slave.datasource.username")
    private String slaveUsername;
    @Value("slave.datasource.password")
    private String slavePassword;
    @Value("slave.datasource.driver")
    private String slaveDriver;
    @Value("slave.datasource.url")
    private String slaveUrl;

    @Bean
    public DruidDataSource masterDruidDataSource(a) {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(masterDriver);
        druidDataSource.setUrl(masterUrl);
        druidDataSource.setUsername(masterUsername);
        druidDataSource.setPassword(masterPassword);
        return druidDataSource;
    }

    @Bean
    public DruidDataSource salveDruidDataSource(a) {
        DruidDataSource druidDataSource = new DruidDataSource();
        druidDataSource.setDriverClassName(slaveDriver);
        druidDataSource.setUrl(slaveUrl);
        druidDataSource.setUsername(slaveUsername);
        druidDataSource.setPassword(slavePassword);
        return druidDataSource;
    }

    @Bean
    public MyDataSource myDataSource(DruidDataSource masterDruidDataSource, DruidDataSource salveDruidDataSource) {
        MyDataSource myDataSource = new MyDataSource();
        myDataSource.setDefaultTargetDataSource(masterDruidDataSource);
        Map dataSourceMap = new HashMap(16);
        dataSourceMap.put("master", masterDruidDataSource);
        dataSourceMap.put("slave", salveDruidDataSource);
        myDataSource.setTargetDataSources(dataSourceMap);
        return myDataSource;
    }

    @Bean
    public SqlSessionFactoryBean sqlSessionFactoryBean(MyDataSource myDataSource) {
        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();
        sqlSessionFactoryBean.setDataSource(myDataSource);
        returnsqlSessionFactoryBean; }}Copy the code

test

package org.msr.masterslave;

import org.msr.masterslave.model.UserInfo;
import org.msr.masterslave.service.UserInfoService;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ConfigurableApplicationContext;

@SpringBootApplication
public class SpringbootDynamicDatasourceApplication {

    public static void main(String[] args) {
        ConfigurableApplicationContext applicationContext = SpringApplication.run(SpringbootDynamicDatasourceApplication.class, args);
        UserInfoService userInfoService = applicationContext.getBean(UserInfoService.class);
        UserInfo userInfo = new UserInfo();
        userInfo.setName("springboot");
        int write = userInfoService.write(userInfo);
        System.out.println(write);

        UserInfo read = userInfoService.read(1); System.out.println(read.toString()); }}Copy the code

disadvantages

It’s the same as the scheme.

Summary of multiple data sources

After seeing the implementation of the above four types, it can be seen that there is no great difference between them. The problem of scheme 1 has not been solved until the end. If we need to solve the above problems, we need to write a set of management code to dynamically switch data sources for read and write separation, failover and load balancing. That’s how the code gets complicated. This is where database middleware comes in, such as Apache ShardingSphere and MyCat. I personally prefer to use ShardingSphere.

Apache ShardingSphere is an ecosystem of open source distributed database solutions. It is composed of JDBC, Proxy and Sidecar (in planning), which can be deployed independently and support mixed deployment. They all provide standardized data level expansion, distributed transactions, and distributed governance, and can be applied to diverse application scenarios such as Java isomorphism, heterogeneous languages, and cloud native.

ShardingSphere(JDBC&Proxy) is used for database middleware