MariaDB日志功能

MYSQL日志:
查询日志:系统产生的查询操作
慢查询日志:查询执行时间长超过指定时长的查询,即为慢查询
错误日志:
二进制日志:复制依赖于次日志
中继日志:
事务日志:随机I/o转换为顺序I/O,ACID:持久性

1,当一条写入数据到mysql,同时会把语句或行写入记录到二进制文件中一份
2,从sql通过授权的用户不断的发起连接请求,并尝试读取主服务器的二进制日志的每一个条目
3,读取到数据后,在执行之前先保存在本地的二进制日志文件中(中继日志),在读取日志中条目执行

因此,从服务器读取数据是从中继日志读取,他的二进制日志则可以关闭来提高性能
另外,从服务器是不能进行额外的写操作,只能从中继中读取,因此从服务器只能只读

innodb_buffer:
事务需要满足永久性和回滚。将随机IO转换线性IO,日志文件并不是日志保存的长久空间,最终将写入到硬盘!
当用户请求到到达后先读取内存缓冲区(innodb_buffer),而后读取事务日志,如果事务日志存储到硬盘,则从硬盘读取
如果此时没有被同步到硬盘,B请求到后仍需到内存或者事务日志中找数据

因此,事务日志中保存的数据接近于数据库表中数据,因为最终数据将保存到硬盘。

日志文件组:至少应该有两个日志文件
为了提高性能尽可能使用小事务引擎
为了保证提交的事务不消失,在不可控的情况下应该立即写入到持久存储上,我们知道在空闲情况下,事务日志会提交到硬盘、一个数据刚从内存同步到事务日志中还没有写入到系统中,此刻系统崩溃,在下次mysql启动他必须保证把提交的事务,从事务日志中同步到数据文件中去,未提交的事务则需要回滚,这个过程则是崩溃恢复!而事务恢复的能力是与身而来,但是对于MyISA事务引擎崩溃恢复则不安全。
事务日志是不能恢复数据,只能保证提交的数据不丢失,未提交的回滚而已!恢复则需要二进制和此前的备份。
而MariaDB的Maria搜索引擎通过其他的机制来实现崩溃安全恢复

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE 'innodb%';
+-------------------------------------------+------------------------+
| Variable_name                             | Value                  |
+-------------------------------------------+------------------------+
| innodb_adaptive_flushing                  | ON                     |
| innodb_adaptive_flushing_method           | estimate               |
| innodb_adaptive_hash_index                | ON                     |
| innodb_adaptive_hash_index_partitions     | 1                      |
| innodb_additional_mem_pool_size           | 8388608                |
| innodb_autoextend_increment               | 8                      |
| innodb_autoinc_lock_mode                  | 1                      |
| innodb_blocking_buffer_pool_restore       | OFF                    |
| innodb_buffer_pool_instances              | 1                      |
| innodb_buffer_pool_populate               | OFF                    |
| innodb_buffer_pool_restore_at_startup     | 0                      |
| innodb_buffer_pool_shm_checksum           | ON                     |
| innodb_buffer_pool_shm_key                | 0                      |
| innodb_buffer_pool_size                   | 134217728              |
| innodb_change_buffering                   | all                    |
| innodb_checkpoint_age_target              | 0                      |
| innodb_checksums                          | ON                     |
| innodb_commit_concurrency                 | 0                      |
| innodb_concurrency_tickets                | 500                    |
| innodb_corrupt_table_action               | assert                 |
| innodb_data_file_path                     | ibdata1:10M:autoextend |
| innodb_data_home_dir                      |                        |
| innodb_dict_size_limit                    | 0                      |
| innodb_doublewrite                        | ON                     |
| innodb_doublewrite_file                   |                        |
| innodb_fake_changes                       | OFF                    |
| innodb_fast_checksum                      | OFF                    |
| innodb_fast_shutdown                      | 1                      |
| innodb_file_format                        | Antelope               |
| innodb_file_format_check                  | ON                     |
| innodb_file_format_max                    | Antelope               |
| innodb_file_per_table                     | OFF                    |

| innodb_flush_log_at_trx_commit            | 1                      |
| innodb_flush_method                       |                        |
| innodb_flush_neighbor_pages               | area                   |

| innodb_force_load_corrupted               | OFF                    |
| innodb_force_recovery                     | 0                      |
| innodb_ibuf_accel_rate                    | 100                    |
| innodb_ibuf_active_contract               | 1                      |
| innodb_ibuf_max_size                      | 67092480               |
| innodb_import_table_from_xtrabackup       | 0                      |
| innodb_io_capacity                        | 200                    |
| innodb_kill_idle_transaction              | 0                      |
| innodb_large_prefix                       | OFF                    |
| innodb_lazy_drop_table                    | 0                      |
| innodb_lock_wait_timeout                  | 50                     |
| innodb_locking_fake_changes               | ON                     |
| 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_max_bitmap_file_size               | 104857600              |
| innodb_max_changed_pages                  | 1000000                |
| innodb_max_dirty_pages_pct                | 75                     |
| innodb_max_purge_lag                      | 0                      |
| innodb_merge_sort_block_size              | 1048576                |
| innodb_mirrored_log_groups                | 1                      |
| innodb_old_blocks_pct                     | 37                     |
| innodb_old_blocks_time                    | 0                      |
| innodb_open_files                         | 300                    |
| innodb_page_size                          | 16384                  |
| innodb_print_all_deadlocks                | OFF                    |
| innodb_purge_batch_size                   | 20                     |
| innodb_purge_threads                      | 1                      |
| innodb_random_read_ahead                  | OFF                    |
| innodb_read_ahead                         | linear                 |
| innodb_read_ahead_threshold               | 56                     |
| innodb_read_io_threads                    | 4                      |
| innodb_recovery_stats                     | OFF                    |
| innodb_recovery_update_relay_log          | OFF                    |
| innodb_replication_delay                  | 0                      |
| innodb_rollback_on_timeout                | OFF                    |
| innodb_rollback_segments                  | 128                    |
| innodb_show_locks_held                    | 10                     |
| innodb_show_verbose_locks                 | 0                      |
| innodb_simulate_comp_failures             | 0                      |
| innodb_spin_wait_delay                    | 6                      |
| innodb_stats_auto_update                  | 1                      |
| innodb_stats_method                       | nulls_equal            |
| innodb_stats_modified_counter             | 0                      |
| innodb_stats_on_metadata                  | ON                     |
| innodb_stats_sample_pages                 | 8                      |
| innodb_stats_traditional                  | ON                     |
| innodb_stats_update_need_lock             | 1                      |
| innodb_strict_mode                        | OFF                    |
| innodb_support_xa                         | ON                     |
| innodb_sync_spin_loops                    | 30                     |
| innodb_table_locks                        | ON                     |
| innodb_thread_concurrency                 | 0                      |
| innodb_thread_concurrency_timer_based     | OFF                    |
| innodb_thread_sleep_delay                 | 10000                  |
| innodb_track_changed_pages                | OFF                    |
| innodb_use_atomic_writes                  | OFF                    |
| innodb_use_fallocate                      | OFF                    |
| innodb_use_global_flush_log_at_trx_commit | ON                     |
| innodb_use_native_aio                     | OFF                    |
| innodb_use_stacktrace                     | OFF                    |
| innodb_use_sys_malloc                     | ON                     |
| innodb_use_sys_stats_table                | OFF                    |
| innodb_version                            | 5.5.43-MariaDB-37.2    |
| innodb_write_io_threads                   | 4                      |
+-------------------------------------------+------------------------+
103 rows in set (0.02 sec)

MariaDB [(none)]> 

查询日志默认是关闭的
参数:
log={ON|OFF}是否记录所有语句的日志信息于一般查询日志(general_log)
log_output={TABLE|FILE|NONE}:TABLE表,FILE文件,NONE不记录,TABLE和FILE可以同时出现,用逗号分隔即可
general_log:是否启用查询日志
general_log_file: 一般查询日志保存文件

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                          | mysql-salve.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                                   | ON                                                                                                           
| log_bin_trust_function_creators           | OFF                                                                                                          
| log_error                                 | /data/mysql/mysql-salve.err                                                                                  
| 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                       | mysql-salve-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.01 sec)

MariaDB [(none)]> 



开启:
MariaDB [(none)]> SET GLOBAL log='ON';
Query OK, 0 rows affected, 1 warning (0.03 sec)

MariaDB [(none)]> SET GLOBAL general_log='ON';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE '%log%';
general_log                               | ON    
log                                       | ON                                                                                                          
log_bin                                   | ON  

查看记录的命令

[root@mysql-master mysql]# cat mysql-salve.log 
/usr/local/mysql/bin/mysqld, Version: 5.5.44-MariaDB-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
160114  1:55:38    21 Query SET GLOBAL general_log='ON'
160114  1:55:41    21 Query SHOW GLOBAL VARIABLES LIKE '%log%'
160114  1:57:01    21 Query SELECT DATABASE()
           21 Init DB   mysql
160114  1:57:04    21 Query show tables
160114  1:57:13    21 Query sece db
160114  2:00:32    21 Query show databases
160114  2:00:36    21 Query SELECT DATABASE()
           21 Init DB   mysql
160114  2:00:40    21 Query show tables
160114  2:01:49    21 Query show tables
160114  2:02:24    21 Query SELECT * PROM user
160114  2:02:39    21 Query SELECT * PROM classes
160114  2:03:07    21 Query SHOW GLOBAL VARIABLES LIKE 'query_cache%'
[root@mysql-master mysql]# 

关闭OFF即可

MariaDB [mysql]> SET GLOBAL log='ON';
MariaDB [mysql]> SET GLOBAL general_log='OFF';

关闭后将不在记录日志

如果需要记录到数据库中,则需要SET GLOBAL log_output=‘TABLE’;即可

慢查询日志

MariaDB [(none)]>  SHOW GLOBAL VARIABLES LIKE 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)

慢查询日志
long_query_time : 10.000000 :查询时长,超过这个时间都被记录为慢查询
slow_query_log : OFF :是否启用慢查询日志,只有ON和OFF,日志的输出位置取决于log_output=(TABLE|FILE|NONE) |
slow_query_log_file : mysql-salve-slow.log :定义日志文件路径和名称

开启慢查询日志

SET GLOBAL slow_query_log=1;

打开日志的存放方式

MariaDB [(none)]> SET GLOBAL log_output='FILE,TABLE';
Query OK, 0 rows affected (0.03 sec)

过滤器

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                          | ON      
log_slow_rate_limit                       | 1                                                                                                             log_slow_verbosity                        |  

错误日志:
不仅是错误信息,还有服务器启动和关闭过程中的信息
服务器运行过程中真正的错误信息
事件调度器运行一个事件产生的信息
在复制架构中的从服务器上启动从服务器线程时产生的信息

| log_error                                 | /data/mysql/mysql-salve.err  
| log_warnings                              | 1      
4 分享

您可以选择一种方式赞助本站

支付宝扫码赞助

支付宝扫码赞助

日期: 2016-01-16分类: Mariadb

标签: mariadb

发表评论