FutureQuest, Inc. FutureQuest, Inc. FutureQuest, Inc.
SEARCH
Knowledgebase
How to Back Up a MySQL Database
Posted by Admin on 24 October 2003 04:26 PM

Update: The CNC MySQL Manager offers an option for creating and downloading backups of databases. The instructions that follow are for manually creating a MySQL backup using SSH.

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:
    mysqldump -q -uxmysqlusername -p -hmysql.example.com xdatabase >./myfile.txt
You will need to make substitutions to reflect your specific information as outlined below. Let's go through each parameter of the above command:
    mysqldump
This is the name of the program we are executing. mysqldump creates a text "dump" of your table and data structure.
    -q
This option tells mysqldump not to buffer the results, but to send it straight to the stdout filehandle. While this is not a required option, some clients with large MySQL databases may encounter "out of memory" errors if the results are buffered, so FutureQuest recommends using this option.
    -uxmysqlusername
-u is the switch to specify the user. Replace xmysqlusername with your MySQL username.
    -p
This tells mysqldump that you need to use a password, so it will prompt you for the password.
    -hmysql.example.com
This is the part that tells mysqldump what host to use. Your host is mysql.example.com where "example.com" is replaced with your actual domain and .tld.
    xdatabase
This is the name of the database that you wish to dump.
    >./myfile.txt
The > symbol tells Linux that you want to redirect the output of mysqldump somewhere, usually to a file. You can specify a full path or a relative path. Since ./ specifies the current directory, the example command above will create a file called myfile.txt in whichever directory you happened to be in when you ran the command.

If you like, you can instead give a full path:
    >/big/dom/xexample/backup.txt
You can name the file whatever you like.

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.

Notes:
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:
    less myfile.txt
To exit, press q. If you see a bunch of queries (things like CREATE TABLE...), the dump was successful.

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.

Advanced Users:
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:
    gzip -9 myfile.txt
This will compress myfile.txt with a compression level of 9 (best). The above command assumes you are within the directory that myfile.txt is in.

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:
    gunzip myfile.txt
Note that we do not type the .gz part. This will replace myfile.txt.gz with the larger, uncompressed myfile.txt.

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.
Help Desk Software by Kayako Resolve