MySQL运维管理

安装MySQL

# Ubuntu/Debian
sudo apt-get install mysql-server
sudo mysql_secure_installation

# CentOS/RHEL
sudo yum install mysql-server
sudo systemctl start mysqld
sudo mysql_secure_installation

# Docker
docker run -d --name mysql \
    -e MYSQL_ROOT_PASSWORD=secret \
    -v mysql_data:/var/lib/mysql \
    -p 3306:3306 \
    mysql:8

基本操作

# 登录
mysql -u root -p
mysql -u user -p -h host database

# 查看数据库
SHOW DATABASES;

# 使用数据库
USE mydb;

# 查看表
SHOW TABLES;

# 查看表结构
DESCRIBE users;

用户管理

-- 创建用户
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

-- 授权
GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';
GRANT SELECT, INSERT ON mydb.* TO 'readonly'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

-- 查看权限
SHOW GRANTS FOR 'newuser'@'localhost';

-- 删除用户
DROP USER 'newuser'@'localhost';

备份与恢复

mysqldump

# 备份单个数据库
mysqldump -u root -p mydb > mydb_backup.sql

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

# 备份特定表
mysqldump -u root -p mydb users > users_backup.sql

# 带时间戳备份
mysqldump -u root -p mydb > "mydb_$(date +%Y%m%d).sql"

恢复

# 恢复数据库
mysql -u root -p mydb < mydb_backup.sql

# 从压缩文件恢复
gunzip < mydb_backup.sql.gz | mysql -u root -p mydb

自动备份脚本

#!/bin/bash
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
KEEP_DAYS=7

mkdir -p $BACKUP_DIR

# 备份所有数据库
mysqldump -u root --single-transaction --routines --triggers \
    --all-databases | gzip > "$BACKUP_DIR/all_$DATE.sql.gz"

# 清理旧备份
find $BACKUP_DIR -name "*.gz" -mtime +$KEEP_DAYS -delete

echo "Backup completed: $DATE"

配置优化

# /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
# 基础设置
bind-address = 0.0.0.0
port = 3306
max_connections = 500

# InnoDB设置
innodb_buffer_pool_size = 4G
innodb_log_file_size = 256M
innodb_flush_log_at_trx_commit = 2
innodb_io_capacity = 2000

# 查询缓存（MySQL 8.0已移除）
# query_cache_type = 1
# query_cache_size = 64M

# 慢查询日志
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 2

性能监控

-- 查看状态
SHOW STATUS LIKE 'Threads%';
SHOW STATUS LIKE 'Innodb_buffer_pool%';

-- 查看进程
SHOW PROCESSLIST;

-- 查看查询缓存
SHOW STATUS LIKE 'Qcache%';

-- 查看慢查询
SHOW VARIABLES LIKE 'slow_query%';

索引优化

-- 查看索引
SHOW INDEX FROM users;

-- 创建索引
CREATE INDEX idx_email ON users(email);

-- 复合索引
CREATE INDEX idx_name_email ON users(name, email);

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

实践：数据库监控

-- 创建监控用户
CREATE USER 'monitor'@'localhost' IDENTIFIED BY 'password';
GRANT PROCESS, REPLICATION CLIENT ON *.* TO 'monitor'@'localhost';

-- 查看复制状态
SHOW MASTER STATUS;
SHOW SLAVE STATUS;

总结

MySQL运维包括安装配置、用户管理、备份恢复和性能优化等多个方面。建立完善的运维体系，可以确保数据库的稳定运行。