Linux sed部署CentOS Docker 安装Mysql8.0

环境准备

hostnamectl selectt-hostname docker
logout root


#修改ip 10.0.0.11
cd /etc/sysconfig/network-scripts
sed -i '/.*BOOTPROTO=*/c\BOOTPROTO="static"' ifcfg-ens33
sed -i '/.*IPADDR=*/c\IPADDR=10.0.0.11' ifcfg-ens33
systemctl restart network

脚本，安装日志正常则不需要后续的步骤。

docker pull mysql:latest
docker run --name mysqldb -v mysqldbvol:/var/lib/mysql8.0 -p 3308:3308 -e MYSQL_USER=mysql -e MYSQL_PASSWORD=mysql -e MYSQL_DATABASE=sample -e MYSQL_ROOT_PASSWORD=root -d mysql/mysql-server:latest

进入mysql 激活root账号

不能使用ip登录

docker exec -it mysqldb bash
#mysql -P3308 -uroot -proot
mysql -h10.0.0.11 -P3308 -uliaozesong -pLzslov123!

1.开启远程连接

检查root用户host是否允许ip访问。既host的值为%

root 用户host为localhost，

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 change
mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | mysql            |
| localhost | healthchecker    |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

mysql> 
mysql> 
mysql> 

# 开启用户授权，允许root远程连接
update user set host='%' where user='root';
grant all privileges on *.* to 'root'@'%' with grant option;
flush privileges;

2. docker安装mysql警告日志,不影响程序启动。但是会导致无法使用远程连接

出现错误：ERROR 2013 (HY000): Lost connection to MySQL server at ‘reading initial communication packet’, system error: 0

[root@jenkins ~]# ocker run -p 3308:3308 --name mysql \
> -v /usr/local/docker/mysql/mysql-files:/var/lib/mysql-files \
> -v /usr/local/docker/mysql/conf:/etc/mysql \
> -v /usr/local/docker/mysql/logs:/var/log/mysql \
> -v /usr/local/docker/mysql/data:/var/lib/mysql \
> -e MYSQL_ROOT_PASSWORD=root \
> -d mysql:8.0.23
WARNING: IPv4 forwarding is disabled. Networking will not work.
b53f137ca0ec2a5e2ce7cfa7188d3f48bea49747b7286b3ccc3bbc7fb0fa325a

[root@jenkins my.conf.d]# docker exec -it mysqldb bash
bash-4.4# mysql -h 10.0.0.11 -P3308 -uroot -p
En: 
ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0
bash-4.4# exit

2.1配置ipv4转发，安装mysql5.7不会出现此问题。

vi /etc/sysctl.conf
# 或者
vi /usr/lib/sysctl.d/00-system.conf
# 添加如下内容：
net.ipv4.ip_forward = 1
# 重启network服务
systemctl restart network
# 查看是否修改成功
sysctl net.ipv4.ip_forward

最终结果。
net.ipv4.ip_forward = 1

开启后可以使用ip登录

docker exec -it mysqldb bash
mysql -h10.0.0.11 -P3308 -uroot -proot
#添加远程登录用户
CREATE USER 'liaozesong'@'%' IDENTIFIED WITH mysql_native_password BY 'Lzslov123!';
GRANT ALL PRIVILEGES ON *.* TO 'liaozesong'@'%';
#测试登录
docker exec -it mysqldb bash
mysql -P3308 -uliaozesong -pLzslov123!
mysql -h10.0.0.11 -P3308 -uliaozesong -pLzslov123!

3.追加: MySQL8.0新特性-账户双密码

1.简介

MySQL8.0允许账户具有双密码，从而允许我们的服务无缝地进行密码更改，无须停机

2.环境

MySQL版本：8.0.26

执行以下命令查看当前MySQL版本号，对应为：8.0.26

SELECT version();

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

mysql> 

查看当前登

录用户名和host，对应为：root@localhost

SELECT user();

mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> 

3.创建双密码

3.1 创建新密码并保留当前密码

为当前用户创建新密码，并保留当前密码

ALTER USER 'root'@'localhost' IDENTIFIED BY '1234567' RETAIN CURRENT PASSWORD;

清除旧密码

ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

3.4 总结，所有指令

[root@hld-application ~]# docker exec -it mysql bash
bash-4.4# mysql  -uroot -pLzslov123!

mysql> SELECT version();
+-----------+
| version() |
+-----------+
| 8.0.31    |
+-----------+
1 row in set (0.00 sec)

mysql> 
mysql> 
mysql> SELECT user();
+----------------+
| user()         |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.00 sec)

mysql> 
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root!xxxx' RETAIN CURRENT PASSWORD;
Query OK, 0 rows affected (0.23 sec)
mysql>
-- 暂未执行
ALTER USER 'root'@'localhost' DISCARD OLD PASSWORD;

Reference：
Install MySQL 8 using Docker easily
https://ganeshchandraselectkaran.com/install-mysql-8-using-docker-easily-10091d575441?gi=0642fa034fd7

Docker 安装 MySQL
https://www.runoob.com/docker/docker-install-mysql.html

WARNING: IPv4 forwarding is disabled. Networking will not work.
https://juejin.cn/post/6844903962236157960

MySQL8.0新特性-账户双密码
https://blog.csdn.net/weixin_43834401/article/details/126467142