mysqldump: Error: Binlogging on server not active-A Comprehensive Solution

mysqldump: Error: Binlogging on server not active

I was using the mysqldump utility to backup MariaDB databases on my ubuntu15.10 server, the following error appeared:

mysqldump: Error: Binlogging on server not active

The solution is fairly simple most of the time. All you need to do is to enable binary logging. Open up the MariaDB configuration file.

sudo vi /etc/mysql/mariadb.conf.d/mysqld.cnf

This config file is for configuring mysqld. Find the following line in the file.

#log_bin = /var/log/mysql/mysql-bin.log

Remove the hash(#) sign. If you can not find it, then add the following line to the file.

log_bin = /var/log/mysql/mysql-bin.log

Save and close the file. Now reload MariaDB configurations.

sudo systemctl reload mysql            or        sudo service mysql reload

Now you should be able to use the mysqldump utility to backup your databases.

MariaDB Reload Won’t Working

If you see the following error

Job for mysql.service failed because the control process exited with error code. See "systemctl status mysql.service" and "journalctl -xe" for details.

That means MariaDB configuration reload failed. So you may be thinking I just have to restart it.

sudo systemctl restart mysql            or             sudo service mysql restart

Well, nothing appeared after you executed the restart command. But when you use mysqldump to backup databases again, you may find that binary logging is still not activated.

mysqldump: Error: Binlogging on server not active

To fix this problem, we need to use the pkill command to kill the mysqld process.

sudo pkill mysqld

MariaDB will be automatically started after the kill. Now you should be able to use mysqldump to backup your databases.

Cheers!