DATABASEDATABASE BACKUP AND RECOVERYBACKUP AND RECOVERY

By - Soumya Dash

Session Overview

When do we need backups?

What needs to be backed up?

When should backups be performed?

Where will the backups be stored?

How can backups be performed?

Restoring from backups

Backup strategy

Backup – Why do it?

● In business today,'You are your data'● Lose data,lose business● Lost productivity, customer relationships,etc

Types of Backup

● Logical backup

● Physical backup

● Local backup

● Remote backup

● Full backup

● Incremental backup

When do we need backup?

● Hardware Failures– A system crash may cause some of the data in the database to be lost.– Hardware failure leads to data loss.

● User/Application Failure– Accidental DROP or DELETE statements– Editing table files with text editors,usually leading to

corrupt tables.

What needs to be backed up?

● Database content– For full backups– Logical or physical backup

● Log files– For incremental backups– Point in time recovery

● Configuration information– /etc/my.cnf– Cron jobs

When should backups be performed?

● On a regular basis

● Not during high usage peaks(off hours)

● Static data can be backed up less frequently

● Schedule it at particular time intervals

Where to store backup?

● On the database serverOn a separate file system/volume or hard disk drive

● Copied to another serverOn or off site

Cloud storage● Backed up to tape/disk● Choose multiple locations

Database Backup Methods

● Backup programs

Mysqldump

Mysqlhotcopy● Copying table files (*.frm, *.MYD, and *.MYI files)

● Delimited-Text files

● Incremental Backups using Binary Log

● Backups using Replication Slaves

Using mysqldump for backups

Mysqldump is a backup program originally written by Igor Romanenko.

Used to dump a database or a collection of databases for backup or transfer to another server(not necessarily MySQL).

Mysqldump writes SQL statements to the standard output.

This output consists of CREATE statements to create dumped objects (databases, tables, stored routines etc) and INSERT statements to load data into tables.

The output can be saved in a file and reloaded later using mysql to recreate the dumped objects.

Options are available to modify the format of the SQL statements, and to control which objects are dumped.

Generates files in CSV, other delimited text, or XML format also.

Backup using mysqldump

Set of one or more tables

$ mysqldump -u [uname] -p[pass] db_name table1 > table_backup.sql$ mysqldump -u [uname] -p[pass] db_name table1 table2 > table_backup.sql

Set of one or more complete databases

$ mysqldump -u [uname] -p[pass] db_name1 db_name2 > db_backup.sql

All databases

$ mysqldump -u [uname] -p[pass] –all-databases > all_db_backup.sql

An entire MySQL

$ mysqldump -u [uname] -p[pass] db_name > db_backup.sql

Copying data from one server to another

$ mysqldump --opt db_name |mysql –host = remote_host -C db_name

Auto-compressing the output using gzip

$ mysqldump -u [uname] -p[pass] db_name | gzip > db_backup.sql.gz

Remote Backup$ mysqldump -P 3306 -h [ip_address] -u [uname] -p[pass] db_name > db_backup.sql

Restore backup from mysqldump

● Create a database on the target machine

● Load the file using the mysql command:

$ mysql -u [uname] -p[pass] [db_to_restore] < [backupfile.sql]● To restore to an existing database

$ mysqlimport -u [uname] -p[pass] [dbname] < [backupfile.sql]

Making delimited text file backups

● This method saves only table data, not the table structure.

● Writes the selected rows to a file on the server host

Syntax

SELECT * INTO OUTFILE 'file_name' FROM tbl_name

● Example of file in the comma-separated values (CSV) format

SELECT a,b,a+b INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM table;

Making Incremental Backups by Enabling the Binary Log

● By default, the server creates binary log files in the data directory.

● The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup.

● To see a listing of all binary log files

mysql> SHOW BINARY LOGS;

● An incremental backup only backs up data that changed since the previous backup.

● Start the server with the --log-bin option to enable binary logging.

● mysqlbinlog utility converts the events in the binary log files from binary format to text so that they can be executed or viewed. mysqlbinlog has options for selecting sections of the binary log based on event times or position of events within the log.

● Copy to the backup location,all binary logs from the moment the last backup was taken to the last but one.

Enable binary logging

Configure mysql to do binary logging.

Edit : /etc/mysql/my.cnf:

Add : log-bin = mybinlog

Specify which databases to do binary logging for, or which databases NOT to do binary logging for.

1) “binlog_do_db” turns binary logging on for a given database.

binlog_do_db = mydb

2) “binlog_ignore_db” turns binary logging on for all databases except the database(s) names.

binlog_ignore_db = mydb

Restart mysql server.

Restore from binary logs

● Go to the folder where mysqlbinlog utility is placed and convert binary log file into sql

C:\xampp\mysql\bin>mysqlbinlog C:\xampp\mysql\data\binlog\bin-log.000001 > sql.txt

● Directly execute sql on the database

C:\xampp\mysql\bin>mysqlbinlog C:\xampp\mysql\data\binlog\bin-log.000001 | mysql -u root -p

● Specify specific duration to extract sql

C:\xampp\mysql\bin>mysqlbinlog --start-datetime="2014-06-01 10:31:44" C:\xampp\mysql\data\binlog\bin-log.000001 > sql.txt

This will extract sql after the date of 2014-06-01 10:31:44.

Point In Time Recovery using binary logs

1) Point-in-Time Recovery Using Event Times

C:\xampp\mysql\bin>mysqlbinlog --start-datetime="2014-05-27 10:01:00" C:\xampp\mysql\data\binlog\bin-log.000001 | mysql -u root -p

C:\xampp\mysql\bin>mysqlbinlog --stop-datetime="2014-05-27 9:59:59" C:\xampp\mysql\data\binlog\bin-log.000001 | mysql -u root -p

2) Point-in-Time Recovery Using Event Position

C:\xampp\mysql\bin>mysqlbinlog --stop-position=368312 C:\xampp\mysql\data\binlog\bin-log.000001 | mysql -u root -p

C:\xampp\mysql\bin>mysqlbinlog --start-position=368315 C:\xampp\mysql\data\binlog\bin-log.000001 | mysql -u root -p

Making Backups Using Replication Slaves

● Used when there are performance problems with your master server while making backups.

● Set up replication and perform backups on the slave rather than on the master.

● Put the master server db in a read-only state by executing these statements:

mysql> FLUSH TABLES WITH READ LOCK;

mysql> SET GLOBAL read_only = ON;

● While Master is read only, perform the backup using mysqldump.

● Restore Master to its normal operational state by executing these statements:

mysql> SET GLOBAL read_only = OFF;

mysql> UNLOCK TABLES;

Recovering from Backups

● Restoring tables to the state before a crash requires both the backup files and the binary log.

Backup files restore the table to the state they were at the time of backup.

The synchronised binary logs are used to extract queries issued between the backup and now.

● If recovering data lost due to unwise queries,dont issue them again.

DB Recovery = Last full backup & binlogs

Recovering from Corrupt tables

● Bring the database in recovery mode

Shut down incase it's still running

Add innodb_force_recovery=1 to my.cnf

Change the port from 3306 to some random value.● Check for the corrupt tables using mysqlcheck –all-databases

● Backup and drop corrupted tables using mysqldump

● Restart mysql in normal mode without changing the port

● Import the backup file

● Change port

Backup Strategy

Perform backups regularly and frequently

Performing Backups Before and After You Make Structural Changes

Turn on the binary update log

Synchronise update logs with the backup files

Store the backups on a different file system than where your databases are

Make periodic full backups, using the mysqldump command

Make periodic incremental backups by flushing the logs

Scheduling backups

Choose the right storage platform for backups

References

● http://dev.mysql.com/doc/refman/5.6/en

● http://www.techflirt.com/

● http://planet.mysql.com/

THANK YOU