Docker安装mysql 5.7主从复制

一、安装master

拉取mysql镜像

docker pull mysql:5.7.19
1

启动主服务器mysql

docker run -m 400m -p 3306:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD="root" -d mysql:5.7.19
1

进入容器

docker exec -it mysql-master /bin/bash
1

安装必要组件

apt-get update & apt-get install vim
1

这两条命令执行不成功的话，可以分开执行，多试几次就可以了。

修改mysql的配置

$ cd /etc/mysql/

$ ls
conf.d  my.cnf  my.cnf.fallback  mysql.cnf  mysql.conf.d

$ cd mysql.conf.d

$ ls
mysqld.cnf

$ vim mysqld.cnf
[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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

在[mysqld]下面添加以下配置，并保存

log-bin=mysql-bin
server-id=1
log-slave-updates=true
1
2
3

最后结果

[mysqld]
log-bin=mysql-bin
server-id=1
log-slave-updates=true
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
1
2
3
4
5
6
7
8
9
10
11
12

然后进入mysql

mysql -uroot -proot
1

创建同步的用户

mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
mysql> FLUSH PRIVILEGES;
1
2
3

重启docker

docker restart mysql-master
1

重新进入容器

docker exec -it mysql-master /bin/bash
1

进入mysql

mysql -uroot -proot
1

查看是否server_id配置成功

mysql> SHOW GLOBAL VARIABLES like 'server\_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 1     |
+---------------+-------+
1 row in set (0.01 sec)
1
2
3
4
5
6
7

可以看到server_id和我们配置的一致

查看master状态

mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000001
         Position: 350
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

刷新日志

mysql> flush logs;
1

再次查看master状态，可以看到mysql-bin文件变成mysql-bin.000002了， Position也变成154了，记录下这两个值，后面配置slave时需要使用到。

mysql> show master status \G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8

二、安装slave

启动从服务器mysql

docker run -m 400m -p 3307:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD="root" -d mysql:5.7.19
1

进入容器

docker exec -it mysql-slave /bin/bash
1

安装必要组件

apt-get update & apt-get install vim
1

修改mysql的配置

在[mysqld]下面添加以下配置，并保存

# server-id要与master不一样
server-id=2
1
2

最后结果

[mysqld]
server-id=2
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
1
2
3
4
5
6
7
8
9
10

重启docker，再次进入容器后进入mysql

mysql -uroot -proot
1

查看配置是否成功

mysql> SHOW GLOBAL VARIABLES like 'server\_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 2     |
+---------------+-------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

开启同步，master_log_file和master_log_pos是查看master状态时的值，需要填写一致。另外master_host配置的是master的IP，可以在master使用cat /etc/hosts查看。

mysql> stop slave;

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=154;

mysql> reset slave;
mysql> start slave;
1
2
3
4
5
6

查看slave状态

mysql> show slave status\G;
1

看到以下两个值都为Yes就表示配置成功了。

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1
2

异常情况

如果Slave_IO_Running: No 的话，需要看下面的日志，具体问题具体分析。

• 网络不通

这个可以通过工具来测试，或者在slave中使用 mysql -uslave -p123456 -h 172.17.0.2 -P3306来测试是否能连接master。

• 配置的change master to不正确

可以看到错误日志

Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file'
1
2

重新在master的mysql上flush logs;再设置一下change master to

mysql> change master to master_host='172.17.0.2', master_user='slave', master_password='123456', master_port=3306, master_log_file='mysql-bin.0000xx', master_log_pos=xxx;
1

这里设置完需要重新启动一下slave

mysql> stop slave;
Query OK, 0 rows affected (0.02 sec)

mysql> reset slave;
Query OK, 0 rows affected (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
1
2
3
4
5
6
7
8

三、其他

docker安装mysql所保存的系统表备份在/usr/share/mysql目录