菜鸟学习配置MariaDB数据库日志

一，首先自己动手执行各种命令和观察执行结果。

1，查看版本

MariaDB [(none)]> show variables like '%version';

+------------------+----------------------+

| Variable_name | Value |

+------------------+----------------------+

| innodb_version | 5.5.61-MariaDB-38.13 |

| protocol_version | 10 |

| version | 5.5.65-MariaDB |

+------------------+----------------------+

3 rows in set (0.00 sec)

2，查看通用日志，OFF是还没有打开。

MariaDB [(none)]> show variables like '%general%';

+------------------+-------+

| Variable_name | Value |

+------------------+-------+

| general_log | OFF |

| general_log_file | 2.log |

+------------------+-------+

2 rows in set (0.00 sec)

3，查看慢查询日志状态

MariaDB [(none)]> show variables like '%query%';

+------------------------------+------------+

| Variable_name | Value |

+------------------------------+------------+

| expensive_subquery_limit | 100 |

| ft_query_expansion_limit | 20 |

| have_query_cache | YES |

| long_query_time | 10.000000 |

| query_alloc_block_size | 8192 |

| query_cache_limit | 1048576 |

| query_cache_min_res_unit | 4096 |

| query_cache_size | 0 |

| query_cache_strip_comments | OFF |

| query_cache_type | ON |

| query_cache_wlock_invalidate | OFF |

| query_prealloc_size | 8192 |

| slow_query_log | OFF |

| slow_query_log_file | 2-slow.log |

+------------------------------+------------+

14 rows in set (0.01 sec)

4，查看错误日志

MariaDB [(none)]> show variables like '%err%';

+--------------------+------------------------------+

| Variable_name | Value |

+--------------------+------------------------------+

| error_count | 0 |

| log_error | /var/log/mariadb/mariadb.log |

| max_connect_errors | 10 |

| max_error_count | 64 |

| slave_skip_errors | OFF |

+--------------------+------------------------------+

5 rows in set (0.00 sec)

5，查看二进制日志

MariaDB [(none)]> show variables like '%log_bin%';

+---------------------------------+-------+

| Variable_name | Value |

+---------------------------------+-------+

| log_bin | OFF |

| log_bin_trust_function_creators | OFF |

| sql_log_bin | ON |

+---------------------------------+-------+

3 rows in set (0.00 sec)

6，查看所有的日志状态

MariaDB [(none)]> show global variables like '%log%';

+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+

| Variable_name | Value |

+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+

| aria_checkpoint_log_activity | 1048576 |

| aria_log_file_size | 1073741824 |

| aria_log_purge_type | immediate |

| aria_sync_log_dir | NEWFILE |

| back_log | 50 |

| binlog_annotate_row_events | OFF |

| binlog_cache_size | 32768 |

| binlog_checksum | NONE |

| binlog_direct_non_transactional_updates | OFF |

| binlog_format | STATEMENT |

| binlog_optimize_thread_scheduling | ON |

| binlog_stmt_cache_size | 32768 |

| expire_logs_days | 0 |

| general_log | OFF |

| general_log_file | 2.log |

| innodb_flush_log_at_trx_commit | 1 |

| innodb_locks_unsafe_for_binlog | OFF |

| innodb_log_block_size | 512 |

| innodb_log_buffer_size | 8388608 |

| innodb_log_file_size | 5242880 |

| innodb_log_files_in_group | 2 |

| innodb_log_group_home_dir | ./ |

| innodb_mirrored_log_groups | 1 |

| innodb_recovery_update_relay_log | OFF |

| innodb_use_global_flush_log_at_trx_commit | ON |

| log | OFF |

| log_bin | OFF |

| log_bin_trust_function_creators | OFF |

| log_error | /var/log/mariadb/mariadb.log |

| log_output | FILE |

| log_queries_not_using_indexes | OFF |

| log_slave_updates | OFF |

| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |

| log_slow_queries | OFF |

| log_slow_rate_limit | 1 |

| log_slow_verbosity | |

| log_warnings | 1 |

| max_binlog_cache_size | 18446744073709547520 |

| max_binlog_size | 1073741824 |

| max_binlog_stmt_cache_size | 18446744073709547520 |

| max_relay_log_size | 0 |

| relay_log | |

| relay_log_index | |

| relay_log_info_file | relay-log.info |

| relay_log_purge | ON |

| relay_log_recovery | OFF |

| relay_log_space_limit | 0 |

| slow_query_log | OFF |

| slow_query_log_file | 2-slow.log |

| sql_log_bin | ON |

| sql_log_off | OFF |

| sync_binlog | 0 |

| sync_relay_log | 0 |

| sync_relay_log_info | 0 |

+-------------------------------------------+--------------------------------------------------------------------------------------------------------------+

54 rows in set (0.00 sec)

二，执行命令打开相应的日志

1，日志对应的名字

通用日志 general_log 

慢查询日志 slow_query_log  

错误日志 log_error 应该有默认打开

二进制日志 log_bin  

更新日志 log_slave_updates = 1 

 log 目录 

2，最终配置文件内容 more /etc/my.cnf

[mysqld]

log

log_bin

log_slave_updates = 1

slow_query_log=on

slow_query_log_file=my-slow.log

datadir=/var/lib/mysql

socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=0

# Settings user and group are ignored when systemd is used.

# If you need to run mysqld under a different user or group,

# customize your systemd unit file for mariadb according to the

# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]

log-error=/var/log/mariadb/mariadb.log

pid-file=/var/run/mariadb/mariadb.pid

# include all files from the config directory
!includedir /etc/my.cnf.d

3,重启数据库

systemctl restart mariadb