mysqldump: Error: Binlogging on server not active

How to Fix Error: Binlogging on server not active

# mysqldump --all-databases --user=root --password --master-data > Fullbackup_26Jul2017.sql
Enter password:

mysqldump: Error: Binlogging on server not active.

While trying to backup the MySQL Databases I am experiencing this error. The error is pretty much self explaining and the simply the reason is that the logging is not enabled and I am trying an online backup

Resolution 1: Take Cold backup Instead of Hotbackup

How to Perform MySQL Database Backup

Cold Backups

Cold backups are a type of physical backup as you copy the database files while the database is offline.

Cold Backup

The basic process of a cold backup involves stopping MySQL, copying the files, the restarting MySQL. You can use whichever method you want to copy the files (cp, scp, tar, zip etc.).

# service mysql stopShutting down MySQL.. SUCCESS!

#cd /var/lib/mysql
#cp -r * /u01/stage/backup

# service mysql startStarting MySQL... SUCCESS!
[root@node1 mysql]#

Resolution 2: Enable Binlogging and then trigger the Logical Backup Again

How to Enable Binary Logging in MySQL

To enable the binary logs, edit the "/etc/my.cnf"  uncomment the line log_bin and restart the MySQL

#service mysql stop
Shutting down MySQL.. SUCCESS!
# service mysql start
Starting MySQL. SUCCESS!

mysql> show variables like 'log_bin';
| Variable_name | Value |
| log_bin       | ON    |
1 row in set (0.00 sec)

mysqldump --all-databases --user=root --password --master-data > Fullbackup_26Jul2017.sql
Enter password:

1 comment:

  1. It is really interesting and valuable blog post on MySQL backup. I want to share important information about MySQL server backup. Thanks