How to Back Up And Restore MySQL Database Via Shell Command Line

Back Up MySQL database. To keep your SQL database save you must regularly back up it at every time you can. Anything can happen on your computer at any time, for example, your computer's hard drive could damage, virus or bug attack your file and program, or stolen by someone, which it causes your database loses or broken. To back up the MySQL database is easy. We can save the MySQL database to flash drive or local drive then restore to the other computers. All step on this guide using a shell command line, so you must write down every command first before performing back up and restore MySQL databases. You must be careful in order to write command here to avoid error syntax.

Now, let we start our guide to Back Up And Restore MySQL Database Via Shell Command Line. We use mysqldump command to create text file dumps of databases managed by MySQL

Back Up MySQL Database.

1. Open XAMPP Control panel.
2. Then click Shell.
3. Don't make any connection to your SQL server.
4. For this case, we have created MySQL database on this guide, this database called customers.

   

   mySQL database

5. Now type the following command at shell prompt;
• mysqldump -uroot database name
6. Example ;

• mysqldump -uroot customers

7. Then press enter button your keyboard.
8. The result must return with this.

   

   back up MySQL database

9. This result only backs up MySQL database on the shell command line. So how to send your database into an SQL text file? now follow the next step.

Send the MySQL database into an SQL text file.

Once your database successfully backup, now it's time to create SQL dump text file. So, you can move your SQL database to the other computer.

1. Create a folder on a specific drive on your computer.
2. For example, we would like to create a data folder at drive E:\ so the result will look like this.

• E:\data

3. Then type the following command at shell prompt;
• mysqldump -uroot database name >D:\folder\file name.sql
4. Example;

• mysqldump -uroot customers >E:\data\customers2.sql

5. Then press enter, here you will see nothing at the shell command prompt.
6. Now, go to drive E:\data
7. There you should see customers2.sql dump text file.

   

   SQL dump text file

8. Finish, now you can move this SQL file to the other computer and restore it to available MySQL database

Restore MySQL database.

1. Once your SQL text file in hand, now you can move to the other SQL server. or you can restore it to the same server.
2. Open XAMPP Control panel.
3. Then click Shell.
4. Connect to SQL server, type the following command ;
• mysql -uroot
5. Now create a new database, for example, type the following command ;
•  create database customers2;
6. Then set active this new database, type the following command ;
• use customers2;
7. Now restore customers2.sql text file to customers2 database. type the following command
• the syntax is : source E:\\folder\\file name.sql
8. Therefore you should type 

•  source E:\\data\\customers2.sql

9. If your database successfully restored your command prompt should look like this.

   

   restore MySQL database - query ok

10. Now you can check whether the database has been successfully restored to MySQL. type the following command ;
• show databases;
11. And you should your new database there.

    

    restore MySQL database

12. Also, you can check all available table on this database. type the following command ;
• show tables;
13. Finish, congratulations you have successfully back up and restore your SQL database

Share this post