Как исправить Connection refused в базе данных

 1# PostgreSQL
 2sudo systemctl start postgresql
 3sudo systemctl status postgresql
 4
 5# MySQL
 6sudo systemctl start mysql
 7sudo systemctl status mysql
 8
 9# MongoDB
10sudo systemctl start mongod
11sudo systemctl status mongod
12
13# Проверь процессы
14ps aux | grep -E "(postgres|mysql|mongod)"

1-- PostgreSQL
2-- Проверь postgresql.conf
3sudo -u postgres psql -c "SHOW listen_addresses;"
4sudo -u postgres psql -c "SHOW port;"
5
6-- Настрой listen_addresses
7# В postgresql.conf:
8listen_addresses = '*'  # или конкретные IP
9port = 5432

 1-- MySQL
 2-- Проверь my.cnf
 3mysql -u root -p -e "SHOW VARIABLES LIKE 'bind_address';"
 4mysql -u root -p -e "SHOW VARIABLES LIKE 'port';"
 5
 6-- Настрой bind_address
 7# В my.cnf:
 8[mysqld]
 9bind_address = 0.0.0.0
10port = 3306

 1# Ubuntu/Debian
 2sudo ufw allow 5432/tcp  # PostgreSQL
 3sudo ufw allow 3306/tcp  # MySQL
 4sudo ufw allow 27017/tcp # MongoDB
 5
 6# CentOS/RHEL
 7sudo firewall-cmd --permanent --add-port=5432/tcp
 8sudo firewall-cmd --permanent --add-port=3306/tcp
 9sudo firewall-cmd --permanent --add-port=27017/tcp
10sudo firewall-cmd --reload
11
12# Проверь открытые порты
13sudo netstat -tlnp | grep -E "(5432|3306|27017)"

1# PostgreSQL pg_hba.conf
2# TYPE  DATABASE        USER            ADDRESS                 METHOD
3local   all             all                                     trust
4host    all             all             127.0.0.1/32            md5
5host    all             all             ::1/128                 md5
6host    all             all             0.0.0.0/0               md5

1# MySQL my.cnf
2[mysqld]
3bind_address = 0.0.0.0
4port = 3306
5
6# Создай пользователя для удаленного доступа
7CREATE USER 'user'@'%' IDENTIFIED BY 'password';
8GRANT ALL PRIVILEGES ON database_name.* TO 'user'@'%';
9FLUSH PRIVILEGES;

 1-- PostgreSQL
 2-- Проверь текущие подключения
 3SELECT count(*) FROM pg_stat_activity;
 4
 5-- Проверь лимит
 6SHOW max_connections;
 7
 8-- Убей зависшие подключения
 9SELECT pg_terminate_backend(pid) 
10FROM pg_stat_activity 
11WHERE state = 'idle';

1-- MySQL
2-- Проверь текущие подключения
3SHOW PROCESSLIST;
4
5-- Проверь лимит
6SHOW VARIABLES LIKE 'max_connections';
7
8-- Убей зависшие подключения
9KILL connection_id;

 1# connection_monitor.py
 2import socket
 3import time
 4from datetime import datetime
 5
 6def check_db_connection(host, port):
 7    try:
 8        start_time = time.time()
 9        sock = socket.socket(socket.AF_INET, socket.SOCK_STREAM)
10        sock.settimeout(5)
11        result = sock.connect_ex((host, port))
12        sock.close()
13        
14        response_time = time.time() - start_time
15        
16        if result == 0:
17            return {
18                'status': 'connected',
19                'response_time': response_time,
20                'timestamp': datetime.now()
21            }
22        else:
23            return {
24                'status': 'refused',
25                'error': f'Connection refused on {host}:{port}',
26                'timestamp': datetime.now()
27            }
28    except Exception as e:
29        return {
30            'status': 'error',
31            'error': str(e),
32            'timestamp': datetime.now()
33        }
34
35# Мониторинг
36while True:
37    # PostgreSQL
38    pg_status = check_db_connection('localhost', 5432)
39    print(f"PostgreSQL: {pg_status}")
40    
41    # MySQL
42    mysql_status = check_db_connection('localhost', 3306)
43    print(f"MySQL: {mysql_status}")
44    
45    time.sleep(30)

 1#!/bin/bash
 2# db_recovery.sh
 3
 4DB_HOST="$1"
 5DB_PORT="$2"
 6MAX_RETRIES=5
 7
 8echo "Checking database connection: $DB_HOST:$DB_PORT"
 9
10for i in {1..$MAX_RETRIES}; do
11    if nc -z "$DB_HOST" "$DB_PORT" 2>/dev/null; then
12        echo "Database is accessible on attempt $i"
13        exit 0
14    else
15        echo "Connection refused on attempt $i"
16        
17        # Попытка перезапуска сервиса
18        if [ "$DB_PORT" = "5432" ]; then
19            sudo systemctl restart postgresql
20        elif [ "$DB_PORT" = "3306" ]; then
21            sudo systemctl restart mysql
22        fi
23        
24        sleep 30
25    fi
26done
27
28echo "Database remains inaccessible after $MAX_RETRIES attempts"
29exit 1