容器化安装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';

巴特西

容器化安装Mysql 8.0 并部署主从复制

两台机器做相同操作

主数据库

从数据库

测试

最新文章

热门文章