查看进程
该命令可查看mysql的连接数及执行的命令、链接IP等
-- 只列出前100条
SHOW PROCESSLIST;
-- 全列出
SHOW FULL PROCESSLIST;查看进程数截图：
在表中搜索内容
不知道要搜索的内容在哪个表里，可以批量搜索
-- 搜索所有表中包含的列名，如"name"
SELECT table_schema, table_name, column_name
FROM information_schema.columns
WHERE column_name LIKE '%name%'
-- 搜索zkeco库里，行数超过10行的表
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'zkeco' -- 替换为您的数据库名称
AND TABLE_ROWS > 10;占用空间最大的表
统计 MySQL 数据库中各个数据库的大小，降序排序
SELECT
table_schema AS 'Database',
ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 3) AS 'Size (GB)'
FROM
information_schema.tables
GROUP BY
table_schema
ORDER BY
SUM(data_length + index_length) DESC;统计 MySQL 数据库中各个表的大小，降序排序
SELECT
table_schema AS `Database`,
table_name AS `Table`,
ROUND(((data_length + index_length) / (1024 * 1024 * 1024)), 2) AS `Size (GB)`
FROM
information_schema.TABLES
WHERE
table_schema NOT IN ('information_schema' , 'performance_schema', 'mysql')
ORDER BY (data_length + index_length) DESC
LIMIT 20;计算每个表的实际占用空间（data_length + index_length）、可用空间（data_free）和碎片率：
SELECT
table_schema AS 'Database',
table_name AS 'Table',
engine AS 'Engine',
ROUND(data_length / 1024 / 1024, 2) AS 'Data Size (MB)',
ROUND(index_length / 1024 / 1024, 2) AS 'Index Size (MB)',
ROUND(data_free / 1024 / 1024, 2) AS 'Free Space (MB)',
ROUND((data_length + index_length + data_free) / 1024 / 1024, 2) AS 'Total Size (MB)',
ROUND(data_free / (data_length + index_length + data_free) * 100, 2) AS 'Fragmentation (%)'
FROM
information_schema.tables
WHERE
table_schema NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
AND engine = 'InnoDB'
ORDER BY
data_free DESC;对碎片率高的表运行以下命令，以实现：
重建表，整理碎片，释放未使用的空间到文件系统。
OPTIMIZE TABLE your_database.your_table;查看/设置并发数
并发数是指同一时刻数据库能处理多少个请求。由最大连接数和最大用户数决定。
最大连接数（
max_connections）：当前数据库的最大连接数，不区分用户。上限是16384
最大用户连接数（max_user_connections）：当前数据库用户所能连接的连接数，0为不限制。
但并不是设置越大越好。因为MySQL会为每个连接提供缓冲区，意味着消耗更多的内存。
-- 查看最大连接数
SHOW VARIABLES LIKE '%max_connections%';
-- 设置最大连接数
SET GLOBAL MAX_CONNECTIONS = 1024;
-- 查看用户最大连接数（重启失效。要想永久生效修改配置文件）
SHOW VARIABLES LIKE '%max_user_connections%';
#-- 设置用户最大连接数（重启失效。要想永久生效修改配置文件）
set GLOBAL max_user_connections = 0;日志保留天数
Mysql数据库由于业务原因，数据量增长迅速，binlog日志会增加较多，占用大部分磁盘空间。
出于节约空间考虑，可进行删除多余binary日志，并设置定期删除操作。
使用以下命令可以控制binlog日志文件保留时间,超过保留时间的binlog日志会被自动删除
-- mysql8.0以下版本查看当前数据库日志binlog保存时效 以天为单位，默认0 永不过期，最多只能设置99天
show variables like 'expire_logs_days';
-- 设置保留天数。（重启失效。要想永久生效修改配置文件）
set global expire_logs_days=60;通过binlog恢复数据
开启binlog
通过以下命令查看是否开启了binlog
show variables like '%log_bin%';通过改命令也可以查看binlog的保存位置
binlog保存位置
查看当前正在写入的binlog文件
在服务器上也能在上述路径汇总找到binlog记录的文件
测试恢复文件
修改前的数据：
修改后数据：
查询要恢复事件的起始位置：
show binlog EVENTS in 'mysql-bin.005105'可看到起始位置是564结束为止是1163
恢复数据
（待补充）释放磁盘空间
数据库的操作日志（如事务日志）可能会占用大量空间，清理无用数据后并不会立即释放磁盘空间。比如执行的delete命令并不会实际释放mysql的磁盘占用。需要通过其他操作释放空间。
找出哪些表未释放空间
首先通过查询 information_schema 来列出所有数据库和表的空间使用情况。
data_free: 显示未释放的空间。这个值越大，说明表中存在未使用的空间。
SELECT
table_schema,
table_name,
ROUND(data_length / 1024 / 1024 / 1024, 2) AS data_length_gb,
ROUND(index_length / 1024 / 1024 / 1024, 2) AS index_length_gb,
ROUND((data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_length_gb,
ROUND(data_free / 1024 / 1024 / 1024, 2) AS data_free_gb
FROM
information_schema.tables
ORDER BY
data_free DESC;
比如要释放db_rong360.t_outprd_cust的空间
1. 使用 OPTIMIZE TABLE
OPTIMIZE TABLE 命令可以帮助你回收未使用的空间并重建表。注意，这个命令会锁表。在执行期间无法对这个表的数据进行读写操作。
执行以下 SQL 命令：
OPTIMIZE TABLE db_rong360.t_outprd_cust;2. 使用 pt-online-schema-change
如果你的表很大，且你希望在不锁定表的情况下优化，可以使用 pt-online-schema-change 工具。执行以下命令：
sudo yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
sudo percona-release setup ps80 # 或选择适合的版本
sudo yum install percona-toolkit
# 验证安装
pt-online-schema-change --version使用工具在线优化表
pt-online-schema-change --alter "ENGINE=InnoDB" D=db_rong360,t=t_outprd_cust --host=数据库地址 --port=3306 --user=用户名 --password=密码
评论(0)