搭建MySQL 8.0主从同步

前言

本文采用的是docker搭建mysql 8.0主从同步，采用了两台云服务器

1. master 106.52.206.63
2. slave 106.13.191.167

前期准备

1. 开通安全组，让两台服务器能够相互通信
2. 分别在两台服务器上拉取 mysql 镜像及启动容器

# 拉取最新 mysql 镜像，目前最新 8.0
docker pull mysql

# 创建挂载目录
sudo mkdir /usr/local/soft

# 跳转到挂载目录
cd /usr/local/soft

# 创建 mysql 容器并挂载目录
docker run \
 -p 3306:3306 \
 --name mysql \
 -v $PWD/mysql/conf:/etc/mysql/conf.d \
 -v $PWD/mysql/logs:/logs \
 -v $PWD/mysql/data:/var/lib/mysql \
 -e MYSQL_ROOT_PASSWORD=123456 \
 -e TZ=Asia/Shanghai \
 -d mysql

正文

1. 操作master

# 进入 docker 容器
docker exec -it mysql /bin/bash

# 安装 vim 编辑器
apt-get update && apt-get install vim

# 修改 master 配置文件
vim /etc/mysql/my.cnf

# 这里是 master的配置文件
[mysqld]
server-id = 1
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

# Custom config should go here
!includedir /etc/mysql/conf.d/
log-bin = mysql-bin
sync_binlog = 1
binlog_format = mixed
expire_logs_days = 7
max_binlog_size = 100m
binlog_cache_size = 4m
max_binlog_cache_size = 512m
binlog-ignore-db=mysql

auto-increment-offset = 1
auto-increment-increment = 1
slave-skip-errors = all

# 退出容器
root@0169c1fcba07:/# exit

# 重启容器
[root@VM_0_16_centos ~]# docker restart mysql

# 进入 mysql 容器
[root@VM_0_16_centos ~]# docker exec -it mysql /bin/bash

# 访问 mysql
root@0169c1fcba07:/# mysql -h127.0.0.1 -uroot -p123456

# 创建用于同步从库的用户，这里是从库ip
mysql> create user 'backup'@'106.13.191.167' identified by 'backuppassword';
Query OK, 0 rows affected (0.01 sec)

# 授权用户同步权限
mysql> grant replication slave on *.* to backup@106.13.191.167;
Query OK, 0 rows affected (0.01 sec)

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

这个时候，可以在主库随意创建表和添加数据了，简直不要太随意

# 查看 master 状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |     2191 |              | mysql            |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

注：记住这里的File和Position，待会同步的时候要用到

# 退出 mysql
mysql> exit

# 导出 binglog 文件
root@0169c1fcba07:/# /usr/bin/mysqldump --port=3306 -u root -p123456 --databases ewing --lock-tables=false --> /var/lib/mysql/ewing.sql

退出容器，用 scp 远程传输命令，将主库 binlog 发送给从库

root@ad14836bf391:/# exit
[root@VM_0_16_centos ~]# scp /usr/local/soft/mysql/data/ewing.sql root@106.13.191.167:/usr/local/soft/mysql/data
root@106.13.191.167's password:
ewing.sql

2. 操作slave

# 进入 docker 容器
docker exec -it mysql /bin/bash

# 安装 vim 编辑器
apt-get update && apt-get install vim

# 修改 slave 配置文件
vim /etc/mysql/my.cnf

# slave配置文件的内容
[mysqld]
server-id = 2
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL

# Custom config should go here
!includedir /etc/mysql/conf.d/
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

# 退出容器
root@0169c1fcba07:/# exit

# 重启容器
[root@VM_0_16_centos ~]# docker restart mysql

# 进入 mysql 容器
[root@VM_0_16_centos ~]# docker exec -it mysql /bin/bash

# 同步 binlog
root@bee2ff2f5f7e:/# mysql --host=127.0.0.1 --port=3306 -uroot -p123456 < /var/lib/mysql/ewing.sql

# 访问 mysql
root@bee2ff2f5f7e:/# mysql -h127.0.0.1 -uroot -p123456

# 主从同步命令
mysql> CHANGE MASTER TO
    -> MASTER_HOST = '106.52.206.63',
    -> MASTER_USER = 'backup',
    -> MASTER_PASSWORD = 'backuppassword',
    -> MASTER_PORT = 3306,
    -> MASTER_LOG_FILE='mysql-bin.000001',
    -> MASTER_LOG_POS=2191,
    -> MASTER_RETRY_COUNT = 60,
    -> MASTER_HEARTBEAT_PERIOD = 10000;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

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

mysql> show slave status\G

Slave_IO_Running为Connecting，不为Yes，说明还没有同步成功，下面有失败的原因，解决方案下面也贴出来了

3. 小插曲

MYSQL8.0报错：Authentication plugin 'caching_sha2_password’问题的解决方案

# 进入主库 mysql
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'backup'@'106.13.191.167' IDENTIFIED WITH mysql_native_password BY 'backuppassword';
Query OK, 0 rows affected (0.01 sec)

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

到这里，就算主从同步成功了，测试用例就不贴出来了

结语

果然这种服务器运维的操作，非常考验一个人的耐心，稍微一点小问题都要从头开始

已经尽量把排版做简洁了，按照顺序把命令敲一遍，不出意外，都可以顺利搭建，当然前提是把服务器地址换成自己的

方便其他小伙伴搭建小demo，也方便自己以后有这方面的需求吧，关于主从同步的理论，后面会再出一篇博客唠唠