Backup & Restore All MySQL Databases

This is one of the very tedious task when we want to backup our local Web server setup and Restore or Move it to some other system. Backing up all Project Files is a simple copy & paste task but backing up MySQL database and restoring them is always something where we make mistakes. Specially when the databases are so many or they are huge in size.

I am going to show a very basic steps which are applicable to all kind of  web servers including LAMP, MAMP, XAMPP, WAMP.

First of all, you need to make sure that you are able to run mysql & mysqldump commands from any folder using terminal. Mainly on windows, you will have to add Path in the Environment variables which is basically ….mysql/bin/ folder. So you will just have to find where exactly this folder is into your web server’s folders.

Once that is done, follow below steps.

Backup

Create a folder named “backups” or whatever name you like and open a terminal from that folder and run below command :

mysqldump -u root -p --all-databases > all-db-dump.sql

This will ask you MySQL server’s password. Type it and hit enter.

If you run MySQL without any password then use below command

mysqldump -u root --all-databases > all-db-dump.sql

It will take few minutes if you have so many databases but in will create a file named all-db-dump.sql into your backups folder.

Restore

Go to your backups folder where all-db-dump.sql file is placed and run below command :

mysql -u username -p < all-db-dump.sql

If your MySQL doesn’t have any password then use below command

mysql -u username < all-db-dump.sql

Again, this will take few minutes but it will definitely restore all the database that we backed up.

I have been using so many different methods like export/import using phpMyAdmin or using MySQL Workbench. But above mentioned are a very simple and standard steps that always works and we don’t need any other tools.

Please share this with others if you find it helpful.