docker 安装mysql实战_Linux系统环境基于Docker搭建Mysql数据库服务实战

妖狐艹你老母 2023-01-12 08:00 137阅读 0赞

开放端口规划：

mysql-develop：3407

mysql-test： 3408

mysql-release： 3409

ps：

1.不推荐使用默认端口-3306，建议自定义端口

2.如果采用阿里云服务器，在安全组开放端口

3.自建服务器依据实际情况打开防火墙开放端口\[各个系统防火墙不一样,操作有所不同\]，譬如：

Centos7 环境-防火墙\[firewall-cmd\]：

firewall-cmd --zone=public --add-port=3407/tcp --permanent

firewall-cmd --zone=public --add-port=3408/tcp --permanent

firewall-cmd --zone=public --add-port=3409/tcp --permanent

4.防火墙\[firewall-cmd\]常用操作

(1)设置开机启用防火墙：systemctl enable firewalld.service

(2)设置开机禁用防火墙：systemctl disable firewalld.service

(3)启动防火墙：systemctl start firewalld

(4)关闭防火墙：systemctl stop firewalld

(5)检查防火墙状态：systemctl status firewalld

二、使用firewall-cmd配置端口

(1)查看防火墙状态：firewall-cmd --state

(2)重新加载配置：firewall-cmd --reload

(3)查看开放的端口：firewall-cmd --list-ports

(4)开启防火墙端口：firewall-cmd --zone=public --add-port=9200/tcp --permanent

命令含义：

–zone \#作用域

–add-port=9200/tcp \#添加端口，格式为：端口/通讯协议

–permanent \#永久生效，没有此参数重启后失效

注意：添加端口后，必须用命令firewall-cmd --reload重新加载一遍才会生效

firewall-cmd --zone=public --add-port=9200/tcp --permanent

(5)关闭防火墙端口：firewall-cmd --zone=public --remove-port=9200/tcp --permanent

查找镜像：docker search mysql

docker search mysql

拉取镜像：docker pull mysql

docker pull mysql

ps：如果不是自建仓库镜像,一般从https://hub.docker.com/拉取官方镜像：

docker pull mysql:5.7 \# 拉取mysql 5.7

docker pull mysql \# 拉取最新版mysql镜像

部署mysql服务：

1.简单命令实例：\[主要使用Docker原生命令部署\]

docker run -itd -p 3306:3306 --restart always --name mysql-server -e MYSQL\_ROOT\_PASSWORD=db-password -e MYSQL\_USER=db-username mysql:tag

2.使用docker-compose 部署实例：使用docker-compose搭建

docker-compose.yml文件进行部署可从,github和码云等云仓库git clone 然后修改执行\[docker-compose up -d\]部署：

docker-compose.yml 配置实例：

version: '2'

services:

db:

image: 'mysql/mysql-server:tag'

restart: always

container\_name: mysql-server

environment:

MYSQL\_USER: username

MYSQL\_PASSWORD: password

MYSQL\_DATABASE: database

MYSQL\_ROOT\_PASSWORD: password

ports:

\- 'server-port\[自定义端口\]: container-port\[默认3306\]'

3.使用Docker Portainer可视化界面自建进行部署

![279dd474d554bcc2e0605aeb61e9b559.png][]

Mysql8.0 数据库配置

基于Docker安装的数据库安装完成之后，只能在本地登录,需要进行授权远程访问连接操作。

1.创建用户和授权

\# 创建自定义myql用户-username 和密码-pssword

create user 'username'@'%' identified by 'pssword';

>ps：create user 'developer'@'%' identified by '123456Abc@2019';

\# 对自定义用户进行授权操作

grant all privileges on \*.\* to 'username'@'%' with grant option;

>ps：grant all privileges on \*.\* to 'developer'@'%' with grant option;

\# 刷新操作权限\[切记此点\]

flush privileges;

进入\[root@mysql-develop\]容器：

root@mysql-develop:/\# mysql -u root -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \\g.

Your MySQL connection id is 15

Server version: 8.0.18 MySQL Community Server - GPL

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> use mysql;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> create user 'developer'@'%' identified by '123456Abc@2019';

Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on \*.\* to 'developer'@'%' with grant option;

Query OK, 0 rows affected (0.00 sec)

mysql>

mysql> flush privileges;

Query OK, 0 rows affected (0.01 sec)

如图：

![8a0bffe66cc348ec3d9bf0e24f613a50.png][]

ps：

1.mysql8.0数据操作授权之前得先自定义创建用户，否则无法授权远程登录访问

2.mysql8.0授权无法使用mysql5.7方式：

grant all privileges on . to 'developer'@'%' identified by '123456Abc@2019';

请使用：grant all privileges on . to 'developer'@'%' with grant option;

第一种：grant all privileges on . to 'developer'@'%' identified by '123456Abc@2019' with grant option;

mysql> use mysql

Database changed

mysql> grant all privileges on \*.\* to 'developer'@'%' identified by '123456Abc@2019' with grant option;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123456Abc@2019' with grant option' at line 1

第二种：grant all privileges on . to 'developer'@'%' identified by 123456Abc@2019'；

mysql> use mysql;

Database changed

mysql> grant all privileges on \*.\* to 'developer'@'%' identified by '123456Abc@2019';

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'identified by '123456Abc@2019 at line 1

mysql>

3.一定而且必须进行刷新权限操作,否则无法生效，甚至无法授权远程访问

2.mysql8.0远程访问链接\[root 和developer\]

在 mysql 数据库的 user 表中查看当前用户的相关信息：

mysql> use mysql

Database changed

mysql> select host, user, authentication\_string, plugin from user;

\+-----------+------------------+------------------------------------------------------------------------+-----------------------+

\+-----------+------------------+------------------------------------------------------------------------+-----------------------+

\_Oo8xLxsqwEOxEkY1i7kToF8VbktysFDQuevvwYqsK61Qi7 | caching\_sha2\_password |

\+-----------+------------------+------------------------------------------------------------------------+-----------------------+

6 rows in set (0.00 sec)

mysql>

root 用户：

mysql> use mysql;

Database changed

mysql> GRANT ALL ON \*.\* TO 'root'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql\_native\_password BY '123456Abc@2019;

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

developer用户：

mysql> use mysql;

Database changed

mysql> GRANT ALL ON \*.\* TO 'developer'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'developer'@'%' IDENTIFIED WITH mysql\_native\_password BY '123456Abc@2019';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

修改加密规则:

mysql> use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456Abc@2019' PASSWORD EXPIRE NEVER;

Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'developer'@'%' IDENTIFIED BY '123456Abc@2019' PASSWORD EXPIRE NEVER;

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

设置完成需要再次验证用户权限信息：

mysql> use mysql

Database changed

mysql> select host, user, authentication\_string, plugin from user;

\+-----------+------------------+------------------------------------------------------------------------+-----------------------+

\+-----------+------------------+------------------------------------------------------------------------+-----------------------+

\_Oo8xLxsqwEOxEkY1i7kToF8VbktysFDQuevvwYqsK61Qi7 | caching\_sha2\_password |

\+-----------+------------------+------------------------------------------------------------------------+-----------------------+

6 rows in set (0.00 sec)

mysql>

到此，Navicat测试连接msql：

![48b1ca1e6177a19c926bada8ae2254d9.png][]

ps\[注意事项\]：

1.mysql8.0版本加密规则插件的plugin 已经换为caching\_sha2\_password，而之前的版本的加密规则是mysql\_native\_password，经过实测已经不适用于Navicat 12以下版本,可依据自身情况升级客户端到Navicat 12+，否则会报2059 或者1251 错误。

\[Question-01\].Navicat 2059错误：

![efc051057b7e8a0e495b0b3a82c38cf7.png][]

\[Question-02\].Navicat 1251错误：

![e35511f9e8b14c6bb5727c63b1e774c1.png][]

2.鉴于第一条的情况，可以将caching\_sha2\_password修改为mysql\_native\_password做一个兼容,低版本也可适用。

3.修改加密规则，使得密码长期有效。

完整sql记录：

mysql> use mysql

mysql> create user 'developer'@'%' identified by '123456Abc@2019';

Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on \*.\* to 'developer'@'%' with grant option;

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON \*.\* TO 'root'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql\_native\_password BY '123456Abc@2019';

Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL ON \*.\* TO 'developer'@'%';

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'developer'@'%' IDENTIFIED WITH mysql\_native\_password BY '123456Abc@2019';

Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'GuangDian@2019' PASSWORD EXPIRE NEVER;

Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER 'developer'@'%' IDENTIFIED BY 'GuangDian@2019' PASSWORD EXPIRE NEVER;

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

mysql>

3套mysql环境：

mysql-develop：

IP：192.168.0.1

Port：3407

Username：root/developer

password：123456Abc@2019

mysql-test：

IP：192.168.0.2

Port：3408

Username：root/developer

password：123456Abc@2019

mysql-release：

IP：192.168.0.3

Port：3409

Username：root/developer

password：123456Abc@2019

数据文件迁移操作

1.基于mysqldump+docker cp 命令进行操作

方式1：直接在宿主机器进行数据备份

docker exec -it docker-id\[容器实际部署id\] mysqldump -u root -p passowrd --databases dbA dbB > /root/all-databases-backup.sql

方式2：先进入到docker在执行mysqldump，然后再将导出的sql拷贝到宿主

\#进入docker

docker exec -it docker-id\[容器实际部署id\] /bin/bash

\#可选的

source /etc/profile

\#执行导出命令

mysqldump -u username -p password --databases dbA dbB > /root/all-databases-backup.sql

\#拷贝到宿主机器

\#退出Docker，执行exit命令

exit

\#此时，已经在宿主的环境，执行拷贝命令，将sql文件从docker红拷贝出来

docker cp docker-id\[容器实际部署id\]: /root/all-databases-backup.sql /root/all-databases-backup.sql

2.导入数据文件到容器

\#拷贝备份的文件到docker中

docker cp /root/all-databases-backup.sql docker-id\[容器实际部署id\]:/root/all-databases-backup.sql

\#先进入docker环境，然后导入到数据库

docker exec -it xxx /bin/bash

mysql -u username -p password < /root/all-databases-backup.sql

[279dd474d554bcc2e0605aeb61e9b559.png]: /images/20221119/b26182e570054db9bb52413e3e61ccf5.png
[8a0bffe66cc348ec3d9bf0e24f613a50.png]: /images/20221119/34fc8337373f4afea5e6228ab18b7e57.png
[48b1ca1e6177a19c926bada8ae2254d9.png]: /images/20221119/1bfd42093eec46388fde4854b95c2160.png
[efc051057b7e8a0e495b0b3a82c38cf7.png]: /images/20221119/9f7e02338ad44cd5ba4acf600e5a7d4c.png
[e35511f9e8b14c6bb5727c63b1e774c1.png]: /images/20221119/b9e7f45b3c304be4bec52262505f360a.png