Overview of master-slave replication

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. The master server is responsible for reading and writing, and the slave server is only responsible for reading. The master-slave replication is also called master/slave. The master is the master and the slave is the slave, but it is not mandatory.

Primary/secondary replication can realize database backup and read/write separation

2. The principle of master-slave replication (process | step)

When data on the master server changes, the changes are written to the binary event log file
The salve slave server probes the binary log files on the master server at certain intervals to see if they have changed (the size of the binary file is different). If the binary event log of the master server is detected to have changed, an I/O Thread is started to request the master binary event log
At the same time, the master server starts a dump Thread for each I/O Thread to send binary event days to it
The slave saves binary event logs received from the server to the local relay log file
The salve slave server will start SQL Threads to read binary logs from the secondary log and replay them locally so that their data is consistent with that of the master server.
Finally, I/O threads and SQL threads go to sleep, waiting to be awakened the next time

Note: The master-slave replication process has very little delay and has little impact

2. Multi-instance construction

1. An overview of the

MySQL multi-instance means that after installing MySQL, multiple MySQL databases (instances) are started simultaneously on a Linux server. It is not necessary to install multiple MySQL databases (suitable for technical research and learning scenarios). So we need to install MySQL separately on each server (for actual online production environment)

We plan to start multiple MySQL servers on one Linux server, which is suitable for our technical research and learning. If we want to start MySQL servers on multiple Linux servers, the configuration and operation are exactly the same as those on one Linux server.

How to start multiple MySQL databases (instances) simultaneously on a Linux server? This is done by configuring separate configuration files for each database instance, that is, each database instance has its own configuration file

2. Configure multiple instances

Download address: cdn.mysql.com//Downloads/…

(1) Check whether mariadb is installed first

Check if Linux has the Mariadb database installed. The Mariadb database is a branch of mysql. It’s free and open source. Mariadb will conflict with MSYQL. First check that mariadb is installed and uninstall it.

Check Check command:

yum  list  installed  |  grep mariadb
Copy the code

If the mariadb database is installed on Linux, uninstall it first, mariadb database may conflict with mysql.

Execute the uninstallation command:

yum -y remove mariadb-libs.x86_64
Copy the code

Mariadb-libs. x86_64 is the mariadb package searched in step 2, which may be different on different machines. -y parameter is used to confirm deletion.

Wait until the uninstallation is Complete. The message “Complete” indicates that the uninstallation is Complete

(2) Decompress and rename MySQL

First, use Xftp to upload the file mysql-5.7.18-linux-glibc2.5-x86_64.tar

Then unzip MySQL:

Execute command:

Tar -zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz -c /usr/local/
Copy the code

Rename the decompressed mysql-5.7.18-linux-glibc2.5-x86_64 to mysql-5.7.18 or mysql. You are advised to rename it to mysql-5.7.18 to determine the version of mysql

Execute command:

Mysql - 5.7.18 - Linux - mv glibc2.5 x86_64 mysql -- 5.7.18Copy the code

(3) Create multiple directories for storing data

The data folder is used by mysql to store database files. Table data of the database is stored in the Data directory. By default, there is no data directory. You can manually create a data directory under the mysql-5.7.18 directory, switch to the mysql-5.7.18 directory, and run the create folder command

Run the following command in the mysql-5.7.18 root directory to create four directories for storing data in the mysql installation home directory

mkdir  data
mkdir  data/3307
mkdir  data/3308
mkdir  data/3309
mkdir  data/3310
Copy the code

(4) Create a user

To create a mysql user, run the mysql command mysqld. This command initializes basic MSYQL information. Execute command:

useradd  mysql
Copy the code

(4) Run the /usr/local/mysql-5.7.18/bin command in the MySQL directory

Parameter description:

–initialize-insecure: initialize mysql, create mysql root, the initial password is empty.
–user: Specifies the Linux user name for running the msyqld command
–datadir: specifies the location where the mysql data file is stored, specifying the four directories just created.
–basedir: Directory of the MSYQL installer. The directory location is referenced to the local Settings

Note: The directory should be modified according to the actual situation

./mysqld --initialize-insecure --basedir=/usr/local/ mysql - 5.7.18 - datadir = / usr /local/mysqld --initialize -- insecure --basedir=/usr/local/ mysql - 5.7.18 - datadir = / usr /local/mysqld --initialize -- insecure --basedir=/usr/local/ mysql - 5.7.18 - datadir = / usr /local/mysqld --initialize -- insecure --basedir=/usr/local/ mysql - 5.7.18 - datadir = / usr /local/ mysql - 5.7.18 / data / 3310 - user = mysqlCopy the code

(5) Configure the main configuration file of MySQL

Create a my.cnf file in the data/3307, data/3308, data/3309, data/3310 directories, and configure the following contents in the four my.cnf files

[client] : indicates the client that configures MySQL
[mysqld] : Configure the MySQL server

Note: The port number is changed for different instance configurations

[client]
port=3307
socket=/usr/local/mysql5.718./data/3307/mysql.sock
default-character-set=utf8

[mysqld]
port=3307
socket=/usr/local/mysql5.718./data/3307/mysql.sock
datadir=/usr/local/mysql5.718./data/3307
log-error=/usr/local/mysql5.718./data/3307/error.log
pid-file=/usr/local/mysql5.718./data/3307/mysql.pid

character-set-server=Utf8 # lower_case_table_names=1Table names are case sensitive. Default is1, case insensitive;0Case sensitive AUTOCOMMIT=1# Whether the transaction is automatically committed. Default is1, automatic submission;0No automatic submissionCopy the code

(6) Start MySQL

Go to /usr/local/mysql-5.7.18/bin and run the msyqld_safe command to specify the configuration file and start the mysql service:

–defaults-file: specifies the configuration file,
& : indicates background startup

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

(7) Configure after startup

Log in to each MySQL instance client and run the following command in mysql-5.7.24/bin directory:
- Method 1: Log in using a host or port (recommended)
```
/mysql -uroot -p -p3307 -h127.0.0.1Copy the code
```
- Method 2: Use the socket file
  - -p specifies the password. If there is no password, you can not write -p.
  - -s is the specified sock file. The mysql.sock file is the IP address and port file used by the server to communicate with the local client
```
./mysql -uroot -p -S /usr/local3307 / mysql/mysql - 5.7.24 / data. The sockCopy the code
```

Mysql > change password (123456) mysql > change password (123456)

alter user 'root'@'localhost' identified by '123456';
Copy the code

Authorize remote access (so that the remote client Navicat can access it)
- Among them*. *One of the first*Represents all database names, second*Represents all database tables
- root@'%'“Root” indicates the user name,%% can also specify a specific IP address, such as [email protected]@192.168.10.129
```
grant all privileges on *.* to root@The '%' identified by '123456';
Copy the code
```
Refresh the permissions
```
flush privileges; 
Copy the code
```
Switch to /usr/local/mysqladmin and run the mysqladmin command to shutdown the multi-instance
- Method 1: Using a Port and Disabling the host (recommended)
```
/mysqladmin -uroot -p -p3307 -h127.0.0.1 shutdownCopy the code
```
- Method 2: Use the socket file
```
 ./mysqladmin -uroot -p -S /usr/local3307 / mysql/mysql - 5.7.24 / data. The sock shutdownCopy the code
```
- Mode 3: Go to the CLI of the MySQL client and run shutdown

Three, one master and many slave architecture

1. An overview of the

When the system reads a lot of data, in order to share the read pressure, one master and many slave architecture can be adopted to realize the separation of read and writeHere, we use multiple MySQL instances configured above, with 3307 as the primary server and 3308,3309,3310 as the secondary server

2. The configuration

My.cnf = my.cnf = my.cnf = my.cnf = my.cnf
- Log-bin: enables binary logging and specifies the binary file name
- Server-id: indicates the server id, which uniquely identifies the MySQL server
```
log-bin=mysql-bin
server-id=3307
Copy the code
```
MySQL (3308,3309,3310) MySQL (3308,3309,3310) MySQL (my.cnf)
```
server-id=3308
Copy the code
```

4. Configure the primary and secondary functions

(1) Create a user

Start the master MySQL server, log in to the master MySQL client, create an account for replication data on the master server and authorize it:

grant replication slave on *.* to 'copy'@The '%' identified by '123456';
Copy the code

Create a MySQL user that the primary server uses to copy data to the secondary server

Note: This statement can be used to authorize, create a user, and change the password

(2) Reset the status of the primary server

Check the primary server status:
```
show master status;
Copy the code
```
- Default default value for mysql master server:
  - File: mysql – bin. 000001
  - Position: 154
If the primary service state is not the initial state, you need to reset the state:
```
reset master;
Copy the code
```

(3) Reset the slave server status

View the status of the slave server
```
show slave status;
Copy the code
```
- The initial state is Empty set

If the slave server is not in the initial state, it is recommended to reset it

stop slave; # Stop replication, which is equivalent to terminating IO and slave serverSQLThread reset slave;Copy the code

To set the master of the slave server, execute on the client of the slave server:
- Master_host: IP address of the master server
- Master_port: indicates the port of the master server
- Master_user, master_password: specifies the user name and password created in 2.4.1 to perform master/slave replication
- Master_log_file: the value of File found in 2.4.2
- Master_log_pos: Position found in 2.4.2, if the master server is the initial value, 154
```
change master to master_host='192.168.235.128',master_user='copy',
master_port=3306,master_password='123456',
master_log_file='mysql-bin.000001',master_log_pos=154;
Copy the code
```
Execute the start copy command on the slave machine:
```
start slave;
Copy the code
```

5. Verify

Run the following command on the client of the secondary server:
- \G: indicates formatted output
```
show slave status \G;
Copy the code
```
Display results:Note: IfSlave_IO_Running 和 Slave_SQL_RunningIf the values are YES, the master/slave relationship is normal
Create the database, tables, and data on the primary server, and then check to see if it has been replicated on the secondary server
To view the contents of the binlog file of the primary/secondary replication:
```
show binlog events in 'mysql-bin.000001'\G;
Copy the code
```
View the results:

Four, multi-master multi-slave architecture

1. An overview of the

One master with many slaves can relieve the pressure of reading, but once the master is down, it can’t write, so we can use two master and two slave architecture to improve its shortcomings.Framework planning:

Master 3307 –> Slave 3309
Master 3308 –> Slave 3310
3307 <– > 3308 Each other is the master and slave
Two write nodes, and two read nodes under each write node

2. Configure the environment

My.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf = my.cnf
```
log-bin=mysql-bin
server-id=3307
Copy the code
```
Select * from MySQL configuration file my.cnf where server id cannot be the same:
```
server-id=3309
Copy the code
```

Add to my.cnf configuration file of the first primary server (3307) :

auto_increment_increment=2# Primary key auto_increment_offset=1# Different place, the primary key grows with the starting value log-slave-updates
sync_binlog=1
Copy the code

In the configuration file my.cnf of the first primary server (3308), add:

auto_increment_increment=2
auto_increment_offset=2# different points correspond to the starting value log-slave-updates
sync_binlog=1
Copy the code

Configuration items:

Auto_increment_increment:

Auto_increment_increment =n auto_increment_increment=n auto_increment_increment=n is set to the number of Master servers

Auto_increment_offset = 1:

Set auto_increment to 1 so that Master’s auto_increment field increments: 1, 3, 5, 7… For example, if offset=1 for master1, offset=2 for master2, offset=3 for master3, offset=3 for master1, offset=2 for master2, offset=3 for master3, offset=3 for master1, offset=1 for master2, offset=3 for master3

The log – slave – updates:

After log-slave-updates is enabled, the data copied from the slave database to the master database is written to the log-bin log file. In dual master mode, the log-slave-updates configuration item must be configured, otherwise the data updated on master1 (3307) will be updated on Master2 (3308) and slave1 (3309), but not on slave2 (3310)

Sync_binlog:

-sync_binlog =0; -sync_binlog =0; -sync_binlog =0; -sync_binlog =0; Let the Filesystem decide when to synchronize or when the cache is full. -sync_binlog =n, after each n commit, MySQL will perform a disk synchronization command such as fsync to force the data in binlog_cache to disk.

3. The service starts

/usr/local/mysql-5.7.24/bin /usr/local/mysql-5.7.24/bin /usr/local/mysql-5.7.24/bin

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

4. Master/slave Settings

(1) Primary server Settings

Run the following command in /usr/local/mysql-5.7.24/bin:

` ` ` bash. / mysql - uroot - p - P3307 | 3308 - h127.0.0.1 ` ` `Copy the code

On two primary server (3307 | 3308) to create account and copy data authorization

grant replication slave on *.* to 'copy'@The '%' identified by '123456';
Copy the code

On two main server (3307 | 3308) stop copying and reset the server status
```
reset master;
Copy the code
```

(2) Secondary server Settings

The client that logs in to the slave server (all four servers are slaves) :

Run the following command in /usr/local/mysql-5.7.24/bin:

` ` ` bash. / mysql - uroot - p - P3307 | 3308 | 3309 | 3310 - h127.0.0.1 ` ` `Copy the code

Stop replication on the slave server and reset server status:
```
stop slave;
reset slave;
Copy the code
```

Set the master of the slave server.

Set master to 3307 for secondary server 3308 and 3309:

change master to master_host='192.168.235.128',master_user='copy',
master_port=3307,master_password='123456',
master_log_file='mysql-bin.000001',master_log_pos=154;
Copy the code

Set primary to 3308 for secondary server 3307 and 3310:

change master to master_host='192.168.235.128',master_user='copy',
master_port=3308,master_password='123456',
master_log_file='mysql-bin.000001',master_log_pos=154;
Copy the code

Run the start copy command on the slave machine (on all 4 MySQL servers)
```
start slave;
Copy the code
```

5. Verify

The verification method of verifying the same master and many slaves will not be described here

Vi. Multiple data sources

The problem of multiple data sources refers to the need to connect multiple databases in a project project. The master and slave of the database are configured above. There may be the following situations in the actual development

Read/write separation: Use of multiple data sources in a project
Only the master library (read and write) is operated in the project, and the master slave is only used as a backup. The programmer does not need to care about the master slave structure

Here we mainly introduce the problem of multiple data sources under Spring+Mybatis and SpringBoot+Mybatis development mode

1. Spring+Mybatis

Core idea: Based on dynamic data sources, you know which data source to use at run time

(1) Add 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 dependent -->
<! --Mybatis framework dependencies -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>
<! --Mybatis integrates with Spring -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>1.3.2</version>
</dependency>
<! MySQL database connection driver -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.43</version>
</dependency>
<! --JDBC database connection pool -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.1</version>
</dependency>
Copy the code

Add resource, specifying to compile Mybatis mapping file

<resource>
    <directory>src/main/java</directory>
    <includes>
        <include>**/*.xml</include>
    </includes>
</resource>

Copy the code

(2) Create the MyThreadLocal class

A value is stored in each thread that indicates which data source the thread is using to operate on the database, using ThreadLocal

public class MyThreadLocal {
    private static ThreadLocal<String> local = new ThreadLocal<String>();

	// Specify which data source is used by the current thread
    public static void addDataSource(String dataSourceName) {
        local.set(dataSourceName);
    }

	// Get the data source used by the current thread
    public static String getDataSource(a) {
        return local.get();
    }

	// Reset the data source used by the current thread
    public static void removeDataSource(a) { local.remove(); }}Copy the code

ThreadLocal Best solution: ThreadLocal

(3) Custom data source

The design idea is to put all the data sources into a map set. By specifying the key of the map set, different data sources can be dynamically obtained
Implement a custom data source, data source object is to realize javax.mail. SQL. The DataSource interface, AbstractRoutingDataSource object’s parent class implements this interface
DynamicDataSource AbstractRoutingDataSource abstract class, the class hierarchy and abstract methods determineCurrentLookupKey ()
In front of the database operation, we use which database set up in advance, and then at the time of the specified data source, automatically calls the determineCurrentLookupKey () method, get the specified value, to invoke the specified data source.
How to determineCurrentLookupKey (dynamic) specifies the key? The intuitive idea might be to define a static variable, but static variables are thread-safe, so we need to maintain a variable for each thread that specifies which database to connect to, so we use the ThreadLocal class, which is a copy of the thread

It is recommended to define constants as keys for dynamic data sources

public static final String DATASOURCE_KEY_3307 = "3307"; .Copy the code

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

// Define a custom data source class that inherits from an abstract parent of a dynamic data source
public class DynamicDataSource extends AbstractRoutingDataSource {
    // Implement the abstract method in the parent class to return a specific data source
    protected Object determineCurrentLookupKey(a) {
        returnMyThreadLocal.getDataSource(); }}Copy the code

(4) Configure the data source

How do you map the specified key to the different data sources? DynamicDataSource class is configured in applicationContext-MyBatis. XML


      
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">
    <! -- specify data source -->
    <bean id="masterDataSource3307" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="JDBC: mysql: / / 192.168.245.128:3307 / workdb"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    <bean id="masterDataSource3308" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="JDBC: mysql: / / 192.168.245.128:3308 / workdb"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    <bean id="slaveDataSource3309" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="JDBC: mysql: / / 192.168.245.128:3309 / workdb"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    <bean id="slaveDataSource3310" class="com.alibaba.druid.pool.DruidDataSource">
        <property name="driverClassName" value="com.mysql.jdbc.Driver"/>
        <property name="url" value="JDBC: mysql: / / 192.168.245.128:3310 / workdb"/>
        <property name="username" value="root"/>
        <property name="password" value="123456"/>
    </bean>
    
    <! -- Define a custom dynamic data source Bean -->
    <bean id="dynamicDataSource" class="com.wkcto.masterslave.datasource.DynamicDataSource">
        <! -- Define the default data source, and use masterDataSource if no data source is switched -->
        <property name="defaultTargetDataSource" ref="masterDataSource"/>
        <! The key of the Map collection must be consistent with the content of the data in the Java business method ThreadLocal. The value of the Map collection must be our specific data source object.
        <property name="targetDataSources">
            <map>
            	<! In Java, we can specify the key here as a constant, and we can map the key in ThreadLoacl to the data source one by one -->
                <entry key="3307" value-ref="masterDataSource3307"></entry>
                <entry key="3308" value-ref="masterDataSource3308"></entry>
                <entry key="3309" value-ref="slaveDataSource3309"></entry>
                <entry key="3310" value-ref="slaveDataSource3310"></entry>
            </map>
        </property>
    </bean>

    <bean id="sessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <! -- Load dynamic data source -->
        <property name="dataSource" ref="dynamicDataSource"/>
    </bean>

    <bean id="mapperScannerConfigurer" class="org.mybatis.spring.mapper.MapperScannerConfigurer">
        <property name="sqlSessionFactoryBeanName" value="sessionFactory"/>
        <property name="basePackage" value="com.wkcto.masterslave.mapper"/>
    </bean>
</beans>
Copy the code

(5) Dynamically switch the database

Before working with the database, select the database to use
Clean ThreadLocal after database operation (reset data source)
- Since Tomcat uses thread pools, when a thread is used up, it is not destroyed. Instead, it is added to the pool again. In order to avoid unnecessary trouble when using the thread again, you need to empty ThreadLoacl

public void addStu(Stu stu) {
   // Data source switch
    MyThreadLocal.addDataSource("master");
    stuMapper.insert(stu);
    stuMapper.insert(stu);
    // Clear ThreadLoacl when it is used up
    MyThreadLocal.removeDataSource();
}

public List<Stu> selectAll(a) {
    MyThreadLocal.addDataSource("slave");
    List<Stu>list=stuMapper.selectAll();
    MyThreadLocal.removeDataSource();
    return list;
}
Copy the code

2. SpringBoot+Mybatis

The same as Spring+Mybatis, but change the configuration file to a configuration class:

The id in the configuration file label is the method name in the configuration class, and class is the return value type of the method

import com.alibaba.druid.pool.DruidDataSource;
import com.wkcto.masterslave.datasource.DynamicDataSource;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;
import java.util.HashMap;
import java.util.Map;

@Configuration
public class DataSourceConfig {

    @Bean
    public DruidDataSource masterDataSource3307(a){
        DruidDataSource druidDataSource=new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("JDBC: mysql: / / 192.168.245.128:3307 / workdb");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("123456");
        return druidDataSource;
    }

    @Bean
    public DruidDataSource masterDataSource3308(a){
        DruidDataSource druidDataSource=new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("JDBC: mysql: / / 192.168.245.128:3308 / workdb");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("123456");
        return druidDataSource;
    }

    @Bean
    public DruidDataSource slaveDataSource3309(a){
        DruidDataSource druidDataSource=new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("JDBC: mysql: / / 192.168.245.128:3309 / workdb");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("123456");
        return druidDataSource;
    }

    @Bean
    public DruidDataSource slaveDataSource3310(a){
        DruidDataSource druidDataSource=new DruidDataSource();
        druidDataSource.setDriverClassName("com.mysql.jdbc.Driver");
        druidDataSource.setUrl("JDBC: mysql: / / 192.168.245.128:3310 / workdb");
        druidDataSource.setUsername("root");
        druidDataSource.setPassword("123456");
        return druidDataSource;
    }

    @Bean
    public DynamicDataSource dynamicDataSource(DataSource masterDataSource, DataSource slaveDataSource){
        DynamicDataSource dataSource=new DynamicDataSource();
        dataSource.setDefaultTargetDataSource(masterDataSource);
        Map map=new HashMap();
        map.put("3307r",masterDataSource3307);
        map.put("3308",masterDataSource3308);
        map.put("3309",slaveDataSource3309);
        map.put("3310",slaveDataSource3310);
        dataSource.setTargetDataSources(map);
        return dataSource;
    }
     @Bean
     public SqlSessionFactoryBean sessionFactory(DataSource dynamicDataSource){
         SqlSessionFactoryBean factoryBean=new SqlSessionFactoryBean();
         factoryBean.setDataSource(dynamicDataSource);
         return factoryBean;
     }
     @Bean
     public MapperScannerConfigurer mapperScannerConfigurer(a){
          MapperScannerConfigurer mapperScannerConfigurer=new MapperScannerConfigurer();
          mapperScannerConfigurer.setSqlSessionFactoryBeanName("sessionFactory");
          mapperScannerConfigurer.setBasePackage("com.wkcto.masterslave.mapper");
          returnmapperScannerConfigurer; }}Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

The MySQL primary and secondary clusters are set up

Overview of master-slave replication

2. The principle of master-slave replication (process | step)

2. Multi-instance construction

1. An overview of the

2. Configure multiple instances

(1) Check whether mariadb is installed first

(2) Decompress and rename MySQL

(3) Create multiple directories for storing data

(4) Create a user

(4) Run the /usr/local/mysql-5.7.18/bin command in the MySQL directory

(5) Configure the main configuration file of MySQL

(6) Start MySQL

(7) Configure after startup

Three, one master and many slave architecture

1. An overview of the

2. The configuration

4. Configure the primary and secondary functions

(1) Create a user

(2) Reset the status of the primary server

(3) Reset the slave server status

5. Verify

Four, multi-master multi-slave architecture

1. An overview of the

2. Configure the environment

3. The service starts

4. Master/slave Settings

(1) Primary server Settings

(2) Secondary server Settings

5. Verify

Vi. Multiple data sources

1. Spring+Mybatis

(1) Add dependencies

(2) Create the MyThreadLocal class

(3) Custom data source

(4) Configure the data source

(5) Dynamically switch the database

2. SpringBoot+Mybatis

The MySQL primary and secondary clusters are set up

Overview of master-slave replication

2. The principle of master-slave replication (process | step)

2. Multi-instance construction

1. An overview of the

2. Configure multiple instances

(1) Check whether mariadb is installed first

(2) Decompress and rename MySQL

(3) Create multiple directories for storing data

(4) Create a user

(4) Run the /usr/local/mysql-5.7.18/bin command in the MySQL directory

(5) Configure the main configuration file of MySQL

(6) Start MySQL

(7) Configure after startup

Three, one master and many slave architecture

1. An overview of the

2. The configuration

4. Configure the primary and secondary functions

(1) Create a user

(2) Reset the status of the primary server

(3) Reset the slave server status

5. Verify

Four, multi-master multi-slave architecture

1. An overview of the

2. Configure the environment

3. The service starts

4. Master/slave Settings

(1) Primary server Settings

(2) Secondary server Settings

5. Verify

Vi. Multiple data sources

1. Spring+Mybatis

(1) Add dependencies

(2) Create the MyThreadLocal class

(3) Custom data source

(4) Configure the data source

(5) Dynamically switch the database

2. SpringBoot+Mybatis

Related Posts

Introduction to Seata in Distributed Transactions (7)

Design of settlement system under microservice architecture

The adapter pattern, a common Java design pattern for beginners