2.2.2 MySQL基本功能与参数文件管理

MySQL数据库管理-实战案例5

需求：

目前业务系统运行比较慢，怀疑是mysql数据库内存参数少低，需要修改

1.请查看当前 innodb_buffer_pool 参数大小。

2.请根据实际物理内存来临时设置全局参数 innodb_buffer_pool

3.再将 innodb_buffer_pool 参数设置永久生效，修改之前请备份当前参数文件。

mysql root@localhost:(none)> show variables like 'innodb_buffer_pool%'
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
10 rows in set
Time: 0.012s
mysql root@localhost:(none)> select @@innodb_buffer_pool_size
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 134217728                 |
+---------------------------+
1 row in set
Time: 0.005s


mysql root@localhost:(none)> set global innodb_buffer_pool_size=805306368
Query OK, 0 rows affected
Time: 0.001s

在参数文件中设置参数 my.cnf
innodb_buffer_pool_size=768M
重启生效，再检查

MySQL数据库管理-实战案例6 

MySQL 日常维护过程中，因系统出现问题，请收集各种日志文件、服务器各种状态信息并提交线二线工程师分析。

错误日志（ error log）: show variables like '%log_error%'
二进制日志（ binlog）: show variables like '%log_bin%';
慢查询日志（ slow query log）: 
show variables like 'slow_query_log';
show variables like 'long_query_time';
全局日志（log）: show variables like "%general%";
中继日志（relay_log）: show variables like '%relay%';

常用操作

1、查看数据库版本
show variables like 'version'
2、列出 MySQL Server上的数据库。
SHOW DATABASES
3、查看服务器状态
mysql> status
mysql> show status
4、査看数据库存储引擎
mysql> show engines
5、查看引擎插件
mysql> show plugins
6、查看引擎状态
mysql> Show engine innodb status
7、查看数据库进程信息
mysql> show processlist
有哪些线程在运行，不仅可以查看当前所有的连接数，还可以查看当前的连接状态帮助识别出有问题的查询语句等
8、查看数据库参数
mysql> show variables
9、查看当前数据库的字符集
mysql> show collation like '%utf8%';
10、查看当前数据库的校对规则
mysql> show variables like 'collation%'
11、查看是否配置 Replication：
show master status
show slave status

 

操作结果

 

mysql root@localhost:(none)> show variables like 'innodb_buffer%'
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_chunk_size       | 134217728      |
| innodb_buffer_pool_dump_at_shutdown | ON             |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_dump_pct         | 25             |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_in_core_file     | ON             |
| innodb_buffer_pool_instances        | 1              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | ON             |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 134217728      |
+-------------------------------------+----------------+
11 rows in set
Time: 0.019s

mysql root@localhost:(none)> select @@innodb_buffer_pool_size
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 134217728                 |
+---------------------------+
1 row in set
Time: 0.008s

mysql root@localhost:(none)> set global innodb_buffer_pool_size=805306368
Query OK, 0 rows affected
Time: 0.006s

mysql root@localhost:(none)> select @@innodb_buffer_pool_size
+---------------------------+
| @@innodb_buffer_pool_size |
+---------------------------+
| 805306368                 |
+---------------------------+
1 row in set
Time: 0.008s

mysql root@localhost:(none)> show variables like 'log_error%'
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
4 rows in set
Time: 0.009s

mysql root@localhost:(none)> show variables like 'log_bin%'
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
+---------------------------------+-----------------------------+
5 rows in set
Time: 0.009s

mysql root@localhost:(none)> show variables like 'slow_quer%'
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_query_log      | OFF                                 |
| slow_query_log_file | /var/lib/mysql/dbhost-2020-slow.log |
+---------------------+-------------------------------------+

mysql root@localhost:(none)> show variables like 'general%'
+------------------+--------------------------------+
| Variable_name    | Value                          |
+------------------+--------------------------------+
| general_log      | OFF                            |
| general_log_file | /var/lib/mysql/dbhost-2020.log |
+------------------+--------------------------------+