配置主从数据库

目的：配置主从数据库，实现从数据库中数据与主数据库存储内容一致。

1.容器中创建主数据库

！！！由于每次重启ip地址都会变因此 最好固定容器ip地址

1.1先用docker network ls 查看本地的docker网络配置，mysqlnet是我后加的，默认是除mysqlnet以外的三个

1.2创建一个自己的docker网络配置，命令如下

docker network create --driver bridge --subnet=172.18.12.0/16 --gateway=172.18.1.1 mysqlnet

docker network 创建   --driver 对应的模式     --subnet 内网网段     --gateway 网关    网络配置的名称

1.3创建主数据库 记得加IP地址

docker run -itd --name mysql_3306 --privileged=true -p 3306:3306 -v /usr/local/software/mysql/3306/data:/var/lib/mysql -v /usr/local/software/mysql/3306/conf/my.cnf:/etc/mysql/my.cnf -v /usr/local/software/mysql/3306/mysql-files:/var/lib/mysql-files -e MYSQL_ROOT_PASSWORD=123 --network=mysqlnet --ip 172.18.12.2 mysql

2.修改my.cnf文件

2.1文件结构 /user/local/software/程序名称

                      

!includedir /etc/mysql/conf.d/
server-id=201
log-bin=mysql-bin
binlog_format=row
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
default_authentication_plugin=mysql_native_password
# Custom config should go here
!includedir /etc/mysql/conf.d/
server-id=200
log-bin=mysql-bin
binlog_format=row

3.master中创建slave账号操作

进入主数据库

[root@localhost conf]# docker exec -it mysql_master bash

登录

root@6c7b6078f4ec:/# mysql -u root -p

Enter password:

创建主从账号”slave”

mysql> create user 'slave'@'%' identified by '123';
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)
mysql> use mysql
mysql> ALTER USER 'slave'@'%' IDENTIFIED WITH mysql_native_password BY '123';
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

获取master容器的状态

显示mysql_3306的主机状态
show master status;

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      157 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

获取mysql_master的ip地址

docker inspect 容器名称 |grep IPA

root@localhost conf]# docker inspect mysql_master |grep IPA
            "SecondaryIPAddresses": null,
            "IPAddress": "172.17.0.2",
                    "IPAMConfig": null,
                    "IPAddress": "172.17.0.2",

4.mysql从服务器

修改从配置文件（复制粘贴人的使用要注意 " " 要自己打，经常会变成中午的）

创建从数据库

docker run -itd --name mysql_3310 --privileged=true -p 3310:3306 -v /usr/local/software/mysql/3310/data:/var/lib/mysql -v /usr/local/software/mysql/3310/conf/my.cnf:/etc/mysql/my.cnf -v /usr/local/software/mysql/3310/mysql-files:/var/lib/mysql-files -e MYSQL_ROOT_PASSWORD=123 --network=mysqlnet --ip 172.18.12.3 mysql

 进入mysql配置主从

change master to master_host='172.18.12.2',master_user='slave',master_password='123',MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=157;

开启主从

start slave;

查看状态

mysql> show slave status \G;

看到这两个yes 就ok了