#9 - Monitoring, troubleshooting và bảo trì liên tục

-- Bật slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;  -- Log queries slower than 1 second
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- Log queries not using indexes

-- Bật slow query logging
ALTER SYSTEM SET log_min_duration_statement = 1000;  -- Log queries slower than 1000ms
ALTER SYSTEM SET log_directory = 'pg_log';
ALTER SYSTEM SET log_filename = 'postgresql-slow-%Y-%m-%d.log';
ALTER SYSTEM SET logging_collector = on;

-- Reload configuration
SELECT pg_reload_conf();

// Bật profiling
db.setProfilingLevel(1, { slowms: 100 }); // Log operations slower than 100ms

// Kiểm tra profiling status
db.getProfilingStatus();

# Sử dụng pt-query-digest (Percona Toolkit)
pt-query-digest /var/log/mysql/mysql-slow.log

-- Sử dụng pg_stat_statements
SELECT
    substring(query, 1, 100) AS short_query,
    round(total_time::numeric, 2) AS total_time,
    calls,
    round(mean_time::numeric, 2) AS mean_time,
    round((100 * total_time / sum(total_time::numeric) OVER ())::numeric, 2) AS percentage
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 10;

-- MySQL: Thêm index cho query chậm
CREATE INDEX idx_large_table_non_indexed_column ON large_table(non_indexed_column);

-- PostgreSQL: Thêm index với INCLUDE
CREATE INDEX idx_orders_customer_date ON orders(customer_id) INCLUDE (order_date);

-- Trước khi tối ưu
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC;

-- Sau khi tối ưu
SELECT o.id, o.order_date, o.total_amount, c.name, c.email
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 100;

-- MySQL: Sử dụng index hints
SELECT * FROM products USE INDEX (idx_category_price)
WHERE category_id = 5 AND price > 100;

# Linux: Kiểm tra memory usage
free -m
vmstat 1
top

# Linux: Kiểm tra CPU usage
top
mpstat -P ALL 1

# Linux: Kiểm tra disk I/O
iostat -x 1
iotop

groups:
  - name: database_alerts
    rules:
      - alert: HighCPUUsage
        expr: rate(node_cpu_seconds_total{mode="system"}[1m]) > 0.8
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "High CPU usage detected"
          description: "CPU usage is above 80% for more than 5 minutes on {{ $labels.instance }}"

      - alert: LowDiskSpace
        expr: node_filesystem_avail_bytes{mountpoint="/var/lib/mysql"} / node_filesystem_size_bytes{mountpoint="/var/lib/mysql"} * 100 < 10
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "Low disk space"
          description: "Less than 10% disk space available on {{ $labels.instance }} MySQL data directory"

import mysql.connector
import pandas as pd
import matplotlib.pyplot as plt
from datetime import datetime
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from email.mime.application import MIMEApplication

def generate_daily_report():
    # Connect to database
    conn = mysql.connector.connect(
        host="localhost",
        user="report_user",
        password="password",
        database="mysql"
    )

    # Query for slow queries
    slow_query_df = pd.read_sql("""
        SELECT db, count(*) as count, avg(query_time) as avg_time
        FROM mysql.slow_log
        WHERE start_time > NOW() - INTERVAL 24 HOUR
        GROUP BY db
        ORDER BY count DESC
    """, conn)

    # Query for resource usage
    resource_df = pd.read_sql("""
        SELECT
            variable_name,
            variable_value
        FROM performance_schema.global_status
        WHERE variable_name IN (
            'Threads_connected',
            'Threads_running',
            'Innodb_buffer_pool_reads',
            'Innodb_buffer_pool_read_requests',
            'Queries',
            'Slow_queries'
        )
    """, conn)

    # Create report
    report_date = datetime.now().strftime("%Y-%m-%d")
    report_file = f"database_report_{report_date}.html"

    # Generate plots
    plt.figure(figsize=(10, 6))
    slow_query_df.plot(kind='bar', x='db', y='count')
    plt.title('Slow Queries by Database')
    plt.tight_layout()
    plt.savefig('slow_queries.png')

    # Create HTML report
    html = f"""
    <html>
    <head>
        <title>Database Performance Report - {report_date}</title>
        <style>
            body {{ font-family: Arial, sans-serif; }}
            table {{ border-collapse: collapse; width: 100%; }}
            th, td {{ border: 1px solid #ddd; padding: 8px; text-align: left; }}
            th {{ background-color: #f2f2f2; }}
        </style>
    </head>
    <body>
        <h1>Database Performance Report - {report_date}</h1>

        <h2>Slow Queries Summary</h2>
        {slow_query_df.to_html()}

        <h2>Resource Usage</h2>
        {resource_df.to_html()}

        <h2>Slow Queries by Database</h2>
        <img src="cid:slow_queries.png">

        <h2>Recommendations</h2>
        <p>Based on the data, consider the following actions:</p>
        <ul>
            <li>Review slow queries in the most affected databases</li>
            <li>Check for missing indexes</li>
            <li>Optimize buffer pool size if read ratio is low</li>
        </ul>
    </body>
    </html>
    """

    # Send email report
    send_email_report(html, report_date)

    conn.close()

def send_email_report(html_content, report_date):
    msg = MIMEMultipart()
    msg['Subject'] = f'Database Performance Report - {report_date}'
    msg['From'] = '[email protected]'
    msg['To'] = '[email protected]'

    # Attach HTML content
    msg.attach(MIMEText(html_content, 'html'))

    # Attach image
    with open('slow_queries.png', 'rb') as f:
        img = MIMEApplication(f.read(), _subtype="png")
        img.add_header('Content-ID', '<slow_queries.png>')
        msg.attach(img)

    # Send email
    with smtplib.SMTP('smtp.example.com', 587) as server:
        server.login('[email protected]', 'password')
        server.send_message(msg)

# Run daily via cron
if __name__ == "__main__":
    generate_daily_report()

-- MySQL: Kiểm tra tăng trưởng database
SELECT
    table_schema as 'Database',
    SUM(data_length + index_length) / 1024 / 1024 as 'Size (MB)',
    SUM(data_free) / 1024 / 1024 as 'Free Space (MB)'
FROM information_schema.TABLES
GROUP BY table_schema;

-- PostgreSQL: Kiểm tra tăng trưởng database
SELECT
    datname as database_name,
    pg_size_pretty(pg_database_size(datname)) as size
FROM pg_database
ORDER BY pg_database_size(datname) DESC;

import pandas as pd
import matplotlib.pyplot as plt
import mysql.connector
from datetime import datetime, timedelta

# Connect to database
conn = mysql.connector.connect(
    host="localhost",
    user="monitor",
    password="password",
    database="monitoring"
)

# Query historical metrics
query = """
SELECT
    collection_time,
    metric_name,
    metric_value
FROM performance_metrics
WHERE
    collection_time >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND metric_name IN ('queries_per_second', 'connections', 'buffer_pool_hit_ratio')
ORDER BY collection_time
"""

df = pd.read_sql(query, conn)

# Pivot data for plotting
pivot_df = df.pivot(index='collection_time', columns='metric_name', values='metric_value')

# Plot trends
plt.figure(figsize=(12, 8))

# Plot queries per second
plt.subplot(3, 1, 1)
pivot_df['queries_per_second'].plot()
plt.title('Queries Per Second - Last 30 Days')
plt.grid(True)

# Plot connections
plt.subplot(3, 1, 2)
pivot_df['connections'].plot()
plt.title('Active Connections - Last 30 Days')
plt.grid(True)

# Plot buffer pool hit ratio
plt.subplot(3, 1, 3)
pivot_df['buffer_pool_hit_ratio'].plot()
plt.title('Buffer Pool Hit Ratio - Last 30 Days')
plt.grid(True)

plt.tight_layout()
plt.savefig('database_trends.png')

# Predict future growth
from sklearn.linear_model import LinearRegression
import numpy as np

# Prepare data for prediction
X = np.array(range(len(pivot_df))).reshape(-1, 1)
y = pivot_df['queries_per_second'].values

# Train model
model = LinearRegression()
model.fit(X, y)

# Predict next 30 days
future_days = 30
future_X = np.array(range(len(pivot_df), len(pivot_df) + future_days)).reshape(-1, 1)
future_y = model.predict(future_X)

# Plot prediction
plt.figure(figsize=(10, 6))
plt.plot(pivot_df.index, pivot_df['queries_per_second'], label='Historical')
future_dates = [pivot_df.index[-1] + timedelta(days=i+1) for i in range(future_days)]
plt.plot(future_dates, future_y, 'r--', label='Predicted')
plt.title('Queries Per Second - Prediction for Next 30 Days')
plt.legend()
plt.grid(True)
plt.savefig('qps_prediction.png')

conn.close()

# Kiểm tra system load
uptime

# Kiểm tra CPU, memory, disk usage
top
vmstat 1
iostat -x 1

# Kiểm tra MySQL status
mysql -e "SHOW GLOBAL STATUS LIKE '%connections%';"
mysql -e "SHOW GLOBAL STATUS LIKE '%threads%';"
mysql -e "SHOW GLOBAL STATUS LIKE '%questions%';"
mysql -e "SHOW GLOBAL STATUS LIKE '%slow%';"

# Phân tích slow query log
pt-query-digest /var/log/mysql/mysql-slow.log

# Kết quả phân tích
# Phát hiện: Query tìm kiếm sản phẩm không sử dụng index hiệu quả
# Query: SELECT * FROM products WHERE category_id = ? AND price BETWEEN ? AND ? ORDER BY popularity DESC

-- Phân tích execution plan
EXPLAIN SELECT * FROM products
WHERE category_id = 5 AND price BETWEEN 100 AND 500
ORDER BY popularity DESC;

-- Kết quả:
-- Using filesort (không tốt)
-- Không sử dụng index cho ORDER BY

-- Tạo composite index phù hợp
CREATE INDEX idx_category_price_popularity ON products(category_id, price, popularity);

-- Tối ưu query
-- Thay vì SELECT *
SELECT id, name, description, price, image_url, stock_quantity
FROM products
WHERE category_id = 5 AND price BETWEEN 100 AND 500
ORDER BY popularity DESC
LIMIT 20;

# my.cnf adjustments
innodb_buffer_pool_size = 6G  # Tăng từ 2G
innodb_log_file_size = 512M   # Tăng từ 128M
max_connections = 300         # Tăng từ 150

# Giám sát hiệu năng sau khi tối ưu
mysqltuner --user=root --pass=password

# Kiểm tra slow queries mới
pt-query-digest /var/log/mysql/mysql-slow.log --since="2 hours ago"

# MySQL: Logical backup với mysqldump
mysqldump --single-transaction --routines --triggers --all-databases > full_backup.sql

# PostgreSQL: Logical backup với pg_dump
pg_dump -Fc -f database_backup.dump database_name

# MySQL: Physical backup với Percona XtraBackup
xtrabackup --backup --target-dir=/backup/mysql/full

# PostgreSQL: Physical backup
pg_basebackup -D /backup/postgresql/full -Ft -z -P

#!/bin/bash
# backup_mysql.sh - Script tự động backup MySQL database

# Cấu hình
BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%Y-%m-%d)
MYSQL_USER="backup_user"
MYSQL_PASSWORD="secure_password"
RETENTION_DAYS=7

# Tạo thư mục backup
mkdir -p $BACKUP_DIR/$DATE

# Thực hiện full backup
mysqldump --user=$MYSQL_USER --password=$MYSQL_PASSWORD \
  --single-transaction --routines --triggers --events --all-databases \
  | gzip > $BACKUP_DIR/$DATE/full_backup.sql.gz

# Backup binary logs
mysql --user=$MYSQL_USER --password=$MYSQL_PASSWORD \
  -e "SHOW BINARY LOGS" | grep -v "Log_name" | awk '{print $1}' | \
  xargs -I{} cp /var/lib/mysql/{} $BACKUP_DIR/$DATE/

# Verify backup
gunzip -c $BACKUP_DIR/$DATE/full_backup.sql.gz | head -n 20 > /dev/null
if [ $? -ne 0 ]; then
  echo "Backup verification failed!" | mail -s "MySQL Backup Failed" [email protected]
  exit 1
fi

# Cleanup old backups
find $BACKUP_DIR -type d -mtime +$RETENTION_DAYS -exec rm -rf {} \;

# Log success
echo "Backup completed successfully at $(date)" >> /var/log/mysql_backup.log

-- MySQL: Point-in-Time Recovery
-- 1. Restore full backup
mysql < full_backup.sql

-- 2. Apply binary logs to specific point in time
mysqlbinlog --stop-datetime="2023-06-15 14:30:00" \
  /var/lib/mysql/binlog.000001 /var/lib/mysql/binlog.000002 | mysql

-- PostgreSQL: Point-in-Time Recovery
-- 1. Restore base backup
pg_restore -d database_name backup_file.dump

-- 2. Create recovery.conf
/*
restore_command = 'cp /path/to/archive/%f %p'
recovery_target_time = '2023-06-15 14:30:00'
*/

-- 3. Start PostgreSQL

#!/bin/bash
# test_recovery.sh - Script kiểm tra quy trình recovery

# Cấu hình
BACKUP_DIR="/var/backups/mysql"
TEST_SERVER="recovery-test.example.com"
MYSQL_USER="recovery_user"
MYSQL_PASSWORD="secure_password"
TEST_DB="recovery_test"

# Lấy backup mới nhất
LATEST_BACKUP=$(find $BACKUP_DIR -name "full_backup.sql.gz" | sort -r | head -n 1)

# Tạo test database
ssh $TEST_SERVER "mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e 'DROP DATABASE IF EXISTS $TEST_DB; CREATE DATABASE $TEST_DB;'"

# Restore backup vào test database
gunzip -c $LATEST_BACKUP | ssh $TEST_SERVER "mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $TEST_DB"

# Kiểm tra integrity
TABLES_COUNT=$(ssh $TEST_SERVER "mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e 'SHOW TABLES FROM $TEST_DB;' | wc -l")

if [ $TABLES_COUNT -lt 2 ]; then
  echo "Recovery test failed: No tables found in restored database"
  exit 1
fi

# Chạy validation queries
VALIDATION_RESULT=$(ssh $TEST_SERVER "mysql -u$MYSQL_USER -p$MYSQL_PASSWORD $TEST_DB -e 'SELECT COUNT(*) FROM users; SELECT COUNT(*) FROM orders;'")

echo "Recovery test completed successfully"
echo "Validation results:"
echo "$VALIDATION_RESULT"

# Cleanup
ssh $TEST_SERVER "mysql -u$MYSQL_USER -p$MYSQL_PASSWORD -e 'DROP DATABASE $TEST_DB;'"

-- Kiểm tra replication status trên replica
SHOW SLAVE STATUS\G

-- Các metrics quan trọng cần giám sát:
-- 1. Slave_IO_Running và Slave_SQL_Running (phải là "Yes")
-- 2. Seconds_Behind_Master (độ trễ replication)
-- 3. Last_IO_Error và Last_SQL_Error (lỗi replication)

-- Kiểm tra replication status
SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn,
       (pg_wal_lsn_diff(sent_lsn, replay_lsn) / 1024 / 1024) AS lag_size_mb
FROM pg_stat_replication;

-- Kiểm tra replication slot
SELECT slot_name, plugin, slot_type, active, restart_lsn
FROM pg_replication_slots;

-- MySQL: Bật parallel replication (MySQL 5.7+)
SET GLOBAL slave_parallel_type = 'LOGICAL_CLOCK';
SET GLOBAL slave_parallel_workers = 8;

-- PostgreSQL: Tăng max_wal_senders
ALTER SYSTEM SET max_wal_senders = 10;

# Cài đặt Orchestrator
docker run -d --name orchestrator -p 3000:3000 \
  -e ORCHESTRATOR_USER=orc_user \
  -e ORCHESTRATOR_PASSWORD=orc_password \
  openark/orchestrator:latest

# Cấu hình Orchestrator (orchestrator.conf.json)
{
  "MySQLTopologyCredentialsConfigFile": "/etc/orchestrator/topology.cnf",
  "MySQLOrchestratorHost": "orchestrator-db",
  "MySQLOrchestratorPort": 3306,
  "MySQLOrchestratorDatabase": "orchestrator",
  "MySQLOrchestratorUser": "orc_user",
  "MySQLOrchestratorPassword": "orc_password",
  "InstancePollSeconds": 5,
  "PromotionIgnoreHostnameFilters": [],
  "DetectClusterAliasQuery": "SELECT SUBSTRING_INDEX(@@hostname, '.', 1)",
  "DetectInstanceAliasQuery": "SELECT @@hostname",
  "PromoteMasterOnFailover": true,
  "FailMasterPromotionOnLagSeconds": 30
}

// Laravel - Cấu hình trong config/database.php
'mysql' => [
    'driver' => 'mysql',
    'url' => env('DATABASE_URL'),
    'host' => env('DB_HOST', '127.0.0.1'),
    'port' => env('DB_PORT', '3306'),
    'database' => env('DB_DATABASE', 'mydb'),
    'username' => env('DB_USERNAME', 'user'),
    'password' => env('DB_PASSWORD', 'password'),
    'unix_socket' => env('DB_SOCKET', ''),
    'charset' => 'utf8mb4',
    'collation' => 'utf8mb4_unicode_ci',
    'prefix' => '',
    'prefix_indexes' => true,
    'strict' => true,
    'engine' => null,
    // Cấu hình connection pool
    'pool' => [
        'min' => 5,     // Tương đương với minimumIdle
        'max' => 10,    // Tương đương với maximumPoolSize
    ],
    // Cấu hình timeout
    'wait_timeout' => 28800,  // Thời gian tối đa một connection có thể ở trạng thái idle
    'connect_timeout' => 10,  // Tương đương với connectionTimeout (tính bằng giây)
    'idle_timeout' => 30,     // Tương đương với idleTimeout (tính bằng giây)
    'max_lifetime' => 1800,   // Tương đương với maxLifetime (tính bằng giây)
],

// Sử dụng connection pool trong code
// Laravel tự động quản lý connection pool, bạn chỉ cần sử dụng DB facade bình thường
$results = DB::table('users')->get();

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 3
max_db_connections = 50
max_user_connections = 50

// Cài đặt package prometheus-exporter
// composer require spatie/laravel-prometheus

// Đăng ký metrics trong app/Providers/AppServiceProvider.php
public function boot()
{
    // Đăng ký metrics cho database connections
    \Spatie\Prometheus\Facades\Prometheus::addGauge(
        name: 'db_connections_active',
        help: 'Number of active database connections',
        callback: fn () => DB::getConnections()->filter->isConnected()->count()
    );

    \Spatie\Prometheus\Facades\Prometheus::addGauge(
        name: 'db_connections_total',
        help: 'Total number of database connections',
        callback: fn () => DB::getConnections()->count()
    );

    // Đăng ký metrics cho query time
    DB::listen(function ($query) {
        \Spatie\Prometheus\Facades\Prometheus::trackHistogram(
            name: 'db_query_duration_seconds',
            help: 'Database query duration in seconds',
            value: $query->time / 1000, // Convert from ms to seconds
            labels: ['connection' => $query->connectionName]
        );
    });
}

// Metrics được thu thập:
// - db_connections_active
// - db_connections_total
// - db_query_duration_seconds

-- Kết nối đến PgBouncer admin console
psql -p 6432 -U pgbouncer pgbouncer

-- Xem thống kê pools
SHOW POOLS;

-- Xem thống kê clients
SHOW CLIENTS;

-- Xem thống kê servers
SHOW SERVERS;

// Laravel - Phát hiện connection leaks

// 1. Cấu hình trong config/database.php
'mysql' => [
    // Các cấu hình khác...

    // Bật chế độ debug connection
    'dump_queries_on_error' => true,

    // Thiết lập thời gian timeout cho connections
    'idle_timeout' => 60, // 60 giây - tương đương với leakDetectionThreshold
],

// 2. Tạo middleware để theo dõi và đóng connections sau mỗi request
class CloseDbConnectionsMiddleware
{
    public function handle($request, Closure $next)
    {
        $response = $next($request);

        // Đóng tất cả các connections sau khi xử lý request
        DB::disconnect();

        return $response;
    }
}

// 3. Đăng ký middleware trong app/Http/Kernel.php
protected $middleware = [
    // Các middleware khác...
    \App\Http\Middleware\CloseDbConnectionsMiddleware::class,
];

// 4. Ghi log các connections bị leak trong AppServiceProvider
public function boot()
{
    DB::listen(function ($query) {
        // Ghi lại thời gian bắt đầu của query
        $query->start_time = microtime(true);
    });

    DB::getEventDispatcher()->listen('connection.disconnecting', function ($connection) {
        // Kiểm tra thời gian kết nối đã mở
        if (isset($connection->start_time) && (microtime(true) - $connection->start_time) > 60) {
            Log::warning('Possible DB connection leak detected', [
                'connection' => $connection->getName(),
                'duration' => microtime(true) - $connection->start_time,
                'trace' => debug_backtrace(DEBUG_BACKTRACE_IGNORE_ARGS),
            ]);
        }
    });
}

-- MySQL: Kiểm tra active connections
SHOW STATUS LIKE 'Threads_connected';

-- MySQL: Kiểm tra max_connections
SHOW VARIABLES LIKE 'max_connections';

-- PostgreSQL: Kiểm tra active connections
SELECT count(*) FROM pg_stat_activity;

-- PostgreSQL: Kiểm tra max_connections
SHOW max_connections;

# /etc/crontab

# Hàng ngày: Backup database
0 1 * * * root /usr/local/bin/backup_mysql.sh

# Hàng tuần: Optimize tables
0 3 * * 0 root /usr/local/bin/optimize_tables.sh

# Hàng ngày: Phân tích slow queries
30 1 * * * root /usr/local/bin/analyze_slow_queries.sh

# Mỗi giờ: Kiểm tra replication status
0 * * * * root /usr/local/bin/check_replication.sh

# Mỗi 5 phút: Kiểm tra database health
*/5 * * * * root /usr/local/bin/check_db_health.sh

# /usr/local/bin/backup_mysql.sh
#!/bin/bash

DATE=$(date +"%Y-%m-%d")
BACKUP_DIR="/var/backups/mysql"
DB_USER="backup_user"
DB_PASS="secure_password"
DB_HOST="localhost"

# Tạo thư mục backup nếu chưa tồn tại
mkdir -p $BACKUP_DIR

# Danh sách database cần backup (loại trừ các system databases)
DATABASES=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

# Backup từng database
for DB in $DATABASES; do
    echo "Backing up database: $DB"
    mysqldump -h $DB_HOST -u $DB_USER -p$DB_PASS --single-transaction --quick --lock-tables=false $DB | gzip > "$BACKUP_DIR/$DB-$DATE.sql.gz"
done

# Xóa các backup cũ hơn 30 ngày
find $BACKUP_DIR -name "*.sql.gz" -type f -mtime +30 -delete

echo "Backup completed at $(date)"

# /usr/local/bin/optimize_tables.sh
#!/bin/bash

DB_USER="maintenance_user"
DB_PASS="secure_password"
DB_HOST="localhost"
LOG_FILE="/var/log/mysql/optimize-$(date +"%Y-%m-%d").log"

echo "Starting table optimization at $(date)" > $LOG_FILE

# Lấy danh sách các database
DATABASES=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema|performance_schema|mysql|sys)")

for DB in $DATABASES; do
    echo "Optimizing tables in database: $DB" >> $LOG_FILE

    # Lấy danh sách các bảng trong database
    TABLES=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "USE $DB; SHOW TABLES;" | grep -v "Tables_in")

    for TABLE in $TABLES; do
        echo "  - Optimizing table: $TABLE" >> $LOG_FILE
        mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "USE $DB; OPTIMIZE TABLE $TABLE;" >> $LOG_FILE 2>&1
    done
done

echo "Table optimization completed at $(date)" >> $LOG_FILE

# /usr/local/bin/analyze_slow_queries.sh
#!/bin/bash

SLOW_LOG="/var/log/mysql/mysql-slow.log"
REPORT_DIR="/var/log/mysql/reports"
DATE=$(date +"%Y-%m-%d")

# Tạo thư mục báo cáo nếu chưa tồn tại
mkdir -p $REPORT_DIR

# Kiểm tra xem pt-query-digest đã được cài đặt chưa
if ! command -v pt-query-digest &> /dev/null; then
    echo "pt-query-digest not found. Please install Percona Toolkit."
    exit 1
fi

# Phân tích slow query log
pt-query-digest $SLOW_LOG > "$REPORT_DIR/slow-query-report-$DATE.txt"

# Tạo báo cáo tổng hợp
echo "Top 10 slow queries on $DATE:" > "$REPORT_DIR/summary-$DATE.txt"
head -n 50 "$REPORT_DIR/slow-query-report-$DATE.txt" >> "$REPORT_DIR/summary-$DATE.txt"

# Xóa các báo cáo cũ hơn 30 ngày
find $REPORT_DIR -name "*.txt" -type f -mtime +30 -delete

# Gửi email báo cáo
mail -s "MySQL Slow Query Report for $DATE" [email protected] < "$REPORT_DIR/summary-$DATE.txt"

echo "Slow query analysis completed at $(date)"

# /usr/local/bin/check_replication.sh
#!/bin/bash

DB_USER="repl_monitor"
DB_PASS="secure_password"
DB_HOST="localhost"
LOG_FILE="/var/log/mysql/replication-check.log"
ALERT_EMAIL="[email protected]"

# Kiểm tra trạng thái replication
REPL_STATUS=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SHOW SLAVE STATUS\G")

# Kiểm tra xem Slave_IO_Running và Slave_SQL_Running có đang chạy không
IO_RUNNING=$(echo "$REPL_STATUS" | grep "Slave_IO_Running" | awk '{print $2}')
SQL_RUNNING=$(echo "$REPL_STATUS" | grep "Slave_SQL_Running" | awk '{print $2}')
SECONDS_BEHIND=$(echo "$REPL_STATUS" | grep "Seconds_Behind_Master" | awk '{print $2}')

TIMESTAMP=$(date +"%Y-%m-%d %H:%M:%S")

# Ghi log
echo "$TIMESTAMP - IO: $IO_RUNNING, SQL: $SQL_RUNNING, Seconds Behind: $SECONDS_BEHIND" >> $LOG_FILE

# Gửi cảnh báo nếu có vấn đề
if [ "$IO_RUNNING" != "Yes" ] || [ "$SQL_RUNNING" != "Yes" ]; then
    echo "ALERT: Replication is not running properly!" | mail -s "MySQL Replication Alert" $ALERT_EMAIL
fi

# Cảnh báo nếu replication lag quá lớn
if [ "$SECONDS_BEHIND" != "NULL" ] && [ $SECONDS_BEHIND -gt 300 ]; then
    echo "ALERT: Replication lag is $SECONDS_BEHIND seconds!" | mail -s "MySQL Replication Lag Alert" $ALERT_EMAIL
fi

# /usr/local/bin/check_db_health.sh
#!/bin/bash

DB_USER="monitor_user"
DB_PASS="secure_password"
DB_HOST="localhost"
LOG_FILE="/var/log/mysql/health-check.log"
ALERT_EMAIL="[email protected]"
TIMESTAMP=$(date +"%Y-%m-%d %H:%M:%S")

# Kiểm tra xem MySQL có đang chạy không
if ! mysqladmin -h $DB_HOST -u $DB_USER -p$DB_PASS ping &>/dev/null; then
    echo "$TIMESTAMP - CRITICAL: MySQL server is not responding!" >> $LOG_FILE
    echo "MySQL server on $DB_HOST is not responding!" | mail -s "CRITICAL: MySQL Down" $ALERT_EMAIL
    exit 1
fi

# Kiểm tra số lượng connections
CONNECTIONS=$(mysqladmin -h $DB_HOST -u $DB_USER -p$DB_PASS status | awk '{print $4}')
MAX_CONNECTIONS=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SHOW VARIABLES LIKE 'max_connections';" | awk 'NR==2 {print $2}')
CONN_PERCENT=$((CONNECTIONS * 100 / MAX_CONNECTIONS))

# Kiểm tra dung lượng bộ nhớ
MEM_USAGE=$(mysql -h $DB_HOST -u $DB_USER -p$DB_PASS -e "SELECT ROUND((SUM(data_length + index_length) / 1024 / 1024), 2) as 'DB Size (MB)' FROM information_schema.TABLES;" | awk 'NR==2 {print $1}')

# Ghi log
echo "$TIMESTAMP - Connections: $CONNECTIONS/$MAX_CONNECTIONS ($CONN_PERCENT%), Memory: $MEM_USAGE MB" >> $LOG_FILE

# Cảnh báo nếu số lượng connections quá cao
if [ $CONN_PERCENT -gt 80 ]; then
    echo "WARNING: High connection usage: $CONNECTIONS/$MAX_CONNECTIONS ($CONN_PERCENT%)" | mail -s "MySQL Connection Alert" $ALERT_EMAIL
fi

# Kiểm tra các bảng bị hỏng
CORRUPT_TABLES=$(mysqlcheck -h $DB_HOST -u $DB_USER -p$DB_PASS --all-databases --check | grep -v "OK" | grep -v "Table" | wc -l)

if [ $CORRUPT_TABLES -gt 0 ]; then
    echo "$TIMESTAMP - CRITICAL: Found $CORRUPT_TABLES corrupted tables!" >> $LOG_FILE
    mysqlcheck -h $DB_HOST -u $DB_USER -p$DB_PASS --all-databases --check | grep -v "OK" | mail -s "MySQL Corrupt Tables Alert" $ALERT_EMAIL
fi

# database_maintenance.yml
---
- name: Database Maintenance Tasks
  hosts: database_servers
  become: yes
  tasks:
    - name: Check MySQL service status
      service_facts:
      register: service_state

    - name: Ensure MySQL is running
      service:
        name: mysql
        state: started
      when: service_state.ansible_facts.services['mysql.service'] is defined and service_state.ansible_facts.services['mysql.service'].state != "running"

    - name: Check disk space
      shell: df -h /var/lib/mysql
      register: disk_space

    - name: Alert if disk space is low
      debug:
        msg: "WARNING: Low disk space on {{ inventory_hostname }}"
      when: disk_space.stdout is regex("([7-9][0-9]|100)%")

    - name: Optimize MySQL tables
      mysql_db:
        state: import
        name: all
        target: /tmp/optimize.sql
      when: ansible_date_time.weekday == "Sunday"

    - name: Backup MySQL databases
      mysql_db:
        state: dump
        name: all
        target: "/backup/mysql/{{ ansible_date_time.date }}_full.sql"

# alertmanager.yml
receivers:
  - name: "database-team"
    webhook_configs:
      - url: "http://automation-server:8080/api/trigger/database-maintenance"
        send_resolved: true

route:
  group_by: ["alertname", "instance"]
  group_wait: 30s
  group_interval: 5m
  repeat_interval: 4h
  receiver: "database-team"
  routes:
    - match:
        severity: critical
      receiver: "database-team"

from flask import Flask, request, jsonify
import subprocess
import json

app = Flask(__name__)

@app.route('/api/trigger/database-maintenance', methods=['POST'])
def trigger_maintenance():
    alert_data = request.json

    # Xử lý các loại alerts khác nhau
    for alert in alert_data['alerts']:
        if alert['labels']['alertname'] == 'HighDiskUsage':
            # Trigger cleanup script
            instance = alert['labels']['instance']
            subprocess.run(['/usr/local/bin/cleanup_database.sh', instance])

        elif alert['labels']['alertname'] == 'SlowQueries':
            # Trigger query optimization
            instance = alert['labels']['instance']
            subprocess.run(['/usr/local/bin/optimize_slow_queries.sh', instance])

        elif alert['labels']['alertname'] == 'ReplicationLag':
            # Trigger replication fix
            instance = alert['labels']['instance']
            subprocess.run(['/usr/local/bin/fix_replication_lag.sh', instance])

    return jsonify({'status': 'success', 'message': 'Maintenance tasks triggered'})

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=8080)