一、安装master
拉取mysql镜像
docker pull mysql:5.7.19
启动主服务器mysql
docker run -m 400m -p 3306:3306 --name mysql-master -e MYSQL_ROOT_PASSWORD="root" -d mysql:5.7.19
进入容器
docker exec -it mysql-master /bin/bash
安装必要组件
apt-get update & apt-get install vim
这两条命令执行不成功的话,可以分开执行,多试几次就可以了。
修改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
在[mysqld]下面添加以下配置,并保存
log-bin=mysql-bin
server-id=1
log-slave-updates=true
最后结果
[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
然后进入mysql
mysql -uroot -proot
创建同步的用户
mysql> CREATE USER 'slave'@'%' IDENTIFIED BY '123456';
mysql> GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slave'@'%';
mysql> FLUSH PRIVILEGES;
重启docker
docker restart mysql-master
重新进入容器
docker exec -it mysql-master /bin/bash
进入mysql
mysql -uroot -proot
查看是否server_id
配置成功
mysql> SHOW GLOBAL VARIABLES like 'server\_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 1 |
+---------------+-------+
1 row in set (0.01 sec)
可以看到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)
刷新日志
mysql> flush logs;
再次查看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)
二、安装slave
启动从服务器mysql
docker run -m 400m -p 3307:3306 --name mysql-slave -e MYSQL_ROOT_PASSWORD="root" -d mysql:5.7.19
进入容器
docker exec -it mysql-slave /bin/bash
安装必要组件
apt-get update & apt-get install vim
修改mysql的配置
在[mysqld]下面添加以下配置,并保存
# server-id要与master不一样
server-id=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
重启docker,再次进入容器后进入mysql
mysql -uroot -proot
查看配置是否成功
mysql> SHOW GLOBAL VARIABLES like 'server\_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 2 |
+---------------+-------+
1 row in set (0.00 sec)
开启同步,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;
查看slave状态
mysql> show slave status\G;
看到以下两个值都为Yes就表示配置成功了。
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
异常情况
如果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'
重新在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;
这里设置完需要重新启动一下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)
三、其他
docker安装mysql所保存的系统表备份在/usr/share/mysql
目录