OpenPro - Backup and Restore MySQL Databases

<wrap leftalign>[[:support:start|Back]]</wrap>

=====Backup and Restore a MySQL Database=====
Backing Up and Restoring Your MySQL Database From the command prompt.

In this article, we 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.

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

  * [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:

<code>mysqldump -u sadmin -p pass21 Customers > custback.sql</code>

You can also ask ''mysqldump'' to add a drop table command before every create command by using the option ''--add-drop-table''. This option is useful if you would like to create a backup file which can rewrite an existing database without having to delete the older database manually first.

<code>mysqldump --add-drop-table -u sadmin -p pass21 Customers > custback.sql</code>

====Backing up only specified tables====
If you'd like restrict the backup to only certain tables of your database, you can also specify the tables you want to backup. Let's say that you want to backup only __customer_master__ & __customer_details__ from the Customers database, you do that by issuing:

<code>mysqldump --add-drop-table -u sadmin -p pass21 Customers customer_master customer_details> custback.sql</code>

So the syntax for the command to issue is:

<code>mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]</code>

  * [tables] - This is a list of tables to backup. Each table is separated by a space.

<code>mysqldump -u root -p pass21 --databases Customers Orders Comments > multibackup.sql</code>

This is okay if you have a small set of databases you want to backup. Now how about backing up all the databases in the server? That's an easy one, just use the ''--all-databases'' parameter to backup all the databases in the server in one step.

<code>mysqldump --all-databases> alldatabases.sql</code>

====Backing up only the Database Structure====
Most developers need to backup only the database structure to while they are developing their applications. You can backup only the database structure by telling ''mysqldump'' not to back up the data. You can do this by using the ''--no-data'' parameter when you call ''mysqldump''.

<code>mysqldump --no-data --databases Customers Orders Comments > structurebackup.sql</code>

====Compressing your Backup file on the Fly====
Backups of databases take up a lot of space. You can compress the output of mysqldump to save valuable space while you're backing up your databases. Since mysqldump sends its output to the console, we can pipe the output through gzip or bzip2 and send the compressed dump to the backup file. Here's how you would do that with bzip2 and gzip respectively.

<code>mysqldump --all-databases | bzip2 -c >databasebackup.sql.bz2</code>

<code>mysqldump --all-databases | gzip >databasebackup.sql.gz</code>

====A Shell Script for Automating Backups?====
You can automate the backup process by making a small shell script which will create a daily backup file. How do you get cron to back up your database without overwriting the older backup? You can use a tiny shell script to add the date to your backup file. An example of a shell script you could use is shown below.

<code>
#!/bin/sh
date=`date -I`
mysqldump --all-databases | gzip > /var/backup/backup-$date.sql.gz
</code>

====Restore using mysql====
If you have to re-build your database from scratch, you can easily restore the ''mysqldump'' file by using the mysql command. This method is usually used to recreate or rebuild the database from scratch. Here's how you would restore your ''custback.sql'' file to the Customers database.

<code>mysql -u sadmin -p pass21 Customers < custback.sql</code>

Easy isn't it? Here's the general format you would follow:

<code>mysql -u [username] -p [password] [database_to_restore] < [backupfile]</code>

Now how about those zipped files? You can restore your zipped backup files by first uncompressing its contents and then sending it to MySQL.

<code>gunzip < custback.sql.sql.gz | mysql -u sadmin -p pass21 Customers</code>

You can also combine two or more backup files to restore at the same time, using the cat command. Here's how you can do that.

<code>cat backup1.sql backup.sql | mysql -u sadmin -p pass21</code>

====Moving Data Directly Between Databases====
How would you like to replicate your present database to a new location? When you are shifting web hosts or database servers, you can directly copy data to the new database without having to create a database backup on your machine and restoring the same on the new server. mysql allows you to connect to a remote database server to run sql commands. Using this feature, we can pipe the output from mysqldump and ask mysql to connect to the remote database server to populate the new database. Let's say we want to recreate the Customers database on a new database server located at 202.32.12.32, we can run the following set of commands to replicate the present database at the new server.

<code>mysqldump -u sadmin -p pass21 Customers | mysql --host=202.32.12.32 -C Customers</code>

====Backing Up and Restoring Your MySQL Database - Backing Up and Restoring Your Database with PHPMyAdmin====
You can backup your database using PHPMyAdmin in just a few mouse clicks. First head over to your database by clicking the database name in the list on the left of the screen. Once you get to your database details, you should get a menu header for your database which looks like so:

{{support:restore-mysql-db:image-001.png}}

Click on Export. This will get you to a screen with the following options.

{{support:restore-mysql-db:image-002.png}}

From here it's just a matter of clicking the right options and the 'Go' Button to get your database dump. To save the backup file on your computer, click on the **Save as file** option and the corresponding compression option to get the backup to download to your system.

====Restoring your Database via PHPMyAdmin====
Restoring your database is as easy as backing it up. If you would like to rewrite the backup over an existing database, click on the database name in the database list on the left, click all the check boxes next to the table names and select **Drop** in the **With selected**: drop down box.

{{support:restore-mysql-db:image-003.png}}

This will drop all existing table in the database. Then head over to the top menu bar and click on **SQL**. This will bring up a window where you can either type in SQL commands, or upload your SQL file. The window should look like this:

{{support:restore-mysql-db:image-004.png}}

Click on the **Browse** button, and select the sql backup file you wish to upload to the server. Once you've selected the file, click on 'Go'. This will upload the backup file to the server and execute the SQL commands in the backup and restore your database.

Now that you know how easy it is to backup and restore your database, spend a few minutes everyday to backup your important data. You can even automate the backup process by using a task scheduler like cron.

----

~~socialite~~