MySQL 主从配置以及 Docker 方式演练

淩亂°似流年 2021-10-15 08:43 206阅读 0赞

> 参考文档：https://dev.mysql.com/doc/refman/5.7/en/replication.html  
> 本文是对上述文档的关键步骤进行整理，有时间还是看看文档全面了解更好

## 一、[Master 配置][Master] ##

在 `my.cnf` 或 `my.ini` 配置文件的 `[mysqld]` 部分添加如下配置：

[mysqld]
    # 服务器 ID，在 1和（2^32）-1 之间的正整数，不能和其他 MySQL 服务器ID重复
    server-id=1
    # 启用二进制日志，值为日志文件的前缀
    log-bin=mysql-bin
    # 为了在使用InnoDB事务的复制设置中实现最大的持久性和一致性 
    # 您应该在主 文件中启用下面两项配置
    innodb_flush_log_at_trx_commit=1
    sync_binlog=1

> 上面最后两个配置可以提供最高的数据安全，但是会导致更高的磁盘 IO，因此 TPS 最低，实际使用时需要根据自己业务进行测试寻求更合适的配置。  
> 参数详细说明：[sysvar\_innodb\_flush\_log\_at\_trx\_commit][sysvar_innodb_flush_log_at_trx_commit]

## 二、[创建用于复制的用户][Link 1] ##

创建用户

CREATE USER 'repl'@'%.example.com' IDENTIFIED BY 'password';

赋予权限

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.example.com';

## 三、[获取二进制日志坐标][Link 2] ##

为了主从一致，需要先禁用主机的增删改操作，给所有表设置读锁。

在 master 执行：

FLUSH TABLES WITH READ LOCK;

> **警告**  
> 让发出 `FLUSH TABLES` 语句的客户端保持运行状态，以使读锁保持有效。如果退出客户端，则会释放锁。

在 master 上打开一个新的会话，执行：

记录 File 和 Position 信息。

## 四、[同步现有数据][Link 3] ##

如果 master 已有数据，需要先同步。同步方法如下。

### 4.1 [使用 mysqldump][mysqldump] ###

在命令行执行：

$ mysqldump --all-databases --master-data > dbdump.db

## 五、[Slaves 配置][Slaves] ##

### 5.1 在 master 上释放锁 ###

`UNLOCK TABLES;`

### 5.2 配置 slave 服务器 ###

修改 `my.cnf` 或 `my.ini` 配置：

[mysqld]
    server-id=2

> 多个 slave 时需要保证 server-id 不重复

slave 不需要开启 `log-bin`，如果开启，就能通过二进制日志进行数据备份或崩溃恢复操作。同时还可以作为另一个 slave 的 master（`主-从(主)-从`）。

### 5.3 设置 master 信息 ###

执行下面的 SQL：

mysql> CHANGE MASTER TO
             MASTER_HOST='master_host_name',
             MASTER_USER='replication_user_name',
             MASTER_PASSWORD='replication_password',
             MASTER_LOG_FILE='recorded_log_file_name',
             MASTER_LOG_POS=recorded_log_position;

将上面参数 `'xxx'` 部分修改为对应的主机名（IP），复制用的用户名和密码，`SHOW MASTER STATUS;` 时看到的二进制文件名和 Position。

> [完整 CHANGE MASTER TO 语法][CHANGE MASTER TO]

### 5.4 恢复 master 数据 ###

如果在前面导出了现有数据，这里需要先将数据导入，执行下面的命令：

$ mysql -h master < dbdump.db

> 如果在 mysqldump 使用了 `--single-transaction`，导入数据时会自动执行上述配置。

### 5.5 启动 Slave 线程 ###

执行SQL：`START SLAVE;`

## 六、实践 ##

简单尝试时可以通过 docker 快速部署两个容器用于学习。

> 参考：https://hub.docker.com/\_/mysql

Docker 宿主机 IP：192.168.200.202

准备挂载目录结构如下：

/docker
    └── mysql
        ├── master
        │   ├── conf
        │   │   └── my.cnf
        │   └── data
        └── slave
            ├── conf
            │   └── my.cnf
            └── data

### 6.1 Master ###

`my.cnf` 配置文件：

[client]
    default-character-set=utf8mb4
    [mysql]
    default-character-set=utf8mb4
    [mysqld]
    collation-server=utf8mb4_unicode_ci
    character-set-server=utf8mb4
    init-connect='SET NAMES utf8mb4'
    max_connections=2000
    
    lower_case_table_names=1
    
    server-id=1
    log-bin=mysql-bin
    innodb_flush_log_at_trx_commit=1
    sync_binlog=1

> 这里设置了字符集为 utf8mb4

通过 docker 启动服务：

docker run --name mysql-master \
      -e MYSQL_ROOT_PASSWORD=root \
      -p 3306:3306 \
      -v /docker/mysql/master/conf:/etc/mysql \
      -v /docker/mysql/master/data:/var/lib/mysql \
      -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

### 6.2 Slave ###

`my.cnf` 配置文件：

> 这里设置了字符集为 utf8mb4

通过 docker 启动服务：

docker run --name mysql-slave \
      -e MYSQL_ROOT_PASSWORD=root \
      -p 3307:3306 \
      -v /docker/mysql/slave/conf:/etc/mysql \
      -v /docker/mysql/slave/data:/var/lib/mysql \
      -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci

### 6.3 测试 ###

#### 6.3.1. 准备数据 ####

先在 master 执行 SQL：[数据库结构.sql][.sql]，这就是有**现有数据**的情况。

#### 6.3.2. 备份 master ####

执行下面的 docker 命令备份数据：

$ docker exec mysql-master sh -c 'exec mysqldump --all-databases --master-data --single-transaction --include-master-host-port -uroot -proot' > /docker/mysql/dbdump.db

#### 6.3.3. 还原 slave ####

执行下面的 docker 命令还原数据：

$ docker exec -i mysql-slave sh -c 'exec mysql -uroot -proot' < /docker/mysql/dbdump.db

#### 6.3.4 创建复制用户 ####

创建用户

CREATE USER 'slave'@'%' IDENTIFIED BY 'slave';

赋予权限

GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';

> **提醒**  
> 第一次使用 `@'192.168.200.202'` 出现了下面的 slave 状态
> 
> > Slave\_IO\_Running: Connecting  
> > Slave\_SQL\_Running: Yes
> 
> 然后在 master 日志发现：
> 
> > \[Note\] Access denied for user ‘slave’@‘172.17.0.1’ (using password: YES)
> 
> 上面展示的是容器 IP没有访问权限，修改为 `@'%'` 后状态变为正常。

#### 6.3.5 slave 配置 master ####

CHANGE MASTER TO
             MASTER_HOST='192.168.200.202',
             MASTER_USER='slave',
             MASTER_PASSWORD='slave',
    		 MASTER_LOG_FILE='mysql-bin.000003', 
             MASTER_LOG_POS=15114;

其中 `MASTER_LOG_FILE` 和 `MASTER_LOG_POS` 值可以在上面备份 `/docker/mysql/dbdump.db` 的 SQL 语句中看到，也可以按照前文加锁保证备份时这两个参数不变。

#### 6.3.6 修改 master 查看 slave 变化 ####

随便增删改数据或增加数据库、表等查看效果。

## 七、小结 ##

很早很早以前就做过主从方面的数据库配置，当初也是看别人文章学，如今发现不同文档配置有差异，因此看官方文档从头操作一遍，MySQL 官方文档很全面，当前只看了一小部分内容，等看全再补充，同时推荐看到这里的朋友看看官方文档了解更多细节。

[Master]: https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html
[sysvar_innodb_flush_log_at_trx_commit]: https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
[Link 1]: https://dev.mysql.com/doc/refman/5.7/en/replication-howto-repuser.html
[Link 2]: https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterstatus.html
[Link 3]: https://dev.mysql.com/doc/refman/5.7/en/replication-snapshot-method.html
[mysqldump]: https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html
[Slaves]: https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html
[CHANGE MASTER TO]: https://dev.mysql.com/doc/refman/5.7/en/change-master-to.html
[.sql]: https://github.com/mybatis-book/book/blob/master/%E6%95%B0%E6%8D%AE%E5%BA%93%E7%BB%93%E6%9E%84.sql