Docker安装Mysql服务并数据持久化
一、Docker安装
不在这里阐述了。后期贴链接。
二、Docker上安装Mysql服务
2.1、下载Mysql镜像
## 默认下载最新版本的mysql
sudo docker pull mysql
## 下载mysql5.7
sudo docker pull mysql:5.7
## 下载MySQL8.x版本
docker pull mysql:8.0.29
2.2、在宿主机创建数据持久化的文件夹
## 创建mysql5.7的数据持久化配置文件夹
sudo mkdir -p /usr/local/mysqlData/mysql5.7/conf
## 创建mysql5.7的数据持久化数据文件夹
sudo mkdir -p /usr/local/mysqlData/mysql5.7/data
## 创建mysql5.7的日志文件夹
sudo mkdir -p /usr/local/mysqlData/mysql5.7/logs
2.3、启动MySQL5.7服务
2.3.1、首先进入到MySQL5.7的数据文件夹下
2.3.2、创建外部挂载配置文件mysql.cnf
sudo vim mysql.cnf
2.3.2.1、MySQL8.0的mysql.cnf
mysql.cnf文件内容
# Copyright (c) 2017, Oracle and/or its affiliates. All rights reserved.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; version 2 of the License.
#
# 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 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
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Custom config should go here
password_require_current=ON
# 设置了修改用户密码的时候不能修改近三次修改的密码
password_history=3
!includedir /etc/mysql/conf.d/
2.3.2.2、MySQL5.7的mysql.cnf(5.7.29)
# Copyright (c) 2014, 2016, Oracle and/or its affiliates. All rights reserved.
#
# 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
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#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
2.3.3、使用docker run语句启动mysql服务
sudo docker run -itd -p 3307:3306 --name mysql5.7.29 --privileged=true -v $PWD/conf:/etc/mysql/conf.d -v $PWD/data:/var/lib/mysql -v $PWD/logs:/logs -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai mysql:5.7.29
## 启动参数说明
## 提升root权限
--privileged=true
## 将容器的 3306 端口映射到主机的 3307 端口。
-p 33067:3306
## 将宿主机上的当前目录下的conf目录挂载到容器的/etc/mysql/conf.d目录
-v $PWD/conf:/etc/mysql/conf.d
## 将宿主机上的当前目录下的data目录挂载到容器的 /var/lib/mysql
-v $PWD/data:/var/lib/mysql
## 将宿主机上的当前目录下的 logs 目录挂载到容器的 /logs。
-v $PWD/logs:/logs
## 设置时区
-e TZ=Asia/Shanghai
使用命令查看mysql5.7是否已启动,
sudo docker ps -a
三、测试远程连接MySQL
使用Navcat测试连接Mysql服务的时候出现了连接失败。
3.1、修改MySQL配置
3.1.1、进入docker容器,登录MySQL
## 1.进入docker的mysql容器
sudo docker exec -it mysql5.7 bash
## 2.登录MySQL服务
mysql -uroot -p
3.1.2、修改root权限
-- 使用mysql_native_password密码插件设置root用户的密码;
alter user 'root'@'%' IDENTIFIED with mysql_native_password by '123456';
-- 刷新配置
flush privileges;
3.1.3、重试连接
四、测试MySQL数据持久化(使用8.0测试)
4.1、查看之前配置的数据持久化本地文件夹
进入我们之前配置的data挂载点文件夹,如下图所示,我的挂载点文件夹地址如下图红色框中所示;
4.2、创建一个测试test数据库,并添加一张user表
创建数据库和user表不在这里阐述
4.3、删除MySQL8.0的容器
4.3.1、停止MySQL8.0容器
## 查看当前容器的状态
sudo docker ps -a
## 停止name为mymysql的容器
sudo docker stop mymysql
## 查看当前容器的状态
sudo docker ps -a
4.3.2、删除MySQL8.0容器
## 删除mymysql容器
sudo docker rm mymysql
## 查看当前的容器
sudo docker ps
4.3.3、使用2.3小结的命令再次启动容器
PS:注意点,当前所使用的方法需要先将目录切换到mysql8.0.17文件夹下,应为用到了$PWD参数,如果不用这个参数,可以在启动变量中写相对路径。
4.3.4、测试数据库数据是否存在
重新开启链接,发现test库还在,打开test数据库可以看到user表也在。至此数据库持久化完成。
五、测试MySQL外部配置(使用8.0测试)
之前在配置/usr/local/mysqlData/mysql8.0.17/conf/mysql.cnf文件中添加了修改用户密码的时候不能修改近三次的密码;
如下图在容器创建的时候我们设置运行参数的时候设置了root用户的密码为:123456;
然后我们进行密码修改,修改为:root@123456;
再进行密码修改,修改为:123456;
则出现不可以修改为近三次的密码的错误;
5.1、Mysql8.0下关于group严格模式的问题
MYSQL8以上已经取消了NO_AUTO_CREATE_USER,sql_mode中不能包含这个。
sql_mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUT