MySQL– Backups

Different types of Backups for MySQL databases

Logical Backup

It is done by dumping the contents of database into text files containing SQL statements which can be used to rebuild the database. They can be used even to build it on a host with different architecture and with different engine. But they are generally slower than the raw kind, for both backup and recovery operations. And they can be larger than the actual data too. It can be performed using the mysqldump utility.

Raw (Binary) Backup

It involves only file copy operations, so can be very fast to perform backup or recoveries. The engine type of tables cannot be changed in this method, as it preserves the actual data format. Depending on engine type, it can be used to take Cold, Hot or Warm backups. Various tools are available for this operation, which will be covered in a slide ahead.

Snapshot Based Backup

This kind of backup uses external utilities to take file system snapshot of MySQL. For InnoDB, a hot backup can be performed using this, and for other engines, a warm backup.

Replication-Based Backup

A copy of the Primary installation can be maintained on a separate server using a Replication setup. Using this method, an exact replica of the databases can be maintained separately, and can be used for backup purposes. But it is comparatively an expensive option.

Incremental Backup

The binary logs, which contain a record of all changes done on the database can be backed up for the  purpose of doing point-in-time-recovery. Using tools like mysqlbinlog, databases can be recovered from Binary logs till a certain point in time, or certain log position also. Binary logging should be enabled on MySQL servers

Backup Tools

Though backups can be taken without the use of any tool, there are some tools designed specifically to make the operation easier. Some of them have been described here

mysqlhotcopy

Provided with the MySQL distribution, this is useful to perform raw backup of MyISAM type tables only. Contrary to its name, it is not exactly a “hot” backup, as database is not fully available during the operation, but is available only for read. It is locked and cannot be altered. So, it is also called a “warm” backup.

mysqldump

It is provided with the MySQL distribution. It helps in taking logical backups for any kind of engine. Our  current masters are designed to automatically enable this backup in cron at the time of installation.

MySQL Administrator

It is available as a separate GUI download from MySQL, and can be used to perform logical backups. It can also be used with any database engine, and has some tracking capabilities also.

InnoDB Hot Backup (ibbackup)

It is a commercial product available from InnoDB. It performs raw backups of MySQL databases using InnoDB engine only. It is in fact a real “hot backup” as databases are completely available for read and update during the operation.

Third Party Tools

Among the many commercially available third party tools available, there is one called Zmanda Recovery Manager, that can perform both logical and raw backups for all engine types. It can do both warm and hot backups, and can drive Replication and Snapshot based backups too. It takes consistent backups, and has extensive reporting and Tracking capabilities

Script to List SQL Server Database Backup History

SELECT 
   CONVERT(CHAR(100), SERVERPROPERTY(‘Servername’)) AS Server,
   bs.database_name, 
   datediff(MINUTE, bs.backup_start_date,   bs.backup_finish_date) AS time_taken,
   CASE bs.type     WHEN ‘D’ THEN ‘Database’         WHEN ‘L’ THEN ‘Log’     END AS backup_type, 
   bs.backup_size, 
   bmf.physical_device_name,  
   bs.name AS backupset_name
FROM   msdb.dbo.backupmediafamily  as bmf
   INNER JOIN msdb.dbo.backupset as bs ON bmf.media_set_id = bs.media_set_id 
WHERE  (CONVERT(datetime, bs.backup_start_date, 102) >= GETDATE() – 7) 
AND bs.type = ‘D’
/* comment out statement above to report all backups */
ORDER BY 
   bs.database_name,
   bs.backup_finish_date

SQL Server Restore vs Repair

Restore vs Repair
- decision depends upon SLA and your situation
    (a) DB Available: no, then restore from backup
    (b) Working backup available? No, repair or restore from older backup with CONTINUE_AFTER ERROR. Is log damaged? yes, restore or run emergency repair or extract to a new DB
    (c) CheckDB Failed? yes, restore
    (d) Just NCI damaged? rebuild indexes. Un-repairable errors? Restore

Backup Recovery
- Best way to avoid data loss, but not best way to avoid downtime
- Backups have to be available when needed, and have to be valid

Repair
- It doesn’t fix data, it fixes structure and tries to be as fast as it can be.
- It tries to run most intrusive errors first.
- It counts # of errors found and fixed. Though some errors might be masked, and hence checkDB is run again.
- Repair is done offline because its hard to get them working in online mode

SQL Server Corruption

Database Corruption
- corruption cant be prevented, so always keep checking 
- mostly caused by IO 
- keep practicing for how to recover from corruption

What to do when Corruption Happens
- Don’t panic 
- Use DR Run book
- check error logs, event log, maintenance log, know the IO errors and what they mean 
- check for backups, know about page protection options 
- CHECKDB (and know what it does), wait for it to finish

How Corruption Occurs
- happens mostly because of IO, or less for bad memory, and even less for SQL bug; 
- consider MTBF (mean time between failure) but know exceptions happen
- it cant happen due to: application, interruption to shrink or rebuild or long batch, log shipping, replication and mirroring
- 1st order corruption: corruption in native page itself. – 2nd page corruption: a wrong value is derived/ calculated because of an underlying corrupted page. 2nd order is hard to find/fix. Log shipping etc are meant to find 1st order corruption, but they cant do anything about 2nd order corruption

Signs of Corruption
- 823,824 errors
- users getting disconnected
- Backup and maintenance job failing – agent alerts – errors in error log

 

Reference: SQL Server MCM Deep Dive Sessions

SQL Server Single Page Recovery

Last Wednesday a friend reached me with a request to help with DB corruption which they encountered from DBCC runs. It was a single page corruption, and was a SQL 2005 instance, UAT database (they had Prod backup, but it has had some data and plus it was a big database so they didn’t wanted to run with restore). I went around and talked it through, and eventually we together sorted it out. Though I have a log of all what we did from out command session, but I can’t release it for security reasons (and too lazy to work with those images right now).

So, here’s the exact run of what we did instead:

DBCC CHECKDB <DB> WITH ALL_ERRORMSGS, NO_INFOMSGS;    – note down slot#, and Page details

ALTER DATABASE <DB> SET SINGLE_USER;   – to ensure things get messy when updating the page
DBCC PAGE (<DB>, 2, 145, 3);    – couldn’t find specific details, so need to run more specific
NOTE: if we would have found some details here, we would have scrolled across to the noted slot# and would have tried to find the issue there, but in our case it simply did any favors.

DBCC PAGE (<DB>, 2, 145, 2);
BACKUP LOG <DB> TO DISK = <location> WITH INIT;     – backup the log, just for safety and to safeguard the log tail
RESTORE DATABASE <DB> PAGE = ’1:145′ DISK = <backup>;    – single page recovery
RESTORE LOG <DB> FROM DISK=<location> WITH NORECOVERY;    – restore the log tail
RESTORE DATABASE <DB> WITH RECOVERY;
DBCC CHECKDB                – important to run DBCC CHECKDB again, always recommended
ALTER DATABASE <DB> SET MULTI_USER;

Important aspects which should be highlighted here is, never panic when you see such page corruption or DBCC error messages, and when you think you have sorted it, validate it with DBCC CHECKDB again.