Docker 部署 Mysql8.0的方法示例

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

908F1B5A-68FB-8408-963A-2BBF706D4AC4.png

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

运行结果:

DE4CF2CA-A548-9460-D5DF-E428316F718F.png

6. 进入test_mysql 容器

Docker exec -it test_mysql bash

如图:

C53BCD16-A56C-742E-227F-66958263060B.png

7.在容器内登录mysql

1A5DDA75-28F3-D81E-7208-E59BA82183D5.png

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,如图:

3261E28A-AED6-F0CA-4204-A75854331C38.png

3BFDD366-3943-A2C4-7A12-074FE1A36D68.png

12.建库、建表、加数据

9835B1C1-3A40-28E8-65DF-C5A4BAD9EA17.png

75478836-63FD-E31E-E628-B6DBD512AB07.png

914076E4-8A25-3BFD-B6F7-7FED73978866.png

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

13BF7DA6-667A-C0FB-560B-EFC68F44771F.png

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

docker rm -f test_msyql

41926F79-D70E-53E7-20F1-1C3442B09423.png

容器已经移除了。重新部署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

5FD5FB14-65DB-DE19-F1E0-09A10A39C536.png

<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 -> 安全组 -> 选择所在的地区 ->配置规则 -> 添加安全组

AA4A90E9-C022-8914-DDA9-FC4BC66A6FC1.png

添加安全组,如图:

9FF9111C-6A81-FE81-FCA9-E63A23DE8A1F.png

5.外网访问,如下图

F6B8A383-82B8-63E4-DD08-39F267EFB919.png

已连接成功。

主从环境搭建:

主库:

[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)

主从库原理分析:

24A90AD8-FCDD-F261-2183-43AF16180D3A.png

i/o线程去请求主库 的binlog,并将得到的binlog日志写到relay log(中继日志) 文件中;

主库会生成一个 log dump 线程,用来给从库 i/o线程传binlog;

SQL 线程,会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致;

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

参考:

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

MAC上用Navicat连接本机Docker的Mysql8.0Mac Navicat连接Docker MySql8.0

Docker Mysql主从复制

Docker MySQL主从环境搭建Docker MySQL主从环境搭建

docker mysql Dockerfilemysql 开源镜像Dockerfile及配置

Mysql主从复制原理主从复制原理、高可用分析

Mysql主从复制实践实战

收藏 (0)
评论列表
正在载入评论列表...
我是有底线的