Binary logs are primarily used to record any operations that may alter the database, and are therefore primarily used to record DDL DML statements, including the database authorization tone. The record is in binary format. We cannot use normal text view commands to view them. Only use the dedicated commands provided by MySQL, called MysqlbinlogBinary logs are very important for our database server crashes when relying on binary logs to recoverIn general, in the data directory, starting with the host name or Mysql-bin, the MySQL server restarts once, the log will scroll once, the log needs to scroll continuously. And records are growing at a very fast pace. is an important tool that enables MySQL replication, as well as instant point recovery, to view all binary log files on the current server show binary logs; see if the database records binary logs show GLOBAL VARIABLES like '%log% '; The binary log on needs to be defined in the configuration file. Comment out the log-bin in front of the binary log format: 1 based on the statement (Statemen T), if the results of two executions are the same as the result is the same as the statement to record 2-based row: record that row of related changes. 3 Mixed Mode (mixed): The binary log grows to a certain extent and scrolls once. If our data file has 10G, we back up 10G in Wednesday, run two days later to Friday, the server hard disk is broken, we backup time only to Wednesday, the next two days the resulting data is not backed up, you need to rely on binary files to retrieve the two days of data, Therefore, our binary log files are best not to be placed on the same hard disk as the database server. This recovery data is called instant-point restore if the data is not backed up, but the binary log files are all in and can be recovered, but the recovery time is slower than the binary log event: The binary time log is logged at record time: 1. The time the event was generated. 2. The relative position of each event in the log file binary log file consists of: Index file (save file in the data directory is called Mysql-bin.index is a text file) from the inside can see how many binary log file binary log file The binary log file is saved by default with data directory, usually named mysql-bin.000001 mysql-bin.000002 mysql-bin.000003 .... Wait, use show master status; View the binary log files currently in use to view the contents of a binary log file such as viewing mysql-bin.000003 using the command show BINLOG EVENTS in 'mysql-bin.000003 ';You can also add the from command to indicate from which location to start viewing show BINLOG EVENTS in ' mysql-bin.000003From 107 ';SERVER_ID: Indicates which MySQL server generated the binary log using Mysqlbinlog can also view the log. and specify the information that the log displays. Common options:--start-datetime--stop-datetime--start-position--stop-position option is to display the entire file content Mysqlbinlog mysql-bin.000003 Specify Location View Mysqlbinlog--start-position = 107--stop-postion = 358 mysql-bin.000003 Specify time view Mysqlbinlog--start-datetime= ' 2016-03-13 10:40:50 ' mysql-bin.000003 on Linux You can save the results of the view to a file in the. SQL end of our specified path Mysqlbinlog--start-datetime= ' 2016-03 -13 10:40:50 ' mysql-bin.000003 >/root/a.sql after you can import this SQL file directly into the database, the SQL file will be inside the SQL to execute the tone. So we usually use the Mysqlbinlog query to the output of the results to redirect to the SQL script, import into the other database can be realized by the Instant Point Restore manual scrolling log: FLUSH LOGS; (Only the binary log is scrolled)never allow the RM command to delete a binary log file, causing the database to crashDelete binary log files usingpurge command Usage purge BINARY LOGS to Log_name; This is to delete all the logs before this log name, instead of deleting the server variables associated with the binary log for this log file. Use show GLOBAL VARIABLES like '%log% '; Binlog_format: Binary log format binlog_cache_size: Cache size, binary logs are cached in memory before they are written to disk binlog_cache_size with Binlog_stmt_cache_size Change the size of the Binlog_stmt_cache_size: is also the cache, record the transaction-related binary log cache size change binlog_stmt_cache_size size, binlog_cache_size will follow the change, not recommended to adjust, Possible data loss Log_bin: If binary logging is enabled, this feature is turned on, but it is not always necessary to log whether the binary log is actually logged and depends on additional variables. You need to specify the path in the configuration file when opening. Sql_log_bin: Used to control whether binary log information is logged into the log file Sql_log_off: Used to control whether the generic query log class is prevented from logging to the query log file, regardless of the binary log Sync_binlog: Set how often the binary log is synchronized to the disk file , 0 means no synchronization, and 0 o'clock depends on Auto_commit (transaction autocommit, immediately written to the log). If this value is any positive number, it indicates how many times the write operation was synchronized to disk. The benefit of setting this to 0 is that the transaction is not committed and the log is not written to disk. This will also reduce the IO, performance to improve the speed of the binary log on the hard disk, the better the IO performance, our server performance is better, because our binary log only one, if the concurrent multiple threads to perform the modification of the database operation, it will require several threads to write the binary log at the same time. So if our hard drive is a SSD, the better the read and write speed, the better the server performance Expire_logs_days: Indicates how many days later the log log will expire and automatically delete the hard disk IO performance metrics: IOPS represents the number of IO operations that hard disk IO can make per second. Desktop HDD: 100 SCSI drives per second: 200 SSDs per second: about 500-2000 per second
MySQL bin Log
