newapi分离mysql
要讲使用docker compose运行的newapi的mysql分离到云数据库上运行
先确定运行情况
root@VM-4-6-ubuntu:/www/dk_project/dk_app/newapi/newapi_Xn78# docker ps -a
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
72be5f6614d7 newapi "/one-api --log-dir …" 2 hours ago Up 2 hours 127.0.0.1:3000->3000/tcp newapi_xn78_new-api_1
01f77c8a50a0 mysql:8.2 "docker-entrypoint.s…" 3 hours ago Up 2 hours 3306/tcp, 33060/tcp newapi_xn78_mysql_1
af9965b6606f redis:latest "docker-entrypoint.s…" 3 hours ago Up 2 hours 6379/tcp newapi_xn78_redis_1
root@VM-4-6-ubuntu:/www/dk_project/dk_app/newapi/newapi_Xn78# netstat -ntlp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:41181 0.0.0.0:* LISTEN 997/containerd
tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN 2861550/nginx: mast
tcp 0 0 0.0.0.0:22 0.0.0.0:* LISTEN 1035/sshd: /usr/sbi
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 2861550/nginx: mast
tcp 0 0 127.0.0.1:3000 0.0.0.0:* LISTEN 3792153/docker-prox
tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN 923/systemd-resolve
tcp6 0 0 :::22 :::* LISTEN 1035/sshd: /usr/sbi
tcp6 0 0 :::80 :::* LISTEN 2861550/nginx: mast(可选)可以看到默认没有将mysql的端口映射出来,把3306映射出来方便后续操作
先修改compose文件
......
mysql:
image: mysql:8.2
restart: always
environment:
MYSQL_ROOT_PASSWORD: 123456
in SQL_DSN
MYSQL_DATABASE: new-api
volumes:
- ${APP_PATH}/mysql_data:/var/lib/mysql
ports:
- "3306:3306" #添加端口映射
labels:
createdBy: "bt_apps"
networks:
- baota_net
......docker-compose down && docker-compose up -d请注意数据是否有做持久化
DTS不支持8.2,只能手动迁移了,先检查库的大小
# docker exec -it newapi_xn78_mysql_1 mysql -uroot -p123456 -e "SELECT table_schema AS 'Database', ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+-----------+
| Database | Size (MB) |
+--------------------+-----------+
| information_schema | 0.00 |
| mysql | 7.95 |
| new-api | 21880.78 |
| performance_schema | 0.00 |
| sys | 0.02 |
+--------------------+-----------+
有点大呢,看下有没数据可以清除
root@VM-4-6-ubuntu:/www/dk_project/dk_app/newapi/newapi_Xn78# docker exec -it newapi_xn78_mysql_1 mysql -uroot -p123456 -e "
> SELECT
> table_name AS '表名',
> ROUND(data_length / 1024 / 1024, 2) AS '数据大小 (MB)',
> ROUND(index_length / 1024 / 1024, 2) AS '索引大小 (MB)',
> ROUND((data_length + index_length) / 1024 / 1024, 2) AS '总大小 (MB)',
> table_rows AS '行数'
> FROM information_schema.TABLES
> WHERE table_schema = 'new-api'
> ORDER BY data_length DESC;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------------------+-------------------+-------------------+----------------+----------+
| 表名 | 数据大小 (MB) | 索引大小 (MB) | 总大小 (MB) | 行数 |
+-------------------------+-------------------+-------------------+----------------+----------+
| logs | 14884.23 | 5066.64 | 19950.88 | 23575846 |
| midjourneys | 1694.00 | 85.13 | 1779.13 | 219093 |
| tasks | 106.59 | 4.83 | 111.42 | 8097 |
| quota_data | 11.52 | 17.58 | 29.09 | 117304 |
| tokens | 2.52 | 1.09 | 3.61 | 5602 |
| users | 1.52 | 1.69 | 3.20 | 3033 |
| orders | 0.31 | 0.70 | 1.02 | 2006 |
| options | 0.28 | 0.00 | 0.28 | 80 |
| abilities | 0.19 | 0.39 | 0.58 | 1218 |
| user_push_settings | 0.19 | 0.06 | 0.25 | 2209 |
| top_ups | 0.11 | 0.17 | 0.28 | 824 |
| channels | 0.09 | 0.05 | 0.14 | 67 |
| redemptions | 0.09 | 0.08 | 0.17 | 412 |
| two_fa_backup_codes | 0.02 | 0.03 | 0.05 | 0 |
| two_fas | 0.02 | 0.05 | 0.06 | 0 |
| vendors | 0.02 | 0.03 | 0.05 | 0 |
| verifications | 0.02 | 0.02 | 0.03 | 0 |
| setups | 0.02 | 0.00 | 0.02 | 0 |
| search_engines | 0.02 | 0.00 | 0.02 | 0 |
| search_analysis_configs | 0.02 | 0.00 | 0.02 | 0 |
| schema_version | 0.02 | 0.00 | 0.02 | 2 |
| prefill_groups | 0.02 | 0.05 | 0.06 | 0 |
| payments | 0.02 | 0.03 | 0.05 | 5 |
| models | 0.02 | 0.05 | 0.06 | 0 |
| invoices | 0.02 | 0.13 | 0.14 | 0 |
| check_ins | 0.02 | 0.03 | 0.05 | 0 |
| announcements | 0.02 | 0.00 | 0.02 | 2 |
| agents | 0.02 | 0.05 | 0.06 | 1 |
| agent_withdraw_records | 0.02 | 0.02 | 0.03 | 0 |
| agent_commission_logs | 0.02 | 0.00 | 0.02 | 0 |
+-------------------------+-------------------+-------------------+----------------+----------+好的,logs表有20G,可以删掉一些
mysql> DELETE FROM `new-api`.`logs`
-> WHERE `created_at` < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 15 DAY));
Query OK, 6323310 rows affected (59 min 3.94 sec)导出
docker exec newapi_xn78-mysql-1 mysqldump -uroot -p123456 new-api > ./new-api-all.sql
docker exec newapi_xn78-mysql-1 mysqldump -uroot -p123456 new-api --ignore-table=new-api.logs new-api > ./new-api-all.sql #忽略logs表导入
apt install -y mysql-client
mysql -h 10.80.0.4 -uroot -p123456 --max_allowed_packet=512M --net_buffer_length=16384 new-api < /www/dk_project/dk_app/newapi/newapi_Xn78/new-api-all.sql修改compose配置,去掉mysql服务,并修改连接配置
services:
new-api:
image: newapi/newapi
restart: always
command: --log-dir /app/logs
ports:
- ${HOST_IP}:${WEB_HTTP_PORT}:3000
volumes:
- ${APP_PATH}/data:/data
- ${APP_PATH}/logs:/app/logs
environment:
- SQL_DSN=root:123456@tcp(10.80.0.4:3306)/new-api # 修改为外部MySQL地址
- REDIS_CONN_STRING=redis://redis
- SESSION_SECRET=${SESSION_SECRET}
- TZ=Asia/Shanghai
# - NODE_TYPE=slave
# - SYNC_FREQUENCY=60
# - FRONTEND_BASE_URL=https://openai.justsong.cn
depends_on:
- redis
#healthcheck:
# test: [ "CMD-SHELL", "wget -q -O - http://localhost:3000/api/status | grep -o '\"success\":\\s*true' | awk -F: '{print $2}'" ]
# interval: 30s
# timeout: 10s
# retries: 3
labels:
createdBy: "bt_apps"
networks:
- baota_net
redis:
image: redis:latest
restart: always
labels:
createdBy: "bt_apps"
networks:
- baota_net
networks:
baota_net:重建服务
docker compose down && docker compose up -d迁移完以后发现谷歌登录使用不了
检查发现是请求头没有origin,导致发送给后端的是http
需要修改nginx反向代理配置
location ^~ / {
proxy_pass http://127.0.0.1:3000;
proxy_set_header Host $http_host;
proxy_set_header X-Real-IP $remote_addr;
proxy_set_header X-Real-Port $remote_port;
proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
proxy_set_header X-Forwarded-Proto $scheme;
proxy_set_header X-Forwarded-Host $host;
proxy_set_header X-Forwarded-Port $server_port;
proxy_set_header REMOTE-HOST $remote_addr;
proxy_buffering off;
proxy_connect_timeout 60s;
proxy_send_timeout 600s;
proxy_read_timeout 600s;
proxy_http_version 1.1;
proxy_set_header Upgrade $http_upgrade;
#proxy_set_header Connection $connection_upgrade;
}
