Backing Up and Restoring Your MySQL Database

Do you need to change your web host or switch your database server? This is probably the only time when you really think of backing up your MySQL data. If you've got a website with a database or your custom database running for your applications, it is imperative that you make regular backups of the database. In this article, I will outline two easy ways of backing up and restoring databases in MySQL.
The easiest way to backup your database would be to telnet to the your database server machine and use the mysqldump command to dump your whole database to a backup file. If you do not have telnet or shell access to your server, don't worry about it; I shall outline a method of doing so using the PHPMyAdmin web interface, which you can setup on any web server which executes PHP scripts.

Playing with mysqldump

If you have either a shell or telnet access to your database server, you can backup the database using mysqldump. By default, the output of the command will dump the contents of the database in SQL statements to your console. This output can then be piped or redirected to any location you want. If you plan to backup your database, you can pipe the output to a sql file, which will contain the SQL statements to recreate and populate the database tables when you wish to restore your database. There are more adventurous ways to use the output of mysqldump.

A Simple Database Backup:

You can use mysqldump to create a simple backup of your database using the following syntax.

mysqldump -u [username] -p [password] [databasename]> [backupfile.sql]

[username] - this is your database username
[password] - this is the password for your database
[databasename] - the name of your database
[backupfile.sql] - the file to which the backup should be written.

The resultant dump file will contain all the SQL statements needed to create the table and populate the table in a new database server. To backup your database 'Customers' with the username 'sadmin' and password 'pass21' to a file custback.sql, you would issue the command:

mysqldump -u sadmin -p pass21 Customers> custback.sql

Continue reading Backing Up and Restoring Your MySQL Database

Backup files with tar

I don't remember where I got this article from but it is full of some good information

TAR is the Unix Tape ARchive utility. It can be used to either store data on a streaming tape device like a DAT drive, or store files in what is commonly called a tarball file- somewhat like a pkzip file, only compression is optional.

[The basics]

In these examples, I will use the following file structure: a top level directory called DIR1 containing the files picture.jpg, document.doc and database.db.


[Creating a tarball]

If we were in the directory DIR1 and wanted to backup all the files to a tarball called backup.tar, we could issue this command:

$ tar cvf backup.tar .
tar: backup.tar is the archive; not dumped

c=create (an archive)
v=verbose (just because)
f=filename (the name of our tarball)
.=current directory (what's going to be backed up)

Also worth mentioning is that by default tar is recursive- meaning it will back up all files and subdirectories recursively unless you otherwise specify with the n flag (non-recursive)

[Displaying the Contents of a Tarball]

The current directory will now contain a file called backup.tar. To display the contents of the tarball file, we could issue this command:

$ tar tvf backup.tar
drwxr-xr-x root/gci 0 Jun 29 10:10 ./
-rw-r--r-- root/gci 1 Jun 29 10:10 picture.jpg
-rw-r--r-- root/gci 1 Jun 29 10:10 document.doc
-rw-r--r-- root/gci 1 Jun 29 10:10 databse.db
Continue reading Backup files with tar