In the actual production environment, to ensure the stability of the database, we usually configure a dual-system hot backup mechanism for the database. In this way, when the master database crashes, the slave database can immediately switch over to the master database and synchronize data from the master database to the slave database through the master-slave replication. Write code in the business code to achieve read/write separation (let the primary database handle transactional add, change, delete operations, while the secondary database handles query operations) to increase the concurrent load of the database.

Let’s use the latest version of Mysql database (8.0.16) in conjunction with SpringBoot to achieve this complete step (one master, one slave).

Mysql > install mysql

  • From the https://dev.mysql.com/downloads/mysql/ page to download mysql installation package, I am here to download mysql8.0.16 Linux – Generic.

  • Prepare two VMS for installing mysql and upload the downloaded file mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz to the server /app/mysql

    • 192.168.249.131 CENTOS7 main
    • 192.168.249.129 CENTOS7 from

  • Check the firewall status. Disable the firewall before starting it

service firewalld status  Check the firewall status
service firewalld stop   ## Disable the firewall
Copy the code

  • Run the following command to decompress the xz file to a tar file: xz -d mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz

  • Decompress the installation package tar -xvf mysql-8.0.16-linux-gl-ibc2.12-x86_64.tar

  • Create data folder under /app/mysql to store data

  • Create a mysql user group and a mysql user

groupadd mysql                                  Create a user group
useradd -g mysql -d /app/mysql mysql    Create mysql user under user group and authorize related directory
groupdel mysql                                  # delete user group name (if user group exists)
userdel mysql                                   ## Delete user (if user already exists)
Copy the code

  • /mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld –user=mysql –basedir=/app/mysql –datadir=/app/mysql/data –initialize

    2019-07-01T02:05:52.681626Z 0 [Warning] [MY-011070] [Server] 'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed inA Future Release. 2019-07-01T02:05:52.681694z 0 [System] [my-013169] [Server] /app/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.16) initializing of serverinProgress as Process 1479 2019-07-01T02:05:52.681726z 0 [ERROR] [my-010338] [Server] Can't find error-message file '/app/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir'Configuration directive. 2019-07-01T02:05:55.713747z 5 [Note] [my-010454] [Server] A temporary password is generated for root@localhost: Xa6 (H>rK/rCopy the code

Note that mysql generates a default temporary password, as shown above, which needs to be saved and then changed

  • Run the following command to create the mysql service: cp mysql-8.0.16-linux-glibc2.12-x86_64/support-files/mysql.server /etc/init.d/mysqld

  • Modify the mysql configuration file vi /etc/my. CNF Add the following configuration

    [mysqld] port=3306 basedir=/app/mysql/mysql-8.0.16-linux-glibc2.12-x86_64 datadir=/app/mysql/data socket=/ TMP /mysql.sock  symbolic-links=0 [mysqld_safe]log-error=/app/mysql/data/log/error.log
pid-file=/app/mysql/data/mysql.pid
user=mysql
tmpdir=/tmp
character_set_server=utf8
default-storage-engine=INNODB
init_connect='SET NAMES utf8'! includedir /etc/my.cnf.dCopy the code

If the log permissions related errors, please set up the corresponding log files, and give the mysql user authorization chown -r mysql: mysql/app/mysql/data/log/error. The log

  • Start mysql service service mysqld start

  • Mysql client software connection ln -s /app/mysql/mysql-8.0.16-linux-glibc2.12-x86_64/bin/mysql /usr/local/bin/mysql

  • Log in to the mysql database to change the password

Mysql -uroot -p Password# # login
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '000000';
Copy the code
  • Setting remote Login
use mysql;
update user set host=The '%' where user='root' limit 1;
flush privileges;
Copy the code

Configure primary/secondary synchronization for mysql (binlog)

The principle

  • The Master logs changes to the binary log specified in the log-bin configuration file. These records are called binary log events.
  • Slave uses the I/O thread to read binary log events from the Master and write them to its relay log.
  • Slave Rewrites events in the trunk log, executing them one by one locally to store the data locally and reflect changes to its own data (data replay).

Replication requirements

  • The operating system versions and bits of the primary and secondary servers are consistent
  • The Master and Slave databases must be of the same version
  • The data in the Master and Slave databases must be consistent
  • Master Enable binary logs. The server_id of the Master and Slave must be unique on the LAN

The configuration steps

Primary database (192.168.249.131)

  • Create and authorize a synchronization user
CREATE USER 'slave'@'192.168.249.129' IDENTIFIED WITH 'mysql_native_password' BY '000000';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'192.168.249.129';
FLUSH PRIVILEGES;
Copy the code

Note that the user must be selected when creating the usermysql_native_passwordEncryption mode plugin, otherwise used by defaultcaching_sha2_passwordIn this way, SSL authentication is required during synchronization. For convenience and simplicity, we directly use SSL authenticationmysql_native_passwordway

  • Modified the /etc/my. CNF configuration, added the following configuration, enable binlog, and restart mysql service

    [mysqld]
Enable the binary log function
log-bin=mysql-bin
Set server_id to be unique within network segments
server-id=131
[Optional] Specifies the database name to synchronize. To synchronize multiple databases, add more replicate-db-db= the database name
binlog-do-db=mydb
# (Optional configuration) Database to ignore
binlog-ignore-db=mysql
Copy the code

  • Check the master server status show master status

Pay attention to the parameters, especially the first two File and Position, which are useful for configuring the master/Slave relationship on the Slave server.

Secondary database (192.168.249.129)

  • Modify /etc/my. CNF, add the following configuration, and restart the service
[mysqld]
server-id=129
log-bin=mysql-bin
replicate-do-db=mydb
replicate-ignore-db=mysql
Copy the code
  • Set the master information in slave and specify the synchronization position
stop slave;
change master to master_host='192.168.249.131',master_user='slave',master_password='000000',master_log_file='mysql-bin.000001',master_log_pos=155;
start slave;
Copy the code

Parameter Description: Master_host =’192.168.249.131′ ## Master IP address master_user=’slave’ ## User used to synchronize data (authorized user in Master) master_password=’000000′ Masterlogfile =’mysql-bin.000001′ masterlogfile=’mysql-bin.000001′ Masterlogpos =155 # Specify the POSITION number from which the Slave will start reading the data from the log File (masterLogpos =155) Masterconnectretry =30 The unit is seconds. The default value is 60 seconds.

  • Show slave status\G;

The primary/secondary configuration at the database level is complete.

Configure primary/secondary read/write separation in SpringBoot

Observe the following rules when working in primary/secondary mode: Perform INSERT,UPDATE, and DELETE operations only on the primary database and SELECT operations only on the secondary database

Here we use the open source project [dynamic - the datasource - spring - the boot - starter] (HTTP: / / https://gitee.com/baomidou/dynamic-datasource-spring-boot-starter/wik Is /) as a read-write separation toolkitCopy the code

Method of use

  1. Create a simple data table user in myDB primary database, after the completion of the secondary database will be automatically synchronized
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`account` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`name` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`position` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Copy the code
  1. Introducing dependent dependencies
<dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> < artifactId > mybatis - spring - the boot - starter < / artifactId > < version > 2.0.1 < / version > < / dependency > < the dependency > < the groupId > com. Baomidou < / groupId > < artifactId > dynamic - the datasource - spring - the boot - starter < / artifactId > < version > 2.5.5 < / version >  </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> < version > 8.0.15 < / version > < / dependency > < the dependency > < groupId > org. Projectlombok < / groupId > <artifactId>lombok</artifactId> <optional>true</optional>
       </dependency>

       <dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-starter-test</artifactId>
           <scope>test</scope>
       </dependency>

   </dependencies>
Copy the code
  1. Configuring a Data Source
spring:
  datasource:
    dynamic:
      primary: master Set the default data source or data source group to master
      strict: false # Set strict mode. Default is false. Throw an exception after startup to match the specified data source. If not, the default data source will be used.
      datasource:
        master:
          type: com. Zaxxer. Hikari. HikariDataSource url: JDBC: mysql: / / 192.168.249.131:3306 / mydb? characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
          username: root
          password: '000000'
          driver-class-name: com.mysql.cj.jdbc.Driver
        slave_1:
          type: com. Zaxxer. Hikari. HikariDataSource url: JDBC: mysql: / / 192.168.249.129:3306 / mydb? characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false
          username: root
          password: '000000'
          driver-class-name: com.mysql.cj.jdbc.Driver
Copy the code
  1. Add the MyBatis scan pack in the startup class entry
@SpringBootApplication@MapperScan("com.jianzh5.dynamic.mapper") public class DynamicDatsourceBootstrap { public static void main(String[] args) { SpringApplication.run(DynamicDatsourceBootstrap.class, args); }}Copy the code
  1. Create the entity class User
@Data
public class User {
    private int id;
    private String account;
    private String name;
    private String position;
}
Copy the code
  1. Create mapper interface file, add two methodsaddUser(User user).getById(int id)
public interface UserDao {
    @Insert("INSERT INTO user(account, name, position) VALUES(#{account}, #{name}, #{position})")
    @Options(useGeneratedKeys = true,keyProperty = "id")
    int addUser(User user);

    @Select("SELECT * FROM user WHERE id = #{id}")
    User getById(int id);
}
Copy the code
  1. Establish the Service layer implementation
public interface UserService {
		int addUser(User user);
		User getById(int id);
}
@Service
public class UserServiceImpl implements UserService {
		@Resource
		private UserDao userDao;

		@Override
		public int addUser(User user) {
			return userDao.addUser(user);
		}
		@DS("slave")
		@Override
		public User getById(int id) {
			returnuserDao.getById(id); }}Copy the code

Since primary: master is configured in the data source, the default operations are performed from the primary library, using the @ds annotation to switch the data source. This annotation can also be used directly on the class file, and there are method annotations that take precedence over class annotations.

  1. Write unit tests to test
``` public class UserServiceTest extends DynamicDatsourceBootstrapTests { @Autowired private UserService userService; @Test public void testAddUser(){ User user = new User(); User. Elegantly-named setName (" li si "); user.setAccount("sili"); User.setposition ("JAVA Developer "); int i = userService.addUser(user); System.out.println(user); } @Test public void testGetById(){ int id = 4; User user = userService.getById(id); Assert.assertEquals("sanzhang",user.getAccount()); }} ` ` `Copy the code
  1. By observing the execution log, it is found that the read and write database will switch according to the @DS annotation, so the Springboot integrated database has completed the separation of primary and secondary read and write.

More exciting content please pay attention to the public number: JAVA daily record