mysql数据库管理

37810

查询数据库

查询当前用户有多少数据库

mysql> show databases;

查询当前使用的是哪个库

mysql> select database();

查询数据库字符集

--方式1：
mysql> select * from information_schema.SCHEMATA;
--方式2：
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | latin1                     |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
character_set_client      为客户端编码方式；
character_set_connection  为建立连接使用的编码；
character_set_database    为数据库的编码；
character_set_results     为结果集的编码；
character_set_server      为数据库服务器的编码；
只要保证以上采用的编码方式一样，就不会出现乱码问题。

查询数据库大小

--方式1
SELECT table_schema AS "Database", 
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES 
WHERE table_schema = "你的数据库名称"
GROUP BY table_schema;

--方式2：
mysql> use information_schema;
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='你的数据库名称';

创建数据库

--创建数据库wjdb，设置字符集为utf8mb4，排列顺序为utf8mb4_general_ci
CREATE DATABASE `wjshujuku` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
CREATE DATABASE `wordpress` CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci';
flush privileges;

登录数据库

#登录本机数据库
[root@localhost ~]# mysql -uroot -pwangjian

#登录其它IP数据库
mysql -h 127.0.0.1 -u <用户名> -p<密码>
mysql -D 数据库名 -h 主机名 -u 用户名 -p
mysql -h <host> -P <端口号> -u <user> -p [db_name]
mysql -h <host> -u <user> -p [db_name]

删除数据库

DROP database `wjshujuku`;
DROP database `wordpress`;

切换数据库

--必须是管理用户，才能切换到其它数据库
#方式1:
use wjdb;
#方式2:
CONNECT wjdb ;

退出数据库

--退出mysql
mysql> exit
或者 退出 quit; 或 \q; 一样的效果

慢sql

--显示是否开启慢查询日志，off则未开启，on表示开启
show VARIABLES like 'slow_query_log'

--显示设置的慢sql时间，当sql执行的时间超过这个值，将会被记录为慢sql 
--这里查询得到long_query_time的值为10秒（具体的值可能根据数据库的配置而不同）。
mysql> SHOW VARIABLES LIKE 'long_query_time'; 
+-----------------+-----------+ 
| Variable_name   | Value     | 
+-----------------+-----------+ 
| long_query_time | 10.000000 | 
+-----------------+-----------+ 
--显示记录慢sql的日志文件
show VARIABLES like 'slow_query_log_file'
--显示记录的慢sql数量
show global status like 'slow_queries'

特定数据查询

超过1000条数据以上的表

SELECT
  table_schema AS 'Database',
  table_name AS 'Table',
  table_rows AS 'Rows' 
FROM
  information_schema.`TABLES` 
WHERE
  table_rows > 1000 
  AND table_schema != 'mysql'  and  table_schema != 'performance_schema'
ORDER BY
  table_rows DESC;
图片[1]-mysql数据库管理-秋风落叶

每个表有多少行数据

写法1，直接查询某个库

SELECT
  table_schema AS 'Database',
  table_name AS 'Table',
  table_rows AS 'Rows' 
FROM
  information_schema.`TABLES` 
WHERE
  table_schema = 'wordpress' 
ORDER BY
  table_rows DESC;

写法2，排查写法

SELECT
  table_schema AS 'Database',
  table_name AS 'Table',
  table_rows AS 'Rows' 
FROM
  information_schema.`TABLES` 
WHERE
  table_schema != 'mysql' 
  AND table_schema != 'performance_schema' 
ORDER BY
  table_rows DESC;
图片[2]-mysql数据库管理-秋风落叶

每个表大小查询

SELECT
    table_schema AS `数据库`,
    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 NOT IN ('information_schema', 'mysql', 'performance_schema', 'sys')
ORDER BY
    (data_length + index_length) DESC;
图片[3]-mysql数据库管理-秋风落叶

如果你想查看某个特定数据库中所有表的大小，可在 WHERE 子句中添加相应的条件，例如：

WHERE table_schema = 'your_database_name';

数据库版本查询

方式1：

mysql> select version();
+-----------+
| version() |
+-----------+
| 8.4.0     |
+-----------+
1 row in set (0.00 sec)

方式2：

mysql> \s
--------------
mysql  Ver 8.4.5 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		12
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.4.0 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/lib/mysql/mysql.sock
Binary data as:		Hexadecimal
Uptime:			7 min 43 sec

Threads: 2  Questions: 63  Slow queries: 0  Opens: 197  Flush tables: 3  Open tables: 113  Queries per second avg: 0.136
--------------

mysql> status;
--------------
mysql  Ver 8.4.5 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:		12
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.4.0 MySQL Community Server - GPL
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/lib/mysql/mysql.sock
Binary data as:		Hexadecimal
Uptime:			8 min 27 sec

Threads: 2  Questions: 66  Slow queries: 0  Opens: 197  Flush tables: 3  Open tables: 113  Queries per second avg: 0.130
--------------

修改字符集

mysql> ALTER DATABASE wjdb CHARACTER SET 'utf8';
Query OK, 1 row affected (0.00 sec)

再次查询wjdb数据库的字符集已修改
mysql> select * from information_schema.SCHEMATA;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME        | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def          | information_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | mysql              | latin1                     | latin1_swedish_ci      | NULL     |
| def          | performance_schema | utf8                       | utf8_general_ci        | NULL     |
| def          | sys                | utf8                       | utf8_general_ci        | NULL     |
| def          | wjdb               | utf8                       | utf8_general_ci        | NULL     |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.00 sec)

端口

--数据库端口查看
mysql>show global variables like 'port';

--修改端口
vim /etc/my.cnf 
[mysqld]
port=3506

连接数并发数

最大连接数
mysql>  show variables like '%max_connections%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

连接数并发数
mysql> show status like 'Threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 1     |
| Threads_created   | 1     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)
Threads_cached : 代表当前此时此刻线程缓存中有多少空闲线程。
Threads_connected :代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数。
Threads_created :代表从最近一次服务启动，已创建线程的数量。
Threads_running :代表当前激活的（非睡眠状态）线程数。并不是代表正在使用的线程数，有时候连接已建立，但是连接处于sleep状态，这里相对应的线程也是sleep状态。
© 版权声明
文章版权归作者所有，未经允许请勿转载。
THE END
mysql
# mysql
点赞10 分享