Как исправить MySQL server has gone away

 1# Проверь статус сервиса
 2sudo systemctl status mysql
 3sudo systemctl status mysqld
 4
 5# Проверь процессы
 6ps aux | grep mysql
 7
 8# Проверь порт
 9sudo netstat -tlnp | grep 3306
10sudo ss -tlnp | grep 3306

 1-- Проверь текущие настройки
 2SHOW VARIABLES LIKE 'wait_timeout';
 3SHOW VARIABLES LIKE 'interactive_timeout';
 4SHOW VARIABLES LIKE 'connect_timeout';
 5
 6-- Увеличь таймауты
 7SET GLOBAL wait_timeout = 28800;
 8SET GLOBAL interactive_timeout = 28800;
 9SET GLOBAL connect_timeout = 60;
10
11-- Или в my.cnf
12[mysqld]
13wait_timeout = 28800
14interactive_timeout = 28800
15connect_timeout = 60

 1-- Проверь текущий размер пакета
 2SHOW VARIABLES LIKE 'max_allowed_packet';
 3
 4-- Увеличь размер пакета
 5SET GLOBAL max_allowed_packet = 16777216;  -- 16MB
 6
 7-- Или в my.cnf
 8[mysqld]
 9max_allowed_packet = 16M
10
11[mysql]
12max_allowed_packet = 16M

 1# connection_pool.py
 2import mysql.connector
 3from mysql.connector import pooling
 4from datetime import datetime
 5
 6# Настрой connection pool
 7db_config = {
 8    'host': 'localhost',
 9    'user': 'user',
10    'password': 'password',
11    'database': 'database',
12    'pool_name': 'mypool',
13    'pool_size': 5,
14    'pool_reset_session': True,
15    'autocommit': True,
16    'connect_timeout': 60,
17    'read_timeout': 30,
18    'write_timeout': 30
19}
20
21# Создай pool
22connection_pool = mysql.connector.pooling.MySQLConnectionPool(**db_config)
23
24def execute_with_retry(query, params=None, max_retries=3):
25    for attempt in range(max_retries):
26        try:
27            connection = connection_pool.get_connection()
28            cursor = connection.cursor()
29            
30            cursor.execute(query, params)
31            result = cursor.fetchall()
32            
33            cursor.close()
34            connection.close()
35            
36            return {'success': True, 'data': result}
37            
38        except mysql.connector.Error as e:
39            if e.errno == 2006:  # MySQL server has gone away
40                print(f"Connection lost, retrying... (attempt {attempt + 1})")
41                if attempt == max_retries - 1:
42                    return {'success': False, 'error': str(e)}
43                continue
44            else:
45                return {'success': False, 'error': str(e)}
46        except Exception as e:
47            return {'success': False, 'error': str(e)}
48
49# Использование
50result = execute_with_retry("SELECT * FROM users LIMIT 10")
51print(f"Query result: {result}")

 1# auto_reconnect.py
 2import mysql.connector
 3from mysql.connector import Error
 4import time
 5
 6class AutoReconnectMySQL:
 7    def __init__(self, host, user, password, database):
 8        self.host = host
 9        self.user = user
10        self.password = password
11        self.database = database
12        self.connection = None
13        self.connect()
14    
15    def connect(self):
16        try:
17            self.connection = mysql.connector.connect(
18                host=self.host,
19                user=self.user,
20                password=self.password,
21                database=self.database,
22                autocommit=True,
23                connect_timeout=60,
24                read_timeout=30,
25                write_timeout=30
26            )
27            print("Connected to MySQL")
28        except Error as e:
29            print(f"Error connecting to MySQL: {e}")
30            self.connection = None
31    
32    def execute(self, query, params=None):
33        if not self.connection or not self.connection.is_connected():
34            print("Reconnecting to MySQL...")
35            self.connect()
36        
37        if not self.connection:
38            return {'success': False, 'error': 'Cannot connect to MySQL'}
39        
40        try:
41            cursor = self.connection.cursor()
42            cursor.execute(query, params)
43            result = cursor.fetchall()
44            cursor.close()
45            
46            return {'success': True, 'data': result}
47            
48        except Error as e:
49            if e.errno == 2006:  # MySQL server has gone away
50                print("Connection lost, reconnecting...")
51                self.connect()
52                if self.connection:
53                    return self.execute(query, params)
54                else:
55                    return {'success': False, 'error': 'Reconnection failed'}
56            else:
57                return {'success': False, 'error': str(e)}
58        except Exception as e:
59            return {'success': False, 'error': str(e)}
60    
61    def close(self):
62        if self.connection and self.connection.is_connected():
63            self.connection.close()
64
65# Использование
66db = AutoReconnectMySQL('localhost', 'user', 'password', 'database')
67result = db.execute("SELECT * FROM users LIMIT 5")
68print(f"Result: {result}")
69db.close()

 1# connection_monitor.py
 2import mysql.connector
 3import socket
 4from datetime import datetime
 5
 6def check_mysql_connection(host, port, user, password, database):
 7    try:
 8        # Проверь TCP соединение
 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        if result != 0:
15            return {
16                'status': 'tcp_failed',
17                'error': f'Cannot connect to {host}:{port}',
18                'timestamp': datetime.now()
19            }
20        
21        # Проверь MySQL соединение
22        conn = mysql.connector.connect(
23            host=host,
24            user=user,
25            password=password,
26            database=database,
27            connect_timeout=10
28        )
29        
30        cursor = conn.cursor()
31        cursor.execute("SELECT 1")
32        result = cursor.fetchone()
33        cursor.close()
34        conn.close()
35        
36        return {
37            'status': 'connected',
38            'result': result,
39            'timestamp': datetime.now()
40        }
41        
42    except mysql.connector.Error as e:
43        return {
44            'status': 'mysql_error',
45            'error': str(e),
46            'timestamp': datetime.now()
47        }
48    except Exception as e:
49        return {
50            'status': 'unknown_error',
51            'error': str(e),
52            'timestamp': datetime.now()
53        }
54
55# Мониторинг
56status = check_mysql_connection(
57    'localhost', 3306, 'user', 'password', 'database'
58)
59print(f"Connection status: {status}")

 1# health_check.py
 2import time
 3import threading
 4
 5class MySQLHealthCheck:
 6    def __init__(self, host, user, password, database, check_interval=30):
 7        self.host = host
 8        self.user = user
 9        self.password = password
10        self.database = database
11        self.check_interval = check_interval
12        self.is_running = False
13        self.last_check = None
14        self.last_status = None
15    
16    def start_monitoring(self):
17        self.is_running = True
18        thread = threading.Thread(target=self._monitor_loop)
19        thread.daemon = True
20        thread.start()
21    
22    def stop_monitoring(self):
23        self.is_running = False
24    
25    def _monitor_loop(self):
26        while self.is_running:
27            status = check_mysql_connection(
28                self.host, 3306, self.user, self.password, self.database
29            )
30            
31            self.last_check = datetime.now()
32            self.last_status = status
33            
34            if status['status'] != 'connected':
35                print(f"MySQL health check failed: {status}")
36                # Отправь алерт
37                self._send_alert(status)
38            
39            time.sleep(self.check_interval)
40    
41    def _send_alert(self, status):
42        # Реализуй отправку алерта
43        print(f"ALERT: MySQL connection issue - {status}")
44
45# Использование
46health_check = MySQLHealthCheck('localhost', 'user', 'password', 'database')
47health_check.start_monitoring()

 1#!/bin/bash
 2# mysql_recovery.sh
 3
 4MYSQL_HOST="$1"
 5MYSQL_PORT="$2"
 6MAX_RETRIES=5
 7
 8echo "Checking MySQL connection: $MYSQL_HOST:$MYSQL_PORT"
 9
10for i in {1..$MAX_RETRIES}; do
11    if mysqladmin ping -h"$MYSQL_HOST" -P"$MYSQL_PORT" --silent; then
12        echo "MySQL is accessible on attempt $i"
13        exit 0
14    else
15        echo "MySQL connection failed on attempt $i"
16        
17        # Попытка перезапуска MySQL
18        sudo systemctl restart mysql
19        sleep 30
20    fi
21done
22
23echo "MySQL remains inaccessible after $MAX_RETRIES attempts"
24exit 1