Linux系統(tǒng)環(huán)境基于Docker搭建Mysql數(shù)據(jù)庫服務(wù)實(shí)戰(zhàn)

路人甲Java 2021-07-16

展開全文

開放端口規(guī)劃：

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

ps：
1.不推薦使用默認(rèn)端口-3306，建議自定義端口
2.如果采用阿里云服務(wù)器，在安全組開放端口
3.自建服務(wù)器依據(jù)實(shí)際情況打開防火墻開放端口[各個系統(tǒng)防火墻不一樣,操作有所不同]，譬如：
Centos7 環(huán)境-防火墻[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）設(shè)置開機(jī)啟用防火墻：systemctl enable firewalld.service
（2）設(shè)置開機(jī)禁用防火墻：systemctl disable firewalld.service
（3）啟動防火墻：systemctl start firewalld
（4）關(guān)閉防火墻：systemctl stop firewalld
（5）檢查防火墻狀態(tài)：systemctl status firewalld
二、使用firewall-cmd配置端口
（1）查看防火墻狀態(tài)：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 #添加端口，格式為：端口/通訊協(xié)議
　　–permanent #永久生效，沒有此參數(shù)重啟后失效
　　注意：添加端口后，必須用命令firewall-cmd --reload重新加載一遍才會生效
    firewall-cmd --zone=public --add-port=9200/tcp --permanent
（5）關(guān)閉防火墻端口：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 pull mysql:5.7 # 拉取mysql 5.7
docker pull mysql # 拉取最新版mysql鏡像

部署mysql服務(wù)：
1.簡單命令實(shí)例：[主要使用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 部署實(shí)例：使用docker-compose搭建
docker-compose.yml文件進(jìn)行部署可從,github和碼云等云倉庫git clone 然后修改執(zhí)行[docker-compose up -d]部署：
docker-compose.yml 配置實(shí)例：

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[默認(rèn)3306]'

3.使用Docker Portainer可視化界面自建進(jìn)行部署

Mysql8.0 數(shù)據(jù)庫配置

基于Docker安裝的數(shù)據(jù)庫安裝完成之后，只能在本地登錄,需要進(jìn)行授權(quán)遠(yuǎn)程訪問連接操作。

1.創(chuàng)建用戶和授權(quán)

# 創(chuàng)建自定義myql用戶-username 和密碼-pssword
create user 'username'@'%' identified by 'pssword';
>ps：create user 'developer'@'%' identified by '123456Abc@2019';

# 對自定義用戶進(jìn)行授權(quán)操作
grant all privileges on *.* to 'username'@'%' with grant option;
>ps：grant all privileges on *.* to 'developer'@'%' with grant option;

# 刷新操作權(quán)限[切記此點(diǎn)]
flush privileges;

進(jìn)入[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

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> 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)

如圖：

ps：
1.mysql8.0數(shù)據(jù)操作授權(quán)之前得先自定義創(chuàng)建用戶，否則無法授權(quán)遠(yuǎn)程登錄訪問
2.mysql8.0授權(quán)無法使用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.一定而且必須進(jìn)行刷新權(quán)限操作,否則無法生效，甚至無法授權(quán)遠(yuǎn)程訪問

2.mysql8.0遠(yuǎn)程訪問鏈接[root 和developer]

在 mysql 數(shù)據(jù)庫的 user 表中查看當(dāng)前用戶的相關(guān)信息：

mysql> use mysql
Database changed
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | developer        | *F286F2787D69B007CFDE83C115325B2A6FF0B6D2                              | caching_sha2_password |
| %         | root             | *F286F2787D69B007CFDE83C115325B2A6FF0B6D2                              | caching_sha2_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
_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>

修改加密規(guī)則:

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>

設(shè)置完成需要再次驗(yàn)證用戶權(quán)限信息：

mysql> use mysql
Database changed
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host      | user             | authentication_string                                                  | plugin                |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| %         | developer        | *F286F2787D69B007CFDE83C115325B2A6FF0B6D2                              | mysql_native_password |
| %         | root             | *F286F2787D69B007CFDE83C115325B2A6FF0B6D2                              | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
_Oo8xLxsqwEOxEkY1i7kToF8VbktysFDQuevvwYqsK61Qi7 | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
6 rows in set (0.00 sec)

mysql>

到此，Navicat測試連接msql：

ps[注意事項(xiàng)]：
1.mysql8.0版本加密規(guī)則插件的plugin 已經(jīng)換為caching_sha2_password，而之前的版本的加密規(guī)則是mysql_native_password，經(jīng)過實(shí)測已經(jīng)不適用于Navicat 12以下版本,可依據(jù)自身情況升級客戶端到Navicat 12+，否則會報(bào)2059 或者1251 錯誤。

[Question-01].Navicat 2059錯誤：

[Question-02].Navicat 1251錯誤：

2.鑒于第一條的情況，可以將caching_sha2_password修改為mysql_native_password做一個兼容,低版本也可適用。
3.修改加密規(guī)則，使得密碼長期有效。

完整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環(huán)境：
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

數(shù)據(jù)文件遷移操作

1.基于mysqldump+docker cp 命令進(jìn)行操作

方式1：直接在宿主機(jī)器進(jìn)行數(shù)據(jù)備份

docker exec -it docker-id[容器實(shí)際部署id] mysqldump -u root -p passowrd --databases dbA dbB > /root/all-databases-backup.sql

方式2：先進(jìn)入到docker在執(zhí)行mysqldump，然后再將導(dǎo)出的sql拷貝到宿主

#進(jìn)入docker
docker exec -it docker-id[容器實(shí)際部署id] /bin/bash
#可選的
source /etc/profile
#執(zhí)行導(dǎo)出命令
mysqldump -u username -p password --databases dbA dbB > /root/all-databases-backup.sql
#拷貝到宿主機(jī)器
#退出Docker，執(zhí)行exit命令
exit
#此時(shí)，已經(jīng)在宿主的環(huán)境，執(zhí)行拷貝命令，將sql文件從docker紅拷貝出來
docker cp docker-id[容器實(shí)際部署id]: /root/all-databases-backup.sql  /root/all-databases-backup.sql

2.導(dǎo)入數(shù)據(jù)文件到容器

#拷貝備份的文件到docker中
docker cp /root/all-databases-backup.sql docker-id[容器實(shí)際部署id]:/root/all-databases-backup.sql
#先進(jìn)入docker環(huán)境，然后導(dǎo)入到數(shù)據(jù)庫
docker exec -it xxx /bin/bash
mysql -u username -p password < /root/all-databases-backup.sql

本站是提供個人知識管理的網(wǎng)絡(luò)存儲空間，所有內(nèi)容均由用戶發(fā)布，不代表本站觀點(diǎn)。請注意甄別內(nèi)容中的聯(lián)系方式、誘導(dǎo)購買等信息，謹(jǐn)防詐騙。如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊一鍵舉報(bào)。

轉(zhuǎn)藏 分享

QQ空間 QQ好友新浪微博微信

獻(xiàn)花（0） +1

來自：路人甲Java > 《待分類》

舉報(bào)/認(rèn)領(lǐng)