MySQL read / write separation

Read / write separation

1、MySQL Master slave copy

Introduce ：

MySQL Master-slave replication means that data can be copied from one MySQL The database server master node is replicated to one or more slave nodes .MySQL Asynchronous replication is used by default , In this way, the slave node does not need to visit the master server all the time to update its data , Data can be updated on a remote connection , The slave node can copy all databases in the master database or specific databases , Or a specific watch .

Configure the main library ：

First step ： modify MySQL Profile of the database /etc/my.cnf

log-bin=mysql-bin   #[ must ] Enable binary logging 
server-id = 100     #[ must ] The server is unique ID

The second step : restart MySQL service

systemctl restart mysqld

The third step ： Sign in MySQL database , Execute the following SQL

--  Create a user for master-slave replication ,slave =  user name  % =  On behalf of all IP All can be connected （ It can be changed into specific ip） 123456 =  password 
CREATE USER 'slave'@'%' IDENTIFIED with mysql_native_password by '1111';
--  Give the user permission 
GRANT replication slave on *.* to 'slave'@'%';
--  Refresh configuration 
FLUSH PRIVILEGES;
--  Check whether the created user exists 
use mysql;
select user, host from user;
--  View the current binlog Nodes and position value 
show master status;

Configure slave Slave

First step ： modify MySQL Profile of the database

server-id = 101     #[ must ] The server is unique ID

The second step ： restart MySQL service

The third step ： Sign in MySQL, Execute the following SQL

--  Add master database information and read binlog Node information of 
CHANGE MASTER TO MASTER_HOST = '47.112.138.176',
MASTER_USER = 'slave',
MASTER_PASSWORD = '1111',
MASTER_LOG_FILE = 'mysql-bin.000001',
MASTER_LOG_POS = 988
--  Start from library 
START SLAVE;
--  Check the master-slave status 
SHOW SLAVE STATUS;

2、 Read write separation cases （Sharding-JDBC）

1、Sharding-JDBC Introduce

2、Sharding-JDBC Introductory cases

1、 Import dependence ：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

2、 Configure the read-write separation rule in the configuration file

spring:
  shardingsphere:
    datasource:
      names:
        master,slave
      #  The main data source 
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip/rw?serverTimezone=Asia/Shanghai
        username: root
        password: 1111
      #  From a data source 
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/rw?serverTimezone=Asia/Shanghai
        username: root
        password: 1111
    masterslave:
      #  Read write separation configuration 
      load-balance-algorithm-type: round_robin
      #  The final data source name 
      name: dataSource
      #  Main database data source name 
      master-data-source-name: master
      #  From the list of library data source names , Multiple commas separate 
      slave-data-source-names: slave
    props:
      sql:
        show: true # Turn on SQL Show , Default false
  # 3、 Allow... To be configured in the configuration file Bean Define override configuration items 
  main:
    allow-bean-definition-overriding: true

3、 The project realizes the separation of reading and writing

1、 Import dependence ：

<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
    <version>4.0.0-RC1</version>
</dependency>

2、 Configure the read-write separation rule in the configuration file

spring:
  shardingsphere:
    datasource:
      names:
        master,slave
      #  The main data source 
      master:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://ip/reggie?serverTimezone=Asia/Shanghai
        username: root
        password: 1111
      #  From a data source 
      slave:
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://localhost:3306/reggie?serverTimezone=Asia/Shanghai
        username: root
        password: 1111
    masterslave:
      #  Read write separation configuration 
      load-balance-algorithm-type: round_robin
      #  The final data source name 
      name: dataSource
      #  Main database data source name 
      master-data-source-name: master
      #  From the list of library data source names , Multiple commas separate 
      slave-data-source-names: slave
    props:
      sql:
        show: true # Turn on SQL Show , Default false
  # 3、 Allow... To be configured in the configuration file Bean Define override configuration items 
  main:
    allow-bean-definition-overriding: true