【Linux学习笔记】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服务

Docker Hub上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