It is important from an administrative and operational point of view to safeguard your data and database from all kind of risks. Applying this to a MySQL database, it has lot of challenges to overcome and survive – so that the data associated with it is safe, with precise data integrity and genuineness over time. As like any software applications, a MySQL database is also facing threats and challenges in the below forms:
- Hardware and software failures
- Operating system/File system crashes
- Data corruption and deletion
- Database upgrades and data corruption
- Etc.
In all these circumstances, Database Backups help a DBA to take backups of the Data over a period of time and restore it back after an issue to recover the database. This article discusses the various database backup and restoration methods briefly and a general strategy for implementing this.
Types of Backup and Restore
Physical Backup is copying the directories and files that store the database and contents along with logs and configuration files to the backup location. When needed, the entire backed up resources are restored by another file system Copy operation. Since this is a file system operation, it is faster and suitable for large database systems. Backup copying must be performed after stopping the database server, so that database is locked and database data changes are prevented during the process. Physical backups are portable only to those machines having similar hardware characteristics and configuration.
Logical Backup is done by using MySQL Queries to obtain database structure and data records. The keys and other indexes also come as part of the database-table structure and hence are backed up. This is slower than physical backup, since the database information is to be accessed and converted to logical formats like SQL, CSV, Text, XML, etc. The output can be available in text format, with a larger size or can be compressed for storage in the same server or to download to the client software like browser, database manager etc. These backups are machine independent and hence highly portable. It can be restored by issuing the backed up queries. Care must be taken during backup and restore so that correct parameters are used, like “CREATE TABLE IF NOT EXISTS” or first delete the old table before restoration, so that the restore queries do not fail due to table already present.
Online Backup (hot backup) is performed while database server is running, and this allows clients to access database while backup operation is being performed. Suitable locking must be used to prevent data modifications that may affect backup data integrity (warm backup). Offline Backup (cold backup) is performed after stopping the server, thus ensuring backup data integrity, but causes downtime for the clients. Restoration for these backups are also similar, with Online Restore is performed while server is running but appropriate locking for WRITE operations are used while READ is allowed to avoid downtime. Offline restore is much simple, but like offline backup, causes downtime.
Local Backup is performed when both client and server are on the same machine and backup resources are stored locally. Remote Backup uses a remote client to backup the database server. The backup files can be either stored in the server itself, downloaded to the client machine or can be copied to another remote machine.
Snapshot backups are file system implementations to take backups of a specific part of the file system at specific points of time. Thus copying entire file system can be avoided by including only the required portions. Full Backup is the entire data associated with MySQL Server at a given point of time. An
Incremental Backup scheme uses MySQL’s binary log to backup only those parts that are changed, during a specific time span. In effect, a set of different versions of backups are created. A Full Recovery restores the entire database to the point when the backup was taken. If data is modified after the backup is taken, Incremental Recovery is used to restore the changes made in the database that will roll-back the database to the desired point of time.
MySQL Backup and Restore Methods
The most prominent tool is the mysqldump program available with MySQL. It offers various configuration parameters to control different aspects of the backup. mysqldump creates dump files that contains dumped objects for each CREATE and INSERT statements in SQL and TEXT format. These objects represent the database, tables, data, routines, procedures, triggers etc. The MySQL program can be used to restore the database from the dump file. For MyISAM tables that store each table in separate files, by copying the relevant table files, backups can be taken. To ensure backup data integrity, either stop the server or lock and flush the concerned tables before copying. The SELECT * INTO OUTFILE … statement can be used to backup the data records (not database structure) to a delimited-text file. The LOAD DATA INFILE.. statement and also program mysqlimport restores the data records from the delimited-text file.
After restoring a Full Backup, an Incremental Backup Recovery or Point-in-Time Recovery can be done to restore the database to a subsequent point of time after the Full Backup was performed. The Binary Log files are used to retrieve the changes that happened after the Full Backup. A MySQL web based client can be used for manually taking and restoring backups as Export and Import operations. In a Linux environment the backup program mysqldump can be set as a Cron Job that runs periodically to take backups. It is important to identify a secure location for the backup. While the local server is a good option in terms of simplicity, in order to survive a hardware failure, it is recommended to store Full/Incremental backups in an external storage device or a remote server including the cloud data storage.
References: https://dev.mysql.com/doc/refman/5.7/en/backup-and-recovery.html