MySQL运维：数据库管理实战

MySQL安装与配置

Docker安装

# 运行MySQL容器
docker run -d \
  --name mysql \
  -p 3306:3306 \
  -e MYSQL_ROOT_PASSWORD=rootpass \
  -e MYSQL_DATABASE=myapp \
  -v mysql_data:/var/lib/mysql \
  -v mysql_conf:/etc/mysql/conf.d \
  mysql:8.0

# 连接MySQL
docker exec -it mysql mysql -uroot -p

配置文件

# /etc/mysql/my.cnf
[mysqld]
# 基础配置
bind-address = 0.0.0.0
port = 3306
max_connections = 1000
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# InnoDB配置
innodb_buffer_pool_size = 4G
innodb_log_file_size = 1G
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT

# 日志配置
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2
log_error = /var/log/mysql/error.log

# 复制配置
server-id = 1
log_bin = /var/log/mysql/mysql-bin
binlog_format = ROW
expire_logs_days = 7

用户与权限管理

-- 创建用户
CREATE USER 'app_user'@'%' IDENTIFIED BY 'SecurePass123!';

-- 授权
GRANT ALL PRIVILEGES ON myapp.* TO 'app_user'@'%';

-- 只读用户
CREATE USER 'readonly'@'%' IDENTIFIED BY 'ReadPass123!';
GRANT SELECT ON myapp.* TO 'readonly'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看权限
SHOW GRANTS FOR 'app_user'@'%';

备份与恢复

逻辑备份

# 备份单个数据库
mysqldump -uroot -p myapp > myapp_backup.sql

# 备份所有数据库
mysqldump -uroot -p --all-databases > all_backup.sql

# 带时间戳备份
mysqldump -uroot -p myapp | gzip > "myapp_$(date +%Y%m%d).sql.gz"

# 恢复
mysql -uroot -p myapp < myapp_backup.sql
gunzip < myapp_backup.sql.gz | mysql -uroot -p myapp

物理备份（xtrabackup）

# 安装xtrabackup
sudo yum install percona-xtrabackup-80

# 全量备份
xtrabackup --backup --target-dir=/backup/full

# 增量备份
xtrabackup --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/full

# 恢复
xtrabackup --prepare --target-dir=/backup/full
xtrabackup --copy-back --target-dir=/backup/full

自动备份脚本

#!/bin/bash
# /opt/scripts/mysql-backup.sh

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

# 创建备份
mysqldump -uroot -p"${MYSQL_ROOT_PASSWORD}" \
    --all-databases \
    --single-transaction \
    --routines \
    --triggers \
    | gzip > "${BACKUP_DIR}/full_${DATE}.sql.gz"

# 清理旧备份
find ${BACKUP_DIR} -name "*.sql.gz" -mtime +${RETENTION_DAYS} -delete

echo "Backup completed: ${DATE}"

性能优化

慢查询分析

# 启用慢查询日志
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

# 分析慢查询
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

索引优化

-- 查看表索引
SHOW INDEX FROM users;

-- 分析查询
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 添加索引
CREATE INDEX idx_email ON users(email);

-- 查看索引使用情况
SELECT * FROM sys.schema_unused_indexes;

连接池配置

# 应用程序连接池配置
max_pool_size = 20
min_idle = 5
connection_timeout = 30000
idle_timeout = 600000

高可用架构

主从复制

-- 主库配置
CHANGE MASTER TO
    MASTER_HOST='master_host',
    MASTER_USER='repl_user',
    MASTER_PASSWORD='repl_pass',
    MASTER_LOG_FILE='mysql-bin.000001',
    MASTER_LOG_POS=0;

START SLAVE;
SHOW SLAVE STATUS\G

组复制

-- 启用组复制
SET GLOBAL group_replication_group_name="aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee";
SET GLOBAL group_replication_start_on_boot=OFF;
SET GLOBAL group_replication_local_address="node1:33061";
SET GLOBAL group_replication_group_seeds="node1:33061,node2:33061,node3:33061";
SET GLOBAL group_replication_single_primary_mode=ON;

START GROUP_REPLICATION;

监控与诊断

-- 查看连接状态
SHOW STATUS LIKE 'Threads_connected';

-- 查看查询统计
SHOW STATUS LIKE 'Queries';

-- 查看InnoDB状态
SHOW ENGINE INNODB STATUS\G

-- 查看进程
SHOW PROCESSLIST;

-- 杀死慢查询
KILL <process_id>;

常用运维命令

# 连接MySQL
mysql -uroot -p -h localhost

# 导入SQL文件
source /path/to/file.sql

# 查看数据库大小
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;

# 查看表大小
SELECT table_name AS 'Table',
       ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'myapp'
ORDER BY (data_length + index_length) DESC;

总结

MySQL运维需要掌握安装配置、备份恢复、性能优化和高可用架构。建立完善的监控和备份策略，是保障数据库可靠性的关键。