1.主服务器要配置binlog
2.从服务器要配置relaylog
3.从服务器如何有权读取master的binlog?(binlog是比较敏感的)----》【主要通过授权的方式:master要授权slave账号】
4.从服务器用账号链接master
原因:
主从分离
主:binlog-format=row/statement/mixed 【日志格式】
Statement: 2进制记录的执行语句,如update,insert.....
Row:2进制记录的是磁盘变化
那个方式好:
Update age =age+1 where id=13; //语句长而磁盘变化少,宜用row
Update salary=salary+100; //语句短,但影响上万行,磁盘变化大,宜用statement
Mixed ,混合,由系统根据语句,来决定
1.拉取官方的镜像,标签为5.7
[root@localhost ~]# docker pull mysql:5.7
[root@localhost ~]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql 5.7 383867b75fd2 2 weeks ago 373MB
说明:此处是通过拉取官方镜像的方式,也可以自行通过 Dockerfile构建
2.创建文件目录
[root@localhost ~]# mkdir -p /var/docker-mysql57
[root@localhost ~]# cd /var/docker-mysql57
创建主的mysql容器配置目录
[root@localhost docker-mysql57]# mkdir -p mysql-master/data mysql-master/logs mysql-master/conf
[root@localhost docker-mysql57]# cd mysql-master/
[root@localhost mysql-master]# ls
conf data logs
3.创建容器并启动【创建主mysql】
一定要在此目录下执行【自行定义的目录】:
[root@localhost mysql-master]# pwd
/var/docker-mysql57/mysql-master
[root@localhost mysql-master]# docker run -p 3500:3306 --name mysql-master -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
8db4f0250932df8219f1d04238b70656ca7a8883dd5343eb55f7e3f30a0b0967
命令说明:
-p 3500:3306:将容器的 3500端口映射到主机的 3306 端口。
-v $PWD/conf:/etc/mysql/conf.d:将主机当前目录下的 conf/my.cnf 挂载到容器的 /etc/mysql/my.cnf。
-v $PWD/logs:/logs:将主机当前目录下的 logs 目录挂载到容器的 /logs。
-v $PWD/data:/var/lib/mysql :将主机当前目录下的data目录挂载到容器的 /var/lib/mysql 。
-e MYSQL_ROOT_PASSWORD=123456:初始化 root 用户的密码。
解释说明:
docker run -p 3500:3306 --name mysql-master(创建容器的名称) -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7(镜像名称加标签--IMAGE)
查看容器
[root@localhost mysql-master]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
8db4f0250932 mysql:5.7 "docker-entrypoint.s…" 2 minutes ago Up 2 minutes 33060/tcp, 0.0.0.0:3500->3306/tcp mysql-master
进入容器:
[root@localhost mysql-master]# docker exec -it mysql-master bash
利用navicat外部链接mysql:
4.创建容器并启动【创建从mysql】
[root@localhost docker-mysql57]# mkdir -p mysql-slave/data mysql-slave/logs mysql-slave/conf
[root@localhost docker-mysql57]# cd mysql-master/
[root@localhost mysql-master]# ls
conf data logs
创建容器
[root@localhost mysql-slave]# pwd
/var/docker-mysql57/mysql-slave
[root@localhost mysql-slave]# docker run -p 3501:3306 --name mysql-slave -v $PWD/conf:/etc/mysql/conf.d -v $PWD/logs:/logs -v $PWD/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
2467554bca690d2378a7bebe1af796a854467ef5336ccb369fd730e7cf35953b
[root@localhost mysql-slave]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
2467554bca69 mysql:5.7 "docker-entrypoint.s…" 5 seconds ago Up 3 seconds 33060/tcp, 0.0.0.0:3501->3306/tcp mysql-slave
8db4f0250932 mysql:5.7 "docker-entrypoint.s…" 14 minutes ago Up 14 minutes 33060/tcp, 0.0.0.0:3500->3306/tcp mysql-master
截图
进入容器:
[root@localhost mysql-slave]# docker exec -it mysql-slave bash
进入mysql:
root@2467554bca69:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.27 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>
利用navicat外部链接mysql:
4.配置主从的配置文件
4.1配置my.cnf配置文件【主mysql】
[root@localhost mysql-master]# vim conf/my.cnf
配置文件内容:
[client]
port = 3306
[mysqld]
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
#user=mysql
character_set_server=utf8
init_connect='SET NAMES utf8'
#主id
server-id=1
#开启bin-log日志
log-bin=mysql-bin
#二进制日志的格式 mixed/row/statement
binlog-format=mixed
#定期自动清理日志【自行决定是否开启】
#expire_logs_days=7
配置好文件之后,重启主的mysql
[root@localhost mysql-master]# docker restart 8db4f0250932
8db4f0250932
[root@localhost mysql-master]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
dce867809902 mysql:5.7 "docker-entrypoint.s…" 13 minutes ago Up 13 minutes 33060/tcp, 0.0.0.0:3502->3306/tcp mysql-test
2467554bca69 mysql:5.7 "docker-entrypoint.s…" 19 minutes ago Up 19 minutes 33060/tcp, 0.0.0.0:3501->3306/tcp mysql-slave
8db4f0250932 mysql:5.7 "docker-entrypoint.s…" 34 minutes ago Up 3 seconds 33060/tcp, 0.0.0.0:3500->3306/tcp mysql-master
截图:(需要留意是否正常启动,反之配置有问题)
4.2配置从的my.cnf配置
[root@localhost mysql-slave]# pwd
/var/docker-mysql57/mysql-slave
[root@localhost mysql-slave]# vim conf/my.cnf
配置文件内容
[client]
port = 3306
[mysqld]
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
character_set_server=utf8
init_connect='SET NAMES utf8'
#从id
server-id=2
#开启bin-log日志
log-bin=mysql-bin
#读取主bin-log日志
relay-bin=mysql-replay
配置好文件之后,重启从的mysql
[root@localhost mysql-slave]# docker restart 2467554bca69
2467554bca69
[root@localhost mysql-slave]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
dce867809902 mysql:5.7 "docker-entrypoint.s…" 27 minutes ago Up 27 minutes 33060/tcp, 0.0.0.0:3502->3306/tcp mysql-test
2467554bca69 mysql:5.7 "docker-entrypoint.s…" 33 minutes ago Up 8 seconds 33060/tcp, 0.0.0.0:3501->3306/tcp mysql-slave
8db4f0250932 mysql:5.7 "docker-entrypoint.s…" 47 minutes ago Up 13 minutes 33060/tcp, 0.0.0.0:3500->3306/tcp mysql-master
734557f4d40a hello-world "/hello" 52 minutes ago Exited (0) 52 minutes ago recursing_banach
(需要留意是否正常启动,反之配置有问题)【请按照实际的容器id操作】
5.进入主的mysql【进入mysql-master容器】,并配置从的账户(以下操作都在mysql-master容器操作【主mysql】)
5.1进入mysql-master容器
[root@localhost mysql-master]# docker exec -it mysql-master bash
root@8db4f0250932:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27 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>
查看bin-log日志是否开启
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)
mysql> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.00 sec)
5.2分配从的用户。并刷新
mysql> grant replication client,replication slave on *.* 'slave_user'@'%' identified by '123456';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''slave_user'@'%' identified by '123456'' at line 1
mysql> grant replication client,replication slave on *.* to 'slave_user'@'%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 615 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.01 sec)
读取主的容器(mysql-master)ip
[root@localhost mysql-master]# docker inspect mysql-master
6.进入从的mysql【进入mysql-slave容器】(以下操作都在mysql-slave容器操作【从mysql】)
6.1进入mysql-slave容器
[root@localhost ~]# docker exec -it mysql-slave bash
root@2467554bca69:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-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>
6.2查看bin-log是否开启:
6.3配置读取主bin-log的用户
mysql> show slave status;
Empty set (0.01 sec)
mysql> change master to
-> master_host='172.17.0.2',
-> master_user='slave_user',
-> master_password='123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=615;
Query OK, 0 rows affected, 2 warnings (0.03 sec)
截图说明:
6.4查看是否连接上主服务器
mysql> show slave status\G;
命令说明:
start slave; ------>开启从服务器
stop slave ------>停止从服务器
reset slave ------>重启从服务器
开启从服务器:
mysql> start slave;
启动状态:
7.在window操作或linux操作
只需要在主mysql创建数据库,然后在从查看是否有数据库
8.总结
1.只可以一主多从,不可以多主