Docker下安装MySQL服务器简单步骤以及常用操作备忘

如果只是为了测试或者开发，需要一个MySQL环境，我觉得使用Docker安装是比较方便的，一个是快速，第二个是干净。

备忘记录下我Docker安装MySQL的步骤。

首先，先决条件是安装docker-ce，详见：https://blog.terrynow.com/2021/12/15/centos-install-latest-docker-ce-simple-steps/

新增MySQL的data目录

我放在宿主机上，防止容器删除后，重新部署数据丢失

# data目录自己定义
sudo mkdir -p /storage/docker/mysql/mysql-data

准备my.cnf配置文件

这个配置，我也是放在外面的宿主机上了，方便修改(我的路径是：/storage/docker/mysql/my.cnf)

# mkdir -p /storage/docker/mysql
# /storage/docker/mysql/my.cnf

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password
skip-host-cache
skip-name-resolve
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
secure-file-priv=/var/lib/mysql-files
user=mysql

max_connections=1000

pid-file=/var/run/mysqld/mysqld.pid

[client]
user=root

Docker启动MySQL

docker pull mysql/mysql-server

# 先新建下面的/storage/docker/mysql/my.cnf 在启动docker run
# MYSQL_ROOT_PASSWORD=my-secret-pw这个好像没用？
# docker logs mysql 看看日志里是否有初始密码或者空密码

# 启动容器
docker run \
--restart=always \
--detach \
--name mysql \
-e MYSQL_ROOT_PASSWORD=my-secret-pw \
-p 3306:3306 \
-v /storage/docker/mysql/my.cnf:/etc/my.cnf \
-v /storage/docker/mysql/mysql-data:/var/lib/mysql \
mysql/mysql-server

这样，简单的步骤，就启动好了MySQL

进入docker容器，修改密码等操作

#进入容器操作
docker exec -it mysql bash
#如果密码不生效，就空白密码试试
#测试的时候删除了docker的contain记得/storage/docker/mysql-data也清空数据
#进入后再建一个外部用的密码类似：
CREATE USER 'root'@'%' IDENTIFIED BY 'my-secret-pw';
GRANT ALL ON *.* TO 'root'@'%';
flush privileges;

#本机密码修改：
alter user root@'localhost' identified by 'my-secret-pw';

其他有用的操作备忘

# 备份数据
docker exec some-mysql sh -c 'exec mysqldump --all-databases -uroot -p"$MYSQL_ROOT_PASSWORD"' > /some/path/on/your/host/all-databases.sql

#or 
docker exec mysql sh -c 'exec mysqldump health-test -uroot -pmy-secret-pw' > /opt/health-test.sql

# 恢复数据
docker exec -i some-mysql sh -c 'exec mysql -uroot -p"$MYSQL_ROOT_PASSWORD"' < /some/path/on/your/host/all-databases.sql
# or
docker exec -i mysql_container mysql -uroot -psecret mysql < /path/to/db_dmp.sql

另外如果Docker下mySQL有时区问题，请查看：https://blog.terrynow.com/2022/06/07/docker-mysql-timezone-not-cst-resolved/