New Backup Features in MySQL 6.0

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

Online backup



Online Backup was introduced in MySQL 6.0. Apart from performing the usual
backup tasks the new feature is that it does not block the concurrent

connections to the database. Hence the word “Online”, which means without

blocking the database. Therefore, unlike other database backup solutions like

mysqldump, which caused other transactions on the database to hang while the

backup was being performed, the Online Backup allows Database Manipulation

Language (DML) statements to execute concurrently while the backup is in

progress. Though some locking of the database still happens. This locking is of

Data Definition Language (DDL) statements like CREATE, ALTER or DROP queries,

which means that only DML queries can execute concurrently while database backup

is in progress. Thus, the backup of the database can be carried without losing

any transaction activity that might be happening during that moment. For DBAs

this could be an interesting feature to opt for with the MySQL 6.0 Server when

its final build is released.

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.

From the MySQL client window, we can issue Backup command to create database backup which, after being dropped, can be restored back using Restore command.

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.