Back to Details

mysql

Compare original and translation side by side

🇺🇸

Original

English
🇨🇳

Translation

Chinese

MySQL / MariaDB

MySQL / MariaDB

Administer, optimize, and secure MySQL and MariaDB databases in development and production environments.
在开发和生产环境中管理、优化并保障MySQL和MariaDB数据库的安全。

When to Use

使用场景

  • You need a mature, widely supported relational database.
  • Your stack depends on MySQL-specific features or compatibility (WordPress, Magento, many PHP frameworks).
  • You are setting up source-replica replication for read scaling.
  • You want to tune InnoDB for high-throughput transactional workloads.
  • 你需要一个成熟、被广泛支持的关系型数据库。
  • 你的技术栈依赖MySQL特定功能或兼容性（如WordPress、Magento、众多PHP框架）。
  • 你正在搭建源-副本复制以实现读取扩展。
  • 你希望针对高吞吐量事务工作负载调优InnoDB。

Prerequisites

前置条件

  • Linux server (Debian/Ubuntu or RHEL-based) or Docker.
  • Root or sudo access for package installation.
  • Familiarity with SQL fundamentals.
  • Linux服务器（Debian/Ubuntu或基于RHEL的系统）或Docker环境。
  • 具备Root或sudo权限以安装软件包。
  • 熟悉SQL基础知识。

Installation and Setup

安装与配置

bash
undefined
bash
undefined

Debian / Ubuntu — MySQL 8

Debian / Ubuntu — MySQL 8

sudo apt update sudo apt install -y mysql-server
sudo apt update sudo apt install -y mysql-server

RHEL / Amazon Linux

RHEL / Amazon Linux

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

Run the secure installation wizard

Run the secure installation wizard

sudo mysql_secure_installation
sudo mysql_secure_installation

Prompts: set root password, remove anonymous users, disable remote root, remove test db

Prompts: set root password, remove anonymous users, disable remote root, remove test db

Verify

Verify

mysql --version sudo systemctl status mysql
undefined
mysql --version sudo systemctl status mysql
undefined

Initial User and Database Setup

初始用户与数据库配置

bash
sudo mysql -u root -p
sql
-- Create a database
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create an application user with strong auth
CREATE USER 'myapp'@'%' IDENTIFIED BY 'strong_password_here';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'myapp'@'%';
FLUSH PRIVILEGES;

-- Verify
SHOW GRANTS FOR 'myapp'@'%';
bash
sudo mysql -u root -p
sql
-- Create a database
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create an application user with strong auth
CREATE USER 'myapp'@'%' IDENTIFIED BY 'strong_password_here';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'myapp'@'%';
FLUSH PRIVILEGES;

-- Verify
SHOW GRANTS FOR 'myapp'@'%';

mysql CLI Reference

mysql命令行参考

bash
undefined
bash
undefined

Connect

Connect

mysql -u myapp -p -h 127.0.0.1 mydb
mysql -u myapp -p -h 127.0.0.1 mydb

Execute a single statement

Execute a single statement

mysql -u myapp -p -e "SELECT COUNT(*) FROM orders;" mydb
mysql -u myapp -p -e "SELECT COUNT(*) FROM orders;" mydb

Import a SQL file

Import a SQL file

mysql -u myapp -p mydb < schema.sql
mysql -u myapp -p mydb < schema.sql

Export query results to CSV

Export query results to CSV

mysql -u myapp -p -e "SELECT * FROM users" mydb
| tr '\t' ',' > users.csv
undefined
-- Inside the mysql shell SHOW DATABASES; USE mydb; SHOW TABLES; DESCRIBE users; SHOW CREATE TABLE users\G SHOW PROCESSLIST; SHOW ENGINE INNODB STATUS\G
undefined
mysql -u myapp -p -e "SELECT * FROM users" mydb
| tr '\t' ',' > users.csv
undefined
-- Inside the mysql shell SHOW DATABASES; USE mydb; SHOW TABLES; DESCRIBE users; SHOW CREATE TABLE users\G SHOW PROCESSLIST; SHOW ENGINE INNODB STATUS\G
undefined

Configuration Tuning

配置调优

Edit 
/etc/mysql/mysql.conf.d/mysqld.cnf
(or 
/etc/my.cnf
on RHEL).
ini
[mysqld]
编辑
/etc/mysql/mysql.conf.d/mysqld.cnf
（RHEL系统为
/etc/my.cnf
）。
ini
[mysqld]

-- Networking --

-- Networking --

bind-address = 0.0.0.0 max_connections = 300 wait_timeout = 600 interactive_timeout = 600
bind-address = 0.0.0.0 max_connections = 300 wait_timeout = 600 interactive_timeout = 600

-- InnoDB (most impactful settings) --

-- InnoDB (most impactful settings) --

innodb_buffer_pool_size = 4G # ~70% of RAM on a dedicated server innodb_buffer_pool_instances = 4 # 1 per GB of pool (up to 64) innodb_log_file_size = 1G innodb_flush_log_at_trx_commit = 1 # 1 = ACID; 2 = faster, slight risk innodb_flush_method = O_DIRECT # avoids double buffering on Linux innodb_io_capacity = 2000 # raise for SSD innodb_io_capacity_max = 4000
innodb_buffer_pool_size = 4G # ~70% of RAM on a dedicated server innodb_buffer_pool_instances = 4 # 1 per GB of pool (up to 64) innodb_log_file_size = 1G innodb_flush_log_at_trx_commit = 1 # 1 = ACID; 2 = faster, slight risk innodb_flush_method = O_DIRECT # avoids double buffering on Linux innodb_io_capacity = 2000 # raise for SSD innodb_io_capacity_max = 4000

-- Query cache (disabled in MySQL 8, use ProxySQL or app cache) --

-- Query cache (disabled in MySQL 8, use ProxySQL or app cache) --

query_cache_type = 0

query_cache_type = 0

-- Logging --

-- Logging --

slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_error = /var/log/mysql/error.log
slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log long_query_time = 1 log_error = /var/log/mysql/error.log

-- Binary log (required for replication) --

-- Binary log (required for replication) --

server-id = 1 log_bin = /var/log/mysql/mysql-bin binlog_expire_logs_seconds = 604800 # 7 days sync_binlog = 1
server-id = 1 log_bin = /var/log/mysql/mysql-bin binlog_expire_logs_seconds = 604800 # 7 days sync_binlog = 1

-- Character set --

-- Character set --

character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci

```bash
character-set-server = utf8mb4 collation-server = utf8mb4_unicode_ci

```bash

Apply changes

Apply changes

sudo systemctl restart mysql
sudo systemctl restart mysql

Verify a setting at runtime

Verify a setting at runtime

mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
undefined
mysql -u root -p -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
undefined

Backup and Restore

备份与恢复

Logical Backups with mysqldump

使用mysqldump进行逻辑备份

bash
undefined
bash
undefined

Single database

Single database

mysqldump -u root -p --single-transaction --routines --triggers
mydb > /backups/mydb_$(date +%F).sql
mysqldump -u root -p --single-transaction --routines --triggers
mydb > /backups/mydb_$(date +%F).sql

All databases

All databases

mysqldump -u root -p --all-databases --single-transaction \
/backups/all_$(date +%F).sql
mysqldump -u root -p --all-databases --single-transaction \
/backups/all_$(date +%F).sql

Compressed backup

Compressed backup

mysqldump -u root -p --single-transaction mydb
| gzip > /backups/mydb_$(date +%F).sql.gz
mysqldump -u root -p --single-transaction mydb
| gzip > /backups/mydb_$(date +%F).sql.gz

Restore

Restore

mysql -u root -p mydb < /backups/mydb_2025-01-15.sql
mysql -u root -p mydb < /backups/mydb_2025-01-15.sql

Restore compressed

Restore compressed

gunzip < /backups/mydb_2025-01-15.sql.gz | mysql -u root -p mydb
undefined
gunzip < /backups/mydb_2025-01-15.sql.gz | mysql -u root -p mydb
undefined

Physical Backups with Percona XtraBackup

使用Percona XtraBackup进行物理备份

bash
undefined
bash
undefined

Install

Install

sudo apt install -y percona-xtrabackup-80
sudo apt install -y percona-xtrabackup-80

Full backup

Full backup

xtrabackup --backup --user=root --password=secret
--target-dir=/backups/full_$(date +%F)
xtrabackup --backup --user=root --password=secret
--target-dir=/backups/full_$(date +%F)

Prepare the backup (apply redo logs)

Prepare the backup (apply redo logs)

xtrabackup --prepare --target-dir=/backups/full_2025-01-15
xtrabackup --prepare --target-dir=/backups/full_2025-01-15

Restore (stop MySQL first)

Restore (stop MySQL first)

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
undefined
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
undefined

Incremental Backup with XtraBackup

使用XtraBackup进行增量备份

bash
undefined
bash
undefined

Incremental based on the full backup

Incremental based on the full backup

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

Prepare: apply full, then incremental

Prepare: apply full, then incremental

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
undefined
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
undefined

Source-Replica Replication

源-副本复制

Source (Primary)

源服务器（主节点）

ini
undefined
ini
undefined

/etc/mysql/mysql.conf.d/mysqld.cnf

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin binlog_format = ROW

```sql
-- Create replication user
CREATE USER 'replicator'@'10.0.0.%' IDENTIFIED BY 'repl_secret';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.0.0.%';
FLUSH PRIVILEGES;

-- Get current binary log position
SHOW MASTER STATUS\G
[mysqld] server-id = 1 log_bin = /var/log/mysql/mysql-bin binlog_format = ROW

```sql
-- Create replication user
CREATE USER 'replicator'@'10.0.0.%' IDENTIFIED BY 'repl_secret';
GRANT REPLICATION SLAVE ON *.* TO 'replicator'@'10.0.0.%';
FLUSH PRIVILEGES;

-- Get current binary log position
SHOW MASTER STATUS\G

Replica

副本服务器

ini
undefined
ini
undefined

/etc/mysql/mysql.conf.d/mysqld.cnf

/etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld] server-id = 2 relay_log = /var/log/mysql/relay-bin read_only = ON

```sql
-- Point replica to source (use SHOW MASTER STATUS values)
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;

-- Verify
SHOW REPLICA STATUS\G
-- Check: Replica_IO_Running = Yes, Replica_SQL_Running = Yes, Seconds_Behind_Source = 0
[mysqld] server-id = 2 relay_log = /var/log/mysql/relay-bin read_only = ON

```sql
-- Point replica to source (use SHOW MASTER STATUS values)
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;

-- Verify
SHOW REPLICA STATUS\G
-- Check: Replica_IO_Running = Yes, Replica_SQL_Running = Yes, Seconds_Behind_Source = 0

Monitoring Queries

监控查询

sql
-- Connection statistics
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- InnoDB buffer pool hit ratio (should be > 99%)
SELECT
  ROUND(100 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  ) * 100, 2) AS buffer_pool_hit_pct;

-- Top 10 slow queries (requires performance_schema)
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1e12 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- Table sizes
SELECT table_name,
       ROUND(data_length / 1024 / 1024, 2) AS data_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length DESC;

-- Check replication lag
SHOW REPLICA STATUS\G
-- Look at Seconds_Behind_Source
sql
-- Connection statistics
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- InnoDB buffer pool hit ratio (should be > 99%)
SELECT
  ROUND(100 - (
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') /
    (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
  ) * 100, 2) AS buffer_pool_hit_pct;

-- Top 10 slow queries (requires performance_schema)
SELECT DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT / 1e12 AS avg_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 10;

-- Table sizes
SELECT table_name,
       ROUND(data_length / 1024 / 1024, 2) AS data_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'mydb'
ORDER BY data_length DESC;

-- Check replication lag
SHOW REPLICA STATUS\G
-- Look at Seconds_Behind_Source

Docker Compose Setup

Docker Compose部署

yaml
undefined
yaml
undefined

docker-compose.yml

docker-compose.yml

version: "3.9"
services: mysql: image: mysql:8.0 restart: unless-stopped ports: - "3306:3306" environment: MYSQL_ROOT_PASSWORD: rootpass MYSQL_DATABASE: mydb MYSQL_USER: myapp MYSQL_PASSWORD: secret volumes: - mysql_data:/var/lib/mysql - ./init.sql:/docker-entrypoint-initdb.d/init.sql command: > --innodb-buffer-pool-size=512M --max-connections=200 --slow-query-log=ON --long-query-time=1 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci healthcheck: test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-prootpass"] interval: 10s timeout: 5s retries: 5
phpmyadmin: image: phpmyadmin:latest restart: unless-stopped ports: - "8080:80" environment: PMA_HOST: mysql PMA_USER: root PMA_PASSWORD: rootpass depends_on: mysql: condition: service_healthy
volumes: mysql_data:

```bash
docker compose up -d
mysql -h 127.0.0.1 -u myapp -psecret mydb
version: "3.9"
services: mysql: image: mysql:8.0 restart: unless-stopped ports: - "3306:3306" environment: MYSQL_ROOT_PASSWORD: rootpass MYSQL_DATABASE: mydb MYSQL_USER: myapp MYSQL_PASSWORD: secret volumes: - mysql_data:/var/lib/mysql - ./init.sql:/docker-entrypoint-initdb.d/init.sql command: > --innodb-buffer-pool-size=512M --max-connections=200 --slow-query-log=ON --long-query-time=1 --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci healthcheck: test: ["CMD", "mysqladmin", "ping", "-h", "localhost", "-u", "root", "-prootpass"] interval: 10s timeout: 5s retries: 5
phpmyadmin: image: phpmyadmin:latest restart: unless-stopped ports: - "8080:80" environment: PMA_HOST: mysql PMA_USER: root PMA_PASSWORD: rootpass depends_on: mysql: condition: service_healthy
volumes: mysql_data:

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

Maintenance Tasks

维护任务

bash
undefined
bash
undefined

Optimize a fragmented table (locks the table briefly)

Optimize a fragmented table (locks the table briefly)

mysql -u root -p -e "OPTIMIZE TABLE mydb.orders;"
mysql -u root -p -e "OPTIMIZE TABLE mydb.orders;"

Analyze tables to update statistics

Analyze tables to update statistics

mysql -u root -p -e "ANALYZE TABLE mydb.orders;"
mysql -u root -p -e "ANALYZE TABLE mydb.orders;"

Check and repair a table

Check and repair a table

mysql -u root -p -e "CHECK TABLE mydb.orders;" mysql -u root -p -e "REPAIR TABLE mydb.orders;"
mysql -u root -p -e "CHECK TABLE mydb.orders;" mysql -u root -p -e "REPAIR TABLE mydb.orders;"

Rotate slow query log

Rotate slow query log

sudo mv /var/log/mysql/slow.log /var/log/mysql/slow.log.old mysqladmin -u root -p flush-logs
undefined
sudo mv /var/log/mysql/slow.log /var/log/mysql/slow.log.old mysqladmin -u root -p flush-logs
undefined

Troubleshooting

故障排查

SymptomLikely CauseFix
Too many connections
Connection limit exceededIncrease 
max_connections
; use connection pooling (ProxySQL)
Slow queries across the board
innodb_buffer_pool_size
too small		Set to ~70% of available RAM and restart
Replication stopped (
SQL_Running: No
)		Duplicate key or schema mismatch on replicaCheck 
SHOW REPLICA STATUS\G
error; skip or fix the row
Table is full
Disk space exhausted or table limit hitFree disk space; check 
innodb_data_file_path
autoextend
Lock wait timeout exceeded
Long-running transaction holding row locksIdentify with 
SHOW ENGINE INNODB STATUS
; kill the blocking query
High IOPS / disk usageRedo log too small causing frequent flushesIncrease 
innodb_log_file_size
(requires restart)
症状可能原因解决方法
Too many connections
连接数超出限制增大
max_connections
；使用连接池（如ProxySQL）
全系统查询缓慢
innodb_buffer_pool_size
设置过小		将其设置为可用内存的~70%并重启服务
复制停止（
SQL_Running: No
）		副本服务器存在重复键或 schema 不匹配查看
SHOW REPLICA STATUS\G
中的错误信息；跳过或修复对应行
Table is full
磁盘空间耗尽或达到表限制释放磁盘空间；检查
innodb_data_file_path
是否开启自动扩展
Lock wait timeout exceeded
长时间运行的事务持有行锁通过
SHOW ENGINE INNODB STATUS
定位问题；终止阻塞查询
高IOPS/磁盘占用重做日志过小导致频繁刷盘增大
innodb_log_file_size
（需重启服务）

Related Skills

相关技能

  • postgresql - Alternative relational database
  • database-backups - Automated backup strategies
  • redis - Caching layer to reduce database load
  • planetscale - Managed MySQL-compatible with branching
  • postgresql - 替代关系型数据库
  • database-backups - 自动化备份策略
  • redis - 缓存层以降低数据库负载
  • planetscale - 支持分支功能的托管式MySQL兼容服务