Jul 132012
 
silver-database-icon2

For those of you that run VB(vBulletin) forums or your personal blog/business site using WordPress(WP) you will likely want to backup your databases from time to time. The obvious factor is that you should have a CRON job setup to automatically backup your database for your site and you can manually download your database backups at your leisure.

Some prerequisites that you will need to know about before you start.
Note: text between the ‘ ‘ marks needs to be replaced, along with the ” characters.

FTP location:
The problem is putting that file in a location that you can easily FTP it back to your computer. So you have to know what the root address is to the files that are accessible to you through FTP. A lot of people don’t fully understand the file system but here is an example of one server’s path to a location that you can download your files easily from, using FTP:
/var/www/vhosts/’yourdomain.com’/httpdocs/the rest is up to your particular file system
The above can easily be completely different depending on your host’s preferences.

If that doesn’t work you will need to find out what the directory structure of your server is and there are couple ways to do this. You will need to refer to this post Finding your directory structure for more details on how to search, find or locate those “critical paths”. Critical paths is a good search term to use when searching for how to find the file paths you need to know about for your host.

Changing directories and Listing files and folders in those directories:
By using the “cd” command you can “change directory” and you’ll be in the folders you want. If you don’t know the folder structure of your system you can list all your file in each directory by simply typing “ls -alh
Once you have a list you can choose a folder and then type:
cd ‘foldername’/

Back to the topic of executing the command line to backup your databases and you can do it from wherever you want on your server, there is no need to be in any specific folder unless you feel like it.

Local host ackup string, executable from any location in your sever:
The below string of commands is designed to tell the system to create a backup of the database and save it to the path that you specify, if you don’t know the path that you should save it to refer to the above information to find the appropriate location to save the backup file to.
mysqldump --opt -Q -u 'dbusername' -p'PassWord' 'databasename' > '/path/to/databasebackup'.sql

Local host backup execution string within your destination folder:
If you have already used the “cd” command to get into the directory that you wish to save the file to then you can run the below string to create the file directly to the folder/directory that you are in.
Once in the folder you want the files saved to use the following string of commands to write the file directly to that folder:
mysqldump --opt -Q -u 'dbusername' -p'PassWord' 'databasename' > databasebackup'.sql

Remote Database (non local host) backup string:
mysqldump --opt -Q -h 'servername' -u 'dbusername' -p'PassWord' 'databasename' > backupname.sql
And you should be able to adjust this depending on if you are in the file you want it to be saved or if you want to send the file to a specific directory.

Note that there is no space between the -p command and the password, that is for a reason and you should follow the same structure as it is shown above. Later I will explain the commands and purposes for each part of the strings above. I will also explain how to make a cron script on your server to automatically back up your systems as well. The key to my version of these tutorials is to make descriptions of how to admin your servers in a reasonable way that allow you to take control of your systems and not have to pay outside help to run a site that you offer for free to the public. Not everyone has the funds to toss around and pay for services to do the simple tasks in administering a server.

Using a cron file with a script to automatically backup your database:
The key to this is that you actually download them and then erase them from your server or at least only leave the most recent of backups on your server, it’s always a smart thing to store backup files in multiple locations/hard drives. If you leave one on the server and one on your computer then you have a copy if either one of these devices fail or get corrupted. Another great option for storing this backup file is to use a system like DropBox where you can store up to 2GB of data for free and have it stored online as well as on your PC and leave it accessible to you from any place as long as you log-in, so if your server database gets messed up you can restore it from your hotel without issue while on vacation and not have your site down for an extended period of time.

So stay tuned and I’ll offer up a lot more details as I have time to write more articles on these topics.