Linux系统:CentOS Linux release 7.4.1708 (Core)
Docker版本: 17.03.0-ce, build 3a232c8
mysql 版本:8.0.27
1、查询并拉取镜像
docker search mysql
docker pull mysql
查看获取的镜像:
[root@wngpenghao mysql]# docker images
REPOSITORY TAG IMAGE ID CREATED SIZE
mysql latest c8ee894bd2bd 5 weeks ago 456 MB
my.cnf
[mysqld]
basedir = /var/lib/mysql/
datadir = /var/lib/mysql/data
log-bin = mysql-bin
server-id = 1
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
2、运行mysql
docker run -p 3306:3306 --privileged=true --name=mysql -v /opt/mysql/data:/var/lib/mysql/data -v /opt/mysql/conf:/etc/mysql/conf.d -e MYSQL_ROOT_PASSWORD=123456 -id mysql
注意:
1、“-v /opt/mysql/conf:/etc/mysql/conf.d” 在/opt/mysql/conf 目录下创建my.cnf文件。
[mysqld]
#跳过数据库权限验证
skip-grant-tables
3、运行中mysql
[root@wngpenghao opt]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
2bcf577091a2 mysql "docker-entrypoint..." About a minute ago Up 25 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql
4、进入mysql并修改权限、密码。
[root@wngpenghao opt]# docker exec -it 2bcf577091a2 /bin/bash
root@2bcf577091a2:/# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.18 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.30 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> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | root | caching_sha2_password | |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 rows in set (0.00 sec)
mysql> update user set host='%' where user='root' and host='localhost';
Query OK, 1 row affected (0.43 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> select host,user,plugin,authentication_string from mysql.user;
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
| % | root | caching_sha2_password | |
| localhost | mysql.infoschema | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.session | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| localhost | mysql.sys | caching_sha2_password | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
+-----------+------------------+-----------------------+------------------------------------------------------------------------+
4 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.01 sec)
mysql> quit
Bye
root@2bcf577091a2:/# exit
5、删除skip-grant-tables属性,进行权限校验,修改my.cnf。
[mysqld]
#跳过数据库权限验证
#skip-grant-tables
6、重启,远程客户端测试,OK了。
7、可能遇到问题。
7.1 客户端登录提示1251,如下图:
原因在docker 安装mysql出现navicat登录失败_求是-CSDN博客_docker安装mysql后无法登录博客中找到:
“mysql8 之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 所以需要把mysql用户登录密码加密规则还原成mysql_native_password. ”
所以需要进入mysql执行:
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)
7.2、MySQL系列:限制IP访问,通过授权的方式实现