New backup features in MySQL 6.0

BANGALORE, INDIA: Database administrators have this enormous responsibility to ensure that data in the various databases remains failsafe. They also have to focus on the performance and have to keep the databases tuned optimally for better performance. They also have to keep a disaster recovery plan in place, for which taking regular database backups is one of the requirements.

This database backup can also be used while doing database migration to another servers or while upgrading to a newer version of the database server. Backup and Recovery are two important tasks for the DBA, to be performed on a regular basis. For MySQL database, a utility called “mysqldump” is available that is used to create the database backup, but the hindrance is that while the backup of the database is taking place the transactions to the database get blocked.

MySQL becoming more attractive in India

And as enterprise databases run into several hundred gigabytes, taking backups will consume a lot of time. Also, the time the enterprise database is being backed up, it will be kept locked from transaction calls. With the alpha release of new MySQL 6.0 Server, a new backup and restore feature has been introduced which answers the problems associated with mysqldump utility.

Performing backups
This new Online Backup/Restore utility in MySQL 6.0 server is easy to use and unlike mysqldump, it is used to issue backup and restore commands right from the mysql client command prompt window. The syntax of the command for performing the backup is as follows:

BACKUP DATABASE database_name_list TO 'image_file_name';

The BACKUP DATABASE copies all data and metadata from one or more MySQL databases, into an image file from where it can be restored later. It is also possible to use SCHEMA as a synonym for DATABASE in the syntax, i.e. “BACKUP SCHEMA...” as a command instead of “BACKUP DATABASE...”. The database_name_list can be the name of a database or a list of multiple databases separated by commas. And the image_file_name is the name of the backup file along with the absolute path where the file will be saved.

The syntax for online backup is easy to remember and can be used to perform backup of databases on MyISAM storage engines. Earlier the backup had to be performed in an offline state for MyISAM type of database, now with this new ability of MySQL 6.0, online backup is possible, which means DML statements like INSERT, UPDATE and DELETE can run concurrently while the backup is in progress. Also note that the backup of the MyISAM type database in MySQL 6.0 is also a native backup unlike the backup files produced by mysqldump utility which are logical backups. The difference between native and logical backup is that in a native backup the backup file is taken from the underlying OS and is not a readable SQL-based file which is generated in case of logical backup. The benefit of a native database is that its executes faster and requires less storage space as compared to logical backup files.

To check this utility we used a test database named “world”, which is available for download from the MySQL website. From the MySQL client command prompt, we issued the following command for backup.

mysql> BACKUP DATABASE world TO 'c:/world-db.backup';

While the database backup was in progress, we simultaneously issued an update command on the database to see that the DML queries can run concurrently. Though the backup of the database was created in about 7 seconds, the update query which was issued just after issuing the BACKUP DATABASE command got executed instantly and before the database backup could be completed. When we tried creating the backup of the database using mysqldump utility from Windows command prompt, we used the following command:

mysqldump -uroot -ppassword --port=3307 --extended-insert --quick world > c:/world.dmp

And issuing an update query along side resulted in the query being halted till the backup of the database got completed.

Performing Restore
If for some reasons the database gets corrupted or gets lost, one can restore the database from the backup file. The syntax for command to restore database to a previous state is:

RESTORE FROM 'image_file_name';
The image_file_name is the argument where location and name are of the backed up file that was made using BACKUP DATABASE command. Using RESTORE command, whatever that was backed up using BACKUP DATABASE command will get restored to the state when backup was performed. The user who performs the restoring of the database must have the CREATE or equivalent rights to perform the task.

As backup tasks can be done online, restore cannot be done online as, RESTORE is classified under DDL statement because it alters the structure of database. Thus “Online Restore” is not possible as during restore some table locks will be caused so that the data could be written to the tables from the backup file.

To restore the “world” database, we first changed the database workspace and then dropped the database by using following command:

mysql> use mysql
Database changed
mysql> drop database world;

As we have saved the backup of the “world” database in location in C drive as “world-db.backup”, we will pass this as argument to the RESTORE command as follows:

mysql> RESTORE FROM 'c:/world-db.backup';

We have used this online backup utility for databases using MyISAM storage engines. Even if the database has multiple storage engines for different tables, the backup can be performed. Suppose, if there is just one table of InnoDB type, then while performing backup, the MyISAM tables will be backed up in native format while the table in InnoDB type will be backed up in logically-based backup format.

Conclusion
This new feature in MySQL 6.0 is easy to use and will be beneficial for DBAs to perform backup of the database on the fly without hindering any transactional query on the database.