MySql管理

Mysql启动命令

nohup ./mysqld --defaults-file=../my.cnf --user=mysql >/dev/null &

统计、分析、重建

理解这三条命令的差异对高效进行MySQL表维护很重要。它们各自针对不同的优化目标。

命令	主要作用	本质	对物理存储空间的影响	操作代价
OPTIMIZE TABLE ...	综合维护：重建表+更新索引统计信息。用于在大量增删改后回收空间、消除碎片。	RECREATE+ ANALYZE	会回收空间，使表文件变小。	高（涉及大量I/O）
ALTER TABLE ... ENGINE=InnoDB;	重建表：创建一个新的、无碎片的表来替换原表。主要目标是消除存储碎片。	RECREATE	会回收空间，使表文件变小。	高（涉及大量I/O）
ANALYZE TABLE ...	更新统计信息：重新计算表和索引的统计信息（如基数），帮助查询优化器选择更优的执行计划。	更新STATS，不移动数据。	不会回收物理空间。	低

下面我们具体看看这些命令的细节和注意事项。

💡 深入了解命令细节

🔄 OPTIMIZE TABLE

• 工作原理：对于InnoDB表，此命令实际上相当于执行了 ALTER TABLE ... ENGINE=INNODB（重建表）再加上 ANALYZE TABLE（更新统计信息）。它会重建表，并更新索引统计信息，释放聚簇索引中未使用的空间 。

• 锁表情况：在MySQL 5.6及以上版本，默认通过Online DDL方式执行，但过程中仍可能需短时间的元数据锁（MDL）。对大表操作时，建议在业务低峰期进行。

🏗️ ALTER TABLE ... ENGINE=InnoDB

• 工作原理：此命令会创建一个新的表结构，将原表数据逐行复制到新表中，然后进行替换。这个过程会整理数据页，填充因删除和更新造成的"空洞"，从而使存储更紧凑 。

• 与OPTIMIZE的关系：可以认为 OPTIMIZE TABLE是 ALTER TABLE ... ENGINE=INNODB的一个超集。在InnoDB表上，OPTIMIZE TABLE在底层通常就是通过这种方式实现的 。

📊 ANALYZE TABLE

• 工作原理：此命令会随机采样数据页，重新计算索引的基数（不同值的数量）等统计信息 。这些信息是查询优化器决定使用哪个索引、如何连接表的关键依据。

• 特点：操作非常快，因为它不移动任何数据，只更新系统的元数据表 。通常只需读锁，对业务影响很小 。

🛠️ 使用场景与建议

为了帮助您更好地决策，这里有一个快速参考指南。

场景	推荐命令	理由
清理大量数据后，希望释放磁盘空间	OPTIMIZE TABLE或 ALTER TABLE ... ENGINE=InnoDB	直接重建表，物理上释放空间。
查询速度变慢，怀疑是索引统计信息过时	ANALYZE TABLE	快速更新统计信息，帮助优化器选择最佳查询计划。
表经历了大量随机插入、更新、删除，存在大量存储碎片	OPTIMIZE TABLE或 ALTER TABLE ... ENGINE=InnoDB	整理碎片，提高数据页的存储密度和顺序访问效率。
常规的月度/季度维护	可对关键大表执行 OPTIMIZE TABLE	综合性的维护，同时回收空间和更新统计信息。
仅仅想优化查询计划，且表数据变动不大	ANALYZE TABLE	代价低，见效快。

重要操作建议：

• 备份先行：在执行 OPTIMIZE TABLE或 ALTER TABLE这类重构表的重要操作前，务必对数据进行备份 。

• 选择时机：对于大表，OPTIMIZE TABLE和 ALTER TABLE是重量级操作，会消耗大量I/O和CPU资源，并可能锁表。务必在业务低峰期进行操作。

• 空间要求：执行 OPTIMIZE TABLE时，MySQL需要额外的磁盘空间来创建临时表。请确保数据库服务器的磁盘有足够的剩余空间（至少与原表大小相当）。

• 不要频繁执行：对于多数场景，并不需要频繁运行OPTIMIZE操作。通常每周或每月整理一次即可，具体频率需根据实际数据变更情况而定 。

希望这些详细的解释能帮助您清晰地理解这三条命令的区别，并在实际数据库维护中做出合适的选择。如果您对特定场景下的选择还有疑问，我们可以继续探讨。

各数据库大小

SELECT 
    table_schema AS '数据库名',
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS '总大小(MB)',
    ROUND(SUM(data_length) / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(SUM(index_length) / 1024 / 1024, 2) AS '索引大小(MB)',
    SUM(table_rows) AS '总记录数'
FROM information_schema.TABLES 
GROUP BY table_schema 
ORDER BY SUM(data_length + index_length) DESC;

表占用空间大小

SELECT 
    table_name AS '表名',
    table_rows AS '记录行数',
    ROUND(data_length / 1024 / 1024, 2) AS '数据空间(MB)',
    ROUND(index_length / 1024 / 1024, 2) AS '索引空间(MB)',
    ROUND((data_length + index_length) / 1024 / 1024, 2) AS '总空间(MB)'
FROM 
    information_schema.tables
WHERE 
    table_schema = 'fjm'  -- 请将“你的数据库名”替换为实际要检查的数据库名称
ORDER BY 
(data_length + index_length) DESC;

当前执行中的sql

select trx_id,trx_state,trx_started,trx_query,trx_rows_locked,trx_rows_modified,trx_mysql_thread_id  FROM information_schema.innodb_trx ORDER BY trx_started ASC LIMIT 10;

SELECT * FROM information_schema.innodb_trx ORDER BY trx_started ASC LIMIT 10;

 

当前执行中的PROCESSLIST

包括一些命令（如kill）的执行情况

SHOW PROCESSLIST;

Mysql日志

[mysqld]
# ========== 核心：修复错误日志 ==========
log_error = /var/log/mysqld/mysqld.err  # 单独目录存放日志，避免和数据盘混放
log_error_verbosity = 3                 # 日志级别设为3（最详细，包含警告/错误/注意）

Mysql主备重启

先停止备，再停止主：

mysqladmin -uroot -pXXXXXX -S /data/mysql/data/mysql.sock  shutdown

启动主，再启动备：

nohup ./mysqld --defaults-file=../my.cnf --user=mysql >/dev/null &

进入备数据库命令查看：

show slave status\G

Seconds_Behind_Master: 0

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

说明备机状态正常