mysql常用命令

1. mysql内部

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

show full processlist;
SHOW PROCESSLIST;

查看当前活跃的连接数：

show status like 'Threads_connected';

查看 MySQL 的最大连接数：

show variables like 'max_connections';

修改最大连接数

SET GLOBAL max_connections = 500;

1.4 查看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 不会自动清理二进制日志文件

1.5 删除表

情况一：
清空表中的所有记录，可以使用下面的两种方法：
> 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;

查看表空间

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. 终端

1.1 备份 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日志，同步则不会被同步。