基于docker搭建mysql主从

目录

创建容器并运行

搭建mysql(master)服务器

 搭建mysql(slave)从服务器3311

 配置主从

---

 

创建容器并运行

---

1.

docker run

-i：以交互模式运行容器

-t：为容器重新分配一个伪输入终端

—name :容器名称

—privileged: 设置容器公开权限（默认为true）

-p :映射端口 linux端口: 容器内置端口(mysql默认端口为3306)

-v : linux挂载文件夹/文件和容器内路径的映射

-e: 容器的环境变量（设置mysql默认用户名&密码)

-d: 后台运行容器，并返回容器ID

2. 进入容器

 3.容器中运行MySQL客户端

        如果登录成功证明docker安装MySQL成功

 4.退出容器、

        输入：exit

5.开放指定端口       

        firewall-cmd —zone=public —add-port=端口号/tcp —permanent

        —zone: public 公开端口

        —add-port: 端口号/协议名称

        —permanent: 永久开放

6. 重新加载防火墙

7. 查看开放的端口号

8.navicat测试连接

---

1.检查binlog是否开启   

        进入主mysql容器，输入命令: show variables LIKE ‘log_%’

        ON: 表示已开启

 2.查看当前mysql容器中的binlog日志文件

        切换目录到 / var/lib/mysql,查看日志文件

 3.查看当前mysql数据库binlog情况

        登录mysql客户端: mysql -uroot -p123

        使用命令: show master status;

搭建mysql(master)服务器

1.编辑my.cnf文件,重启docker

        

# Copyright (c) 2017, 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
secure-file-priv= NULL
default_authentication_plugin=mysql_native_password

# Custom config should go here
!includedir /etc/mysql/conf.d/

在文件的路径下输入

        vim my.cnf

进入编辑模式

按 i  进入编辑

#上面的文件不用修改，在上面的文件最下面添加上这些

server_id=200 #服务器id

log_bin=mysql-bin #binlog名称

binlog_format=row #binlog的格式

 

编辑结束以后按esc然后按shift+；输入wq保存退出

 

2.查询容器内my.cnf

1. [root@localhost conf]# docker exec -it mysql_3306 bash
2. root@1b32ebb973d1:/# cd /etc/mysql
3. root@1b32ebb973d1:/etc/mysql# cat my.cnf

代表修改成功 

3.创建与从(slave)服务通信的用户

1. create user ‘slave’@’%’ identified by ‘123’;
2. GRANT REPLICATION SLAVE,REPLICATION CLIENT ON . TO ‘slave’@’%’;
3. ALTER USER ‘slave’@’%’ IDENTIFIED WITH mysql_native_password BY ‘123’;
4. flush privileges；

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

4.查询master的状态

 

 

 5.获取master容器ip地址

        退出容器，查询master容器内部地址。

 

 搭建mysql(slave)从服务器3311

---

1.修改3310内的配置文件

 如果要在创建第2个从服务器，log_bin的名字需要修改

 2.创建运行slave02容器

 

 

3.开放3311端口

[root@localhost conf]# firewall-cmd --zone=public --add-port=3310/tcp --permanent

success

[root@localhost conf]# firewall-cmd --reload

success

//检查是否成功

[root@localhost conf]# firewall-cmd --zone=public --list-ports

3306/tcp 3310/tcp

 

4.navicat测试

 

 

 配置主从

1.查询master的状态

 

 2.修改从(slave)服务器与master关联

        要去从服务器下配置

        master_host是之前获取的容器IP地址

        user和password都是之前创建的通信账号

        FTILE和POS和上面的第一步对应

 

3. 开启slave

         start slave;

 4.

show slave status \G;

 

 5.测试主从

在master创建库book_db,查看从（slave）是否也创建成功。

 

 

 

这样就是配置成功了