Backup and Restore MySQL Database

October 7th, 2006 liewsheng Posted in Admin, mysql, mysqldump | Hits: 40860 | 12 Comments »

Last month when my web server (whic is now host this page ;p) is crash, I was so sad and have to setup the server again :( It take a lot of time to setup this server again, lucky I have backup the MySQL database and able to retrieve back ;)

To Backup the MySQL DB, it just a simple command:

mysqldump --add-drop-table -u user --password=mypassword database> lbesql

–add-drop-table switch will drop the database table before create it again, -u is the username for the MySQL administrator, -p is to tell mysql to prompt you (thanks to aizatto for the correction :D) and of cause –password is the password for the user.

So from now on, i have using a cron job to do the jobs for me ;p For more information on how to create a backup script can refer to WordPress MySQL backup script or Backing Up Your Database

So how to restore back the backup DB? It is easy:

mysql -u user -p -Ddatabase --password=mypassword < lbesql

after you have enter the command, of it will ask you the password, just give it the password and wahla...the DB is restore back :) more detail on how to retrieve back the database can refer to Restoring Back Your Database.

12 Responses to “Backup and Restore MySQL Database”

  1. Actually you shouldnt include the arguments “–password=” as your leaving it in CLEARTEXT for people to see. That’s insane!

    Your commands should be:

    mysqldump --add-drop-table -u user -p database > lbesql
    mysql -u user -p database < lbesql

    And it'll prompt you for the password. The "-p" argument is the option so that it prompts you, not the database you want to dump or restore.

  2. yeah i do agree with you, is there have another ways to do provide password in secure way?

    har…you are correct, the -p will prompt you to enter password (if the user have password protected)

    Thanks aizatto, for the correction!!! :)

  3. how to configure vpn in linux

  4. It works easy and fast. Thanks from a newbie of Linux ;)

  5. [...] sure you backup your mysql database, wordpress folder weekly to other places, you don’t want to lose your data, if anythings [...]

  6. @kulpreet
    what’s the meaning of life. now.

  7. Thank, i have restored db using above cmd
    :)

  8. When restoring the database, do we have first create a database with the same name before running the command?
    Because when I try to run the given command it gives an error called

    “ERROR 1049 (42000): Unknown database”

    But after I create a database with the same name it restore the rest.

  9. Kudos for one’s great post! I definitely loved reading Backup and Restore MySQL Database » Linux by Examples, you are an great source. I shall make sure to save this web page and will eventually come back sometime soon, I wish to encourage you to definitely carry on your great posts, enjoy your afternoon Flash Websites !

  10. thnx… above command saved me…

  11. Thanks! It was great!

  12. Thanks in support of sharing such a pleasant idea, piece of writing
    is fastidious, thats why i have read it entirely

Leave a Reply