All Database Backup and Restore

           To backup all  of your MySQL databases, you’ll need to use  mysqldump which comes  with mysql. If you have MySQL installed, you probably have mysqldump installed already.

To backup all databases use the following command:

 mysqldump -uroot -ppassword --all-databases > BackupDB.sql
 or
mysqldump -uroot -p --all-databases > BackupDB.sql

To restore all database use the following command:

mysql -u root -p < BackupDB.sql

Troubleshooting:

ERROR 1556 (HY000) at line 75485: You can't use locks with log tables.

Solution :

You may need to disable “--lock-tables” option on your dump statement ie –lock-tables=0 . 

mysqldump -uroot -ppassword --all-databases --lock-tables=0 > BackupDB.sql

Explanation:

   when we use “–lock-tables” option, the current table which is being backed up will be operating on “read only” mode to avoid further write operations during the time. The cached querry which containing “write’ operation will be executed later once after the table dump has been done.