正文
接上文 Centos7.9 Docker安装及相关服务实现 。 这里说一下 MySQL 服务的安装和搭建。
下载镜像
下载 MySQL8.0 镜像：
docker pull mysql:8.0.28
[root@localhost ~]# docker pull mysql:8.0.28
Trying to pull repository docker.io/library/mysql ...
8.0.28: Pulling from docker.io/library/mysql
15115158dd02: Pull complete
d733f6778b18: Pull complete
1cc7a6c74a04: Pull complete
c4364028a805: Pull complete
82887163f0f6: Pull complete
28abcb7f57e0: Pull complete
46d27a431703: Pull complete
8e745fe86aaf: Pull complete
ab75add93486: Pull complete
09e3960383f3: Pull complete
59f780965951: Pull complete
8ead2303095c: Pull complete
Digest: sha256:b17a66b49277a68066559416cf44a185cfee538d0e16b5624781019bc716c122
Status: Downloaded newer image for docker.io/mysql:8.0.28
[root@localhost ~]#
[root@localhost ~]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
docker.io/mysql 8.0.28 826efd84393b 3 days ago 521 MB
docker.io/php 7.4-fpm 186ff5171cde 10 days ago 460 MB
docker.io/gitlab/gitlab-ce latest 6f6c9f0dd251 3 weeks ago 2.39 GB
docker.io/jenkins/jenkins lts 306e80c103c6 4 weeks ago 441 MB
docker.io/registry latest 9c97225e83c8 4 weeks ago 24.2 MB
docker.io/nginx latest c316d5a335a5 6 weeks ago 142 MB
[root@localhost ~]#
[root@localhost ~]#
创建容器
为了备份数据，把与容器挂载的目录准备好：
mkdir -p /usr/local/docker/mysql/conf /usr/local/docker/mysql/logs /usr/local/docker/mysql/data /usr/local/docker/mysql/mysql-files
conf 目录里的配置文件将映射为mysql容器的配置文件
logs 目录将映射为mysql容器的日志目录
data 目录将映射为mysql容器配置的数据文件存放路径/var/lib/mysql
mysql-files mysql8版本中如果映射 /var/lib/mysql 数据目录，则这个目录也要映射
创建容器并运行：
docker run \
--name mysql8.0 \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /usr/local/docker/mysql/conf:/etc/mysql \
-v /usr/local/docker/mysql/logs:/var/log/mysql \
-v /usr/local/docker/mysql/data:/var/lib/mysql \
-v /usr/local/docker/mysql/mysql-files:/var/lib/mysql-files \
-v /etc/localtime:/etc/localtime:ro \
--privileged=true \
-d mysql:8.0.28
--name：容器名，此处命名为mysql8.0
-e：配置信息，此处配置mysql的root用户的登陆密码
-p：端口映射，此处映射 主机3306端口 到 容器的3306端口
-d：后台运行容器，保证在退出终端后容器继续运行
-v：主机和容器的目录映射关系，":"前为主机目录，之后为容器目录
-v /etc/localtime:/etc/localtime:ro 映射这个目录是为了校正容器内的时间跟宿主机时间一致
过程：
[root@localhost ~]# mkdir -p /usr/local/docker/mysql/conf /usr/local/docker/mysql/logs /usr/local/docker/mysql/data /usr/local/docker/mysql/mysql-files
[root@localhost ~]#
[root@localhost ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
9990da899f8e nginx "/docker-entrypoin..." 4 days ago Exited (0) 21 hours ago nginx-php7.4
02891538d8c9 php:7.4-fpm "docker-php-entryp..." 8 days ago Exited (0) 2 days ago php-fpm7.4
b3d1b7734937 jenkins/jenkins:lts "/sbin/tini -- /us..." 2 weeks ago Exited (143) 11 days ago jenkins
388479626cad gitlab/gitlab-ce:latest "/assets/wrapper" 2 weeks ago Exited (137) 2 weeks ago gitlab
e8314bf2b462 registry "/entrypoint.sh /e..." 2 weeks ago Exited (2) 11 days ago registry
[root@localhost ~]#
[root@localhost ~]# docker run \
> --name mysql8.0 \
> -p 3306:3306 \
> -e MYSQL_ROOT_PASSWORD=123456 \
> -v /usr/local/docker/mysql/conf:/etc/mysql \
> -v /usr/local/docker/mysql/logs:/var/log/mysql \
> -v /usr/local/docker/mysql/data:/var/lib/mysql \
> -v /usr/local/docker/mysql/mysql-files:/var/lib/mysql-files \
> -v /etc/localtime:/etc/localtime:ro \
> --privileged=true \
> -d mysql:8.0.28
d009927658c1742d6eff9a03f1473e4beb0eada7b74c655d14e1c33b981cfc14
[root@localhost ~]#
[root@localhost ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
d009927658c1 mysql:8.0.28 "docker-entrypoint..." 5 seconds ago Up 4 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql8.0
9990da899f8e nginx "/docker-entrypoin..." 4 days ago Exited (0) 21 hours ago nginx-php7.4
02891538d8c9 php:7.4-fpm "docker-php-entryp..." 8 days ago Exited (0) 2 days ago php-fpm7.4
b3d1b7734937 jenkins/jenkins:lts "/sbin/tini -- /us..." 2 weeks ago Exited (143) 11 days ago jenkins
388479626cad gitlab/gitlab-ce:latest "/assets/wrapper" 2 weeks ago Exited (137) 2 weeks ago gitlab
e8314bf2b462 registry "/entrypoint.sh /e..." 2 weeks ago Exited (2) 11 days ago registry
[root@localhost ~]#
[root@localhost ~]#
个人电脑
如果想备份到个人电脑上：
mkdir -p /media/sf_www/docker/mysql8.0/conf /media/sf_www/docker/mysql8.0/logs /media/sf_www/docker/mysql8.0/data /media/sf_www/docker/mysql8.0/mysql-files
创建容器并运行：
docker run \
--name mysql8.0 \
-p 3306:3306 \
-e MYSQL_ROOT_PASSWORD=123456 \
-v /media/sf_www/docker/mysql8.0/conf:/etc/mysql \
-v /media/sf_www/docker/mysql8.0/logs:/var/log/mysql \
-v /media/sf_www/docker/mysql8.0/data:/var/lib/mysql \
-v /media/sf_www/docker/mysql8.0/mysql-files:/var/lib/mysql-files \
-v /etc/localtime:/etc/localtime:ro \
--privileged=true \
-d mysql:8.0.28
需要修改权限。
新建用户
进入mysql容器：
docker exec -it mysql8.0 /bin/bash
首先用root登录mysql：
mysql -u root -p
输入上面设定的密码，进入。
创建用户，如：
CREATE USER 'test'@'%' IDENTIFIED BY '123456';
test：要创建用户的名字
%：表示这个新创建的用户允许从外网登陆；如果只允许从本地登陆，则填 localhost；指定ip地址登录，则填 IP地址
123456：新创建用户的登陆密码，如果没密码可以不写
[root@localhost ~]# docker exec -it mysql8.0 /bin/bash
root@d009927658c1:/#
root@d009927658c1:/# CREATE USER 'test'@'%' IDENTIFIED BY '123456';
bash: CREATE: command not found
root@d009927658c1:/#
root@d009927658c1:/#
root@d009927658c1:/# 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.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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>
mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.27 sec)
mysql>
查看新建的用户：
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>
mysql> select host,user,authentication_string from user;
+-----------+------------------+------------------------------------------------------------------------+
| host | user | authentication_string |
+-----------+------------------+------------------------------------------------------------------------+
| % | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| % | test | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | $A$005$g:Ig/3;TX73l>/ZKCfDQoOzvXWicgOGo7H4eimZPaNWhPsQ7Ka/qg/V0B |
+-----------+------------------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)
mysql>
别忘了退出，以免影响下面的操作：
exit
登录授权
用户登录管理授权，mysql8.x版本必须使用这种模式，否则Navicate无法正确连接：
ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
刷新权限：
flush privileges;
mysql> grant all privileges on *.* to test@'%' identified by '123456';
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 'identified by '123456'' at line 1
mysql>
mysql> ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.04 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql>
mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.03 sec)
mysql>
mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)
mysql>
新建数据库
新建数据库，test是数据库名：
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
mysql> CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
Query OK, 1 row affected (0.04 sec)
mysql>
Navicate 刷新，可以看到该数据库。
用户数据库赋权
语法：GRANT privileges ON databasename.tablename TO 'username'@'host';
privileges：表示要授予什么权力，例如可以有 select ， insert ，delete，update等，如果要授予全部权力，则填 ALL。
databasename.tablename：表示把用户的权限约束再哪个库的哪个表中，如果用户的权限没有限制，则填 *.*，*是一个通配符，表示全部。
'username'@'host'：username表示授权给哪个用户，host表示登录环境。
如：
GRANT ALL ON test.* TO 'test'@'%';
注意：
用以上命令授权的用户不能给其他用户授权，如果想这个用户能够给其他用户授权，就要在后面加上 WITH GRANT OPTION
如：
GRANT ALL ON test.* TO 'test'@'%' WITH GRANT OPTION;
mysql> GRANT ALL ON test.* TO 'test'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.04 sec)
mysql>
问题
ONLY_FULL_GROUP_BY
新建表时报错：
[Err] 1055 - Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column ‘information_schema.
PROFILING.SEQ’ which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
在执行 SELECT id, name FROM table_name GROUP BY id 时也会报上面的错。
MySql从5.7版本开始默认开启only_full_group_by规则，规则核心原则如下，没有遵循原则的sql会被认为是不合法的sql。
不允许select后面的非聚合列不出现在group by中，如上面不允许name的存在。
下面这篇博文说了三种解决方案： https://www.pudn.com/news/623c2009afe4bf23f7fa2249.html
这里使用方案一（每次重启容器时都要重新执行一次）：
set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
set @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
进入mysql容器后，执行过程如下：
root@d009927658c1:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 75
Server version: 8.0.28 MySQL Community Server - GPL
Copyright (c) 2000, 2022, 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>
mysql>
mysql> SELECT @@GLOBAL.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> SELECT @@SESSION.sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@SESSION.sql_mode |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> set @@GLOBAL.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> set @@SESSION.sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
Query OK, 0 rows affected (0.00 sec)
mysql>
参考资料
使用Docker搭建MySQL服务 https://www.cnblogs.com/sablier/p/11605606.html
mysqld: Error on realpath() on ‘/var/lib/mysql-files’ (Error 2 - No such file or directory) https://blog.csdn.net/weixin_30338497/article/details/96153193
Docker安装MySQL8.0 https://www.cnblogs.com/ybyn/p/13698058.html
mysql新建用户，修改权限 https://www.cnblogs.com/wuxunyan/p/9095016.html