腾讯云ECS使用docker部署MySQL8并配置任意host可远程连接到MySQL

终端环境下直接运行MySQL8.0容器

如果本地没有MySQL8.0镜像会自动去Dockerhub拉取。

[root@VM_0_7_centos ~]# docker run -d --name mysql8 -p 3306:3306 -v /home/mysql/datadir:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 mysql
bf91ae481949874630acbab6dbdbbd0f3377f5a4b271235db5c6a9c6a5120e5c
[root@VM_0_7_centos ~]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
bf91ae481949        mysql               "docker-entrypoint.s…"   16 seconds ago      Up 15 seconds       0.0.0.0:3306->3306/tcp, 33060/tcp   mysql8
  • -d 后台运行容器并且打印出容器ID。
  • --name mysql8自定义容器名称为mysql8
  • -p 3306:3306 指定宿主机的3306端口号映射到容器的3306端口号。
  • -v /home/mysql/datadir:/var/lib/mysql 将宿主机目录/home/mysql/datadir挂载到容器的 /var/lib/mysql用来保存数据文件。
  • -e MYSQL_ROOT_PASSWORD=123456 通过环境变量设置MySQLroot用户的密码为123456

配置任意host可远程连接到docker容器中的MySQL

Mysql8之前的版本中加密规则是mysql_native_password, 而在Mysql8之后,加密规则是 caching_sha2_password

进入容器

[root@VM_0_7_centos ~]# docker exec -it mysql8 /bin/bash
root@bf91ae481949:/# pwd
/
root@bf91ae481949:/# whoami
root

连接MySQL并修改加密规则为mysql_native_password

root@bf91ae481949:/# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.21 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> 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> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | caching_sha2_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

mysql> alter user 'root'@'%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

mysql> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set (0.00 sec)

测试连接到MySQL服务正常

 ~  mycli -h 45.74.33.298 -P 3306 -u root                                                                                                                  ✔  16:50:34 
Password: 
Version: 1.8.1
Chat: https://gitter.im/dbcli/mycli
Mail: https://groups.google.com/forum/#!forum/mycli-users
Home: http://mycli.net
Thanks to the contributor - Lewis Peckover
mysql root@45.74.33.298:(none)> show databases;
+--------------------+
| Database           |
|--------------------|
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set
Time: 0.008s
mysql root@45.74.33.298:(none)> use mysql;
You are now connected to database "mysql" as user "root"
Time: 0.020s
mysql root@45.74.33.298:mysql> select host, user, plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
|-----------+------------------+-----------------------|
| %         | root             | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+
5 rows in set
Time: 0.006s

参考资料

以下内容为演示如何将自己制作的docker image推送到dockerhub

使用docker commit命令将当前修改完MySQL配置的容器生成新的镜像

docker commit命令详解

  • 作用:将运行着的容器映射成新的镜像
  • 格式: docker commit -a='作者' -m='修改内容--随意写' 容器名称或者ID 新生成镜像的名称
  • 例子:docker commit -a='passerby223' -m='修改MySQL8.0的加密规则为caching_sha2_password使任意host可以正常链接MySQL' mysql8 passerby223/mysql8
[root@VM_0_7_centos ~]# docker ps
CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
bf91ae481949        mysql               "docker-entrypoint.s…"   26 minutes ago      Up 26 minutes       0.0.0.0:3306->3306/tcp, 33060/tcp   mysql8
[root@VM_0_7_centos ~]# docker commit -a='passerby223' -m='修改MySQL8.0的加密规则为caching_sha2_password使任意host可以正常链接MySQL' mysql8 passerby223/mysql8
sha256:e6e4f1944659dbfd06a7895d9d5f452b2d60bb4e8e9141f2e58fc3d1b9e7fbd5
[root@VM_0_7_centos ~]# docker image ls
REPOSITORY           TAG                 IMAGE ID            CREATED             SIZE
passerby223/mysql8   latest              e6e4f1944659        7 seconds ago       544MB
mysql                latest              6e447ce4863d        6 hours ago         544MB
nginx                latest              0901fa9da894        3 days ago          132MB

将新生成的镜像推送至Dockerhub以备后需

如果没有dockerhub账号的需要先去dockerhub注册一个账号

终端环境下执行以下命令推送imagedockerhub

[root@VM_0_7_centos ~]# docker login
Login with your Docker ID to push and pull images from Docker Hub. If you don't have a Docker ID, head over to https://hub.docker.com to create one.
Username: passerby223
Password: 
WARNING! Your password will be stored unencrypted in /root/.docker/config.json.
Configure a credential helper to remove this warning. See
https://docs.docker.com/engine/reference/commandline/login/#credentials-store

Login Succeeded

[root@VM_0_7_centos ~]# docker push passerby223/mysql8
The push refers to repository [docker.io/passerby223/mysql8]
49825307dca9: Pushed 
cfb67f6f22a6: Mounted from library/mysql 
4c4ffeb533c7: Mounted from library/mysql 
f9a888e8f03c: Mounted from library/mysql 
a870f7b4d931: Mounted from library/mysql 
acffc629dd99: Mounted from library/mysql 
3a2464d8e0c0: Mounted from library/mysql 
44853bb67274: Mounted from library/mysql 
61cbb8ea6481: Mounted from library/mysql 
66c45123fd43: Mounted from library/mysql 
c3f46b20a0d3: Mounted from library/mysql 
365386a39e0e: Mounted from library/mysql 
13cb14c2acd3: Mounted from library/mysql 
latest: digest: sha256:205988d37811db7e7864e1f8eedc879f362d946fd0ffb6b371565acb58ce64fd size: 3035

去dockerhub查看image已推送成功

image.png

  • 以后需要用到MySQL8.0的时候直接使用pull命令从dockerhub拉取下来就直接可以创建容器拿来用了。
  • 使用该镜像运行的容器可使用账号:root 密码:123456连接到MySQL8.0数据库。