## 日常运维
$ mysql -u root -p -e “SHOW GLOBAL STATUS LIKE ‘Threads%'”;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 10 |
| Threads_connected | 150 |
| Threads_created | 1000 |
| Threads_running | 5 |
+——————-+——-+

## 备份管理
$ mysqldump -u root -p –all-databases > /backup/full_backup.sql
Enter password:
$ ls -lh /backup/full_backup.sql
-rw-r–r– 1 root root 1.2G Apr 5 02:00 /backup/full_backup.sql

## 性能监控
$ mysql -u root -p -e “SHOW VARIABLES LIKE ‘innodb_buffer_pool_size'”;
+————————-+————+
| Variable_name | Value |
+————————-+————+
| innodb_buffer_pool_size | 1677721600 |
+————————-+————+

## 故障处理
$ mysql -u root -p -e “SHOW SLAVE STATUS\G”;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 123456
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 123456
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 123456
Relay_Log_Space: 123456
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 123e4567-e89b-12d3-a456-426614174000
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)

## 数据模型设计
CREATE TABLE `users` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`username` VARCHAR(50) NOT NULL,
`email` VARCHAR(100) NOT NULL,
`password` VARCHAR(100) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE `orders` (
`id` INT PRIMARY KEY AUTO_INCREMENT,
`user_id` INT NOT NULL,
`total_amount` DECIMAL(10,2) NOT NULL,
`status` VARCHAR(20) NOT NULL,
`created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
`updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
FOREIGN KEY (`user_id`) REFERENCES `users`(`id`)
);

## ETL过程
— 每日销售数据汇总
INSERT INTO sales_summary (date, total_sales, order_count)
SELECT
DATE(created_at) as date,
SUM(total_amount) as total_sales,
COUNT(*) as order_count
FROM orders
WHERE DATE(created_at) = CURDATE() – INTERVAL 1 DAY
GROUP BY DATE(created_at);

## 报表查询
— 月度销售报表
SELECT
DATE_FORMAT(created_at, ‘%Y-%m’) as month,
SUM(total_amount) as total_sales,
COUNT(*) as order_count,
AVG(total_amount) as avg_order_value
FROM orders
WHERE created_at BETWEEN ‘2026-01-01’ AND ‘2026-01-31’
GROUP BY DATE_FORMAT(created_at, ‘%Y-%m’);

## 性能优化
— 添加索引
ALTER TABLE orders ADD INDEX idx_user_id (user_id);
ALTER TABLE orders ADD INDEX idx_created_at (created_at);

— 执行计划分析
EXPLAIN SELECT * FROM orders WHERE user_id = 1001 AND created_at > ‘2026-01-01’;
+—-+————-+——–+————+——+—————+———–+———+——-+——+———-+————-+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered |
Extra |
+—-+————-+——–+————+——+—————+———–+———+——-+——+———-+————-+
| 1 | SIMPLE | orders | NULL | ref | idx_user_id | idx_user_id | 4 | const | 5 | 33.33 | Using where |
+—-+————-+——–+————+——+—————+———–+———+——-+——+———-+————-+

## 客户行为分析
— 客户活跃度分析
SELECT
DATE(login_time) as login_date,
COUNT(DISTINCT user_id) as active_users,
COUNT(*) as login_count
FROM user_logins
WHERE login_time BETWEEN ‘2026-03-01’ AND ‘2026-03-31’
GROUP BY DATE(login_time)
ORDER BY login_date;

## 业务指标分析
— 转化率分析
SELECT
campaign_id,
COUNT(DISTINCT user_id) as visitors,
COUNT(DISTINCT CASE WHEN action = ‘purchase’ THEN user_id END) as purchasers,
ROUND(COUNT(DISTINCT CASE WHEN action = ‘purchase’ THEN user_id END) * 100.0 / COUNT(DISTINCT user_id), 2)
as conversion_rate
FROM campaign_events
WHERE event_date BETWEEN ‘2026-03-01’ AND ‘2026-03-31’
GROUP BY campaign_id
ORDER BY conversion_rate DESC;

## 预测分析
— 月度销售额预测
SELECT
DATE_FORMAT(created_at, ‘%Y-%m’) as month,
SUM(total_amount) as sales
FROM orders
GROUP BY DATE_FORMAT(created_at, ‘%Y-%m’)
ORDER BY month;

## 数据可视化准备
— 准备图表数据
SELECT
product_category,
SUM(total_amount) as sales,
COUNT(*) as order_count
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE created_at BETWEEN ‘2026-01-01’ AND ‘2026-03-31’
GROUP BY product_category
ORDER BY sales DESC;