Linux系统环境基于Docker搭建Mysql数据库服务实战

开放端口规划：

mysql-develop：3407
mysql-test： 3408
mysql-release： 3409

ps： 1.不推荐使用默认端口-3306，建议自定义端口 2.如果采用阿里云服务器，在安全组开放端口 3.自建服务器依据实际情况打开防火墙开放端口[各个系统防火墙不一样,操作有所不同]，譬如： Centos7 环境-防火墙[firewall-cmd]：

1firewall-cmd --zone=public --add-port=3407/tcp --permanent
2firewall-cmd --zone=public --add-port=3408/tcp --permanent
3firewall-cmd --zone=public --add-port=3409/tcp --permanent

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

1（1）设置开机启用防火墙：systemctl enable firewalld.service
2（2）设置开机禁用防火墙：systemctl disable firewalld.service
3（3）启动防火墙：systemctl start firewalld
4（4）关闭防火墙：systemctl stop firewalld
5（5）检查防火墙状态：systemctl status firewalld
6二、使用firewall-cmd配置端口
7（1）查看防火墙状态：firewall-cmd --state
8（2）重新加载配置：firewall-cmd --reload
9（3）查看开放的端口：firewall-cmd --list-ports
10（4）开启防火墙端口：firewall-cmd --zone=public --add-port=9200/tcp --permanent
11　　命令含义：
12　　–zone #作用域
13　　–add-port=9200/tcp #添加端口，格式为：端口/通讯协议
14　　–permanent #永久生效，没有此参数重启后失效
15　　注意：添加端口后，必须用命令firewall-cmd --reload重新加载一遍才会生效
16    firewall-cmd --zone=public --add-port=9200/tcp --permanent
17（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 配置实例：

1version: '2'
2services:
3  db:
4    image: 'mysql/mysql-server:tag'
5    restart: always
6    container_name: mysql-server
7    environment:
8      MYSQL_USER: username
9      MYSQL_PASSWORD: password
10      MYSQL_DATABASE: database
11      MYSQL_ROOT_PASSWORD: password
12    ports:
13      - 'server-port[自定义端口]: container-port[默认3306]'

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

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]容器：

1root@mysql-develop:/# mysql -u root -p
2Enter password:
3Welcome to the MySQL monitor.  Commands end with ; or \g.
4Your MySQL connection id is 15
5Server version: 8.0.18 MySQL Community Server - GPL
6
7Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
8
9Oracle is a registered trademark of Oracle Corporation and/or its
10affiliates. Other names may be trademarks of their respective
11owners.
12
13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15mysql> use mysql;
16Reading table information for completion of table and column names
17You can turn off this feature to get a quicker startup with -A
18
19Database changed
20mysql> create user 'developer'@'%' identified by '123456Abc@2019';
21Query OK, 0 rows affected (0.01 sec)
22
23mysql> grant all privileges on *.* to 'developer'@'%' with grant option;
24Query OK, 0 rows affected (0.00 sec)
25
26mysql>
27mysql> flush privileges;
28Query OK, 0 rows affected (0.01 sec)

如图：

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;

1mysql> use mysql
2Database changed
3mysql> grant all privileges on *.* to 'developer'@'%'  identified by '123456Abc@2019' with grant option;
4ERROR 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'；

1mysql> use mysql;
2Database changed
3mysql> grant all privileges on *.* to 'developer'@'%'  identified by '123456Abc@2019';
4ERROR 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
5mysql>

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

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

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

1mysql> use mysql
2Database changed
3mysql> select host, user, authentication_string, plugin from user;
4+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5| host      | user             | authentication_string                                                  | plugin                |
6+-----------+------------------+------------------------------------------------------------------------+-----------------------+
7| %         | developer        | *F286F2787D69B007CFDE83C115325B2A6FF0B6D2                              | caching_sha2_password |
8| %         | root             | *F286F2787D69B007CFDE83C115325B2A6FF0B6D2                              | caching_sha2_password |
9| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
10| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
11| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
12_Oo8xLxsqwEOxEkY1i7kToF8VbktysFDQuevvwYqsK61Qi7 | caching_sha2_password |
13+-----------+------------------+------------------------------------------------------------------------+-----------------------+
146 rows in set (0.00 sec)
15mysql>

root 用户：

1mysql> use mysql;
2Database changed
3mysql> GRANT ALL ON *.* TO 'root'@'%';
4Query OK, 0 rows affected (0.00 sec)
5mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456Abc@2019;
6Query OK, 0 rows affected (0.01 sec)
7mysql> flush privileges;
8Query OK, 0 rows affected (0.00 sec)

developer用户：

1mysql> use mysql;
2Database changed
3mysql> GRANT ALL ON *.* TO 'developer'@'%';
4Query OK, 0 rows affected (0.00 sec)
5mysql> ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY '123456Abc@2019';
6Query OK, 0 rows affected (0.00 sec)
7mysql> flush privileges;
8Query OK, 0 rows affected (0.00 sec)
9mysql>

修改加密规则:

1mysql> use mysql
2Reading table information for completion of table and column names
3You can turn off this feature to get a quicker startup with -A
4
5Database changed
6mysql> ALTER USER 'root'@'%' IDENTIFIED BY '123456Abc@2019' PASSWORD EXPIRE NEVER;
7Query OK, 0 rows affected (0.01 sec)
8
9mysql> ALTER USER 'developer'@'%' IDENTIFIED BY '123456Abc@2019' PASSWORD EXPIRE NEVER;
10Query OK, 0 rows affected (0.01 sec)
11
12mysql> flush privileges;
13Query OK, 0 rows affected (0.00 sec)
14
15mysql>

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

1mysql> use mysql
2Database changed
3mysql> select host, user, authentication_string, plugin from user;
4+-----------+------------------+------------------------------------------------------------------------+-----------------------+
5| host      | user             | authentication_string                                                  | plugin                |
6+-----------+------------------+------------------------------------------------------------------------+-----------------------+
7| %         | developer        | *F286F2787D69B007CFDE83C115325B2A6FF0B6D2                              | mysql_native_password |
8| %         | root             | *F286F2787D69B007CFDE83C115325B2A6FF0B6D2                              | mysql_native_password |
9| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
10| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
11| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
12_Oo8xLxsqwEOxEkY1i7kToF8VbktysFDQuevvwYqsK61Qi7 | caching_sha2_password |
13+-----------+------------------+------------------------------------------------------------------------+-----------------------+
146 rows in set (0.00 sec)
15
16mysql>

到此，Navicat测试连接msql：

ps[注意事项]： 1.mysql8.0版本加密规则插件的plugin 已经换为caching_sha2_password，而之前的版本的加密规则是mysql_native_password，经过实测已经不适用于Navicat 12以下版本,可依据自身情况升级客户端到Navicat 12+，否则会报2059 或者1251 错误。

[Question-01].Navicat 2059错误：

[Question-02].Navicat 1251错误：

2.鉴于第一条的情况，可以将caching_sha2_password修改为mysql_native_password做一个兼容,低版本也可适用。 3.修改加密规则，使得密码长期有效。

完整sql记录：

1mysql> use mysql
2mysql> create user 'developer'@'%' identified by '123456Abc@2019';
3Query OK, 0 rows affected (0.01 sec)
4
5mysql> grant all privileges on *.* to 'developer'@'%' with grant option;
6Query OK, 0 rows affected (0.01 sec)
7
8mysql> flush privileges;
9Query OK, 0 rows affected (0.00 sec)
10
11mysql> GRANT ALL ON *.* TO 'root'@'%';
12Query OK, 0 rows affected (0.00 sec)
13
14mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456Abc@2019';
15Query OK, 0 rows affected (0.01 sec)
16
17mysql> GRANT ALL ON *.* TO 'developer'@'%';
18Query OK, 0 rows affected (0.00 sec)
19
20mysql> ALTER USER 'developer'@'%' IDENTIFIED WITH mysql_native_password BY '123456Abc@2019';
21Query OK, 0 rows affected (0.01 sec)
22
23mysql>  ALTER USER 'root'@'%' IDENTIFIED BY 'GuangDian@2019' PASSWORD EXPIRE NEVER;
24Query OK, 0 rows affected (0.01 sec)
25
26mysql> ALTER USER 'developer'@'%' IDENTIFIED BY 'GuangDian@2019' PASSWORD EXPIRE NEVER;
27Query OK, 0 rows affected (0.01 sec)
28
29mysql> flush privileges;
30Query OK, 0 rows affected (0.00 sec)
31
32mysql>

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.导入数据文件到容器

1#拷贝备份的文件到docker中
2docker cp /root/all-databases-backup.sql docker-id[容器实际部署id]:/root/all-databases-backup.sql
3#先进入docker环境，然后导入到数据库
4docker exec -it xxx /bin/bash
5mysql -u username -p password < /root/all-databases-backup.sql

Linux系统环境基于Docker搭建Mysql数据库服务实战

Mysql8.0 数据库配置

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

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

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

数据文件迁移操作

评论区

相关推荐