Binary log error in mysql

Question

When I try to check the binary log:

SHOW BINARY LOGS; 

I get this error:

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

How to resolve this? Can anyone help?

Answer 1

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

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

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

After a restart, MySQL automatically creates a new binary log (does this every time it restarts). You can also see the following variables:

 server-id = 1 expire_logs_days = 4 sync_binlog = 1 

Read more in the MySQL documentation. If after configuring replication (the main reason for using binary logs), check the replication configuration checklist .

Answer 2

Line

 log-bin=mysql-bin 

should be above the lines:

 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid 

Answer 3

You need to activate the binary log at startup

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

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

Then run this

 service mysql restart 

The next time you log into mysql, you will see a list of binary logs and will rotate 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 name binlog, then this is the folder.

for instance

 [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 login

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

Answer 4

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

If base_name is not specified, the default value is the pid-file parameter (the default is the host name), followed by -bin.

If a base name is specified, the server writes the file to the data directory, if the base name is not specified with a leading absolute path name to specify a different directory. A base name is recommended.

Or you can directly use:

 log-bin=mysql-bin 

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

Answer 5

FWIW, I had the same problem after I tried to configure my.cnf.master and my.cnf.slave and symlink files in my.cnf for master and slave, respectively. The idea was to easily switch the machine from master to slave and vice versa.

It turned out that mysqld just didn't handle the symlink as expected. The hard-linking file worked (ln my.cnf.master my.cnf). Caution if you do something like this, since rewriting one of the hard-coded file names can break the link and create two separate files instead (depending on the rewriting method used by the software that you use for it).

Answer 6

I found that logging will not be possible even if my.cnf configuration is correct, so you can also try re-creating your log folder.

This may not be necessary if the logs are in an odd state. (In my case, I just stopped logging into my.cnf and then turned it back on, 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 

Mandatory warning: Obviously, you should be careful when deleting anything on the database server. This will destroy / upset / ruin any replication using this database as the master (although you can resume replication as a slave). However, I believe that this should be safe, as it does not delete the database itself.

Answer 7

I went crazy because of this problem with the MySQL 5.5 wizard running Debian. None of the above worked. Finally, I rebooted the server and registration was turned on.

Answer 8

I'm not sure what you expect to get here. You are not using binary log; therefore SHOW BINARY LOGS you have nothing to show.

You will need to enable binary logging in the MySQL server configuration or stop trying to get information about binary logs that you are not using.

Answer 9

Delete the [mysqld_safe] section and replace it with [mysqld]. This works for me.