MySQL Learning binary log

The binary log records all changes to the database so that any Slave can keep the data consistent by executing the Master binary log.

The binary log contains only statements that may alter the database. Statements that are not yet possible to alter the database are also recorded, paying attention to statements that can change, such as the DROP TABLE ifexists CREATE if not EXISTS, and those that do not match any rows. Select statements are generally not logged because they do not make any changes to the database.

transactions on the server are usually not executed one after the other, but are interleaved parallel execution, in order to prevent inconsistent results from conflicts between two transactions, the server ensures that the execution of the transaction is sequential. Binary logs record transactions in the order in which they are submitted on master, although transactions may be interleaved on master , but the order of each transaction in the binary log is constant, depending on the commit of the transaction ( commit) time.

The structure of binary logs

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/4C/7E/wKioL1Q-bhvz628zAADCAobzMzM496.jpg "title=" 9b9 ]3u5efa@_q{ydq{ry~a9.png "alt=" wkiol1q-bhvz628zaadcaobzmzm496.jpg "/>

look, The binary log consists of several files, the events are stored in a series of binlog file , the file name is similar to host-bin.000001 host-bin.index binlog file, Each line of the index file contains a binlog purge BINARY LOGS restart MASTER FLUSH LOGS binlog

Data security

The more common sensitive information is the password, the event that contains the password is written to the binary log when the statement that changes the table in the database is executed, and it contains the required password to access the table.

Cases:

Update employee set pass= password (' 123456 ') where email = ' [email protected] ';

If replication is correct, it is best to rewrite this statement by calculating and storing the hash password to the user-defined variable and then using it in the expression:

Set @password =password (' 123456 ');

Update employee set pass= @password where email = ' [email protected] ';

because the set statement is not replicated, the password is not stored in the binary log, but is stored in server memory only when the statement is executed.

Clear Binlog file

Over time, the server gathers the Binlog file, setting the expire-logs-days option for the server to automatically clean up the old binlog file . This option can be used as a server variable to reach the number of days you want to keep the Binlog file, and if you want to remain automatically cleared after a restart, you must add the setting to the my.cnf file.

Use The PURGE BINARY LOGS command cleans binlog files manually, in the following two formats:

PURGE Binarylogs before datetime

This command format clears all binlog files before the given time . If datetime is in the middle of a log file, all files before the datetime file will be purged.

Practical Walkthrough:

1, because it is deleted by date time, we must first know the need to delete the binary log before that time.

Mysqlbinlog--force-if-open--base64-output=never mysql-bin.000003

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/4C/7E/wKioL1Q-byHyk5DoAAJv_OFARIo967.jpg "title=" Gmzvnbe@zklhb0iy{05e3]i.jpg "alt=" Wkiol1q-byhyk5doaajv_ofario967.jpg "/>

2.If we need to delete the log before 2014/09/09, execute the following command:

PURGE BINARY LOGS before 140909;

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/4C/7D/wKiom1Q-b2ahjwdVAAJM8b2znxw457.jpg "title=" 19zvkkpc5i]65 (}@b) p4[vc.jpg "alt=" Wkiom1q-b2ahjwdvaajm8b2znxw457.jpg "/>

PURGE BINARY LOGS to ' filename '

This command format clears all files before the given file. that is , all files in the Show masterlogs command output will be deleted before the filename.

Practical Walkthrough:

PURGE BINARY LOGS to ' mysql-bin.000002 ';

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M02/4C/7E/wKioL1Q-b96x3XpCAAGBmvM3Ujg201.jpg "title=" Qq20141015172403.png "alt=" Wkiol1q-b96x3xpcaagbmvm3ujg201.jpg "/>

Mysqlbinlog use of tools

Mysqlbinlog

--short-form print only the executed SQL statement information, ignoring event comment information about the binary log.

--force-if-open if the Binlog file is being written to, or because the server crashes,mysqlbinlog will print a warning message saying that the binlog file is not properly closed and this option ignores the print warning message.

--base64-output=never prevents mysqlbinlog from printing base64-encoded events.

Start-position=bytepos the byte position of the first event to dump. If several Binlog are provided to Mysqlbinlog , this location will be interpreted as the location of the first file in the serial number.

Stop-position=bytepos The byte position of the last printed event, if multiple binlog files are given , the location of the last file in the sequence.

Start-datetime=datetime only events that have a timestamp or datetime are printed .

Stop-datetime=datetime prints only those events that were before the time stamp or datetime .

To read a remote file instance:

Mysqlbinlog--read-from-remote-server--host=192.168.18.202--base64-output=never--user=repl_user--password-- Start-position=386--stop-position=643 mysqld-bin.000001

we then use Mysqlbinlog to read a binary log file:

Mysqlbinlog--force-if-open--base64-output=never/mylogbin/mysql-bin.000001 | Cat-n

1/*!50530 [email protected] @SESSION. pseudo_slave_mode=1*/;

2/*!40019set @ @session. max_insert_delayed_threads=0*/;

3/*!50003set @[email protected] @COMPLETION_TYPE, completion_type=0*/;

4 delimiter/*!*/;

5 #at 4

6 #141015 9:03:53 Server ID 1 end_log_pos 245 start:binlog v 4, Server v 5.5.32-mariadb-log created141015 9:03 : + at startup

7 rollback/*!*/;

8 #at 245

9 #141015 9:09:21 Server ID 1 end_log_pos 428 Query thread_id=1 exec_time=0 error_code=0

Ten use ' Test '/*!*/;

One by one settimestamp=1413335361/*!*/;

[Email protected] @session. pseudo_thread_id=1/*!*/;

[Email protected] @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0,@ @session. Unique_checks=1, @@ session.autocommit=1/*!*/;

[Email protected] @session. sql_mode=0/*!*/;

[Email protected] @session. auto_increment_increment=1, @ @session. auto_increment_offset=1/*!*/;

/*!\cutf8 *//*!*/;

[Email protected] @session. character_set_client=33,@ @session. collation_connection=33,@ @session. Collation_ server=8/*!*/;

[Email protected] @session. lc_time_names=0/*!*/;

[Email protected] @session. collation_database=default/*!*/;

CreateTable Employee (id int auto_increment,name char (+) not Null,emailchar (+), password char, primary key (ID) )

21/*!*/;

#at 428

#141015 9:12:27 Server ID 1 end_log_pos 496 Query thread_id=1 exec_time=0 error_code=0

settimestamp=1413335547/*!*/;

BEGIN

26/*!*/;

1~7 The corresponding sections of the row, they fix the values set in the prologue, and then roll back the active transaction.

every time the database changes, the USE statement for line One is printed, and when a use statement appears, he is the first line of a new event.

The following line immediately below The use statement is the SET TIMESTAMP this statement provides the timestamp from the time the epoch Time begins execution of an event that is computed in seconds.

The 12~19 line contains the general settings, similar to the use of line Ten,only the first event and when their values are changed, they are printed.

Detailed comments

# at 245

#141015 9:09:21 Server ID 1 end_log_pos 428 Query thread_id=1

Exec_time=0 error_code=0

The parts of the note have the following meanings:

At 245

The byte position at which the event begins, that is, the first byte of the event;

141015 9:09:21

The time at which the event was written to the binary log;

Server ID 1

Server IDs of the servers that generated the events ;

End_log_pos 428

Immediately after the event, the byte position at which the next event begins;

Query

event types, such as User_var,intvar and Xid;

Thread_id=1

The thread that executed the event ID;

Exec_time=0

The query execution time in seconds;

Error_code=0

The error code obtained from the execution result of the statement;

Binary Log configuration options

Expire-log-days=days

Binlog The number of days that need to be retained. When the binary log is rotated or the server restarts, files older than the specified value are removed from the file system. This option defaults to 0, which means that the Binlog file will never be deleted.

Log-bin [=basename]

Turn on the binary log by adding the Log-bin option in my.cnf , which gives the basic name of the Binlog file, which is the part of the file name before the dot. If the option does not specify basename, the base name defaults to Host-bin, and it is strongly recommended that you assign a value to the Log-bin option. Otherwise, when hostname changes,the Binlog file will be renamed (unless Pid-file is given an exact value).

Log-bin-index [=filename]

given the name of the index file, similar to the Log-bin option,hostname will be used to make up the index file name, meaning that if the hostname changes, the copy will be interrupted, For this reason, it is recommended to assign a value to this option.

Binlog-cache-size=bytes

Transaction cache, when the size of the transaction cache exceeds this value, the remaining data is entered into the disk. This has the potential to cause performance problems, so if you use many large transactions, increasing this option can improve performance.

Note that allocating very large buffers is not a good idea because it means that the rest of the server gets less memory, which can lead to performance degradation.

Max-binlog-cache-size=bytes

Use this option to limit the size of each transaction in the binary log. Because large transactions have the potential to block binary logs for a long time, they will cause significant performance problems for other threads to escort two binary logs. If the size of the transaction exceeds bytes, the statement is terminated with an error.

Max-binlog-size=bytes

specifying the size of each binlog file, such as a transaction or statement exceeding max-binlog-size, the binarylog will be rotated and the entire contents of the transaction will be written to the new file because the transaction will never be split into a different Binlog file.

Sync-binlog=period ( period, period )

Specifies how long to write the binary log to disk once through Fdatasync, and if the given value is 1, call Fdatasync each time the transaction commits , and if the given values are ten, each A fdatasync will be called once for the ten transaction commits . A value of 0 means that it is never called, and the server trusts that the operating system writes binary logs to disk as part of normal file processing.

Read-only

prevents any client process (except Slave processes and users with SUPER permissions Update any data on the server.

The content of this article is purely hand-hit, is the author read the knowledge learned, from which some of the more critical content and the author's experimental process recorded down.

This article from the "Bad Guys Blog" blog, reproduced please contact the author!

MySQL Learning binary log