MySQL数据库管理-实战案例5
需求：
目前业务系统运行比较慢，怀疑是mysql数据库内存参数少低，需要修改1.请查看当前 innodb_buffer_pool 参数大小。2.请根据实际物理内存来临时设置全局参数 innodb_buffer_pool3.再将 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 |
+------------------+--------------------------------+