What is the difference between MySQL's Log_bin and sql_log_bin?

The use of binary restore database, suddenly a bit tangled, log_bin and sql_log_bin What is the difference? OK, search, combined with their own experience, simply say a bit.

Log_bin: Binary log.

When MySQL starts, the command line or configuration file determines whether Binlog is turned on, and Log_bin is the only state that reports the current Binlog system (open or not). If you want to close the Binlog, you can modify the Sql_log_bin and kill the original connection, you can also modify the Log_bin, and then restart MySQL, the latter is more thorough, the disadvantage is the need to restart.

When Binlog is turned on in a configuration file or command line, you can specify a value for Log_bin, such as –log_bin=mysql.bin, as the prefix name for the Binlog file.

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/7E/D9/wKioL1cLEnfj5-UzAASp9Cv3yGc696.jpg "title=" 1.jpg " alt= "Wkiol1clenfj5-uzaasp9cv3ygc696.jpg"/>

For example, if you update a table, or delete some data, even if the statement does not have matching data, the command is stored in the log file, including the time each statement was executed and also recorded.

To put it bluntly, the statement that executes in MySQL is recorded in the binary log.

So what do these binary logs do, you know?

1: Data recovery

If your database is out of order and you have a previous backup, you can look at the log file to find out which command caused your database to go wrong and try to recover the loss. Also, you can use the binary log to restore a database that you have mistakenly manipulated. But the specific how to operate also need to learn yo!

2: Synchronizing data between master and slave servers

All operations on the primary server are recorded in the log, which can be performed from the server to ensure two synchronizations. Therefore, the mysql-salva that we often do is also using the binary log of master to be consistent with the master data.

Binary logs are generally not small, what if you want to shut down?

1: Only one MySQL server, then you can simply comment out this option is OK.

Vim/etc/my.cnf the inside of the Log-bin this line commented out, restart the MySQL service.

So what is Sql_log_bin?

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/7E/DC/wKiom1cLEdrQBHEiAAS3j_SPXg8416.jpg "title=" 2.jpg " alt= "Wkiom1cledrqbheiaas3j_spxg8416.jpg"/>

Sql_log_bin is a dynamic variable that, when modified, can take effect only on the current session, or globally, when the variable is modified globally, only for new sessions (which means that it will not take effect for the current session). Therefore, after the general modification of this variable, all the original connection will be killed.

Use:

When restoring the database, if you do not shut down the binary log, then your restore process will still be recorded in the binary log, not only wasted resources, then increased the capacity of the disk, there is no need (especially when using the binary restore database) so the general restore will choose to close the binary log, You can restart the shutdown binary log by modifying the configuration file. You can also turn off sql_log_bin on a dynamic command, and then import the database.

This article comes from "? Only! "Blog, be sure to keep this provenance http://renzhiyuan.blog.51cto.com/10433137/1762539

What is the difference between MySQL's Log_bin and sql_log_bin?