MySQL数据库常用操作命令

2023-05-09 · 2 分钟阅读 · MySQL

查询数据库有哪些用户

1use mysql;
2select host,user from user;

查询进程列表

1show full processlist

查看数据库及表容量⼤⼩并排序查看所有数据库容量⼤⼩

 1SELECT
 2    table_schema AS '数据库',
 3    sum(table_rows) AS '记录数',
 4    sum(
 5        TRUNCATE (data_length / 1024 / 1024, 2)
 6    ) AS '数据容量(MB)',
 7    sum(
 8        TRUNCATE (index_length / 1024 / 1024, 2)
 9    ) AS '索引容量(MB)'
10FROM
11    information_schema. TABLES
12GROUP BY
13    table_schema
14ORDER BY
15    sum(data_length) DESC,
16    sum(index_length) DESC;

查看所有数据库各表容量⼤⼩

 1SELECT
 2    table_schema AS '数据库',
 3    table_name AS '表名',
 4    table_rows AS '记录数',
 5    TRUNCATE (data_length / 1024 / 1024, 2) AS '数据容量(MB)',
 6    TRUNCATE (index_length / 1024 / 1024, 2) AS '索引容量(MB)'
 7FROM
 8    information_schema. TABLES
 9ORDER BY
10    data_length DESC,
11    index_length DESC;

查看指定数据库容量⼤⼩

 1SELECT
 2    table_schema AS '数据库',
 3    sum(table_rows) AS '记录数',
 4    sum(
 5        TRUNCATE (data_length / 1024 / 1024, 2)
 6    ) AS '数据容量(MB)',
 7    sum(
 8        TRUNCATE (index_length / 1024 / 1024, 2)
 9    ) AS '索引容量(MB)'
10FROM
11    information_schema.tables where table_schema = 'your_table_name';

查看指定数据库各表容量⼤⼩

 1SELECT
 2    table_schema AS '数据库',
 3    table_name AS '表名',
 4    table_rows AS '记录数',
 5    TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
 6    TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)' 
 7FROM
 8    information_schema.TABLES 
 9WHERE
10    table_schema = '指定的库名' 
11ORDER BY
12    data_length DESC,
13    index_length DESC;

查看mysql各表数据存储碎片大小

 1SELECT
 2    table_schema AS '库名',
 3    table_name AS '表名',
 4    ENGINE AS '存储引擎',
 5    table_rows AS '行数',
 6    trim(
 7        concat(
 8            round(DATA_LENGTH / 1024 / 1024, 1)
 9        )
10    ) AS '数据大小MB',
11    trim(
12        round(index_length / 1024 / 1024, 1)
13    ) AS '索引大小MB',
14    trim(
15        round(DATA_FREE / 1024 / 1024, 1)
16    ) AS '碎片大小MB'
17FROM
18    information_schema. TABLES
19WHERE
20    table_schema NOT IN (
21        'information_schema',
22        'phpmyadmin',
23        'scripts',
24        'test',
25        'performance_schema',
26        'mysql'
27    )
28AND DATA_FREE / 1024 / 1024 > 1000
29ORDER BY
30    DATA_FREE DESC;

更新数据碎片刷新

1OPTIMIZE TABLE 表名;

导入数据

1##登陆mysql后
2source /home/abc/abc.sql;
3
4mysqldump -P 3306 -h 192.168.3.118 -u root -p database_name>d:\test.sql

导出数据

1#从test数据库的test_data表中导出id大于100的数据到 /tmp/test.sql 这个文件中
2mysqldump -uroot -p123456 test test_data --where=" id > 100" > /tmp/test.sql