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 the following to export the database:

$ 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 and import the database back (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 correct 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.

Nov 18 2012:

While working with a backup off of my ISP, I wanted to replicate a LAMP configuration and setup.  For this I had to get the SQL dump from my ISP and work with it locally.  Here's a few things I ran into on the way to getting this done and the corresponding resolutions:

# mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
# chkconfig|grep -i mysql
# service mysqld restart
mysqld: unrecognized service
# service mysql restart
mysql: unrecognized service
#

In this case, it turned out I'm missing the mysql server software:

# rpm -aq|grep -i mysql
mysql-libs-5.1.66-1.el6_3.x86_64
qt3-MySQL-3.3.8b-29.el6.x86_64
qt-mysql-4.6.2-17.el6_1.1.x86_64
mysql-5.1.66-1.el6_3.x86_64
#

A simple yum install mysql-server.x86_64 resolved that.  Don't forget to turn it on:

# chkconfig –level 5 mysqld on
# chkconfig –level 4 mysqld on
# chkconfig –level 3 mysqld on

Another problem encountered is the ever common Access denied error:

# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
#

On initial installation, the server won't need a password:

# mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
# mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.1.66 Source distribution

Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

 

But you should set one after logging into mysql:

mysql> UPDATE mysql.user SET Password=PASSWORD('NOTaGoodMySQLPass') WHERE User='root';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye
# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
#

and now you'll need to add the -p option when trying to login to MySQL.

Cheers,
TK