Docker中安装mysql

Linux利用docker安装mysql

一、安装mysql

1.1 下载mysql镜像

docker pull mysql:8.0.20

1.2 docker启动创建mysql实例并启动

docker run -d -p 3306:3306 --privileged=true -v /mydata//mysql/log:/var/log/mysql -v /mydata/mysql/data:/var/lib/mysql -v /mydata/mysql/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 --name mysql mysql:8.0.20

启动命令讲解

docker run:表示启动一个容器，就是相当于在linux中开辟一个新的Linux，然后这个mysql容器装在这个新的Linux中
虚拟机(主机）中3306端口与docker容器中的3306端口（mysql)相对应
–name :为我们当前启动的容器命名
-p 3306:3306:将容器的3306端口映射到主机的3306端口
-v/mydata/mysql/conf:/etc/mysql:将配置文件夹挂载到主机，目录挂载，将容器内部的文件 挂载到 虚拟机目录中
-v /mydata/mysg/log:/ar/log/mysql:将日志文件夹挂载到主机
-v /mydata/myq/data/var/lib/mysql/:将配置文件夹挂载到主机
-e MYSQL ROOT PASSWORD=root: 初始化root用户的密码
-d:后台启动 版本为8.0.20的mysql的镜像
--lower_case_table_names=1 数据库名忽略大小写在数据库初始的时候才能设置成功
--restart=always docker中的mysql开机自动启动或者docker update 容器命名 --restart=always

1.3 查看创建的docker容器实例

[root@localhost mysql]# docker ps -a
CONTAINER ID   IMAGE          COMMAND                   CREATED         STATUS         PORTS                                                  NAMES
ecad913970bf   mysql:8.0.20   "docker-entrypoint.s…"   2 minutes ago   Up 2 minutes   0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp   mysql
e3bc4fe5b4e1   redis          "docker-entrypoint.s…"   9 hours ago     Up 5 hours     0.0.0.0:6379->6379/tcp, :::6379->6379/tcp              redis
[root@localhost mysql]# pwd
/mydata/mysql
[root@localhost mysql]# ll
总用量 4
drwxr-xr-x. 2 root    root    6 8月  20 00:32 conf
drwxr-xr-x. 6 polkitd root 4096 8月  20 00:32 data
drwxr-xr-x. 2 root    root    6 8月  20 00:32 log

1.4 进入到docker容器实例中

[root@localhost mysql]# docker exec -it mysql /bin/bash
bash-4.2#

1.5 配置mysql用户权限

[root@localhost mysql]# docker exec -it mysql /bin/bash
root@ecad913970bf:/# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.20 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> CREATE USER 'lpb'@'localhost' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'lpb'@'localhost';
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> exit
Bye

测试：

root@ecad913970bf:/# mysql -ulpb -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.20 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> exit
Bye

外部连接测试，注意防火墙问题，我的虚拟机IP是192.168.137.10

报错原因
用图形化用户界面连接的MySQL8.0时，
报错：Authentication plugin ‘caching_sha2_password’ cannot be loaded

MySQL8.0之前的版本中加密规则是mysql_native_password,
而在MySQL8.0之后,加密规则是caching_sha2_password。
解决方法
1、升级驱动
2、MySQL用户登录密码加密规则还原成mysql_native_password
以方式2为例：

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

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

mysql> exit
Bye

再次测试：