開放埠規劃：
- 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可視化界面自建進行部署
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
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資料操作授權之前得先自定義創建用戶，否則無法授權遠程登錄訪問
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;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| 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>
修改加密規則:
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;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| 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[注意事項]：
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記錄：
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
著作權宣告：本文為博主原創文章，遵循相關著作權協議，如若轉載或者分享請附上原文出處鏈接和鏈接來源，
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/106632.html
標籤：其他
上一篇：一線架構師的一些專案管理心得