How to Back Up a MySQL Database
Posted on 24 October 2003 04:26 PM
MySQL is hosted on a separate physical machine from the web servers. This provides extreme high-performance and speed, without affecting the serving of web pages. There are some minor drawbacks, however these are easily worked around.
If you want to create a backup of your database, the first thing you will need to do is log in to your account via SSH. Do not log in to MySQL - these commands are done from the standard command line prompt.Once you are connected via SSH, you will type a command like this:
If you like, you can instead give a full path:
Once you have executed the command, it will ask you for your password. Type carefully, as you will not see the password as you type it.
1. Since you are redirecting all output to the file you specify, any errors that occur will be written to the file. For example, if you misspelled your username or specified an incorrect database name, the error message will go to the file.
It is best to view the file and double check that it did what you expected. Simply type the following:
2. Linux is CasE-SenSItiVe. You must type the commands in all lowercase. Your password must be typed in the exact case it was requested in.
3. Be careful not to specify a filename that already exists - if you do, it will be overwritten by the command without any prompting.
If you want, you can compress the file you have created to make it easier to download. If you have Winzip, you should be able to open a gz compressed file (files with an extention of .gz).
You can compress your file by typing the following:
You should now find a file named myfile.txt.gz. The myfile.txt is replaced with this new compressed file.
If you later need to uncompress the file within your account, simply type:
To use this backup, see:
How to Restore a Database from Backup
For backing up larger databases, see:
MySQL Backup File Size Limit?
Important Note: You can find the information for the host name and username by visiting the MySQL Manager in your CNC (Command'N'Control) panel. If you have forgotten your MySQL password, you will also be able to reset it through your MySQL Manager. Note that doing so will change the MySQL password for all of your MySQL databases under that package.