docker 安装MySQL并配置
1. 下载
[root@host-10-23-110-128 redis]# docker pull mysql:latest
latest: Pulling from library/mysql
72a69066d2fe: Pull complete
93619dbc5b36: Pull complete
99da31dd6142: Pull complete
626033c43d70: Pull complete
37d5d7efb64e: Pull complete
ac563158d721: Pull complete
d2ba16033dad: Pull complete
688ba7d5c01a: Pull complete
00e060b6d11d: Pull complete
1c04857f594f: Pull complete
4d7cfa90e6ea: Pull complete
e0431212d27d: Pull complete
Digest: sha256:e9027fe4d91c0153429607251656806cc784e914937271037f7738bd5b8e7709
Status: Downloaded newer image for mysql:latest
docker.io/library/mysql:latest
[root@host-10-23-110-128 redis]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
redis latest 7614ae9453d1 11 months ago 113MB
mysql latest 3218b38490ce 11 months ago 516MB
2. 配置
[root@host-10-23-110-128 mysql]# ls
data log my.cnf
[root@host-10-23-110-128 mysql]# nano my.cnf
[root@host-10-23-110-128 mysql]#
[mysql]
#设置mysql客户端默认字符集
default-character-set=UTF8MB4
[mysqld]
#设置3306端口
port=3306
#允许最大连接数
max_connections=200
#允许连接失败的次数
max_connect_errors=10
#默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=UTF8MB4
#开启查询缓存
explicit_defaults_for_timestamp=true
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#等待超时时间秒
wait_timeout=60
#交互式连接超时时间秒
interactive-timeout=600
[root@host-10-23-110-128 mysql]# docker run -p 3306:3306 -v /home/docker/data/mysql/data:/var/lib/mysql -v /home/docker/data/mysql/my.cnf:/etc/mysql/conf.d/my.cnf -v /home/docker/data/mysql/log:/var/log/mysql -e MYSQL_ROOT_PASSWORD=hett --name mysql -d mysql
129f0779069d2e96f068a7615ecd0e9adf838dfc9d30ea6c7c75dbb90a0dd32b
[root@host-10-23-110-128 mysql]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
129f0779069d mysql "docker-entrypoint.s…" 6 seconds ago Up 5 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
a7aafa90cd99 redis "docker-entrypoint.s…" About an hour ago Up About an hour 0.0.0.0:6379->6379/tcp redis
[root@host-10-23-110-128 mysql]# docker exec -it mysql bash
root@129f0779069d:/#
root@45e541a4313d:/# 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.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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
mysql> create database agv;
Query OK, 1 row affected (0.01 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> create user 'hett'@'%' identified by 'hett';
Query OK, 0 rows affected (0.02 sec)
mysql> grant all privileges on *.* to 'hett'@'%' with grant option;
Query OK, 0 rows affected (0.02 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> use mysql;
Database changed
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | hett | $A$005$t-wy%_~dc9K<enAthLGwOX1U4RSIxSs/ynZKR6MQ4ns4G8YdXRJ1U6GJ.vB | caching_sha2_password |
| % | root | $A$005$w{Wkd4rYNKAvdCFSHKfVkYpV.W1CmJFDa.RDdaLqPk.QjVBfNuPH8.va2 | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root | $A$005$|>>9N,~%p(|Y}FLho
czunnuldmhLeVRxtPcSF2IaktKsRAcwVdsJAUH3nM6aS0 | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
6 rows in set (0.00 sec)
配置密码：
update user set authentication_string='' where user='root';
ALTER user 'root'@'localhost' IDENTIFIED BY 'hett';
3. 如果出现错误
Navicat连接Mysql报错：Client does not support authentication protocol requested by server；
需要mysql插入一下数据：此针对是mysql8出现的问题
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 'hett'@'%' identified with mysql_native_password by 'hett';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
在使用过程中mysql8还会出现一下问题：
ORDER BY clause is not in GROUP BY clause and contains nonaggregated column
需要在配置文件中新增以下内容：针对mysql 5.7以上的版本
#设置mysql客户端默认字符集
default-character-set=UTF8MB4
[mysqld]
#设置3306端口
port=3306
#允许最大连接数
max_connections=200
#允许连接失败的次数
max_connect_errors=10
#默认使用“mysql_native_password”插件认证
default_authentication_plugin=mysql_native_password
#服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=UTF8MB4
#开启查询缓存
explicit_defaults_for_timestamp=true
#创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
#等待超时时间秒
wait_timeout=60
#交互式连接超时时间秒
interactive-timeout=600
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
如果还是出现问题：
需要手工更改：
select version(), @@sql_mode;
SET sql_mode="STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION";
select version(), @@sql_mode;
mysql8 navicati无法登录的密钥问题
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
更新user为root，host为% 的密码为123456
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
