MySQL 双主搭建及简单 CURD

[toc]

在 Linux 系统上面搭建 MySQL 双主（liunx 系统采取 CentOS7 以上）
搭建 maven spring boot 工程，引入 Mybatis Plus，简单增删改查模块

MySQL 双主部署

确定服务器信息

首先输入 lsb_release -a 确定服务器系统信息：

A 机器（39.97.69.140）：

LSB Version:    :core-4.1-amd64:core-4.1-noarch
Distributor ID: CentOS
Description:    CentOS Linux release 7.3.1611 (Core)
Release:        7.3.1611
Codename:       Core

B 机器（49.235.232.61）：

LSB Version:    :core-4.1-amd64:core-4.1-noarch:cxx-4.1-amd64:cxx-4.1-noarch:desktop-4.1-amd64:desktop-4.1-noarch:languages-4.1-amd64:languages-4.1-noarch:printing-4.1-amd64:printing-4.1-noarch
Distributor ID: CentOS
Description:    CentOS Linux release 7.6.1810 (Core)
Release:        7.6.1810
Codename:       Core

A 机器系统为 CentOS 7.3 版本，B 机器系统为 CentOS 7.6。

安装和配置 MySQL

因为版权原因，CentOS yum 包管理器默认不包含 MySQL，只有 MariaDB：

# yum info mariadb
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
Available Packages
Name        : mariadb
Arch        : x86_64
Epoch       : 1
Version     : 5.5.65
Release     : 1.el7
Size        : 8.7 M
Repo        : base/7/x86_64
Summary     : A community developed branch of MySQL
......
......

由以上信息可以看到 MariaDB 版本为 5.5.65，是比较老的版本且可能和 MySQL 有差异。

为了便于使用 yum 进行依赖管理、升级和维护，决定使用 MySQL 官方推荐的方法安装 MySQL：使用官方维护的 yum 仓库安装 MySQL。

# 下载 MySQL rpm 包
wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm
# 本地方式安装刚才下载的软件包
sudo yum localinstall mysql80-community-release-el7-3.noarch.rpm

使用 yum repolist all | grep mysql 命令查看软件仓库中 MySQL 的启用情况：

mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community   disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community   disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - disabled
mysql-cluster-8.0-community/x86_64 MySQL Cluster 8.0 Community   disabled
mysql-cluster-8.0-community-source MySQL Cluster 8.0 Community - disabled
mysql-connectors-community/x86_64  MySQL Connectors Community    enabled:    153
mysql-connectors-community-source  MySQL Connectors Community -  disabled
mysql-tools-community/x86_64       MySQL Tools Community         enabled:    110
mysql-tools-community-source       MySQL Tools Community - Sourc disabled
mysql-tools-preview/x86_64         MySQL Tools Preview           disabled
mysql-tools-preview-source         MySQL Tools Preview - Source  disabled
mysql55-community/x86_64           MySQL 5.5 Community Server    disabled
mysql55-community-source           MySQL 5.5 Community Server -  disabled
mysql56-community/x86_64           MySQL 5.6 Community Server    disabled
mysql56-community-source           MySQL 5.6 Community Server -  disabled
mysql57-community/x86_64           MySQL 5.7 Community Server    disabled
mysql57-community-source           MySQL 5.7 Community Server -  disabled
mysql80-community/x86_64           MySQL 8.0 Community Server    enabled:    177
mysql80-community-source           MySQL 8.0 Community Server -  disabled

可看到 MySQL 8.0 分支是被启用状态，禁用 8.0 源，启用 5.6 源：

sudo yum-config-manager --disable mysql80-community
sudo yum-config-manager --enable mysql56-community

随后安装 MySQL：

sudo yum install -y mysql-community-server

使用 systemd 开启 MySQL 服务、启用开机自启动：

sudo systemctl start mysqld
sudo systemctl enable mysqld

使用 sudo，以 root 身份打开一个 MySQL 客户端连接：

sudo mysql -u root

新增一个名为 bolitao 的用户，密码也为 bolitao：

mysql> CREATE USER 'bolitao'@'localhost' IDENTIFIED BY 'bolitao';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO 'bolitao'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

之后便可以 bolitao 身份登入 MySQL：

配置 MySQL 双主

备份原来的 MySQL 配置文件：

sudo mv /etc/my.cnf /etc/my.cnf.bak

修改机器 A 上的 MySQL 配置文件如下：

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
[client]
port = 3306
socket=/var/lib/mysql/mysql.sock

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

basedir = /usr/local/mysql
port = 3306
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log

server-id = 1
auto_increment_offset = 1
auto_increment_increment = 2



# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

log-bin = mysql-bin
binlog-format=ROW
#binlog-row-p_w_picpath=minimal
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
slave-parallel-workers=0
sync_binlog=0
binlog-checksum=CRC32
master-verify-checksum=1
slave-sql-verify-checksum=1
binlog-rows-query-log_events=1
expire_logs_days=5
max_binlog_size=1024M

replicate-ignore-db = mysql                                             #忽略不同步主从的数据库
replicate-ignore-db = information_schema
replicate-ignore-db = performance_schema
replicate-ignore-db = test

max_connections = 3000
max_connect_errors = 30

skip-character-set-client-handshake                                     #忽略应用程序想要设置的其他字符集
init-connect='SET NAMES utf8'                                           #连接时执行的SQL
character-set-server=utf8                                               #服务端默认字符集
wait_timeout=1800                                                       #请求的最大连接时间
interactive_timeout=1800                                                #和上一参数同时修改才会生效
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES                     #sql模式
max_allowed_packet = 10M
bulk_insert_buffer_size = 8M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
key_buffer_size = 256M
read_buffer_size = 16K

skip-name-resolve
slow_query_log=1
long_query_time = 6
slow_query_log_file=slow-query.log
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 16M


[mysql]
no-auto-rehash


[myisamchk]
key_buffer_size = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqldump]
quick
max_allowed_packet = 16M


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

重启 MySQL 并查看其状态：

由以上信息可知 MySQL 重启成功、运行正常。

同理，修改 B 机器上的 MySQL 配置文件和 A 机器类似，只有主键自增策略不同：

server-id = 2
auto_increment_offset = 2
auto_increment_increment = 2

添加主从同步账户

在 A 机器上执行以下 SQL：

mysql> grant replication slave on *.* to 'bolitao'@'49.235.232.61' identified by 'bolitao';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

在 B 机器上执行以下 SQL：

mysql> grant replication slave on *.* to 'bolitao'@'39.97.69.140' identified by 'bolitao';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看主库的状态

A 主机上：

B 主机上：

配置同步信息

A 机器：

mysql> change master to master_host='49.235.232.61',master_port=3306,master_user='bolitao',master_password='bolitao',master_log_file='mysql-bin.000001',master_log_pos=551;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

B 机器：

mysql> change master to master_host='39.97.69.140',master_port=3306,master_user='bolitao',master_password='bolitao',master_log_file='mysql-bin.000001',master_log_pos=552;
Query OK, 0 rows affected, 2 warnings (0.16 sec)

在 A 机和 B 机执行以下 SQL，开启基于 GTID 的复制：

mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected (0.69 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

开启服务并查看状态：

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 49.235.232.61
                  Master_User: bolitao
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 551
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 314
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB: mysql,information_schema,performance_schema,test
           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: 551
              Relay_Log_Space: 519
              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: 2
                  Master_UUID: fba31258-aa80-11ea-8004-5254001c64d1
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           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: 662f1b0e-aa52-11ea-bed4-00163e32c4be:1-2
                Auto_Position: 0
1 row in set (0.00 sec)

A、B 机执行 show slave status 时，可以发现以下值：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

表示双主架构 MySQL 运行正常。

简单测试

在 A 机上新建名为 testdb 的库，并进行一些数据插入：

mysql> create database testdb;
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

> INSERT INTO `testdb`.`test_table` (`title`) VALUES ('test1');
> INSERT INTO `testdb`.`test_table` (`title`) VALUES ('test2');

随后在 B 机可以查询到同步的库和数据：

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)


mysql> use testdb;
Database changed
mysql> select * from test_table;
+----+-------+
| id | title |
+----+-------+
|  1 | test1 |
|  3 | test2 |
+----+-------+
2 rows in set (0.00 sec)

可以看到数据已经被同步至 B 机，在 A 机插入记录的自增主键为 1, 3, 5...等，都为奇数。

在 B 机：1. 删除 testdb 库中的一些数据；2. 新建 testdb2 库；3. 在 testdb2 库新增一些数据：

> DELETE FROM `testdb`.`test_table` WHERE  `id`=1;

mysql> create database testdb2;
Query OK, 1 row affected (0.00 sec)

mysql> use testdb2;
Database changed
mysql> CREATE TABLE `test_table` (
    ->   `id` int(11) NOT NULL AUTO_INCREMENT,
    ->   `title` varchar(255) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO `testdb`.`test_table` (`title`) VALUES ('test3');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO `testdb`.`test_table` (`title`) VALUES ('test4');
Query OK, 1 row affected (0.00 sec)

在 A 机可查询到同步的数据：

mysql> use testdb;
Database changed
mysql> select * from test_table;
+----+-------+
| id | title |
+----+-------+
|  3 | test2 |
+----+-------+
1 row in set (0.00 sec)

mysql> use testdb2;
Database changed
mysql> select * from test_table;
+----+-------+
| id | title |
+----+-------+
|  6 | test3 |
|  8 | test4 |
+----+-------+
2 rows in set (0.00 sec)

可以看到，A 机同步了 B 机的数据删除和新增记录。

基于 Mybatis Plus 的 CURD

创建项目

使用 IDEA 新建项目中的“Sprnig Initializr”功能新建 Spring 项目：

添加以下额外 Maven 模块：

<!-- web 支持 -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-web</artifactId>
</dependency>

<!--MySQL 支持-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

<!--MyBatis Plus 支持-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.3.2</version>
</dependency>

<!--swagger 支持-->
<dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger2</artifactId>
    <version>2.9.2</version>
</dependency>
<dependency>
    <groupId>io.springfox</groupId>
    <artifactId>springfox-swagger-ui</artifactId>
    <version>2.9.2</version>
</dependency>

<!-- dynamic-datasource-spring-boot-starter-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>dynamic-datasource-spring-boot-starter</artifactId>
    <version>3.1.1</version>
</dependency>

<!--lombok-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <optional>true</optional>
</dependency>

数据源和其他配置

设置默认数据源为 master 组，使用组名为主数据源时，采用负载均衡算法切换。

spring:
  datasource:
    dynamic:
      primary: master
      strict: false #设置严格模式,默认false不启动. 启动后在未匹配到指定数据源时候回抛出异常,不启动会使用默认数据源.
      datasource:
        master_1:
          url: jdbc:mysql://39.97.69.140:3306/test123
          username: bolitao
          password: bolitao
          driver-class-name: com.mysql.cj.jdbc.Driver
        master_2:
          url: jdbc:mysql://49.235.232.61:3306/test123
          username: bolitao
          password: bolitao
          driver-class-name: com.mysql.cj.jdbc.Driver
          
logging:
  level:
    com.baomidou.mybatisplus.samples.quickstart: debug

双主 CURD 策略：

新增：都可以进行新增。

删除：写 A 机。

修改：为避免冲突，只写 A 机，修改的记录再同步至 B 机。

查询：都可进行查询。

配置测试数据库和基础 Java 类

新建一个 test123 库：

create database test123;

新增用户表：

CREATE TABLE user
(
	id int NOT NULL AUTO_INCREMENT,
	name VARCHAR(30) NULL DEFAULT NULL,
	age INT(11) NULL DEFAULT NULL,
	email VARCHAR(50) NULL DEFAULT NULL,
	PRIMARY KEY (id)
);

插入测试数据：

INSERT INTO user (id, name, age, email) VALUES
(NULL, 'Jone', 18, 'test1@baomidou.com'),
(NULL, 'Jack', 20, 'test2@baomidou.com'),
(NULL, 'Tom', 28, 'test3@baomidou.com'),
(NULL, 'Sandy', 21, 'test4@baomidou.com'),
(NULL, 'Billie', 24, 'test5@baomidou.com');

新建实体类 User：

@Data
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String name;
    private Integer age;
    private String email;

    public User(String name, Integer age, String email) {
        this.name = name;
        this.age = age;
        this.email = email;
    }
}

查询

编写一个查询接口，用于查询所有用户信息，Service 指定数据源为 master 组，会自动根据组进行负载均衡，实现如下：

@DS("master")
public List<User> getUsers() {
    List<User> users = userMapper.selectList(null);
    return users;
}

控制层实现如下：

@GetMapping("getAll")
public List<User> getAll() {
    return userService.getUsers();
}

访问 http://localhost:8080/getAll，返回的结果如下：

新增

新建一个新增数据的接口，指定数据源为 master 组，新增数据的方法如下，addUsers() 方法连续新增十条数据。：

@DS("master")
public void addUsers() {
    for (int i = 0; i < 10; i++) {
        userMapper.insert(new User("name" + i, i, "mail" + i));
    }
}

控制层代码如下：

@PostMapping("addUsers")
public void addUsers() {
    userService.addUsers();
}

使用 postman 发送一次请求后，发现 A 库新增 10 条记录，ID 为奇数的是自己新增的，ID 为偶数的是从 B 机器同步过来的：

查询 B 库，同样也新增了 10 条数据。

修改

指定使用 master_1（即 A 机）进行数据修改操作，service 层实现如下，将相应 ID 记录的 name 字段改为 updatedName：

@DS("master_1")
public void update(int id) {
    User user = userMapper.selectById(id);
    user.setName("updatedName");
    userMapper.updateById(user);
}

控制层实现如下：

@PostMapping("update")
public void update(@RequestParam(name = "id") int id) {
    userService.update(id);
}

使用 postman 发送如下请求：

查询 A 库，发现姓名已经更改：

查询 B 库，发现相应记录已被同步：

删除

通过 id 删除某条记录，删除操作限定在 A 机器上进行，所以 DS 注解设置数据源为 master_1，service 层实现代码如下：

@DS("master_1")
public void delete(int id) {
    userMapper.deleteById(id);
}

控制层代码如下：

@DeleteMapping("delete")
public void delete(@RequestParam(name = "id") int id) {
    userService.delete(id);
}

使用 postman 以 delete 方式请求 delete url，删除 id 为 62 的记录，如下图所示：

操作完毕后，查看 A 库，可以发现 id 为 62 的纪录已被删除：

B 库也同步了该条删除：

停掉 A 库

使用 systemctl stop mysqld 命令停止 A 库，以 get 方式访问 getAll URL，进行添加操作，仍可以读取数据：

MySQL 双主搭建及简单 CURD

相关文章：