Docker 部署MySQL

# 在本地创建MySQL映射目录
cd /local/mysql/data/path
# 执行部署命令
docker run -p 3306:3306 --restart always \
 --name mysql-server --privileged=true \
 -v $PWD/conf:/etc/mysql/conf.d \
 -v $PWD/backup:/backup \
 -v $PWD/logs:/logs \
 -v $PWD/data:/var/lib/mysql \
 -e MYSQL_ROOT_PASSWORD=MyPassWord \
 -d mysql:5.7

# 查看部署情况
docker ps 

# 相关参数说明
-p : 端口映射
--restart: 开机启动
--name ： docker容器名称
--privileged ： 宿主机root权限，不开启此项则无法启动mysql进程
-v  ： 本地磁盘目录映射
-e  ： 环境变量设置，镜像内定义的环境变量值
-d  ： 后台进程形式启动


# 创建conf/my.cnf文件

CREATE USER 'winkerdb'@'%' IDENTIFIED By 'MyPassWD';
GRANT USAGE ON *.* TO 'winkerdb'@'%' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
GRANT ALL PRIVILEGES ON `winkerdb\_%`.* TO 'winkerdb'@'%';
GRANT PROCESS ON *.* to winkerdb;
flush privileges;

# 上命令中winkerdb改成自己的用户名
# 如果限制指定ip才可以访问则将'winkerdb'@'%'中的%改成ip地址，如 'winkerdb'@'192.168.1.110'
# winkerdb改成自己的用户名
# MyPassWD 改成自己的密码

# 因为我的MySQL和PMA在同一台服务器内用docker部署，所以采用docker link关联链接容器进行部署
docker run --name myadmin-mysql-server -d --link mysql-server:db -p 8086:80 phpmyadmin

# 执行完整后浏览器访问ip:8086 即可

# Copyright (c) 2014, 2021, Oracle and/or its affiliates.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License, version 2.0,
# as published by the Free Software Foundation.
#
# This program is also distributed with certain software (including
# but not limited to OpenSSL) that is licensed under separate terms,
# as designated in a particular file or component or in included license
# documentation.  The authors of MySQL hereby grant you an additional
# permission to link the program and your derivative works with the
# separately licensed software that they have included with MySQL.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License, version 2.0, for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA  02110-1301 USA

#
# The MySQL  Server configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

[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
# 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
# 忽略dns验证，否则远程访问慢
skip-name-resolve
# 忽略大小写
lower_case_table_names=1

#1、命令行模式执行
# 查看当前sql_mode
select @@sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
# 查看全局sql_mode
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_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)
# 上面结果在严格模式下NO_ZERO_IN_DATE，NO_ZERO_DATE开启后值为0的日期数据无法插入（旧数据迁移时经常出现这种问题）

# 2、在严格模式下 NO_ZERO_DATE和NO_ZERO_IN_DATE都不启用
# 修改全局
set @@global.sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
# 修改当前
set @@sql_mode = 'STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';