MySQL 配置与运维

 SHOW DATABASES;
 CREATE DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;
 CREATE DATABASE IF NOT EXISTS mydb;
 CREATE DATABASE mydb;
 DROP DATABASE IF EXISTS mydb;
 use mydb;
 SELECT DATABASE();
 SHOW CREATE DATABASE mydb;
 ALTER DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

 CREATE DATABASE mydb
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

 CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
  username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
  email VARCHAR(100) NOT NULL COMMENT '邮箱',
  password VARCHAR(255) NOT NULL COMMENT '密码（加密存储）',
  age INT UNSIGNED COMMENT '年龄',
  status TINYINT DEFAULT 1 COMMENT '状态：1-正常，0-禁用',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
 )
 DESCRIBE users;
 SHOW COLUMNS FROM users;
 SHOW CREATE TABLE users;
 SHOW TABLES;
 SHOW TABLE STATUS FROM mydb;

 ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email;
 ALTER TABLE users ADD COLUMN last_login DATETIME AFTER updated_at;
 ALTER TABLE users MODIFY COLUMN age INT UNSIGNED NOT NULL DEFAULT 0;
 ALTER TABLE users CHANGE COLUMN username user_name VARCHAR(50) NOT NULL;
 ALTER TABLE users DROP COLUMN phone;
 ALTER TABLE users ADD INDEX idx_email (email);
 ALTER TABLE users ADD UNIQUE INDEX idx_username (username);
 ALTER TABLE orders ADD CONSTRAINT fk_user_id FOREIGN KEY (user_id) REFERENCES users(id);
 ALTER TABLE users RENAME TO customers;
 RENAME TABLE users TO customers, orders TO purchase_orders;
 DROP TABLE IF EXISTS users;
 TRUNCATE TABLE users;

 CREATE TABLE orders (
  order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_no VARCHAR(32) NOT NULL UNIQUE COMMENT '订单编号',
  user_id BIGINT NOT NULL COMMENT '用户ID',
  total_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '订单总额',
  discount_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '优惠金额',
  pay_amount DECIMAL(10,2) NOT NULL DEFAULT 0.00 COMMENT '实付金额',
  pay_type TINYINT COMMENT '支付方式：1-微信 2-支付宝 3-银行卡',
  status TINYINT NOT NULL DEFAULT 1 COMMENT '订单状态：1-待付款 2-已付款 3-已发货 4-已收货 5-已取消',
  order_time DATETIME NOT NULL COMMENT '下单时间',
  pay_time DATETIME COMMENT '支付时间',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_user_id (user_id),
  INDEX idx_order_time (order_time),
  INDEX idx_status (status)
 )
 CREATE TABLE order_items (
  item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL COMMENT '订单ID',
  product_id BIGINT NOT NULL COMMENT '商品ID',
  product_name VARCHAR(100) NOT NULL COMMENT '商品名称（冗余）',
  sku_id BIGINT COMMENT 'SKU ID',
  sku_name VARCHAR(100) COMMENT 'SKU名称（冗余）',
  price DECIMAL(10,2) NOT NULL COMMENT '商品单价',
  quantity INT NOT NULL DEFAULT 1 COMMENT '购买数量',
  subtotal DECIMAL(10,2) NOT NULL COMMENT '小计金额',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
  INDEX idx_order_id (order_id),
  INDEX idx_product_id (product_id)
 )

 INSERT INTO users (username, email, password, age) VALUES ('张三', 'zhangsan@example.com', 'encrypted_pass', 25);
 INSERT INTO users (username, email) VALUES ('李四', 'lisi@example.com');
 INSERT INTO users (username, email, password, age) VALUES
 ('王五', 'wangwu@example.com', 'pass1', 30),
 ('赵六', 'zhaoliu@example.com', 'pass2', 28),
 ('钱七', 'qianqi@example.com', 'pass3', 35);
 INSERT INTO users (username, email, age)
 SELECT username, email, age FROM old_users WHERE status = 1;
 INSERT INTO users SET username='孙八', email='sunba@example.com', age=27;
 INSERT INTO users (id, username, email) VALUES (1, '张三', 'new_email@example.com')
 ON DUPLICATE KEY UPDATE email='new_email@example.com', updated_at=NOW();
 replace INTO users (id, username, email) VALUES (1, '张三', 'new_email@example.com');
 SELECT LAST_INSERT_ID();

 SELECT * FROM users;
 SELECT id, username, email FROM users;
 SELECT id AS user_id, username AS name FROM users;
 SELECT DISTINCT status FROM users;
 SELECT COUNT(DISTINCT status) FROM users;
 SELECT * FROM users LIMIT 10;
 SELECT * FROM users LIMIT 10 OFFSET 20;
 SELECT * FROM users LIMIT 20, 10;
 SELECT username, price, quantity, price * quantity AS total FROM order_items;
 SELECT * FROM users WHERE age > 25 AND status = 1;
 SELECT * FROM users WHERE age BETWEEN 20 AND 30;
 SELECT * FROM users WHERE username LIKE '张%';
 SELECT * FROM users WHERE email IN ('a@example.com', 'b@example.com');
 SELECT * FROM users ORDER BY created_at DESC;
 SELECT * FROM users ORDER BY age ASC, created_at DESC;
 SELECT status, COUNT(*) AS count FROM users GROUP BY status;
 SELECT status, AVG(age) AS avg_age FROM users GROUP BY status HAVING AVG(age) > 25;
 SELECT u.username, o.order_no, o.total_amount
 from users u
 INNER JOIN orders o ON u.id = o.user_id
 WHERE o.status = 2;

 UPDATE users SET age = 26 WHERE id = 1;
 UPDATE users SET age = age + 1 WHERE age < 30;
 UPDATE users SET age = 27, email = 'new_email@example.com', updated_at = NOW() WHERE id = 1;
 UPDATE users SET status = 0 WHERE created_at < '2024-01-01';
 START TRANSACTION;
 UPDATE accounts SET balance = balance - 100 WHERE id = 1;
 UPDATE accounts SET balance = balance + 100 WHERE id = 2;
 commit;
 SELECT * FROM users WHERE id = 1 FOR UPDATE;
 UPDATE users SET age = 26 WHERE id = 1;

 delete FROM users WHERE id = 1;
 delete FROM users WHERE status = 0 AND created_at < '2024-01-01';
 delete FROM users;
 TRUNCATE TABLE users;
 DROP TABLE IF EXISTS users;
 delete FROM orders WHERE user_id = 1;
 ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;
 SELECT * FROM users WHERE id = 1;
 delete FROM users WHERE id = 1;

 CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
 CREATE USER 'newuser'@'%' IDENTIFIED BY 'password'; -- 允许远程连接
 CREATE USER 'newuser'@'192.168.1.%' IDENTIFIED BY 'password'; -- 允许特定网段
 ALTER USER 'newuser'@'localhost' IDENTIFIED BY 'new_password';
 SET PASSWORD FOR 'newuser'@'localhost' = 'new_password';
 DROP USER 'newuser'@'localhost';
 SELECT user, host FROM mysql.user;
 SHOW GRANTS FOR 'newuser'@'localhost';
 RENAME USER 'olduser'@'localhost' TO 'newuser'@'localhost';

 GRANT ALL PRIVILEGES ON mydb.* TO 'newuser'@'localhost';
 FLUSH PRIVILEGES;
 GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'newuser'@'localhost';
 GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost';
 GRANT CREATE USER ON *.* TO 'admin'@'localhost';
 GRANT RELOAD ON *.* TO 'admin'@'localhost';
 GRANT BACKUP ADMIN ON *.* TO 'admin'@'localhost';
 GRANT SELECT, INSERT ON mydb.orders TO 'newuser'@'localhost';
 GRANT EXECUTE ON PROCEDURE mydb.sp_name TO 'newuser'@'localhost';
 REVOKE ALL PRIVILEGES ON mydb.* FROM 'newuser'@'localhost';
 REVOKE DELETE ON mydb.* FROM 'newuser'@'localhost';
 CREATE ROLE 'app_read', 'app_write';
 GRANT SELECT ON mydb.* TO 'app_read';
 GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_write';
 GRANT 'app_read' TO 'user1'@'localhost';
 GRANT 'app_write' TO 'user2'@'localhost';
 SET DEFAULT ROLE 'app_read' FOR 'user1'@'localhost';

 SET GLOBAL max_connections = 500;
 SET GLOBAL wait_timeout = 600;
 SET GLOBAL interactive_timeout = 600;
 SHOW STATUS LIKE 'Threads_connected';
 SHOW VARIABLES LIKE 'max_connections';

 [mysqld]
 # InnoDB 配置
 innodb_buffer_pool_size=4G # 建议为服务器内存的 70%
 innodb_redo_log_capacity=1G # redo 总容量（8.0.30+，在线可调；innodb_log_file_size 已在 8.4 移除）
 innodb_log_buffer_size=64M
 innodb_flush_log_at_trx_commit=1 # 1-最安全，2-性能好，0-最快但可能丢数据
 innodb_flush_method=O_DIRECT # Linux 下推荐，减少系统缓存
 innodb_file_per_table=1 # 每个表独立的表空间
 innodb_io_capacity=4000 # 根据磁盘 IO 能力设置

 CREATE INDEX idx_username ON users(username);
 CREATE INDEX idx_email_status ON users(email, status);
 CREATE INDEX idx_status_created ON users(status, created_at);

 SELECT * FROM users WHERE YEAR(created_at) = 2024;
 SELECT * FROM users WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01';
 SELECT * FROM orders WHERE MONTH(order_time) = 1;
 SELECT * FROM orders WHERE order_time >= '2024-01-01' AND order_time < '2024-02-01';
 EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

 SET GLOBAL slow_query_log = 'ON';
 SET GLOBAL long_query_time = 1;
 SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
 SHOW FULL PROCESSLIST;
 EXPLAIN SELECT u.username, o.total_amount
 from users u
 INNER JOIN orders o ON u.id = o.user_id
 WHERE o.created_at > '2024-01-01';

 CREATE TABLE sales (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  sale_date DATE NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  region VARCHAR(50)
 )
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION pmax VALUES LESS THAN MAXVALUE
 )
 CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(50)
 )
 CREATE TABLE products (
  id INT PRIMARY KEY,
  category_id INT,
  name VARCHAR(50)
 )
  PARTITION p_electronics VALUES IN (1, 2, 3),
  PARTITION p_clothing VALUES IN (4, 5, 6),
  PARTITION p_other VALUES IN (NULL)
 )

 ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass@123';
 delete FROM mysql.user WHERE User = '';
 delete FROM mysql.user WHERE User = 'root' AND Host != 'localhost';
 FLUSH PRIVILEGES;
 CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppPass@2024';
 GRANT SELECT, INSERT, UPDATE, DELETE ON production_db.* TO 'app_user'@'%';
 FLUSH PRIVILEGES;
 CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPass@2024';
 CREATE USER 'app_user'@'10.%.%.%' IDENTIFIED BY 'AppPass@2024';

 SHOW VARIABLES LIKE 'have_ssl';
 SHOW VARIABLES LIKE 'have_openssl';
 ALTER USER 'root'@'localhost' REQUIRE SSL;
 SELECT user, host, ssl_type FROM mysql.user;

 SELECT * FROM mysql.general_log WHERE command_type='Connect' ORDER BY event_time DESC LIMIT 100;
 SELECT * FROM information_schema.processlist WHERE Command != 'Sleep' AND Time > 60;
 SELECT * FROM sys.innodb_lock_waits;   -- 锁等待（8.0 移除旧表 information_schema.INNODB_LOCK_WAITS）
 SELECT * FROM information_schema.innodb_trx;

 SHOW STATUS; -- 所有状态变量
 SHOW GLOBAL STATUS; -- 全局状态
 SHOW VARIABLES; -- 所有配置变量
 SHOW GLOBAL VARIABLES;
 SHOW STATUS LIKE 'Threads_connected'; -- 当前连接数
 SHOW STATUS LIKE 'Max_used_connections'; -- 历史最大连接数
 SHOW STATUS LIKE 'Slow_queries'; -- 慢查询数量
 SHOW STATUS LIKE 'Innodb_row_lock%'; -- 锁等待情况
 SHOW STATUS LIKE 'Com_select'; -- 查询次数
 SHOW STATUS LIKE 'Com_insert'; -- 插入次数
 SHOW STATUS LIKE 'Com_update'; -- 更新次数
 SHOW STATUS LIKE 'Com_delete'; -- 删除次数
 SHOW PROCESSLIST;
 SHOW FULL PROCESSLIST;
 SHOW ENGINE INNODB STATUS;
 SHOW TABLE STATUS FROM database_name;
 SHOW INDEX FROM table_name;

 ANALYZE TABLE users;
 CHECK TABLE users;
 REPAIR TABLE users;
 OPTIMIZE TABLE users;
 ANALYZE TABLE users;
 PURGE BINARY LOGS BEFORE '2024-01-01 00:00:00';
 PURGE BINARY LOGS TO 'mysql-bin.000010';
 SELECT TABLE_NAME, Data_free FROM information_schema.tables WHERE Data_free > 0;

 #!/bin/bash
 # 每日备份脚本示例
 backUP_DIR="/backup/mysql"
 DATE=$(date +%Y%m%d)
 MYSQL_USER="backup_user"
 MYSQL_PASS="backup_password"
 # 创建备份目录
 mkdir -p $BACKUP_DIR
 # 备份所有数据库
 mysqldump -u$MYSQL_USER -p$MYSQL_PASS --all-databases --routines --triggers --events > $BACKUP_DIR/all_db_$DATE.sql
 # 压缩备份
 gzip $BACKUP_DIR/all_db_$DATE.sql
 # 删除 7 天前的备份
 find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete
 # 备份完成
 echo "Backup completed: $DATE"

-- 查看当前大小与单位
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- 专用数据库服务器建议物理内存的 60%-80%（16GB 内存配 10GB）
SET GLOBAL innodb_buffer_pool_size = 10737418240;   -- 10GB，5.7+ 在线调整

-- 调整以 chunk 为单位发生（默认 128MB）
SHOW VARIABLES LIKE 'innodb_buffer_pool_chunk_size';

-- 多实例降低内部争用：Buffer Pool >= 1GB 时按每实例约 1GB 分
SHOW VARIABLES LIKE 'innodb_buffer_pool_instances';
SET GLOBAL innodb_buffer_pool_instances = 8;

-- 关闭时保存热点页清单，启动时自动加载（默认都开）
SHOW VARIABLES LIKE 'innodb_buffer_pool_dump_at_shutdown';
SHOW VARIABLES LIKE 'innodb_buffer_pool_load_at_startup';

-- 手动触发（重启演练、维护窗口后快速回温）
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

-- 看进度
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

-- 命中率 = 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
SELECT
    ROUND((1 - (SELECT Variable_value + 0 FROM performance_schema.global_status
                WHERE Variable_name = 'Innodb_buffer_pool_reads') /
              (SELECT Variable_value + 0 FROM performance_schema.global_status
               WHERE Variable_name = 'Innodb_buffer_pool_read_requests')) * 100, 3)
    AS hit_rate_pct;

-- 哪些表占着缓存（缓存被谁挤占的答案）
SELECT OBJECT_SCHEMA AS db, OBJECT_NAME AS tbl, COUNT(*) AS pages_cached
FROM information_schema.INNODB_BUFFER_PAGE
GROUP BY OBJECT_SCHEMA, OBJECT_NAME
ORDER BY pages_cached DESC LIMIT 20;

A. innodb_buffer_pool_size = 1G,   instances = 16
B. innodb_buffer_pool_size = 10G,  instances = 8
C. innodb_buffer_pool_size = 16G,  instances = 1

#!/bin/bash
# 每分钟巡检
mysql -e "SELECT OBJECT_SCHEMA, OBJECT_NAME, COUNT(*)
          FROM information_schema.INNODB_BUFFER_PAGE
          GROUP BY 1,2 ORDER BY 3 DESC LIMIT 20"
mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'" | awk '{...计算命中率...}'