Shulou(Shulou.com)06/03 Report--

Mysql log type: 1. Query log (usually not enabled and not recommended) 2, slow log (recommended) 3, error log (recommended) 4, binary log (recommended) 5, relay log 6, transaction log (to help transactional storage engine complete ACID test) mysql > SHOW GLOBAL VARIABLES LIKE'% log # query log with variables related to log: general_log=ON | whether OFF # enables query log general_log_file=PATH # query log file path log_output=FILE | TABLE | NONE # output mode The default is FILE slow query log: mysql > SHOW GLOBAL VARIABLES LIKE 'long_query_time' for query operations that take longer than the specified time. # View timeout server variable: slow_query_log=ON | whether OFF # enables slow log log-slow-queries=PATH # Storage path of slow log. Default is HOSTNAME-slow.log log_output=FILE | TABLE # output method The default is FILE mysqldumpslow # slow query log analysis tool error log: 1. Record the event information generated during mysql startup and shutdown 2, record the error message generated during mysql operation 3, in the master-slave architecture Start the information generated from the server thread from the server server variable: log_error=OFF | ON | PATH # start error log If you do not specify the error log location, the default location log_warnings=0 will be used | 1 # whether to record warning messages binary log: record SQL statement function that causes or may cause data changes: used to implement the "playback" of events in the log file. Make a copy of the data mysql > SHOW MASTER LOGS # View the list of available binary log files mysql > SHOW MASTER STATUS; # View the binary log files mysql > SHOW BINLOG EVENTS that are in use # View binary log file content server variable: sql_log_bin=OFF | whether ON # enables binary log log_bin=OFF | ON # whether binary log is enabled Must be enabled at the same time to take effect binlog_format=STATEMENT | ROW | MIXED # binary log format max_binlog_size= # maximum size of a single binary file 1, automatic scrolling when the maximum size is reached 2, automatic scrolling after restart | 1 # whether to write synchronously The default is asynchronous write 1, synchronous write will affect server performance 2, Asynchronous writes may cause data loss binary file format: statement-based records: STATEMENT advantages: data recorded in the same volume has more disadvantages: there is no guarantee that the playback data is exactly the same as the source data, such as: mysql > INSERT INTO test VALUE (1) Now () # the system time obtained each time is different based on row (data) records: ROW advantages: it can ensure that the playback data is basically the same as the source data. Disadvantages: less data is recorded in the same volume. Mixed mode: MIXED The system determines the way in which binaries are stored: log file: mysql-bin.00000X # binary file index file: mysql-bin.index # saves the list of currently available binary log files Format: mysqlbinlog # binary file analysis tool use format: mysqlbinlog [--start-position= event start location] binary log file example: mysqlbinlog mysql-bin.000003 # at 655 # 170220 8:33:37 server id 1 end_log_pos 731 Query thread_id=12 exec_time=0 error_code=0 SET timestamp 1487550817 BEGIN / *! * / Start position of the event: at 655 the time of occurrence of the event: 170220 8:33:37 server id 1:server id When doing a mysql cluster, you must ensure the end position of the server id globally unique event: the type of end_log_pos 731 event: the time error when the server where the Query event occurs executes the thread ID:thread_id=12 statement of this event and writes it to the binary log Error code: error_code relay log: slave service log for master-slave replication in master-slave architecture Mainly used to save read event transaction logs from binary log files: managed by transactional storage itself and using cleanup logs: PURGE BINARY LOGS TO 'mysql-bin.010' # clear the binary log before the specified log PURGE BINARY LOGS BEFORE '2008-04-02 22 46 purge 26 log; # clear the binary log before the specified time

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.