One of the things you definitely want to take a backup of on a regular basis, especially if you are a WebAdmin/WebMaster is that of your MySQL database for things such as a forum, blog or anything else you have that may use MySQL on your site. For this you'll need telnet/ssh login to allow you the function to do this from the command line (See later on for a GUI phpMyAdmin option). To get a dump, or backup of your DB, you simply need to run:

$ mysqldump –opt mydatabase > myDBbackup.sql

 Alternately you can use:

$ mysqldump -u root -p –opt mydatabase > myDBbackup.sql
$ mysqldump -u username -ppassword mydatabase > myDBbackup.sql
$ mysqldump –user=user_name –password=user_password –host=host_name dbname > myDBbackup.sql

as well.

In any of the above cases, this will result in a text file called 'myDBbackup.sql' which will hold the source SQL code required to repopulate your database later, and is essentially your backup. As a text file it can get quite large and if you have a slow connection, it'll be a problem for you to zip up the file and send it over to another location for safe keeping. Zip and Tar the file using:

$ tar -zcvf myDBbackup.tar.gz myDBbackup.sql

To restore the file (After unzipping it using 'tar -zxvf myDBbackup.tar.gz') use something like this:

$ mysql -p -h DBSERVER dbname < myDBbackup.sql

Use your database name instead of 'dbname' and 'DBSERVER' with your corect database server name/IP. If you configured '/etc/my.cnf' you can use:

$ mysql < dbname.sql

and it will use 'dbname' and 'DBSERVER' settings defined in the '/etc/my.cnf' file. To specify a username/password combination when restoring use:

$ mysql -u user_name -p dbname < myDBbackup.sql

OR

$ mysql –user=user_name –password=auser_password –host=host_name dbname < myDBbackup.sql

 There is a clear advantage to using CLI tools for MySQL discussed here along with Bash/Ksh, Linux and Cron.  One could setup a customized backup scheme for their website using such scripts to run on a regular basis.  This however will be discussed in another article.