SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW VARIABLES： shows the values of MySQL system variables.
登入後複製
2、運作狀態：
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW STATUS： provides server status information.
登入後複製
修改系統設定
方法1：設定檔設定my.cnf 如：binlog_cache_size = 1M
方法2：set global binlog_cache_size = 1048576;
註：查看mysql的版本
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using EditLine wrapper
登入後複製
或
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 11
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.40 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 87 days 2 hours 22 min 4 sec
Threads: 1 Questions: 61 Slow queries: 0 Opens: 114 Flush tables: 1 Open tables: 107 Queries per second avg: 0.000
--------------
登入後複製
或
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.40 |
+-----------+
1 row in set (0.00 sec)
登入後複製
一般而言，日誌等級的定義沒有會話變數都只是在全域層級下進行定義錯誤日誌的狀態：
mysql> show global variables like '%log_error%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /usr/local/mysql/data/mysql.log |
| log_error_verbosity | 3 |
+---------------------+---------------------------------+
3 rows in set (0.00 sec)
登入後複製
其中log_error定義為錯誤日誌檔案路徑log_error_verbosity:
verbosity value
message types logged
##1
#errors only
2
errors and warnings
#3
errors,warinigs,and notes(default)
更改错误日志位置可以使用log-error来设置形式如下
[root@localhost ~]# vim /etc/my.cnf
log-error = /usr/local/mysql/data/mysqld.err
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log_bin=my-bin //二进制日志[路径[指定日志文件的名字]]
Expire_logs_days = 10 //清除日志的天数
Max_binlog_size = 100M //单个日志文件的大小限制，超出会新建一个默认为1GB
server_id=1 //mysql5.7版本以后需要添加serverid
[root@localhost ~]# service mysqld restart
登入後複製
Show variables 或show variables like 'log_%'; 语句来查询日志设置
mysql> show variables like 'log_bin%';
+---------------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/my-bin |
| log_bin_index | /usr/local/mysql/data/my-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+------------------------------------+
5 rows in set (0.00 sec)
登入後複製
查看二进制日志
MySQL的二进制日志包含了所有修改的信息，因此常被广泛应用。当MySQL创建二进制日志文件时，首先创建一个以’filename’为名称，以’.index’为后缀的文件；在创建一个以’filename’为名称，以’.000001’为后缀的文件。当MySQL服务重启一次，以’.000001’为后缀的文件会增加一个，并且后缀名加1递增。如果日志长度超过max_binlog_size的上限，也会创建一个新的日志。You can view the number and file names of current binary log files by using the command "Show binary logs;".。若需查看二进制日志的内容，应使用mysqlbinlog命令，而无法直接查看。
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| my-bin.000001 | 154 |
+---------------+-----------+
1 row in set (0.00 sec)
登入後複製
或者
mysql> show master logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| my-bin.000001 | 154 |
+---------------+-----------+
1 row in set (0.00 sec)
登入後複製
查看二制日志的内容
退出mysql在命令行
[root@mysql ~]# mysqlbinlog myusql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'myusql-bin.000001' not found (Errcode: 2 - No such file or directory)
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysql> show global variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/mysql-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)
mysql> show global variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name | Value |
+----------------------------------------------------------+-----------+
| long_query_time | 10.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)
登入後複製
启动和设置慢查询日志：
方法1：通过配置文件my.cnf开启慢查询日志：
注：在不同的mysql版本中，开启慢查询日志参数不太一样，不过都可以通过 show variables like "%slow%" 和show variables like "%long%"查看出来。
mysql> show global variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/mysql-slow.log |
+---------------------------+--------------------------------------+
5 rows in set (0.00 sec)
