Docker 搭建 mysql8 遇到的问题

1 拉去mysql的镜像

[root@lianhe ~]# docker pull mysql
1

2 创建mysql和宿主机共享的文件

2.1 创建共享数据库目录

[root@lianhe ~]# mkdir -p /opt/mysql/data/
1

2.2 创建共享配置文件

[root@lianhe ~]# mkdir -p /opt/mysql/conf/

[root@lianhe ~]# touch /opt/mysql/conf/my.cnf
1
2
3

2.3 编写配置文件

• 写配置文件的时候是为了让mysql初始化的时候读取这个配置文件，如果配置文件中没有内容，在启动mysql的时候会报错。

[root@lianhe ~]#  cd /opt/mysql/
[root@lianhe mysql]# vi conf/my.cnf 
[mysqld]
user=root
datadir=/var/lib/mysql/ 
port=3306
# 这个可以不加，加上后，在容器启动后进入容器设置一个软连接，这样在登录mysql的时候不会报错，提示找不到socket文件
# ln  -s /tmp/mysql.sock  /var/run/mysqld/mysqld.sock
socket=/tmp/mysql.sock  
character-set-server=utf8mb4
collation-server=utf8mb4_bin
default-storage-engine=INNODB
max_allowed_packet=256M
innodb_log_file_size=2GB
transaction-isolation=READ-COMMITTED
binlog_format=row
log_bin_trust_function_creators = 1


[mysql]
default-character-set=utf8mb4
[client]
port=3306
default-character-set=utf8mb4
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3 启动mysql遇到的问题

3.1 启动mysql

由于本机已经安装过一个mysql版本，故从容器中映射出3307作为访问容器mysql的端口

[root@lianhe mysql]# docker run --name mysql-server -p 3307:3306  -p 33070:33060   -v /opt/mysql/data/:/var/lib/mysql/  -v /opt/mysql/conf/my.cnf:/etc/mysql/my.cnf  -e MYSQL_ROOT_PASSWORD=123456 -d mysql
cb67b9eeae246bbb085f36e6eeb381ad6b35c38cc97d3f97055984cacff42c56
1
2

3.2 查看进程

[root@lianhe mysql]# docker ps -a
CONTAINER ID   IMAGE                          COMMAND                  CREATED         STATUS                     PORTS                                                 NAMES
cb67b9eeae24   mysql                          "docker-entrypoint.s…"   7 seconds ago   Exited (1) 2 seconds ago   
1
2
3

• 从进程中可以看到mysql容器的进程没有正常启动，故需要查看日志。

3.3 查看日志

[root@lianhe mysql]# docker logs cb
2022-11-20 13:02:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2022-11-20 13:02:35+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-11-20 13:02:35+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2022-11-20 13:02:35+00:00 [Note] [Entrypoint]: Initializing database files
2022-11-20T13:02:35.672884Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.27) initializing of server in progress as process 42
2022-11-20T13:02:35.674261Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2022-11-20T13:02:35.674352Z 0 [Warning] [MY-010161] [Server] You need to use --log-bin to make --binlog-format work.
2022-11-20T13:02:35.678506Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-11-20T13:02:36.106687Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-11-20T13:02:37.029570Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-11-20T13:02:37.029589Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-11-20T13:02:37.114816Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2022-11-20 13:02:39+00:00 [Note] [Entrypoint]: Database files initialized
2022-11-20 13:02:39+00:00 [Note] [Entrypoint]: Starting temporary server
mysqld: Error on realpath() on '/var/lib/mysql-files' (Error 2 - No such file or directory)
2022-11-20T13:02:39.701705Z 0 [ERROR] [MY-010095] [Server] Failed to access directory for --secure-file-priv. Please make sure that directory exists and is accessible by MySQL Server. Supplied value : /var/lib/mysql-files
2022-11-20 13:02:39+00:00 [ERROR] [Entrypoint]: Unable to start server.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

• 从上面的log可以看到/var/lib/mysql-files这个文件夹没有创建成功

3.4 删除容器mysql共享到宿主机目录的data数据

• 容器在创建的时候已经生成了一部分数据，需要把这部分数据删除

[root@lianhe mysql]# rm -rf data/*

1
2

3.5 创建mysql-files文件夹

[root@lianhe mysql]# mkdir mysql-files
1

3.6 再次启动mysql容器

[root@lianhe mysql]# docker run --name mysql-server -p 3307:3306  -p 33070:33060   -v /opt/mysql/data/:/var/lib/mysql/  -v /opt/mysql/conf/my.cnf:/etc/mysql/my.cnf   -v /opt/mysql/mysql-files:/var/lib/mysql-files -e MYSQL_ROOT_PASSWORD=123456 -d mysql 
21683ed444fb3ce100230b7363b2562f1bc9beea0b3efeb35e6f2298dde00834

# 查看日志，发现这次没有报错信息
[root@lianhe mysql]# docker logs 21
2022-11-20 13:05:33+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2022-11-20 13:05:33+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2022-11-20 13:05:33+00:00 [Note] [Entrypoint]: Entrypoint script for MySQL Server 8.0.27-1debian10 started.
2022-11-20 13:05:33+00:00 [Note] [Entrypoint]: Initializing database files
2022-11-20T13:05:33.704649Z 0 [System] [MY-013169] [Server] /usr/sbin/mysqld (mysqld 8.0.27) initializing of server in progress as process 41
2022-11-20T13:05:33.705943Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2022-11-20T13:05:33.706024Z 0 [Warning] [MY-010161] [Server] You need to use --log-bin to make --binlog-format work.
2022-11-20T13:05:33.710111Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-11-20T13:05:34.132392Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-11-20T13:05:34.969236Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-11-20T13:05:34.969254Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-11-20T13:05:35.002468Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
2022-11-20 13:05:37+00:00 [Note] [Entrypoint]: Database files initialized
2022-11-20 13:05:37+00:00 [Note] [Entrypoint]: Starting temporary server
2022-11-20T13:05:37.736229Z 0 [Warning] [MY-010101] [Server] Insecure configuration for --secure-file-priv: Location is accessible to all OS users. Consider choosing a different directory.
2022-11-20T13:05:37.736293Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.27) starting as process 90
2022-11-20T13:05:37.740992Z 0 [Warning] [MY-010122] [Server] One can only use the --user switch if running as root
2022-11-20T13:05:37.747548Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-11-20T13:05:37.902129Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-11-20T13:05:38.076756Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-11-20T13:05:38.076776Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-11-20T13:05:38.077523Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2022-11-20T13:05:38.077559Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2022-11-20T13:05:38.091147Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Socket: /var/run/mysqld/mysqlx.sock
2022-11-20T13:05:38.091243Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.27'  socket: '/tmp/mysql.sock'  port: 0  MySQL Community Server - GPL.
2022-11-20 13:05:38+00:00 [Note] [Entrypoint]: Temporary server started.
Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/leap-seconds.list' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.

2022-11-20 13:05:39+00:00 [Note] [Entrypoint]: Stopping temporary server
2022-11-20T13:05:39.432034Z 10 [System] [MY-013172] [Server] Received SHUTDOWN from user root. Shutting down mysqld (Version: 8.0.27).

# 查看下进程
CONTAINER ID   IMAGE                          COMMAND                  CREATED          STATUS                  PORTS                                                 NAMES
21683ed444fb   mysql                          "docker-entrypoint.s…"   19 minutes ago   Up 19 minutes           0.0.0.0:3307->3306/tcp, 0.0.0.0:33070->33060/tcp      mysql-server
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

4 给mysql数据库授权

• 在mysql配置文件中已经配置了mysql的socket文件，访问mysql数据库的时候需要指定socker文件

[root@lianhe mysql]# docker exec -it mysql-server   mysql -uroot -p  -S /tmp/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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>  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.01 sec)

mysql> exit
Bye
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

5 放行端口

由于本次使用的虚拟机为阿里云ECS服务器，我们在访问这个容器mysql的时候需要放行3307端口才可以访问