mysql

用途

MySQL 技能用于在开发和生产环境中管理、优化和保护 MySQL 与 MariaDB 数据库。当需要成熟且被广泛支持的关系型数据库、技术栈依赖 MySQL 特有功能或兼容性（如 WordPress、Magento 及众多 PHP 框架）、需要搭建源-副本复制以实现读扩展，或希望针对高吞吐事务负载调优 InnoDB 时，可使用本技能。

主要能力

安装与初始化：在 Debian/Ubuntu 或 RHEL/Amazon Linux 上安装 MySQL 8，运行安全安装向导，创建数据库与应用用户并授权。
mysql CLI 操作：连接数据库、执行单条语句、导入 SQL 文件、导出查询结果为 CSV，以及查看数据库、表结构、进程列表和 InnoDB 状态。
配置调优：调整网络、InnoDB、日志、二进制日志和字符集等参数，并可在运行时验证设置。
备份与恢复：使用 mysqldump 进行逻辑备份（单库、全库、压缩），使用 Percona XtraBackup 进行物理全量与增量备份及恢复。
源-副本复制：配置主库二进制日志与复制用户，配置副本并启动复制，验证复制状态。
监控查询：查看连接统计、InnoDB 缓冲池命中率、慢查询、表大小和复制延迟。
Docker Compose 部署：通过 docker-compose 启动 MySQL 与 phpMyAdmin，并配置健康检查。
维护任务：优化、分析、检查与修复表，轮转慢查询日志。
故障排查：针对连接过多、查询普遍变慢、复制停止、表满、锁等待超时和高 IOPS 等问题给出可能原因与修复方法。

使用方式

前置条件包括：Linux 服务器（Debian/Ubuntu 或 RHEL 系）或 Docker；安装软件包所需的 root 或 sudo 权限；熟悉 SQL 基础。

安装示例（Debian/Ubuntu — MySQL 8）：

sudo apt update
sudo apt install -y mysql-server

RHEL / Amazon Linux：

sudo dnf install -y mysql-server
sudo systemctl enable --now mysqld

运行安全安装向导：

sudo mysql_secure_installation

验证：

mysql --version
sudo systemctl status mysql

初始用户与数据库设置：

sudo mysql -u root -p

CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'myapp'@'%' IDENTIFIED BY 'strong_password_here';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'myapp'@'%';
FLUSH PRIVILEGES;
SHOW GRANTS FOR 'myapp'@'%';

mysql CLI 参考：

mysql -u myapp -p -h 127.0.0.1 mydb
mysql -u myapp -p -e "SELECT COUNT(*) FROM orders;" mydb
mysql -u myapp -p mydb < schema.sql
mysql -u myapp -p -e "SELECT * FROM users" mydb | tr '\t' ',' > users.csv

在 mysql shell 内可执行：

SHOW DATABASES;
USE mydb;
SHOW TABLES;
DESCRIBE users;
SHOW CREATE TABLE users\G
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS\G

配置调优：编辑 /etc/mysql/mysql.conf.d/mysqld.cnf（RHEL 上为 /etc/my.cnf），示例参数包括 bind-address、max_connections、wait_timeout、interactive_timeout、innodb_buffer_pool_size、innodb_buffer_pool_instances、innodb_log_file_size、innodb_flush_log_at_trx_commit、innodb_flush_method、innodb_io_capacity、innodb_io_capacity_max、slow_query_log、long_query_time、server-id、log_bin、binlog_expire_logs_seconds、sync_binlog、character-set-server、collation-server 等。应用更改后重启服务，并可在运行时验证：

sudo systemctl restart mysql
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

备份与恢复（mysqldump）：

mysqldump -u root -p --single-transaction --routines --triggers mydb > /backups/mydb_$(date +%F).sql
mysqldump -u root -p --all-databases --single-transaction > /backups/all_$(date +%F).sql
mysqldump -u root -p --single-transaction mydb | gzip > /backups/mydb_$(date +%F).sql.gz
mysql -u root -p mydb < /backups/mydb_2025-01-15.sql
gunzip < /backups/mydb_2025-01-15.sql.gz | mysql -u root -p mydb

物理备份（Percona XtraBackup）：

sudo apt install -y percona-xtrabackup-80
xtrabackup --backup --user=root --password=secret --target-dir=/backups/full_$(date +%F)
xtrabackup --prepare --target-dir=/backups/full_2025-01-15
sudo systemctl stop mysql
sudo rm -rf /var/lib/mysql/*
xtrabackup --move-back --target-dir=/backups/full_2025-01-15
sudo chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

增量备份：

xtrabackup --backup --user=root --password=secret --target-dir=/backups/inc_$(date +%F) --incremental-basedir=/backups/full_2025-01-15
xtrabackup --prepare --apply-log-only --target-dir=/backups/full_2025-01-15
xtrabackup --prepare --target-dir=/backups/full_2025-01-15 --incremental-dir=/backups/inc_2025-01-16

源-副本复制：主库配置 server-id、log_bin、binlog_format=ROW，创建复制用户并获取二进制日志位置：

CREATE USER 'replicator'@'10.0.0.%' IDENTIFIED BY 'repl_secret';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.0.0.%';
FLUSH PRIVILEGES;
SHOW MASTER STATUS\G

副本配置 server-id、relay_log、read_only=ON，并指向主库：

CHANGE REPLICATION SOURCE TO
  SOURCE_HOST     = '10.0.0.1',
  SOURCE_USER     = 'replicator',
  SOURCE_PASSWORD = 'repl_secret',
  SOURCE_LOG_FILE = 'mysql-bin.000003',
  SOURCE_LOG_POS  = 154;
START REPLICA;
SHOW REPLICA STATUS\G

检查 Replica_IO_Running、Replica_SQL_Running 与 Seconds_Behind_Source。

监控查询示例包括连接统计、InnoDB 缓冲池命中率、慢查询、表大小和复制延迟。

Docker Compose 部署：通过 docker-compose.yml 定义 mysql 与 phpmyadmin 服务，配置环境变量、卷、命令参数和健康检查，然后执行：

docker compose up -d
mysql -h 127.0.0.1 -u myapp -psecret mydb

维护任务：

mysql -u root -p -e "OPTIMIZE TABLE mydb.orders;"
mysql -u root -p -e "ANALYZE TABLE mydb.orders;"
mysql -u root -p -e "CHECK TABLE mydb.orders;"
mysql -u root -p -e "REPAIR TABLE mydb.orders;"
sudo mv /var/log/mysql/slow.log /var/log/mysql/slow.log.old
mysqladmin -u root -p flush-logs

故障排查覆盖：连接过多、查询普遍变慢、复制停止、表满、锁等待超时、高 IOPS/磁盘使用等，并给出可能原因与修复方法。

适用场景

需要成熟且广泛支持的关系型数据库。
技术栈依赖 MySQL 特有功能或兼容性（WordPress、Magento、众多 PHP 框架）。
搭建源-副本复制以实现读扩展。
针对高吞吐事务负载调优 InnoDB。

限制

资料未提供关于收费、价格、平台支持范围、性能效果保证或具体版本兼容性之外的额外限制说明。部分参数（如 innodb_log_file_size 调整）需要重启服务；OPTIMIZE TABLE 会短暂锁表；查询缓存已在 MySQL 8 中禁用，需改用 ProxySQL 或应用层缓存。

技能介绍

用途

主要能力

使用方式

适用场景

限制