Binary log error in mysql

Question

When I am trying to check binary log:

 SHOW BINARY LOGS;

I get this error:

ERROR 1381 (HY000): You are not using binary logging.

How to resolve this? Can anybody help?

Answer 1

Set the log-bin variable in your MySQL configuration file, then restart MySQL.

An example my.cnf (on Linux/unix) or my.ini (on Windows) would look like:

[client]
...

[mysqld]
...
log-bin=mysql-bin
---

Once restarted, MySQL automatically creates a new binary log (does so upon every restart). You may also wish to look at the following variables:

server-id        = 1
expire_logs_days = 4
sync_binlog      = 1

Read details on the MySQL documentation. If you're after replication setup (a primary reason for using binary logs), check out Replication configuration checklist.

Answer 2

Line

log-bin=mysql-bin

must placed above lines:

[mysqld_safe]

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

Answer 3

You will need to activate binary logging at startup

Add the following lines in /etc/my.cnf under the [mysqld] section

[mysqld]
log-bin=mysql-bin
expire-logs-days=7

Then, run this

service mysql restart

The next time you login to mysql, you will see a binary log listing and will rotate out after 7 days.

The default location of the binary logs will be /var/lib/mysql or where datadir is defined. If you specify a folder before the binlog name, then that folder is the location.

For example

[mysqld]
log-bin=/var/log/mysql-bin
expire-logs-days=7

UPDATE 2012-07-12 02:20 AM EDT

Please restart mysql as follows and tell us if binary logging in on

service mysql restart --log-bin=mysql-bin

Answer 4

To enable the binary log, start the server with the --log-bin[=base_name] option.

If no base_name value is given, the default name is the value of the pid-file option (which by default is the name of host machine) followed by -bin.

If the basename is given, the server writes the file in the data directory unless the basename is given with a leading absolute path name to specify a different directory. It is recommended that you specify a basename.

Or you can directly use:

log-bin=mysql-bin

and then restart your mysql service. Then binary file will be generated. If you are using lampp on Linux machine then you will find this file in /lampp/var/mysql/mysql-bin.000001

Answer 5

FWIW, I had the same issue after I tried to set up my.cnf.master and my.cnf.slave files and symlink them to my.cnf for master and slave, respectively. The idea was to be able to switch the machine from master to slave and back easily.

It turned out that mysqld simply did not handle the symlink as expected. Hard-linking the file worked (ln my.cnf.master my.cnf). Careful if you do something like this, as overwriting one of the hard-linked filenames could break the link and create two separate files instead (depending on the method of rewriting employed by the software you use for it).

Answer 6

I've found logging will silently fail to happen even if my.cnf config is right, so you can also try re-creating your log folder.

This may be necwssary if the logs are in an odd state. (In my case, I had simply ceased logging in my.cnf and then re-enabled it, but nothing happened, probably because the existing files were not the latest updates?).

Something like this should work:

sudo service mysql stop
sudo mv /var/log/mysql /tmp/mysqlold # or rm -fr if you're brave
mkdir /var/log/mysql
chown -R mysql:mysql /var/log/mysql
sudo service mysql start

Obligatory warning: Obviously, take care when deleting anything on a database server. This will destroy/disrupt/corrupt any replication using this database as master (though you can resume replication as a slave). That said, I believe this should be safe insofar as it doesn't delete the database itself.

Answer 7

I went out of my mind with this issue on a MySQL 5.5 master running Debian. None of the above worked. Finally, I rebooted the server and logging was enabled.