Menu Close

MariaDB参数查看(持续更新中)

lcc0556

1. 数据库总体运行情况

1.1. 数据库字符集与Collation

SELECT schema_name,
       default_character_set_name AS 'charset',
       default_collation_name AS 'collation'
  FROM information_schema.schemata
 WHERE schema_name = SCHEMA();
/* WHERE SCHEMA_NAME = 'database_name'; */

  • 修改数据库字符集

    ALTER DATABASE 数据库名(实例名) DEFAULT CHARACTER SET 字符集 COLLATE 排序规则;

  • 示例

    ALTER DATABASE easytongdb DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

1.2. 数据库中某张表所用的字符集

SHOW CREATE TABLE 表名(或 实例名.表名);
  • 修改当前数据库中所有表的字符集和排序规则(修改所有表的字符集为utf8mb4) 
    SELECT CONCAT('ALTER TABLE ',
   t.table_schema, '.', t.table_name,
   ' CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;')
FROM information_schema.tables t
WHERE t.table_schema = SCHEMA();

1.3. 查看表中所有列的字符集

SHOW FULL COLUMNS FROM 表名(或 实例名.表名);

  • 查询当前数据库中所有列的字符集和排序规则

    SELECT c.table_name AS '表名称',
   c.column_name AS '字段名称',
   c.character_set_name AS '字符集',
   c.collation_name AS '排序规则'
FROM information_schema.`columns` c
WHERE c.table_schema = SCHEMA();

  • 修改当前数据库中所有列的字符集和排序规则(将gbk的字符集转换成utf8mb4字符集)

    SELECT c.table_schema       AS '数据库',
   c.table_name         AS '表名',
   c.column_name        AS '字段名',
   c.character_set_name AS '字符集',
   c.collation_name     AS '排序规则',
   CONCAT('ALTER TABLE ',
          c.table_schema, '.', c.table_name,
          ' MODIFY COLUMN ', c.column_name, ' ',
          c.column_type,
          ' CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ',
           CASE WHEN c.column_default IS NOT NULL THEN CONCAT(' DEFAULT ',  c.column_default) ELSE ' ' END,
           CASE WHEN c.is_nullable = 'NO' THEN ' NOT NULL ' ELSE ' ' END ,
           ';'
          ) AS'修正SQL'
FROM information_schema.columns c
WHERE c.table_schema = SCHEMA()
AND c.character_set_name = 'gbk';

1.4. 查看数据库使用端口

show variables  like 'port';

1.5. 查看当前数据库数据大小、索引大小以及总大小

SELECT concat(round(sum(data_length)/(1024*1024),2),' MB') AS 'DbData Size',
       concat(round(sum(index_length)/(1024*1024),2),' MB') AS 'DbIndex Size',
       concat(round(sum(a.data_length)/(1024*1024),2) +
       round(sum(a.index_length)/(1024*1024),2),' MB') AS 'DbTotal Size'
  FROM information_schema.tables a
 WHERE a.table_schema=SCHEMA();

1.6. 查看数据库的所有用户信息

SELECT DISTINCT concat('''',user,'''@''',host,''';') AS users
  FROM mysql.user;

1.7. 查看某个具体用户的权限

show grants for 'root'@'localhost';

1.8. 获取当前会话/进程用户名

SELECT  CURRENT_USER();
或：
SELECT SESSION_USER();

1.9. 获取当前会话/进程ID

SELECT connection_id();

file

1.10. 查看数据库连接的用户情况

1.11. 查询用户正在执行的SQL语句

SELECT *
  FROM information_schema.PROCESSLIST
 WHERE info IS NOT NULL;

file

1.12. kill正在执行SQL的进程

KILL [CONNECTION | QUERY] thread_id;

file

2. 数据库用户、权限及配置

2.1. 查询所有用户

SELECT user,host, CONCAT('User: ''',user,'''@''',host,''';') AS user_detail
  FROM mysql.user;

2.2. 查看数据库中具体某个用户的权限

SHOW GRANTS FOR 'username'@'host';

授予某个字段的权限，权限信息保存在mysql.columns_priv表中

SELECT * FROM mysql.columns_priv;

2.3. 赋予和回收删除数据库的权限

-- 赋予删除数据库权限
GRANT DROP ON databasename.* TO 'username'@'host';
-- 回收删除数据库权限
REVOKE DROP ON databasename.* FROM 'username'@'host';

file

2.4. 赋予用户查询information_schema数据库权限

任何用户都有information_schema下的表或者视图的访问权限, 但是有些视图或者表需要一些特殊的权限，比如说innodb_locks表就需要用户具有 process 权限才能访问。

file

GRANT PROCESS ON *.* TO 'username'@'host';

file

3. 日志

3.1. 查看日志开启状态

show variables like 'log_%';

file

3.2. 查看所有binlog日志列表

show master logs;
  • 示例
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000480 |       342 |
+------------------+-----------+

3.3. 查看最新一个binlog日志的编号名称，及其最后一个操作事件结束点

show master status;
  • 示例
MariaDB [(none)]>  show master status;
+------------------+----------+---------------+------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |
+------------------+----------+---------------+------------------+
| mysql-bin.000480 |      342 | paymentdb_nsd |                  |
+------------------+----------+---------------+------------------+

3.4. 刷新log日志，立刻产生一个新编号的binlog日志文件，跟重启一个效果

flush logs;
  • 示例
MariaDB [(none)]> flush logs;

MariaDB [(none)]>  show master status;
+------------------+----------+---------------+------------------+
| File             | Position | Binlog_Do_DB  | Binlog_Ignore_DB |
+------------------+----------+---------------+------------------+
| mysql-bin.000481 |      385 | paymentdb_nsd |                  |
+------------------+----------+---------------+------------------+

MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000480 |       389 |
| mysql-bin.000481 |       385 |
+------------------+-----------+

3.5. 使用命令mysqlbinlog去解析mysql日志文件

mysqlbinlog --no-defaults --database=easytongwlwdb -vv --base64-output=decode-rows --start-datetime='2022-03-15 18:10:00'  --stop-datetime='2022-03-15 18:40:00' ./mysql-bin.000572 > /tmp/parse-mysql-bin.000572.txt

3.6. 慢查询日志参数

-- 慢查询日志是否开启以及日志记录的位置
show variables like '%slow_query_log%';

-- 慢查询sql的设置时间，默认10s，sql执行时间大于该时间时才会被记录到慢查询日志中
show variables like 'long_query_time';

-- 慢查询日志存储方式
show variables like '%log_output%';

-- 未使用索引的查询是否记录到慢查询日志
show variables like 'log_queries_not_using_indexes';
  • 示例
MariaDB [(none)]> show variables like '%slow_query_log%';
+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| slow_query_log      | ON                                |
| slow_query_log_file | /data/etmaridb/log/mysql-slow.log |
+---------------------+-----------------------------------+

MariaDB [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 0.500000 |
+-----------------+----------+

MariaDB [(none)]> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | FILE  |
+---------------+-------+

MariaDB [(none)]> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+

3.7. 错误日志文件路径

show variables like 'log_error';
  • 示例
MariaDB [(none)]> show variables like 'log_error';
+---------------+------------------------------+
| Variable_name | Value                        |
+---------------+------------------------------+
| log_error     | /data/etmaridb/log/error.log |
+---------------+------------------------------+

4. 性能参数

参考：https://www.cnblogs.com/zengkefu/p/5608096.html

4.1. 读写比例

show global status like 'com_select';  获得服务器启动到目前查询操作执行的次数；
show global status like 'com_insert';  获得服务器启动到目前插入操作执行的次数；
show global status like 'com_update';  获得服务器启动到目前更新操作执行的次数；
show global status like 'com_delete';  获得服务器启动到目前删除操作执行的次数；
  • 计算读百分比
select concat(com_select / (com_select+com_insert+com_update+com_delete)* 100,'%');
  • 计算写百分比
select concat(com_insert+com_update+com_delete / (com_select+com_insert+com_update+com_delete) * 100,'%');

通过检查数据库读写比例，可反映出应用是读密集型还是写密集型。

4.2. 慢查询比例

开启慢查询日志：

slow_query_log = 1 # 开启慢查询日志
log_output = FILE|TABLE # 指定日志存储方式，默认为 file
slow_query_log_file = slow-query.log # 指定慢查询日志文件位置
long_query_time = 1 # 执行及响应时间超过该参数设置的值记录日志
show global status like 'Slow_queries '; # 获得服务器启动到目前慢查询操作记录的次数；

注意，慢查询包括 select 、 update 以及 delete ，没有 insert。

计算慢查询比例：

select concat(Slow_queries / (Com_select+Com_update+Com_delete) * 100,'%');

通过计算慢查询比例，可反映出数据库运行效能。

4.3. 连接数检查

show global status like 'max_connections'; # 获得数据库运行的最大连接数  //允许的最大连接数
show global status like 'Max_used_connections'; # 获得最大一次的连接数 //最大突发并行连接数
show global status like 'connections'; # 获得数据库运行到目前，总共被连接了多少次 //登陆的次数
show global status like 'Threads_connected'; # 获得当前连接数
show global status like 'Threads_running'; # 获得当前正在运行的连接数

计算当前连接数的比例：

select concat(Threads_connected / max_connections *100,'%'); # 计算最大一次的连接数比例：
select concat(Max_used_connections / max_connections *100,'%');# 通过连接数检查，可得知数据库在不同时间段被请求的压力

4.4. 线程缓存

show global status like 'Connections'; # 获得数据库运行到目前，总共被连接了多少次
show global status like 'Threads_created'; # 获得数据库运行到目前，创建连接线程的次数

计算连接线程缓存命中率:

select concat((Connections-Threads_created) / Connections × 100,'%');

通过计算连接线程缓存命中率，可反映出连接线程的命中情况，命中率越大越好。
如果命中率过低，则表示缓存连接线程的数量过少，可以考虑加大 thread_cache_size 的值。

4.5. 表缓存

show global status like 'Table_open_cache_misses'; # 新打开的表的次数。 不命中
show global status like 'Table_open_cache_hits'; # 从表缓存中拿已打开的表的次数，该状态变量 5.6 才开始存在  命中
show global status like 'Opened_tables'; # 打开表的总次数

通过已知自己数据库中有多少表，再观察 Opened_tables 的值，可以得知表缓存的数量是否合理，如果打开表的次数大于数据库中已有的表数量，则表示 table_open_cache 的值不够，可以考虑加大。

计算表缓存的命中率：

select concat(Table_open_cache_hits /(Table_open_cache_misses + Table_open_cache_hits) * 100,'%');

通过计算表缓存的命中率，可反映出表缓存的情况，该比例越大越好。

4.6. 临时表

show global status like 'Created_tmp_disk_tables'; # 查看在磁盘上创建临时表的次数
show global status like 'Created_tmp_tables'; # 查看创建临时表的总次数，包括在内存中和磁盘。

如果发现在磁盘上创建临时表的次数过多，则表示临时表的缓存区内存不够，可以考虑加大 tmp_table_size 和 max_heap_table_size 的值。

计算在磁盘上创建临时表的比例：

select concat(Created_tmp_disk_tables / Created_tmp_tables * 100,'%');

通过计算在磁盘上创建临时表的比例，可反映出数据库的使用临时表的情况，该比例越小越好。

4.7. 额外的排序

show global status like 'Sort_merge_passes'; # 在磁盘中进行额外排序的次数
show global status like 'Sort_scan'; # 通过表扫描进行排序的总次数，也就是额外排序的总次数

如果发现在磁盘上进行排序的次数过多，则表示排序缓冲区内存不够，可以考虑加大 sort_buffer_size 的值。

计算磁盘排序的比例：

select concat(Sort_merge_passes / Sort_scan * 100,'%');

通过计算在磁盘上进行额外排序的比例，可反映出数据库排序的情况，该比例越小越好。

4.8. binlog 缓冲

show global status like 'Binlog_cache_disk_use'; # 在磁盘上创建临时文件用于保存 binlog 的次数
show global status like 'Binlog_cache_use'; # 缓冲 binlog 的总次数，包括 binlog 缓冲区和在磁盘上创建临时文件保存 binlog 的总次数

如果发现在磁盘上创建临时文件保存 binlog 的次数过多，则表示 binlog 缓冲区内存不够，可以考虑加大 binlog_cache_size 的值。

计算在磁盘上创建临时文件保存 binlog 的比例：

select concat(Binlog_cache_disk_use / Binlog_cache_use * 100,'%');

通过计算在磁盘上创建临时文件保 binlog 的比例，可反映出数据库 binlog 的情况，该比例越小越好。

4.9. redo 日志

select global status like 'Innodb_log_waits'; # 查看 innodb redo 日志等待缓冲区刷新的次数。

当 redo 缓冲区容纳不下事务产生的 redo 日志时，本次事务产生的 redo 日志在写入 redo 缓冲区之前就必须等待 redo 缓冲区有足够的空间才能写入。

如果发现 redo 日志等待刷新的次数过多，则表示 innodb redo 日志缓冲区的大小不够，可以考虑加大 innodb_log_buffer_size 的值。

4.10. InnoDB 缓存

show global status like 'Innodb_buffer_pool_read_requests'; # 读取页的总次数
show global status like 'Innodb_buffer_pool_read'; # 从磁盘读取页的次数

如果发现从磁盘读取页的次数过多，则有可能是因为 innodb 缓冲池的大小不够，此时可以考虑加到 innodb_buffer_pool_size 的值。

计算 innodb 缓存命中率：

select concat((Innodb_buffer_pool_read_requests - Innodb_buffer_pool_read) / Innodb_buffer_pool_read_requests * 100,'%';

通过计算 innodb 缓存命中率，可反映出 innodb 缓存的效率，该比例越大越好。

Post Views: 1,327
Tagged ,