MYSQL Database Backup

MYSQL Database Backup

The purpose of this tutorial is to create a backup of a mySQL database. The tutorial will two methods for which this can be accomplished; one using a GUI tool called Cpanel and the other using the Linux command line. Although both methods have pros and cons, using the command line and getting to know it well will allow you to easily work on any Linux server and accomplish your goals very quickly.

Database Backup With Cpanel

To backup a database,
1) Select ‘Backup Wizard’.

2) Select Backup.

3) Select ‘mySQL Databases’.
4) Select a Database.
Note: You can click ‘Databases’ to order the databases in alphabetical order. This can become handy as you have 20-100+ databases.
5) Click OK

Non GUI Backup

Although using a GUI makes it nice and easy to backup databases. The code below can be used to backup a mySQL database and save it in a desired folder. After that, the public-html folder is made into a tar file which has all of the files and database. This type of backup can take place as a weekly cron so that you have a n updated backup of the files and database.

 root# mysqldump -u root -p mydatabase > /home/username/public_html/backup.sql 
Enter Password: 
root# tar -cvf mybackup.tar /home/username/public_html

One more method to backup mySQL databases is to copy them from their folder.

 root# cp -R /var/lib/mysql/* /home/username/backups/

September 1, 2014 by kent