一、单实例安装
1.1 下载镜像
使用docker search mysql
INDEX NAME DESCRIPTION STARS OFFICIAL AUTOMATED
docker.io docker.io/mysql MySQL is a widely used, open-source relati... 8178 [OK]
docker.io docker.io/mariadb MariaDB is a community-developed fork of M... 2785 [OK]
//...
选择第一个镜像docker.io/mysql，可以自己指定版本号，这里使用的是5.7。使用如下命令下载mysql镜像：
# docker pull docker.io/mysql:5.7
Trying to pull repository docker.io/library/mysql ...
5.7: Pulling from docker.io/library/mysql
743f2d6c1f65: Pull complete
3f0c413ee255: Pull complete
aef1ef8f1aac: Pull complete
f9ee573e34cb: Pull complete
3f237e01f153: Pull complete
f9da32e8682a: Pull complete
4b8da52fb357: Pull complete
6f38e9cfd49b: Pull complete
9f4834b3f44f: Pull complete
af631d92fdba: Pull complete
0e771ddab25c: Pull complete
Digest: sha256:196fe3e00d68b2417a8cf13482bdab1fcc2b32cf7c7575d0906c700688b352b4
Status: Downloaded newer image for docker.io/mysql:5.7
1.2 启动mysql
启动mysql除了端口映射之外，还需要使用-e MYSQL_ROOT_PASSWORD指定一下root密码，命令如下：
# docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
d16116181b61544db1681dde4f07fc79774f08144044617b20a19f5d46128071
# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d16116181b61 mysql:5.7 "docker-entrypoint..." 3 seconds ago Up 3 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
使用docker ps可以看到mysql已经正常启动起来了。可以用命令行或者mysql客户端连接上去看看。
如果mysql是安装在远程服务器，使用本地的mysql客户端连接不上的话，一般有两个原因：
- 服务器端口号未开放
- mysql未开启远程登录
解决方法：
对于原因1，检查下服务器的防火墙等相关配置。
对于原因2，需在服务器上登录mysql并开启远程登录。
开启远程登录方法如下：
在服务器上通过docker命令进入mysql实例内部并登录mysql
# docker exec -it d16116181b61 /bin/bash
root@d16116181b61:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26 MySQL Community Server (GPL)
// ....
mysql>
开启mysql远程登录
这一步只需要在mysql的user表中设置一条host='%'并且user='root'的记录即可。
mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
4 rows in set (0.00 sec)
检查下mysql数据库的user表中是否有host='%'并且user='root'的记录，用Docker安装的mysql默认是有的。
如果没有，可以执行如下的更新语句：
update set host = '%' where host = 'localhost' and user = 'root';
flush privileges;
user表中默认都会有一条user='root'并且host='localhost'的记录，可以直接将其host改为%。
二、主从安装
2.1 主服务器安装
2.1.1 配置主服务器
首先为主服务器创建一个文件夹，例如/home/mysql1，用来存放主服务器的配置文件和数据文件。
在mysql1下创建conf/my.cnf，文件内容如下。
# Copyright (c) 2014, 2016, 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
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 主数据库端ID号
server_id = 1
# 开启二进制日志
log-bin = mysql-bin
# 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
# binlog-do-db = db
# 复制时需要忽略的数据库
binlog-ignore-db=mysql
# 为每个session分配的内存，在事务过程中用来存储二进制日志的缓存
binlog_cache_size=1M
# 主从复制的格式(mixed, statement, row, 默认格式是statement)
binlog_format=mixed
# 将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中
log-slave-updates
# 控制binlog的写入频率。每执行多少次事务写入一次(这个参数性能消耗很大，但可减小MySQL崩溃造成的损失)
sync_binlog = 1
# 这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_offset = 1
# 这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
auto_increment_increment = 1
# 二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除
expire_logs_days = 7
# 将函数复制到slave
log_bin_trust_function_creators = 1
前面都是基础配置，从server-id开始是主服务器的配置。
2.1.2 启动主服务器
使用docker run来启动一个mysql实例作为主服务器
# docker run --name mysql1 -v /home/mysql1/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysql1/data:/var/lib/mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci
8a54b5ee92d2137b96879eec77c79c20507346d3c57cc34fbddbe3f6c6bcc323
2.1.3 创建复制账号
登录mysql，为从服务器创建一个账号，并赋予其相应权限。
root@8a54b5ee92d2:/# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.26-log MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create user 'slave'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> grant replication slave, replication client on *.* to 'slave'@'%';
Query OK, 0 rows affected (0.00 sec)
2.1.4 查看master状态
使用show master status查看当前的状态，并记录下File和Position，后面从服务器要用。
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 617 | | mysql | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
2.1.5 获取master IP
使用docker inspect查看主服务器IP，后面从服务器要用。
# docker inspect --format='{{.NetworkSettings.IPAddress}}' 4264f76d7003
172.17.0.2
2.2 从实例安装
2.2.1 配置从服务器
同样也为从服务器创建一个文件夹，例如/home/mysql2，用来存放从服务器的配置文件和数据文件。
在mysql2下创建conf/my.cnf，文件内容如下:
# Copyright (c) 2014, 2016, 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
#log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
#bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# 从数据库端ID号
server_id = 2
#开启二进制日志
log-bin = mysql-bin
# 需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可
# binlog-do-db = db
# 复制时需要忽略的数据库
binlog-ignore-db = mysql
# 为每个session分配的内存
binlog_cache_size = 1M
# 主从复制的格式(mixed、statement、row，默认是statement)
binlog_format = mixed
# 将从服务器从主服务器收到的更新记入到从服务器自己的二进制日志文件中
log_slave_updates = 1
# 这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
# auto_increment_offset = 1
# 这个参数一般用在主主同步中，用来错开自增值, 防止键值冲突
# auto_increment_increment = 1
# 二进制日志自动删除的天数，默认值为0,表示“没有自动删除”，启动时和二进制日志循环时可能删除
expire_logs_days = 7
#将函数复制到slave
log_bin_trust_function_creators = 1
# relay_log 配置中继日志
relay_log = mysql-relay-bin
# 防止改变数据
read_only = 1
2.2.2 启动从服务器
使用docker run来启动一个mysql实例作为从服务器。
# docker run --name mysql2 -v /home/mysql2/conf/my.cnf:/etc/mysql/my.cnf -v /home/mysql2/data:/var/lib/mysql -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --character-set-server=utf8mb4 --collation-server=utf8mb4_general_ci
ceb421c503a6533ead15708a92534c47f544410a32dd5d0f1d8bdf036c9e215d
2.2.3 为从服务器指定主服务器
mysql> change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.000002', master_log_pos=617, master_connect_retry=30;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
说明：
- master_host: 主服务器ip，对应上面2.1.5中获取到的ip
- master_user: 复制用户，对应上面2.1.3中创建的用户
- master_password: 复制用户密码，对应上面2.1.3中的创建的用户密码
- master_log_file: 日志文件名，对应上面2.1.4中的File
- master_log_pos: 日志文件位置，对应上面2.1.4中的Position
2.2.4 查看从服务器状态
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.17.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 617
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: No
Slave_SQL_Running: No
// 省略
1 row in set (0.00 sec)
ERROR:
No query specified
Slave_IO_Running和Slave_SQL_Running都是NO，表明slave还没开始复制过程
2.2.5 开启主从同步
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.0.2
Master_User: slave
Master_Port: 3306
Connect_Retry: 30
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 617
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
// 省略
1 row in set (0.00 sec)
到这里主从配置就已经好了，接下来就可以进行测试了。先在主服务器中进行操作，然后看从服务器有没有自动同步主服务器的数据。
注: 对于同一个mysql实例，先使用docker stop再使用docker start，即时没加-v参数指定数据文件，之前的数据也不会丢失。如果启动了一个新实例，同时还想用已有的实例的数据，则可以先使用docker cp mysql:/var/lib/mysql /home/tmp/mysql/data将已有实例的数据拷贝出来。然后在启动新实例时使用-v参数指定该数据文件。
