docker Mysql:5.7的持久化存储及远程连接
文章目录1. 前置条件:2. 拉取mysql:5.7的docker镜像3. 预先配置4. 建立配置文件5. 运行命令6. 检查1. 前置条件:centos 环境下docker 已经安装完成docker 服务已经启动2. 拉取mysql:5.7的docker镜像[root@laoliu mysql]# docker pull mysql:5.7# 使用这个命令拉取mysql镜像...
·
文章目录
1. 前置条件:
- 华为云 centos7.5 环境下
- docker 已经安装完成
- docker 服务已经启动
2. 拉取mysql:5.7的docker镜像
[root@laoliu mysql5.7]# docker pull mysql:5.7 # 使用这个命令拉取mysql镜像
Trying to pull repository docker.io/library/mysql ...
5.7: Pulling from docker.io/library/mysql
fc7181108d40: Pull complete
787a24c80112: Pull complete
a08cb039d3cd: Pull complete
4f7d35eb5394: Pull complete
5aa21f895d95: Pull complete
a742e211b7a2: Pull complete
0163805ad937: Pull complete
62d0ebcbfc71: Pull complete
559856d01c93: Pull complete
c849d5f46e83: Pull complete
f114c210789a: Pull complete
Digest: sha256:c3594c6528b31c6222ba426d836600abd45f554d078ef661d3c882604c70ad0a
Status: Downloaded newer image for docker.io/mysql:5.7
[root@laoliu mysql5.7]# docker images # 查看镜像
REPOSITORY TAG IMAGE ID CREATED SIZE
docker.io/mysql 5.7 a1aa4f76fab9 11 days ago 373 MB
[root@laoliu mysql5.7]#
3. 预先配置
- 配置好本地的文件目录
[root@laoliu mysql5.7]# mkdir -p /home/project/mysql5.7/{data,conf}
[root@laoliu mysql5.7]# ll
total 0
drwxr-xr-x. 2 root root 6 Mar 10 10:01 conf
drwxr-xr-x. 2 root root 6 Mar 10 10:01 data
[root@laoliu mysql5.7]# pwd
/home/project/mysql5.7
4. 建立配置文件
- 坑1: 配置文件最好是以
.cnf作为后缀, 试过以.conf后缀, 可以启动mysql, 但配置文件不起作用. - 坑2: 修改完配置文件, 记得
docker restart containername一下
[root@laoliu conf]# pwd
/home/project/mysql5.7/conf
[root@laoliu conf]# cat mysql5.7.cnf # 注意这里, 我是已配置好了`mysql5.7.cnf`, 使用cat命令只是为了查看这个文件的内容.
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
init_connect='SET collation_connection = utf8mb4_unicode_ci'
init_connect='SET NAMES utf8mb4'
character-set-server=utf8
collation-server=utf8_unicode_ci
skip-character-set-client-handshake
# default: sql_mode= STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# modeified:
sql_mode= STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
max_allowed_packet=10M
default-time_zone='+8:00'
[root@laoliu conf]#
5. 运行命令
[root@laoliu conf]# docker run -d -p 3306:3306 --restart=always --privileged=true -v /home/project/mysql5.7/conf:/etc/mysql/conf.d -v /home/project/mysql5.7/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name mysql5.7 mysql:5.7
e2c88740425a9b3eecfb3945e632255776e0fe636b008715f1444c8b8e7fd613
# 查看 容器运行状态
[root@laoliu conf]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
e2c88740425a mysql:5.7 "docker-entrypoint..." 8 seconds ago Up 7 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql5.7
# 查看 容器运行状态
[root@laoliu conf]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
e2c88740425a mysql:5.7 "docker-entrypoint..." 19 seconds ago Up 18 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql5.7
[root@laoliu conf]#
docker run是启动容器的命令；-
--restart=always: 配置此项后, 当 Docker 重启时，容器总是可以自动启动, 其它参数可以参考:[no,on-failure,always]- 1.
no为默认值，表示容器退出时，docker不自动重启容器 - 2.
on-failure表示，若容器的退出状态非0，则docker自动重启容器,还可以指定重启次数，若超过指定次数未能启动容器则放弃： - 3.
always表示，只要容器退出，则docker将自动重启容器
- 1.
-
--privileged=true: 使用该参数，container内的root拥有真正的root权限, 否则，container内的root只是外部的一个普通用户权限 -
--name：指定了容器的名称，方便之后进入容器的命令行 -
-d：d指的是在后台运行。 也可以使用-idt，i是交互式操作，t是一个终端， -
-p：指在本地生成一个随机端口，用来映射mysql的3306端口 -
-e：设置环境变量 -
MYSQL_ROOT_PASSWORD=emc123123：指定了mysql的root密码 -
mysql:5.7：指运行mysql镜像及tag -
-v:表示挂载, 持久化存储的关键所在
-
6. 使用navicat连接mysql进行检查
在电脑本地使用navicat连接时报错:
Host '127.0.0.1' is not allowed to connect to this MySQL server
其实就是我们的MySQL不允许远程登录，所以远程登录失败了，解决方法如下：
[root@laoliu conf]# docker exec -it mysql5.7 /bin/bash #进入mysql5.7 容器
root@7e8060b03125:/# mysql -u root -p # 登录mysql服务器
# 在这里输入mysql密码:123456
mysql> show databases; # 查看数据库
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.09 sec)
mysql> select host, user,plugin,authentication_string from mysql.user;
+-----------+---------------+-----------------------+-------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+---------------+-----------------------+-------------------------------------------+
| localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | mysql_native_password | *50208BAA1E29F372145D89CC187279F75FBEC2CC |
+-----------+---------------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
# 备注：host为 % 表示不限制ip
# localhost表示本机使用
# plugin 非mysql_native_password则需要修改密码
也有可能需要
mysql> update mysql.user set host='%' where user='root'; # 更新 root的 远程登录为所有
Query OK, 1 row affected (0.10 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select host, user,plugin,authentication_string from mysql.user;
+-----------+---------------+-----------------------+-------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+---------------+-----------------------+-------------------------------------------+
| localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | mysql_native_password | *50208BAA1E29F372145D89CC187279F75FBEC2CC |
+-----------+---------------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
# 备注：host为 % 表示不限制ip localhost表示本机使用 plugin非mysql_native_password 则需要修改密码
mysql> flush privileges; # 刷新权限
Query OK, 0 rows affected (0.03 sec)
但是还报错了,报错内容不一样了:
1045 Access denied for user 'root'@'192.168.31.43' (usingpassword:YES)
解决方法如下:
mysql> grant all privileges on *.* to root@'%' identified by '123456'; # 给用户授权
Query OK, 0 rows affected, 1 warning (0.11 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)
mysql>
7. 顺便说下mysql给用户授权
mysql> grant 权限1,权限2, … 权限n on 数据库名称.表名称 to 用户名@用户地址 identified by ‘连接口令’;
权限1，权限2，… 权限n 代表 select、insert、update、delete、create、drop、index、alter、grant、references、reload、shutdown、process、file 等14个权限。
当权限1，权限2，… 权限n 被 all privileges 或者 all 代替时，表示赋予用户全部权限。
当 数据库名称.表名称 被 . 代替时，表示赋予用户操作服务器上所有数据库所有表的权限。
用户地址可以是localhost，也可以是IP地址、机器名和域名。也可以用 ‘%’ 表示从任何地址连接。
‘连接口令’ 不能为空，否则创建失败。
举几个例子：
mysql> grant select,insert,update,delete,create,drop on vtdc.employee to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对数据库vtdc的employee表进行select,insert,update,delete,create,drop等操作的权限，并设定口令为123。
mysql> grant all privileges on vtdc.* to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对数据库vtdc所有表进行所有操作的权限，并设定口令为123。
mysql> grant all privileges on . to joe@10.163.225.87 identified by ‘123′;
给来自10.163.225.87的用户joe分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
mysql> grant all privileges on . to joe@localhost identified by ‘123′;
给本机用户joe分配可对所有数据库的所有表进行所有操作的权限，并设定口令为123。
8. 连接云端mysql
- 在华为云上使用docker安装好mysql后, 电脑本地连接不上,解决过程如下:
- 前置条件: 1-7都已经调试完成
# 前置条件检查
# 检查docker mysql 有没有启动
[root@ecs-s6-medium-2-linux-20191113090041 ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
00d7fb16f4d1 mysql:5.7 "docker-entrypoint..." 23 hours ago Up 23 hours 0.0.0.0:3306->3306/tcp, 33060/tcp mysql5.7
# 果然已经停了, 那就重启它
[root@ecs-s6-medium-2-linux-20191113090041 ~]# docker restart 00d7fb16f4d1
00d7fb16f4d1
# 现在mysql5.7服务已经启动
[root@ecs-s6-medium-2-linux-20191113090041 ~]# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
00d7fb16f4d1 mysql:5.7 "docker-entrypoint..." 23 hours ago Up 7 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql5.7
[root@ecs-s6-medium-2-linux-20191113090041 ~]# docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
00d7fb16f4d1 mysql:5.7 "docker-entrypoint..." 23 hours ago Up 14 seconds 0.0.0.0:3306->3306/tcp, 33060/tcp mysql5.7
# 进入mysql5.7 容器中检查细节
[root@ecs-s6-medium-2-linux-20191113090041 ~]# docker exec -it mysql5.7 /bin/bash
# 已经进入容器, 使用root账户 登陆mysql
root@00d7fb16f4d1:/# mysql -u root -p
Enter password: ##### 在这里输入密码 ########
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
# 查看数据库用户及其访问权限控制
mysql> select host, user,plugin,authentication_string from mysql.user;
+-----------+---------------+-----------------------+-------------------------------------------+
| host | user | plugin | authentication_string |
+-----------+---------------+-----------------------+-------------------------------------------+
| localhost | mysql.session | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | mysql_native_password | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| % | root | mysql_native_password | *50208BAA1E29F372145D89CC187279F75FBEC2CC |
+-----------+---------------+-----------------------+-------------------------------------------+
3 rows in set (0.00 sec)
mysql> exit;
Bye
root@00d7fb16f4d1:/# exit
exit
# 至此,数据库里的配置及权限都已经验证完毕, 查看mysql服务有没有启动
[root@ecs-s6-medium-2-linux-20191113090041 ~]# netstat -an | grep 3306
tcp6 0 0 :::3306 :::* LISTEN
# 查看防火墙是否状态 返回running 或 not running
[root@ecs-s6-medium-2-linux-20191113090041 ~]# firewall-cmd --state
not running
# 关闭防火墙
[root@ecs-s6-medium-2-linux-20191113090041 ~]# systemctl stop firewalld.service
# 禁用防火墙
[root@ecs-s6-medium-2-linux-20191113090041 ~]# systemctl disable firewalld.service
# 启动防火墙
[root@ecs-s6-medium-2-linux-20191113090041 ~]# systemctl start firewalld.service
[root@ecs-s6-medium-2-linux-20191113090041 ~]# firewall-cmd --state
running
# 打开3306端口
[root@ecs-s6-medium-2-linux-20191113090041 ~]# firewall-cmd --zone=public --add-port=3306/tcp --permanent
success
# 重载防火墙
[root@ecs-s6-medium-2-linux-20191113090041 ~]# firewall-cmd --reload
success
# 查看80端口是否打开
[root@ecs-s6-medium-2-linux-20191113090041 ~]# firewall-cmd --query-port=80/tcp
no
[root@ecs-s6-medium-2-linux-20191113090041 ~]# firewall-cmd --zone=public --add-port=80/tcp --permanent
success
[root@ecs-s6-medium-2-linux-20191113090041 ~]# firewall-cmd --query-port=3306/tcp
yes
[root@ecs-s6-medium-2-linux-20191113090041 ~]# firewall-cmd --zone=public --add-port=80/tcp --permanent
Warning: ALREADY_ENABLED: 80:tcp
success
8.2 华为云安全组配置
- 8 以上全部配置完成,就可以在电脑本地使用
navicate premuim访问了mysql了2
权威｜前沿｜技术｜干货｜国内首个API全生命周期开发者社区
更多推荐
4
0- 0
已为社区贡献4条内容
所有评论(0)