Rootop 服务器运维与web架构

2025-02-28
发表者 Venus
配置 docker container 连接到主机上的 MySQL 服务已关闭评论

配置 docker container 连接到主机上的 MySQL 服务

docker-compose.yml 里加入这一条:

extra_hosts:
  - "host.docker.internal:host-gateway"


变成:
services:
  app:
    image: ...
    container_name: ...
    ...
    extra_hosts:
      - "host.docker.internal:host-gateway"


与此同时, MySQL 需要监听在 docker 的网卡上:


# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc fq state UP group default qlen 1000
    link/ether 00:0c:ff:cc:af:af brd ff:ff:ff:ff:ff:ff
    altname enp0s18
    altname ens18
    inet 172.16.212.135/24 brd 172.16.212.255 scope global eth0
       valid_lft forever preferred_lft forever
    inet6 fe80:20c:29ff:3::bca/64 scope global
       valid_lft forever preferred_lft forever
3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
    link/ether 76:1f:8c:eb:62:f8 brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       valid_lft forever preferred_lft forever
25: br-c5516318dfee: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
    link/ether 7e:e9:0e:61:6e:cb brd ff:ff:ff:ff:ff:ff
    inet 172.18.0.1/16 brd 172.18.255.255 scope global br-c5516318dfee
       valid_lft forever preferred_lft forever
    inet6 fe80::7ce9:eff:fe61:6ecb/64 scope link
       valid_lft forever preferred_lft forever


这里可以看到 docker0 网卡的地址是 172.17.0.1, 也就是容器内 host.docker.internal 会解析到的地址


打开 MySQL 配置文件, 位于:
/etc/mysql/mysql.conf.d/mysqld.cnf


将
bind-address            = 127.0.0.1
改为
bind-address            = 127.0.0.1,172.17.0.1
重启 MySQL:
systemctl restart mysql
即可完成配置

2025-02-24
发表者 Venus
mysql load data导入数据报 The used command is not allowed with this MySQL version 错误已关闭评论

mysql load data导入数据报 The used command is not allowed with this MySQL version 错误

版本：mysql 8.0.18

mysql> use dkparel_prod;
mysql> load data local infile '/mnt/upload_sql/2024-01-10_gameAccountingHistory.csv' into table gameaccountinghistory3 fields terminated by '\t' lines terminated by '\n';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

# 通过mysql命令行临时修改也没效果
mysql> set global local_infile=on;
mysql> show global variables like 'local_infile';

# 可以在进mysql命令行时加参数解决
[root@db01 ~]# mysql -uroot -pxxx --local-infile=1

# 或者在shell中加参数直接执行sql语句
[root@db01 ~]# mysql -uroot -pxxx --local-infile=1 dkparel_prod -e "load data local infile '/mnt/upload_sql/2024-01-10_gameAccountingHistory.csv' into table gameaccountinghistory3 fields terminated by '\t' lines terminated by '\n';"

2025-02-13
发表者 Venus
almalinux9.5安装阿里ossfs已关闭评论

almalinux9.5安装阿里ossfs

# 官方文档
https://help.aliyun.com/zh/oss/developer-reference/install-ossfs

# 通过源码方式安装
sudo yum makecache
sudo yum install automake gcc-c++ git libcurl-devel libxml2-devel fuse-devel make openssl-devel

git clone https://github.com/aliyun/ossfs.git
cd ossfs
./autogen.sh 

# 在下面这一步会报错，提示需要fuse版本需要大于指定版本
./configure 
make
make install



# 在almalinux9中，fuse叫fuse3和fuse3-devel，但是无法被ossfs编译使用，手动安装fuse和fuse-devel

# 通过rpmfind网站查到centos stream9的包并安装

# 先装fuse
yum install -y https://rpmfind.net/linux/centos-stream/9-stream/BaseOS/x86_64/os/Packages/fuse-2.9.9-17.el9.x86_64.rpm

# 装fuse-libs，会被fuse-devel依赖
yum install -y https://rpmfind.net/linux/centos-stream/9-stream/BaseOS/x86_64/os/Packages/fuse-libs-2.9.9-17.el9.x86_64.rpm
yum install -y https://rpmfind.net/linux/centos-stream/9-stream/CRB/x86_64/os/Packages/fuse-devel-2.9.9-17.el9.x86_64.rpm
再次 ./configure 通过

按照官方文档继续配置剩下的部分。

2025-02-07
发表者 Venus
ngx_http_realip_module 获取客户端真实ip模块测试已关闭评论

ngx_http_realip_module 获取客户端真实ip模块测试

此模块需要在编译安装nginx时加上 –with-http_realip_module 参数

测试环境：
PC主机 -> nginx反向代理 -> 源nginx
192.168.6.88 -> 192.168.6.151 -> 192.168.6.114

# 192.168.6.151 中 nginx 反向代理配置
location / {
	proxy_pass http://192.168.6.114;
	proxy_set_header host t1.test.com;
	proxy_set_header x-forwarded-for $remote_addr,192.168.6.151,1.1.1.1;
}

# 192.168.6.114中nginx配置：
real_ip_header x-forwarded-for;
set_real_ip_from 192.168.6.151;
set_real_ip_from 1.1.1.1;
real_ip_recursive on;

当用PC浏览器访问反向代理nginx ip时，源nginx日志中为：
192.168.6.88 - - [07/Feb/2025:01:31:55 -0500] "GET /

real_ip_header 用于配置从哪个请求头中获取真实ip。
set_real_ip_from 配置指令可以有多个，用于配置受信任的ip，移除 x-forwarded-for 字段中 set_real_ip_from 中定义的值。
real_ip_recursive 为on，先从 x-forwarded-for 中排除 set_real_ip_from 指令指定的ip，然后取最后一个ip作为客户端ip。
real_ip_recursive 为off，取x-forwarded-for中最后一个ip作为客户端ip

当 set_real_ip_from 的值都匹配不到 x-forwarded-for 中的值时，则不处理，直接用上一级代理的ip作为客户端ip（也就是 nginx反向代理机器ip ）

这样可以实现在不改动后端代码的情况下获取真实客户端ip
例如php

2024-12-26
发表者 Venus
解决mysql8.0.40自带openssl库文件版本过低已关闭评论

解决mysql8.0.40自带openssl库文件版本过低

安装的mysql8.0.40版本，在漏扫中出现一个漏洞提示openssl版本过低

# 系统自带的openssl链接库文件在/usr/lib64下
[root@localhost ~]# cd /usr/lib64/
[root@localhost lib64]# ll libcrypto*
lrwxrwxrwx 1 root root      18 Oct  1 09:38 libcrypto.so -> libcrypto.so.3.2.2
lrwxrwxrwx 1 root root      18 Oct  1 09:38 libcrypto.so.3 -> libcrypto.so.3.2.2
-rwxr-xr-x 1 root root 5435440 Oct  1 09:38 libcrypto.so.3.2.2

[root@localhost lib64]# ll libssl*
-rwxr-xr-x. 1 root root 449560 Sep 16 11:38 libssl3.so
lrwxrwxrwx  1 root root     15 Oct  1 09:38 libssl.so -> libssl.so.3.2.2
lrwxrwxrwx  1 root root     15 Oct  1 09:38 libssl.so.3 -> libssl.so.3.2.2
-rwxr-xr-x  1 root root 957480 Oct  1 09:38 libssl.so.3.2.2

# 查看mysql载入的动态链接库路径
[root@localhost lib64]# ldd /usr/local/mysql/bin/mysql | grep libcrypto
	libcrypto.so.3 => /usr/local/mysql/bin/../lib/private/libcrypto.so.3 (0x00007f59b5e00000)
[root@localhost lib64]# ldd /usr/local/mysql/bin/mysqld | grep -i "ssl"
	libssl.so.3 => /usr/local/mysql/bin/../lib/private/libssl.so.3 (0x00007fa5b1e00000)
	
可以看到都是用的mysql自带的库

[root@localhost lib64]# strings /usr/local/mysql/lib/private/libcrypto.so.3 | grep -i "OpenSSL"
OpenSSL 3.0.15 3 Sep 2024
自带的是3.0.15版本，所以漏扫提示此版本有漏洞

解决方法：
# 通过输出环境变量，让mysql去读系统自带的openssl
[root@localhost lib64]# export LD_LIBRARY_PATH=/usr/lib64:$LD_LIBRARY_PATH
[root@localhost lib64]# systemctl restart mysql

# 再次确认
[root@localhost lib64]# ldd /usr/local/mysql/bin/mysqld | grep -i "ssl"
	libssl.so.3 => /usr/lib64/libssl.so.3 (0x00007fe9b4f1a000)
[root@localhost lib64]# ldd /usr/local/mysql/bin/mysql | grep libcrypto
	libcrypto.so.3 => /usr/lib64/libcrypto.so.3 (0x00007f262d600000)

[root@localhost ~]# strings /usr/lib64/libcrypto.so.3 | grep -i "OpenSSL"
OpenSSL 3.2.2 4 Jun 2024