Docker 部署 Mysql8.0

于 2018-07-06 17:31:08 发布 37426 收藏 90
分类专栏： 数据库(DataBase) 容器(Docker) 文章标签： docker mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/xsj34567/article/details/80940238
版权

1. 参照官网，安装docker 

2.拉取mysql镜像 （默认拉取最新的镜像）8.0.11

docker pull mysql

3.在宿主机创建持久化 mysql data 及mysql.cnf 

mkdir /usr/local/mysqlData/test/cnf
mkdir /usr/local/mysqlData/test/data
vi /usr/loal/mysqlData/test/cnf/mysql.cnf

    设置本地文件共享：

    Docker -> Preferences... -> File Sharing

4.添加操作权限 

chmod 777 /usr/local/mysqlData/test/data
    备注：挂载时权限验证（操作权限）

5.运行镜像，设置初始密码、本机与docker端口的映射与挂载本地数据盘 (启动msyql服务)

docker run -itd -p 3307:3306 --name test_mysql -v /usr/local/mysqlData/test/conf:/etc/mysql
 -v /usr/local/mysqlData/test/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql


# 说明： 新版需要挂载 /var/lib/mysql-files/ 目录  （一行代码）

docker run -itd -p 3307:3306 --name test_mysql -v /usr/local/mysqlData/test/conf:/etc/mysql
 -v /usr/local/mysqlData/test/data:/var/lib/mysql -v /usr/local/mysqlData/test/mysql-files:/var/lib/mysql-files/ -e MYSQL_ROOT_PASSWORD=123456 mysql

运行结果：

6. 进入test_mysql 容器

Docker exec -it test_mysql bash

如图：

 

7.在容器内登录mysql

8.查看用户信息

mysql> select user,host,authentication_string from mysql.user;

+------------------+-----------+------------------------------------------------------------------------+

| user             | host      | authentication_string                                                  |

+------------------+-----------+------------------------------------------------------------------------+

| root             | %         | $A$005$7o{'|'AomAw(QvF#.p5wLtCnrG6yX6XQdDVQivGr96POVL.gKnhIAhUhl3. |

| mysql.infoschema | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |

| mysql.session    | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |

| mysql.sys        | localhost | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE                              |

| root             | localhost | $A$005$0.-%i)H{uYi@zFo7uYF82fYw7DsA93vYLr4uZv6I1tSKao0sbzzcDap3 |

+------------------+-----------+------------------------------------------------------------------------+

5 rows in set (0.00 sec)

9.设置权限（为root分配权限，以便可以远程连接）

mysql> grant all PRIVILEGES on *.* to root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

如果出现异常：（初始密码不成功，密码默认为空，直接Enter）

root@7786717a8774:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.22 MySQL Community Server - GPL

Copyright (c) 2000, 2020, 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> grant all PRIVILEGES on *.* to root@'%' WITH GRANT OPTION;
ERROR 1410 (42000): You are not allowed to create a user with GRANT
mysql> 
mysql> 
mysql> update user set host='%' where user='root';
ERROR 1046 (3D000): No database selected
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

10.由于Mysql5.6以上的版本修改了Password算法，这里需要更新密码算法，便于使用Navicat连接

mysql> grant all PRIVILEGES on *.* to root@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER user 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.11 sec)

mysql> ALTER user 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.11 sec)

mysql>  FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.01 sec)

11. 使用navicat 连接mysql,如图：

 

12.建库、建表、加数据

 

查看挂载本地数据盘的内容：

 

13. 测试将容器移除后，数据是否仍然存在

docker rm -f test_msyql

容器已经移除了。重新部署test_mysql,参照第5的步骤，进入新容器，访问数据库：

xushijiandeiMac:data xushijian$ docker run -itd -p 3307:3306 --name test_mysql -v /usr/local/mysqlData/test/conf:/etc/mysql -v /usr/local/mysqlData/test/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql
65b7a60050aaef5765ed055acfd071c7c76f60e85dc25d0e73e0d56eae14aed1
xushijiandeiMac:data xushijian$ docker exec -it test_mysql bash
root@65b7a60050aa:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from user_user;
+---------+-----------+-------------+--------+
| user_id | user_name | phone       | note   |
+---------+-----------+-------------+--------+
|       1 | 没长正 | 13980000000 | 测试 |
+---------+-----------+-------------+--------+
1 row in set (0.06 sec)

发现数据仍然可以使用，不需要额外的配置，实现了数据的持久化。

 

阿里云上docker 部署 MySQL(通过编排模板部署)

<1. 配置同第3步类似（只是目录变化）

master:
  image: 'mysql:latest'
  environment:
    - MYSQL_ROOT_PASSWORD=123456
  ports:
    - '3307:3306/tcp'
  volumes:
    - '/usr/local/mysqlData/master/conf:/etc/mysql:rw'
    - '/usr/local/mysqlData/master/data:/var/lib/mysql:rw'
  labels:
    aliyun.scale: '1'

<2.如下图,已部署完成

[root@c13a6d832fd0a49398c62002361d75c60-node1 ~]# clear
[root@c13a6d832fd0a49398c62002361d75c60-node1 ~]# docker ps
CONTAINER ID        IMAGE                                                                   COMMAND                  CREATED             STATUS              PORTS                                            NAMES

8597b7539a3a        mysql:latest                                                            "docker-entrypoint..."   3 minutes ago       Up 3 minutes        0.0.0.0:3307->3306/tcp                           mysql_master_1

 

<3.进入容器，进行权限设置，后续过程参照本机

[root@c13a6d832fd0a49398c62002361d75c60-node1 /]# docker exec -it mysql_master_1 bash
root@2fc0bbf48941-mysql-master-1:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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>

 

<4. 开放3307端口，使得外网可以访问

 

      云服务器ECS -> 安全组 -> 选择所在的地区 ->配置规则 -> 添加安全组

添加安全组，如图：

 

5.外网访问，如下图

已连接成功。

 

主从环境搭建：

主库：

[root@c13a6d832fd0a49398c62002361d75c60-node1 ~]# docker exec -it mysql_master_1 bash
root@2fc0bbf48941-mysql-master-1:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.30 sec)

mysql> create database test;
Query OK, 1 row affected (0.12 sec)

mysql> show database;
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 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

 

从库：    

 

[root@c13a6d832fd0a49398c62002361d75c60-node1 ~]# docker exec -it  mysql-slave_slave_1 bash
root@c8661e16e3fd-mysql-slave-slave-1:/# mysql -uroot -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.40 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event  主从配置成功！
                  Master_Host: 47.94.225.124
                  Master_User: rep
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 155
               Relay_Log_File: c8661e16e3fd-mysql-slave-slave-1-relay-bin.000004
                Relay_Log_Pos: 363
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 155
              Relay_Log_Space: 762
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: a482f5fe-80fb-11e8-9fb1-0242ac12020c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (1.01 sec)

mysql> show database;
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 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)Waiting for master to send event  主从配置成功！
                  Master_Host: 47.94.225.124
                  Master_User: rep
                  Master_Port: 3307
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 155
               Relay_Log_File: c8661e16e3fd-mysql-slave-slave-1-relay-bin.000004
                Relay_Log_Pos: 363
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 155
              Relay_Log_Space: 762
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 1
                  Master_UUID: a482f5fe-80fb-11e8-9fb1-0242ac12020c
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
       Master_public_key_path: 
        Get_master_public_key: 0
1 row in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (1.01 sec)

mysql> show database;
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 'database' at line 1
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

主从库原理分析：

 

i/o线程去请求主库 的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中；
主库会生成一个 log dump 线程，用来给从库 i/o线程传binlog；
 
SQL 线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；

 

即： 从库IO线程请求 -> 中继日志 ->获取binlog ->从库SQL线程，解析

参考：

    https://docs.docker.com/ docker 官网文档

    https://blog.csdn.net/gf0515/article/details/80466213 Mac Navicat连接Docker  MySql8.0

    docker mysql 主从复制

    https://blog.csdn.net/qq_28804275/article/details/80891951 主从环境搭建

    docker mysql Dockerfile mysql 开源镜像Dockerfile及配置

    https://www.cnblogs.com/Aiapple/p/5792939.html 主从复制原理、高可用分析

    http://www.cnblogs.com/Aiapple/p/5793786.html 实战

xsj34567
关注
  • 17
    点赞
  • 踩
  • 90
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 10
    评论
专栏目录
使用 docker-compose 运行 MySQL的方法
09-09
主要介绍了使用 docker-compose 运行 MySQL的方法，本文给大家介绍的非常详细，具有一定的参考借鉴价值,需要的朋友可以参考下
docker安装mysql8教程
lo浅安灬时光ve
11-13 6796
Docker安装MySQL8.0教程（小白教程） 1、查看可用的 MySQL 版本MySQL 镜像库：Docker Hub。我们还可以用docker search mysql命令来查看可用版本： 2.拉取 MySQL 镜像shitf+ins 粘贴到服务器上 回车等待这就安装好了，方便快捷 3.查看本地镜像查看MySQL是否拉取成功：docker images可以看到已经拉取到了mysql 8.0.27 的镜像。 4.运行容器 运行 mysql 容器：docker run -itd --n...
Docker安装Mysql8.0
最新发布
渣
04-24 49
在有外网的机器下将镜像下载下来，然后存储到磁盘，拷贝到对应的内网服务器。内网（服务器不可联网）
docker 安装mysql8详细说明（复制命令即可跑起来）
qq_36252295的博客
01-21 1万+
docker 安装mysql8详细说明，手把手教学，懂吧？
写最好的Docker安装最新版MySQL8（mysql-8.0.31）教程（参考Docker Hub和MySQL官方文档）
u014282578的博客
11-19 8555
本教程是笔者参考Docker Hub和MySQL官方文档，经过多次操作，踩坑无数才撰写出来的，内容不能说是全网最好，但绝对不会很差，起码比网上很多博文要详细得多。例如配置编码集参数解决中文乱码问题，本教程中有多种方法处理，其它博文可能就只写一种。Docker中安装MySQL8容器，要注意的细节很多，比安装MySQL5.7要麻烦得多。
Docker安装mysql8.0.16、配置时区、配置密码、配置忽略大小写
没事别学JAVA
07-31 513
Docker安装mysql8.0.16、配置时区、配置密码、配置忽略大小写
docker安装mysql8
qq_43050077的博客
08-15 3450
docker查找mysql镜像 docker search mysql docker拉取mysql8镜像 #安装MySQL8 docker pull mysql:8.0.16 #默认安装mysql最新版： docker pull mysql 启动MySQL #创建数据存储目录 mkdir -p /data/docker_volumn/mysql/conf mkdir -p /data/docker_volumn/mysql/data mkdir -p /data/docker_volumn/my
docker安装mySQL 8
HELLO WORLD
04-05 4043
docker安装mySQL 8
Docker安装MySql8
m0_37875239的博客
02-03 263
Docker安装MySql8
docker部署mysql8避坑版，看这一篇就够了
zwjzone的博客
08-04 1061
docker部署mysql8
Docker 部署 Mysql8.0的方法示例
01-08
docker pull mysql 3.在宿主机创建持久化 mysql data 及mysql.cnf  mkdir /usr/local/mysqlData/test/cnf mkdir /usr/local/mysqlData/test/data vi /usr/loal/mysqlData/test/cnf/mysql.cnf  设置本地文件共享：...
使用Docker部署MySQL 5.7&8.0主从集群的方法步骤
09-09
主要介绍了使用Docker部署MySQL 5.7&8.0主从集群的方法步骤，文中通过示例代码介绍的非常详细，对大家的学习或者工作具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧
mac 中docker安装mysql的图文教程
09-09
今天在Docker中安装了MySQL ，是自己打的docker包.首先新建Dockerfile,新建Dockerfile后执行build命令,具体详情在本教程中给大家介绍的非常详细，感兴趣的朋友参考下
mysql主主部署(docker+mysql+nginx+springboot)
02-15
Docker安装mysql8.0集群-主主（双主）部署实现 1. 主从复制的原理 2. 主主（双主）实现方案 3. 前置条件 4. 安装步骤 （1） 拉取镜像 （2） docker启动mysql容器 1. 数据卷挂载 2. 编写my.cnf文件（这一步...
docker-mysql.rar
01-14
docker部署mysql8.0 开箱即用
docker安装mysql并配置文件_Docker安装MySQL 8.0并挂载数据及配置文件
weixin_39941262的博客
01-20 392
安装部署环境Ubuntu 18.04.3 LTSDocker 19.03.2MySQL latest(8.0.17)下载镜像# docker从仓库中拉取最新版的mysql镜像，如果没加标签的话，默认获取最新的版本Docker pull mysql挂载数据卷以及配置文件Docker容器原则上是短暂的，如果容器被删除或损坏，任何数据或配置都将丢失。因为，我们需要将 Docker 内的 MySQL...
Docker 容器启动 mysql -V 挂载数据目录 备份数据
热门推荐
yan_zuoyu的博客
06-19 1万+
1.通过 docker  下载 mysql:8.0.1 镜像2.创建文件备份目录 mkdir -p /home/mysql/{conf,logs,data/mysql}3.my.cnf 文件  大概是这样 ，可自行修改# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved. # # This program i...
在Docker中安装MySQL8完整版
随风的博客
03-04 296
在Docker中安装MySQL8完整版
docker部署mysql8.0
03-02
很高兴回答您的问题。要部署MySQL 8.0，您可以使用Docker容器。首先，您需要在服务器上安装Docker，然后您可以通过运行以下命令来安装MySQL 8.0：docker run --name mysql8 -e MYSQL_ROOT_PASSWORD=yourpassword -d mysql:8.0.

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 10
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

xsj34567

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额：-- )
扫码支付
扫码支付：¥2
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、C币套餐、付费专栏及课程。

余额充值