Home » » Guide | Back Up And Restore MySQL Database Via Shell Command Line

Guide | Back Up And Restore MySQL Database Via Shell Command Line

Posted by Rom Flash Media on Friday, October 14, 2016

Guide | 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 cause your database loses or broken. To back up MySQL database is easy. We can save MySQL database to flash drive or local drive than restore to the other computers. All step on this guide using shell command line, so you must write down every command first before perform 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 create MySQL database on this guide, this database called customers.
    mySQL database
    mySQL database
  5. Now type the 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
    back up mysql database
  9. This result only back up MySQL database on shell command line. So how to send your database into sql text file ? now follow the next step.

Send MySQL database into 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 to create a data folder at drive E:\, so the result will look like this.
    • E:\data
  3. Then type the 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 shell command prompt.
  6. Now, go to drive E:\data
  7. There you should see customers2.sql dump text file.
    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 at 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
    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
    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


    0 comments:

    Post a Comment

    Popular Posts

    Blog Archive

    .comment-content a {display: none;}