mysql常用命令

1. mysql内部

查看当前连接数和线程状态

show full processlist;
SHOW PROCESSLIST;

查看当前活跃的连接数：

show status like 'Threads_connected';

查看 MySQL 的最大连接数：

show variables like 'max_connections';

修改最大连接数

SET GLOBAL max_connections = 500;

查看binlog配置

mysql> SHOW VARIABLES LIKE 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.02 sec)

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

mysql> SHOW VARIABLES LIKE 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.02 sec)

binlog_format
> ROW 模式是指在二进制日志中记录每一行数据的变化情况，包括修改前后的完整数据。这种模式可以确保数据的完整性和一致性，但是会产生大量的日志数据，对磁盘空间和性能都有一定的影响

> STATEMENT 模式是指在二进制日志中记录 SQL 语句的执行情况，包括执行前后的 SQL 语句文本。这种模式可以减少日志数据的量，但是可能会因为执行计划的变化而导致数据不一致

> MIXED 模式是指 MySQL 根据具体情况自动选择 `ROW` 或 `STATEMENT` 模式。这种模式可以兼顾数据完整性和日志数据量的平衡，但是可能会因为执行计划的变化而导致数据不一致


expire_logs_days
`Value` 字段为 `0`，则表示 MySQL 不会自动清理二进制日志文件

删除表

情况一：
清空表中的所有记录，可以使用下面的两种方法：
> delete from tablename
> truncate table tablename

情况二：
删除表中的部分记录，只能使用delete语句。
> table1 WHERE ;

情况三:
只想删除数据，但是表中数据太多，会进行锁表，可以分批操作
> START TRANSACTION; 
> delete from t_chaos_scheduler_trigger_log LIMIT 10000; 
> COMMIT; 

备注:
truncate table是将表结构重新建一次速度要比使用delete from快很多,而delete from是一行一行的删除,速度很慢.

查看表结构

DESCRIBE table_name;

# 或者缩写
DESC table_name;

查看创建表时的 SQL 语句

SHOW CREATE TABLE table_name;

查看创建表时的SQL语句

SHOW CREATE DATABASE db_name;

查看所有表的索引

-- 查询当前数据库中所有表的索引
SELECT 
    TABLE_NAME AS '表名',
    INDEX_NAME AS '索引名', 
    COLUMN_NAME AS '列名',
    SEQ_IN_INDEX AS '列顺序',
    INDEX_TYPE AS '索引类型',
    NON_UNIQUE AS '是否非唯一'
FROM INFORMATION_SCHEMA.STATISTICS 
WHERE TABLE_SCHEMA = DATABASE()
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

查看指定表的索引

-- 查询特定表的索引
SHOW INDEX FROM your_table_name;

-- 或者使用
SHOW INDEXES FROM your_table_name;

查看索引大小和统计信息

-- 查看索引大小
SELECT 
    table_name AS '表名',
    index_name AS '索引名',
    round(stat_value * @@innodb_page_size / 1024 / 1024, 2) AS '大小(MB)'
FROM mysql.innodb_index_stats 
WHERE stat_name = 'size'
AND database_name = DATABASE();

查看表空间

SELECT t.TABLE_SCHEMA AS 库名, t.TABLE_NAME AS 表名, t.TABLE_ROWS AS 行数, t.DATA_LENGTH AS 数据字节长度, t.INDEX_LENGTH AS 索引字节长度, concat(round(t.DATA_FREE / 1024 / 1024, 2), 'M') AS 占用空间  FROM information_schema.tables t  WHERE t.TABLE_SCHEMA = 'twenty48net';

SELECT 
    TABLE_SCHEMA AS '数据库名',
    TABLE_NAME AS '表名',
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS '总大小(MB)',
    ROUND(DATA_LENGTH / 1024 / 1024, 2) AS '数据大小(MB)',
    ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS '索引大小(MB)',
    ROUND(DATA_FREE / 1024 / 1024, 2) AS '碎片空间(MB)'
FROM 
    information_schema.TABLES
WHERE 
    TABLE_SCHEMA NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY 
    (DATA_LENGTH + INDEX_LENGTH) DESC;
    

2. 终端

备份 mysqldump

导出表结构及表数据 mysqldump 用法

mysqldump -u用戶名 -p密码 -d 数据库名 表名 脚本名;

1、导出数据库为 dbname 的表结构(其中用户名为root，密码为dbpasswd，生成的脚本名为db.sql)

mysqldump -uroot -pdbpasswd -d dbname >db.sql;

2、导出数据库为 dbname 的某张表(test)结构;

mysqldump -uroot -pdbpasswd -d dbname test>db.sql;

3、导出数据库为 dbname 所有表结构及表数据（不加-d）

mysqldump -uroot -pdbpasswd  dbname >db.sql;

4、导出数据库为 dbname 某张表(test)结构及表数据（不加-d）

mysqldump -uroot -pdbpasswd dbname test>db.sql; 

5、导出表数据

mysqldump -uroot -pdbpasswd -t dbname test>db.sql; 

–set-gtid-purged=OFF

加了--set-gtid-purged=OFF时，在会记录binlog日志，如果不加，不记录binlog日志，所以在我们做主从用了gtid时，用mysqldump备份时就要加--set-gtid-purged=OFF，否则你在主上导入恢复了数据，主没有了binlog日志，同步则不会被同步。

备份问题汇总

问题一: mysqldump: Couldn’t execute ‘SELECT COLUMN_NAME, JSON_EXTRACT(HISTOGRAM, ‘$.”number-of-buckets-specified”’) FROM information_schema.COLUMN_STATISTICS WHERE SCHEMA_NAME = ‘lark’ AND TABLE_NAME = ‘ai_img_subtask’;’: Unknown table ‘column_statistics’ in information_schema (1109)

原因: 因为使用了较高版本的 mysqldump 工具（如 MySQL 8.0 自带版本）去连接一个较低版本的 MySQL 服务器（如 MySQL 5.7 或 MariaDB），而低版本数据库中不存在 information_schema.COLUMN_STATISTICS 表

解决: mysqldump 命令中直接添加 –column-statistics=0 参数来禁用列统计信息功能

比如：

// 原sql
mysqldump -u [用户名] -p [数据库名] > 备份文件.sql

//修改后sql
mysqldump --column-statistics=0 -u [用户名] -p [数据库名] > 备份文件.sql