Docker 部署 Mysql8.0的方法示例

站长资源 2024/11/23 佚名

73 1538 73

DDR爱好者之家 Design By 杰米

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

运行结果：

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)

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)

主从库原理分析：

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 实战

Docker,部署,Mysql8.0

DDR爱好者之家 Design By 杰米

广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站资源来自互联网收集,仅供用于学习和交流,请遵循相关法律法规,本站一切资源不代表本站立场,如有侵权、后门、不妥请联系本站删除！

DDR爱好者之家 Design By 杰米

评论“Docker 部署 Mysql8.0的方法示例”

暂无评论...

Docker 部署 Mysql8.0的方法示例

docker常用命令总结(推荐)

详解tomcat各个端口的作用

评论“Docker 部署 Mysql8.0的方法示例”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

Docker 部署 Mysql8.0的方法示例

docker常用命令总结(推荐)

详解tomcat各个端口的作用

评论“Docker 部署 Mysql8.0的方法示例”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新日志

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存