Live Chat!

Backup and Restore MySQL Database

          0 votos

October 7th, 2006 liewsheng Posted in Admin, mysql, mysqldump | Hits: 6372 |

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.

Related Posts
mysql command lines
You extract info from mysql databases easily by execute the command line mysql -e. The results of sql statements can be ...

customize date output
By typing date on your terminal, it will display current date and time like: #output Wed Aug 23 08:00:58 MYT 2006 But...

Setup your own apache server to host your weblog free
Do you feel like you want to create your own blog? Earn some side income through blogging by putting adsense, advertlets...

5 Responses to “Backup and Restore MySQL Database”

  1. aizatto Says:
    October 19th, 2006 at 7:00 am

    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. liewsheng Says:
    October 21st, 2006 at 10:17 pm

    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. kulpreet Says:
    July 30th, 2007 at 3:55 pm

    how to configure vpn in linux

  4. Chemo Says:
    October 26th, 2007 at 8:53 pm

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

  5. Setup your own apache server to host your weblog free » Linux by Examples Says:
    November 1st, 2007 at 11:21 pm

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

Leave a Reply