Docker容器下配置MySQL主从服务
文章目录
一、docker安装mysql
1.1从仓库中查询镜像
[root@shigj ~]# docker search mysql
NAME DESCRIPTION STARS OFFICIAL AUTOMATED
mysql MySQL is a widely used, open-source relation… 12514 [OK]
mariadb MariaDB Server is a high performing open sou… 4814 [OK]
mysql/mysql-server Optimized MySQL Server Docker images. Create… 925 [OK]
percona Percona Server is a fork of the MySQL relati… 575 [OK]
phpmyadmin phpMyAdmin - A web interface for MySQL and M… 523 [OK]
mysql/mysql-cluster Experimental MySQL Cluster Docker images. Cr… 93
centos/mysql-57-centos7 MySQL 5.7 SQL database server 93
bitnami/mysql Bitnami MySQL Docker Image 70 [OK]
ubuntu/mysql MySQL open source fast, stable, multi-thread… 31
circleci/mysql MySQL is a widely used, open-source relation… 25
mysql/mysql-router MySQL Router provides transparent routing be… 24
google/mysql MySQL server for Google Compute Engine 21 [OK]
vmware/harbor-db Mysql container for Harbor 10
bitnami/mysqld-exporter 3
mysqlboy/mydumper mydumper for mysql logcial backups 3
mysqlboy/docker-mydumper docker-mydumper containerizes MySQL logical … 3
ibmcom/mysql-s390x Docker image for mysql-s390x 2
newrelic/mysql-plugin New Relic Plugin for monitoring MySQL databa… 1 [OK]
cimg/mysql 0
mysql/mysql-operator MySQL Operator for Kubernetes 0
ibmcom/tidb-ppc64le TiDB is a distributed NewSQL database compat… 0
newrelic/k8s-nri-mysql New Relic Infrastructure MySQL Integration (… 0
mysqlboy/elasticsearch 0
mysqleatmydata/mysql-eatmydata 0
mirantis/mysql 0
1.2下载镜像
[root@shigj ~]# docker pull mysql
Using default tag: latest
latest: Pulling from library/mysql
4be315f6562f: Pull complete
96e2eb237a1b: Pull complete
8aa3ac85066b: Pull complete
ac7e524f6c89: Pull complete
f6a88631064f: Pull complete
15bb3ec3ff50: Pull complete
ae65dc337dcb: Pull complete
573c3c7fa18d: Pull complete
9d10771b98b8: Pull complete
3d8ef442614b: Pull complete
7dc17a6cea26: Pull complete
752752efdaea: Pull complete
Digest: sha256:2dafe3f044f140ec6c07716d34f0b317b98f8e251435abd347951699f7aa3904
Status: Downloaded newer image for mysql:latest
docker.io/library/mysql:latest
查看镜像
[root@shigj ~]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql latest 96d0eae5ed60 6 days ago 524MB
1.3运行docker 容器(container, 主服务器(master))
[root@shigj ~]# docker run -itd --name=mysql_master -p 3310:3306 -e MYSQL_ROOT_PASSWORD=oa123456 mysql
5c78d54375063164b106d808d304853e994bd84f314a17329b8458b6449e4dc8
1.4运行docker 容器(container, 主服务器(slave))
docker run -itd --name=mysql_slave -p 3311:3306 -e MYSQL_ROOT_PASSWORD=oa123456 mysql
1.4查看启动的container
[root@shigj ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS
NAMES740559b636eb mysql "docker-entrypoint.s…" 29 minutes ago Up 16 minutes 33060/tcp, 0.0.0.0:3311->3306/tcp, :
::3311->3306/tcp mysql_slave5c78d5437506 mysql "docker-entrypoint.s…" 30 minutes ago Up 16 minutes 33060/tcp, 0.0.0.0:3310->3306/tcp, :
::3310->3306/tcp mysql_master
二、配置主(master)容器
2.1进入master服务器容器内
[root@shigj ~]# docker exec -it mysql_master /bin/bash
root@5c78d5437506:/#
root@5c78d5437506:/# cd etc/mysql/
root@5c78d5437506:/etc/mysql# ls
conf.d my.cnf my.cnf.fallback
root@5c78d5437506:/etc/mysql#
root@5c78d5437506:/etc/mysql# vi my.cnf
bash: vi: command not found
执行以下命令行
apt-get update
apt-get install vim
编辑my.cnf
vim my.cnf
[mysqld]
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/
server-id=200
log-bin=mysql-bin
重启容器让配置文件生效
docker restart mysql_master
2.2创建专用账户(slave)进行主从同步
使用root账户登入mysql
docker exec -it mysql_master /bin/bash
mysql -u root -p
创建专用同步账户(slave)
//创建slave账户,并初始化密码
CREATE USER 'slave'@'%' IDENTIFIED BY 'oa123456';
//给账户slave授权,REPLICATION SLAVE 常用于建立复制时所需要用到的用户权限
//REPLICATION CLIENT 不可用于建立复制,有该权限时,只是多了可以使用如"SHOW SLAVE STATUS"、"SHOW MASTER STATUS"等命令。
GRANT REPLICATION SLAVE,REPLICATION CLIENT ON *.* TO 'slave'@'%';
flush privileges
三、配置主(slave)容器
3.1进入slave服务器容器内
[root@shigj ~]# docker exec -it mysql_slave /bin/bash
root@740559b636eb:/#
root@740559b636eb:/# cd /etc/mysql/
root@740559b636eb:/etc/mysql# ls
conf.d my.cnf my.cnf.fallback
编辑my.cnf文件
apt-get update
apt-get install vim
[mysqld]
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/
server-id=201
log-bin=mysql-slave-bin
relay_log=edu-mysql-relay-bin
四、主(master)从(Slave)复制
4.1 查看master状态
docker exec -it mysql_master /bin/bash
mysql -u slave -p
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 157 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
4.2 查询容器的独立IP
[root@shigj ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql_master
172.17.0.2
[root@shigj ~]# docker inspect --format='{{.NetworkSettings.IPAddress}}' mysql_slave
172.17.0.3
4.3 进入slave容器中
docker exec -it mysql_slave /bin/bash
mysql -u root -p
执行SQL语句
change master to master_host='172.17.0.2',//独立IP地址
master_user='slave', //数据同步用户
master_password='oa123456', //数据同步用户密码
MASTER_LOG_FILE='mysql-bin.000002',//主服务器状态
MASTER_LOG_POS=157;//主服务器端口号
开启主从复制过程
start slave;
查看状态
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 172.17.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 157
Relay_Log_File: edu-mysql-relay-bin.000002
Relay_Log_Pos: 326
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes