容器化安装Mysql 8.0 并部署主从复制
系统： Centos 7.4
数据库版本：8.0.20
两台机器做相同操作
安装Docker
export VERSION=18.06 && curl -fsSL http://rainbond-pkg.oss-cn-shanghai.aliyuncs.com/releases/docker/install-docker.sh | bash -s docker
启动并开机自启
systemctl start docker
systemctl enable docker
拉取镜像
docker pull mysql
附配置文件，在容器启动时分别挂载主从的配置文件
主
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 服务端默认utf8编码
character-set-server=utf8mb4
# 默认存储引擎
default-storage-engine=INNODB
# 主从配置
log-bin=binlog
server-id=1
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on
expire_logs_days=14
# Compatible with versions before 8.0
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve
[client]
#设置客户端编码
default-character-set=utf8mb4
[mysql]
# 设置mysql客户端默认编码
default-character-set=utf8mb4
# Custom config should go here
!includedir /etc/mysql/conf.d/
# Custom config should go here
!includedir /etc/mysql/conf.d/
从
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
#
# The MySQL Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 服务端默认utf8编码
character-set-server=utf8mb4
# 默认存储引擎
default-storage-engine=INNODB
# 主从配置
server-id=2
gtid-mode=on
enforce-gtid-consistency=on
log-slave-updates=on
expire_logs_days=14
# Compatible with versions before 8.0
default_authentication_plugin=mysql_native_password
skip-host-cache
skip-name-resolve
[client]
#设置客户端编码
default-character-set=utf8mb4
[mysql]
# 设置mysql客户端默认编码
default-character-set=utf8mb4
# Custom config should go here
!includedir /etc/mysql/conf.d/
# Custom config should go here
!includedir /etc/mysql/conf.d/
主数据库
启动数据库
docker run --name mysql_master --restart=always -p 3306:3306 --privileged=true -v /root/mysql/my.cnf:/etc/mysql/my.cnf -v /root/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
查看数据库字符编码（可选）,并创建用户授权
# 进入数据库
docker exec -it mysql_master bash
# 查看字符编码
mysql> show global variables like'%character_set%';
# 创建用户授权
mysql> CREATE USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY 'slave';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%';
mysql> flush privileges;
获取主节点当前binary log文件名和位置（position）
mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000003 | 868 | | | 1b009ef8-a67f-11ea-8c9a-0242ac110002:1-8 |
+---------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)
从数据库
启动数据库
docker run --name mysql_slave --restart=always -p 3306:3306 --privileged=true -v /root/mysql/my.cnf:/etc/mysql/my.cnf -v /root/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql
配置主从复制
# 进入数据库
docker exec -it mysql_slave bash
# 主从配置
mysql> CHANGE MASTER TO
mysql> MASTER_HOST='192.168.0.162',
mysql> MASTER_USER='slave',
mysql> MASTER_PASSWORD='slave',
mysql> MASTER_PORT=3306,
mysql> MASTER_LOG_FILE='binlog.000003',
mysql> MASTER_LOG_POS=868;
# 开启主从同步
mysql> start slave;
# 再查看主从同步状态
mysql> show slave status;
这里只要看到两个参数Slave_IO_Running和Slave_SQL_Running都为true且Error字段都为空则代码主从正常复制
测试
通过在主服务器创建数据库建表插入数据的方式来进行测试，查看从服务器是否同步更新了数据
在主库创建库
mysql> create database kong;
在从库查看
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| kong |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
数据同步成功，主从复制部署完成
创建用户与授权
ALTER USER 'username'@'ip_address' IDENTIFIED WITH mysql_native_password BY 'password';
