|
Backing up MySQL Databases
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 into your account via SSH. Do not log into 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 -uxyourdomain -p -hmysql.yourdomain.com xyourdomain_2 >./myfile.txt
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.
As with the mysql command, you need to specify your MySQL username. This is always your xdomain, which is an x followed by your domain name, without the .com part.
mysql and mysqldump both take the argument as -u followed by the username.
This tells mysqldump that you need to use a password, and it will prompt you for the password.
This is the part that tells mysqldump where your data is located.
You must specify the name of the database you wish to dump. This is usually your xdomain, and is followed by a _2 through _6 for your databases beyond your first one.
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/xyourdomain/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 occurr will be written to the file. For example, if you mis-spelled 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:
To exit, press q. This will bring up the Midnight Commander Text Editor. 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:
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 the next tutorial.
Back to Top
|
|
|
