【Docker】之安装MySQL5并挂载数据

从仓库下拉镜像

sudo docker pull mysql:5.7.37

新建挂载目录

sudo mkdir -p /root/dockerdata/mysql5/{data,logs,conf}
chmod -R 777  /root/dockerdata/mysql5

新建my.cnf

vi /root/dockerdata/mysql5/conf/my.cnf

[client]
default-character-set=utf8mb4
 
[mysql]
default-character-set=utf8mb4

[mysqld] 
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
#log-error      = /var/log/mysql/error.log

general_log = 1
general_log_file = /var/log/mysql/access.log
log-error = /var/log/mysql/error.log

# By default we only accept connections from localhost
#bind-address   = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
lower_case_table_names=1
max_allowed_packet=16M


init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
skip-character-set-client-handshake

# 允许最大连接数
max_connections = 1000

# Custom config should go here
!includedir /etc/mysql/conf.d/

运行镜像

docker run --restart=always --privileged=true -p 3308:3306 --name mysql5 \
--net mynet --ip 172.172.0.6  \
-e TZ="Asia/Shanghai" \
-v /etc/localtime:/etc/localtime:ro \
-v /root/dockerdata/mysql5/conf:/etc/mysql/conf.d \
-v /root/dockerdata/mysql5/data:/var/lib/mysql \
-v /root/dockerdata/mysql5/logs:/var/log/mysql \
-e MYSQL_ROOT_PASSWORD=12345678 \
-d mysql:5.7.37

相关参数描述：
-p 3308:3306：将容器的 3306 端口映射到主机的 3308 端口。
-v $PWD/conf:/etc/mysql/conf.d：将主机当前目录下的 conf/my.cnf 挂载到容器的 /etc/mysql/my.cnf。
-v $PWD/logs:/logs：将主机当前目录下的 logs 目录挂载到容器的 /logs。
-v $PWD/data:/var/lib/mysql ：将主机当前目录下的data目录挂载到容器的 /var/lib/mysql 。
-e MYSQL_ROOT_PASSWORD=your_root_passwd：初始化 root 用户的密码。
-v /etc/localtime:/etc/localtime:ro 设置容器的时间与宿主机同步
-d :表示在后台运行 mysql:5.7.37 镜像名+版本号

登录容器

sudo docker exec -it mysql5 bash mysql -uroot -pyour_root_passwd

开启远程连接服务

mysql> grant all privileges on *.* to root@'%' identified by 'your_root_passwd' with grant option; 
mysql> flush privilesges;

ALTER USER 'root'@'localhost' IDENTIFIED BY '12345678';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '12345678';

grant all privileges on *.* to 'root'@'%' with grant option;

flush privileges;

修改密码

update mysql.user set authentication_string=password('新密码') where user='root' ;
flush privileges;

PS

# 用户信息
select host,user,plugin,authentication_string from mysql.user;
# 时区
show variables like '%time_zone%';
# 变量
SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';
# 更改数据库编码
ALTER DATABASE caitu99 CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
# 更改表编码
ALTER TABLE TABLE_NAME CONVERT TO CHARACTER SET utf8mb4 COLLATEutf8mb4_general_ci;