MySQL 双主搭建及简单 CURD

[toc]

  1. 在 Linux 系统上面搭建 MySQL 双主(liunx 系统采取 CentOS7 以上)
  2. 搭建 maven spring boot 工程,引入 Mybatis Plus,简单增删改查模块

MySQL 双主部署

确定服务器信息

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

A 机器(39.97.69.140):

1
2
3
4
5
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):

1
2
3
4
5
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:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# 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。

1
2
3
4
# 下载 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 的启用情况:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
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 源:

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

随后安装 MySQL:

1
sudo yum install -y mysql-community-server

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

1
2
sudo systemctl start mysqld
sudo systemctl enable mysqld

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

1
sudo mysql -u root

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

1
2
3
4
5
6
7
8
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 配置文件:

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

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

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
# 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 机器类似,只有主键自增策略不同:

1
2
3
server-id = 2
auto_increment_offset = 2
auto_increment_increment = 2
  • 添加主从同步账户

    在 A 机器上执行以下 SQL:

1
2
3
4
5
6
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:

1
2
3
4
5
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 机器:

    1
    2
    
    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 机器:

    1
    2
    
    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 的复制:

    1
    2
    3
    4
    5
    6
    7
    8
    
    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)
    

    开启服务并查看状态:

     1
     2
     3
     4
     5
     6
     7
     8
     9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    
    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 时,可以发现以下值:

    1
    2
    
    Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
    

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

简单测试

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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
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 机可以查询到同步的库和数据:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
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 库新增一些数据:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
> 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 机可查询到同步的数据:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
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 模块:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
<!-- 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 组,使用组名为主数据源时,采用负载均衡算法切换。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
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 库:

1
create database test123;	

新增用户表:

1
2
3
4
5
6
7
8
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)
);

插入测试数据:

1
2
3
4
5
6
INSERT INTO user (id, name, age, email) VALUES
(NULL, 'Jone', 18, '[email protected]'),
(NULL, 'Jack', 20, '[email protected]'),
(NULL, 'Tom', 28, '[email protected]'),
(NULL, 'Sandy', 21, '[email protected]'),
(NULL, 'Billie', 24, '[email protected]');

新建实体类 User:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
@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 组,会自动根据组进行负载均衡,实现如下:

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

控制层实现如下:

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

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

新增

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

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

控制层代码如下:

1
2
3
4
@PostMapping("addUsers")
public void addUsers() {
    userService.addUsers();
}

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

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

修改

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

1
2
3
4
5
6
@DS("master_1")
public void update(int id) {
    User user = userMapper.selectById(id);
    user.setName("updatedName");
    userMapper.updateById(user);
}

控制层实现如下:

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

使用 postman 发送如下请求:

查询 A 库,发现姓名已经更改:

查询 B 库,发现相应记录已被同步:

删除

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

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

控制层代码如下:

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

使用 postman 以 delete 方式请求 delete url,删除 id62 的记录,如下图所示:

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

B 库也同步了该条删除:

停掉 A 库

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

参考

加载评论