Enable the MySQL Binary log

Question

How do I enable the Binary log in mySQL. I have tried:

1) Entering this SQL code on phpmyadmin

SET GLOBAL log_bin ='ON'

This gave me an error message that bin_log is a read-only file;

2) Inserting this into my.ini file below [mysqld]

--log-bin= C:\mySqlbinlog

This displays that I cannot access the database when I try to get to the webpage.

Any idea how I can enable the Binary Log through phpmyadmin or in the my.ini file? Any help appreciated.

Solution 1

Just Put log_bin parameter after mysqld. Please give path for binlog where you have large amount of space because binlog eats lots of disk space.

log_bin = "path where want to store bin files.extension"

You can also use some other parameters like max_binlog_size, max_binlog_cache_size, sync_binlog for well performance

For More Here's [a link] (http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html)

Solution 2

According to the documentation, the option-file format is log-bin, so you should put this in your my.ini (under the [mysqld] heading):

log-bin=C:\mySqlbinlog

Don't forget to restart the MySQL server process afterward!

Solution 3

I currently use wamp with MySQL ver 5.6.17 so your milage may vary.

Open your my.ini file and scroll through it. You're looking for the [mysqld] section. Add log-bin=C:\Your_Dir_Here to the [mysqld] section. Restart the MySQL services. Once the restart has finished, login to MySQL. Run the command SHOW BINARY LOGS;

You should then see an output like this:

+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| MySQL_Log.000001 |       120 |
+------------------+-----------+
1 row in set (0.00 sec)

NOTE I don't know exactly how log-bin works but I would recommend an absolute path so you know exactly where your log files will be. Also, your MySQL installation won't read the my.ini file until it's services restart.

Further reading http://dev.mysql.com/doc/refman/5.6/en/binary-log.html

This link covers a great deal about the binary logs. It literally covers too much for me to even gloss over.

If I've left something out or if I was vague on something, leave a comment and I'll try to reply back.

Comments

[Jeremy Smyth]

The format for the command line option to mysqld is --log-bin, not log_bin. Also, "If you supply an extension in the log name (for example, --log-bin=base_name.extension), the extension is silently removed and ignored." - dev.mysql.com/doc/refman/5.5/en/binary-log.html

[Ronak Vyas]

log_bin is a variable name for 'my.ini' file variable should be put after [mysqld] section. --log-bin is parameter for commandline-format. That's why i have preferred log_bin. About extension you are right, thanks for that.

[K Clark]

where do I put the command line in my my.ini file. No matter what I try I keep getting the error that the page cannot connect to the server I have tried right after the [mysqld] line, after the server port connection, before the innodb section and right at the end of the document

[K Clark]

I have done it! it needed 'server-id=1 log-bin=mysqlbin sync_binlog=1'

[K Clark]

I now cannot view the file binlog000001. Any ideas how I entered this into phpmyadmin shell> mysqlbinlog binlog.000001

[Ronak Vyas]

TO VIEW BINLOG FILE IN readable FORMAT USE mysqlbinlog utility we can VIEW the BINLOG FILE content. IF it's a single file you can recover using: mysqlbinlog /var/lib/mysql-bin.000016 | mysql –uroot –pRe12 If it's multiple files THEN just EXTRACT ALL content TO ONE .SQL FILE AND directly RESTORE it: mysqlbinlog -v /var/lib/mysql-bin.000016 > /LOGS/allbinlog.sql Try this below FOR more musqlbinlog UTILITY OPTIONS: mysqlbinlog --help

[Jeremy Smyth]

@KClark I suggest you create a new question if you've solved your first problem and now have a separate one. It will help others who search for your new problem in future, and keeps the site cleaner.

[Soon Santos]

In my case log-bin was commented with #. Don't forget to uncomment it if that is the case.