MySQL Backup

Hello,

How to Backup MySQL

The easiest way to backup your database would be to telnet to the 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 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

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.

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

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

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

So the syntax for the command to issue is:

mysqldump -u [username] -p [password] [databasename] [table1 table2 ....]

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

Backup & Restore for Multiple Mysql Database:-

·         If you are a database administrator who has to look after multiple databases, you'll need to back up more than one database at a time. Here's how you can backup multiple databases in one shot.

·         If you want to specify the databases to backup, you can use the --databases parameter followed by the list of databases you would like to backup. Each database name has to be separated by at least one space when you type in the command. So if you have to backup 3 databases, let say Customers, Orders and Comments, you can issue the following command to back them up. Make sure the username you specify has permissions to access the databases you would like to backup.

·         mysqldump -u root -p pass21 --databases Customers Orders Comments > multibackup.sql

·         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.

·         mysqldump --all-databases> alldatabases.sql

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.

·         mysqldump --no-data --databases Customers Orders Comments > structurebackup.sql 

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.

·         mysqldump --all-databases | bzip2 -c >databasebackup.sql.bz2

·         mysqldump --all-databases | gzip >databasebackup.sql.gz

Backing Up and Restoring Your MySQL Database - Easy Restore

Now that you've got backups of your database, let's learn how to restore your backup in case your database goes down. 

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.

mysql -u sadmin -p pass21 Customers < custback.sql

mysql -u [username] -p [password] [database_to_restore] < [backupfile]

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

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

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.

cat backup1.sql backup.sql | mysql -u sadmin -p pass21 

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  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.

mysqldump -u sadmin -p pass21 Customers | mysql --host=202.32.12.32 -C Customers

Backing Up and Restoring Your MySQL Database – PHPMyAdmin

PHPMyAdmin is an easy to use web-interface to control MySQL databases. You can run this from any system which has a web server which runs PHP scripts. PHPMyAdmin also lets you administer multiple database servers using the same interface. If you don't have telnet or shell access to your, this is probably how you would be administering your databases. If you haven't used PHPMyAdmin, head over to and download the latest version to experiment with.

Configure PHPMyAdmin

Before I go on to show you how you can back up and restore your databases using PHPMyAdmin, let me take a little while to show those who going to be first time users of PHPMyAdmin how to configure their installation. Once you've downloaded the latest version of PHPMyAdmin, copy the files from the installation source to your. Before you run the program, you'll have to edit a few lines in config.inc.php to get your installation to work properly. You can find this file in the root folder of the installation.

Head over to the line which reads:

$cfg['PmaAbsoluteUri'] = '';

Change this to the web URL of the installation. Let's say you've copied the files to the directory 'phpmyadmin' under the URL 'http://dbserver.com', you should change the option to:

$cfg['PmaAbsoluteUri'] = 'http://dbserver.com/phpmyadmin/';

Here are some more options about your database server which you'll have to modify before you save the configuration file. Let's say you have to connect to your database server, which is residing at 202.32.12.2 and connects on port 432, with the username 'jester' and password 'freddy32', the options you have to change will look like this:

$cfg['Servers'][$i]['host']  = '202.32.12.2';

$cfg['Servers'][$i]['port'] = '432';

$cfg['Servers'][$i]['connect_type'] = 'tcp';

$cfg['Servers'][$i]['auth_type'] = 'config';

$cfg['Servers'][$i]['user'] = 'jester';

$cfg['Servers'][$i]['password'] = 'freddy32'; 

Once you've got the correct setting for the database entered in these options, you can save your configuration file and head over to your PHPMyAdmin installation through your browser.

Thanks & regards,

Gaurav Singh