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:
You will need to make substitutions to reflect your specific information as outlined below. Let's go through each parameter of the above command:
This is the name of the program we are executing. mysqldump creates a text "dump" of your table and data structure.
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.
-u is the switch to specify the user. Replace xmysqlusername with your MySQL username.
This tells mysqldump that you need to use a password, so it will prompt you for the password.
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.
This is the name of the database that you wish to dump.
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.
mysqldump -q -uxmysqlusername -p -hmysql.example.com xdatabase >./myfile.txt
If you like, you can instead give a full path:
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.
To exit, press q. If you see a bunch of queries (things like CREATE TABLE...), the dump was successful.
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:
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:
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.