Welcome, Guest. [ Log In ]
Question   How do I back up my database?
Search KBase


Top 5 in this Area:
1. What hostname do I use to connect to my database?
2. Is there an easier way to manage my database?
3. What username/password do I use to connect to my database?
4. How do I back up my database?
5. How long does it take for a new database to start working?

 
How do I back up my database?
To backup your MySQL database you can:
  1. Use phpMyAdmin and use the options there to dump the contents to a text file.
  2. From the shell, run this command:
    mysqldump --opt -uusername -ppassword -h yourMySQLHostname dbname > filename.sql


To restore your database from a backup:
    Take that db dump and do:
    cat filename.sql | mysql -uusername -ppassword -h yourMySQLHostname dbname

Last updated: Feb 07, 2005.

User Post (2006-01-09 15:18:16 by dhejuti)
I found that I was getting the "Error 1044: Access Denied" until I changed my formatting to look like this at the shell prompt:

mysqldump --opt -h yourMySQLHostname -uusername -p dbname > filename.sql

Note: I did not include the password until it was requested. My backup was then placed by default in my HD/Users/Yourname folder.

BTW: It is much easier to use PHP administration.
User Post (2006-01-09 08:58:38 by ozort)
I keep getting "Error 1044: Access Denied" when I try to back up my database. Is there a way to do this from the graphic interface or from being logged in? I have no trouble logging into my database, but this simply isn't working for me.
User Post (2005-11-11 18:10:33 by encodable)
I don't know if it's just a problem on my particular Dreamhost server, or what, but I need to escape the percent-signs in the date command, or else the cron-job doesn't run:

30 1 * * * mysqldump -uuser -ppass -h sql-hostname dbname > filename.`date +\%Y\%m\%d`
User Post (2005-04-21 20:26:15 by evymigad)
User Post (2004-10-20 17:39:58 by areutter)

30 1 * * * mysqldump -uusername -ppassword -h yourMySQLHostname dbname > filename.`date +%Y%m%d`

This will result in filenames like:

dbdump.20041020


Where does the resultant file get saved by default? Is there a way to specify a directory?
User Post (2005-03-09 12:19:59 by internodium)
If you want to get (gzip-)compressed database dumps, use this command:

mysqldump -uusername -ppassword -h yourMySQLHostname dbname | gzip > filename.gz
User Post (2004-10-20 17:39:58 by areutter)
A nice expansion upon the extremely useful hint below is to datestamp
your filenames. You can do that by adding a bit to the end of your
crontab line:

30 1 * * * mysqldump -uusername -ppassword -h yourMySQLHostname dbname > filename.`date +%Y%m%d`

This will result in filenames like:

dbdump.20041020

where the 20041020 is in YYYYMMDD format, of course.
User Post (2004-07-03 00:46:26 by apv)
You can also do this yourself, VERY carefully. You'll have to use a command line text editor.

Type "crontab -e" from the command line. It will open your default text editor (pico or emacs or vi, almost certainly). If you don't know how to use them, beware, you'll get stuck.

If you don't already have this text in there, it's a good guide so you should add it (one line, with the "#" first):

# <min> <hour> <day> <month> <week_day> [<command>]

You should also add in the following with your real email.

MAILTO="your@email.here"

Then follow the format. To do a nightly backup at 1:30am is (all one line again and change the parts you need to, username, password, your hostname and the filename to dump to):

30 1 * * * mysqldump -uusername -ppassword -h yourMySQLHostname dbname > filename

Read the man page for crontab and search online for more help and tutorials.