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
