Backup & Restore All MySQL Databases
This is one of the very tedious tasks when we want to back up 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. Especially when the databases are so many or they are huge in size.
I am going to show very basic steps which are applicable to all kinds of web servers including LAMP, MAMP, XAMPP, and WAMP.
First of all, you need to make sure that you are able to run mysql & mysqldump commands from any folder using the 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 in your web server’s folders.
Once that is done, follow the below steps.
Backup
Create a folder named “backups” or whatever name you like and open a terminal from that folder and run the 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 the below command
mysqldump -u root --all-databases > all-db-dump.sql
It will take a few minutes if you have so many databases but it 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 the below command :
mysql -u username -p < all-db-dump.sql
If your MySQL doesn’t have any password then use the below command
mysql -u username < all-db-dump.sql
Again, this will take a few minutes but it will definitely restore all the databases that we backed up.
I have been using so many different methods like export/import using phpMyAdmin or using MySQL Workbench. But the above mentioned are very simple and standard steps that always work and we don’t need any other tools.
Please share this with others if you find it helpful.
Thank You for stopping by 🙂