# 建议设置为可用内存的70-80%
innodb_buffer_pool_size = 4G

# 缓冲池实例数，提高并发性能
innodb_buffer_pool_instances = 8

# 缓冲池预热，避免重启后性能骤降
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

query_cache_type = 1
query_cache_size = 64M
query_cache_limit = 2M

# 启用二进制日志用于主从复制和点恢复
log-bin = mysql-bin

# 二进制日志格式，建议使用ROW格式
binlog_format = ROW

# 日志过期时间，避免占用过多磁盘空间
expire_logs_days = 7

# 同步策略，0=由操作系统决定，1=每次事务提交都同步
sync_binlog = 1

# 启用慢查询日志
slow_query_log = 1

# 慢查询阈值（秒）
long_query_time = 2

# 记录未使用索引的查询
log_queries_not_using_indexes = 1

-- 分析查询执行计划
EXPLAIN SELECT * FROM users WHERE email = 'example@domain.com';

-- 更详细的分析（MySQL 8.0+）
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 12345;

-- 低效的分页方式（大偏移量时性能差）
SELECT * FROM products LIMIT 10000, 20;

-- 优化方案：使用游标分页
SELECT * FROM products WHERE id > 10000 ORDER BY id LIMIT 20;

-- 确保JOIN列有索引
ALTER TABLE orders ADD INDEX idx_customer_id (customer_id);

-- 小表驱动大表原则
-- 将小结果集的表放在JOIN前面

-- 使用批量插入替代单条插入
INSERT INTO users (name, email) VALUES
('user1', 'user1@example.com'),
('user2', 'user2@example.com'),
('user3', 'user3@example.com');

-- 禁用自动提交，减少事务开销
SET autocommit = 0;
-- 执行批量操作
COMMIT;

-- 创建按时间范围分区的订单表
CREATE TABLE orders (
    id INT NOT NULL,
    order_date DATE NOT NULL,
    customer_id INT,
    amount DECIMAL(10,2),
    PRIMARY KEY (id, order_date)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION p2026 VALUES LESS THAN (2027),
    PARTITION pfuture VALUES LESS THAN MAXVALUE
);

# 示例：2GB内存VPS的MySQL配置
innodb_buffer_pool_size = 1G        # 50%内存
key_buffer_size = 32M               # MyISAM索引缓存
query_cache_size = 0                # MySQL 8.0已移除
tmp_table_size = 64M                # 临时表大小
max_heap_table_size = 64M           # 内存表大小

# 增加InnoDB缓冲池实例，提高并发
innodb_buffer_pool_instances = 8

# 调整并发线程数
innodb_thread_concurrency = 0  # 0表示不限制，由MySQL自动调整

# 并行查询（MySQL 8.0+）
SET SESSION optimizer_switch='derived_merge=on';

-- 主服务器配置
server-id = 1
log-bin = mysql-bin
binlog-do-db = mydatabase

-- 从服务器配置
server-id = 2
relay-log = mysql-relay-bin
read-only = 1

#!/bin/bash
# MySQL自动备份脚本

BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="mydatabase"

# 使用xtrabackup进行热备份
xtrabackup --backup --target-dir=$BACKUP_DIR/$DATE

# 压缩备份文件
tar -czf $BACKUP_DIR/$DATE.tar.gz $BACKUP_DIR/$DATE

# 删除7天前的备份
find $BACKUP_DIR -name "*.tar.gz" -mtime +7 -delete

-- 创建应用专用用户，遵循最小权限原则
CREATE USER 'app_user'@'localhost' IDENTIFIED BY 'StrongPassword123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydatabase.* TO 'app_user'@'localhost';

-- 禁止root远程登录
DELETE FROM mysql.user WHERE User='root' AND Host!='localhost';
FLUSH PRIVILEGES;

-- 查看慢查询日志
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

-- 使用pt-query-digest分析
pt-query-digest /var/log/mysql/slow.log

-- 查看当前锁等待情况
SELECT * FROM performance_schema.data_lock_waits;

-- 查找阻塞的查询
SELECT 
    r.trx_id AS waiting_trx_id,
    r.trx_mysql_thread_id AS waiting_thread,
    r.trx_query AS waiting_query,
    b.trx_id AS blocking_trx_id,
    b.trx_mysql_thread_id AS blocking_thread,
    b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_trx_id;

-- 查看当前连接数
SHOW STATUS LIKE 'Threads_connected';

-- 查看连接详情
SHOW PROCESSLIST;

-- 终止空闲连接
KILL CONNECTION <connection_id>;