Thursday, March 7, 2013

BACKUP AND RECOVER MySQL DATABASE

     It is important to back up your databases so that you can recover your data and be up and running again in case problems occur, such as system crashes, hardware failures, or users deleting data by mistake.

Types of Backup:

logical backup This type of backup is created by saving information that represents the logical database structures using SQL statements like create database, create table and insert. This type of backup is ideal when you want to upgrade from one version of MySQL to another however it is a slower method of backing up.
physical backup This type of backup is a backup of the actual database files or disk partitions, this type of backup can be very fast to backup and restore.
full backup A full backup is a standalone backup containing everything in the database, this could then be restored on another server. A full backup can be either logical or physical.
incremental backup This type of backup only contains the data that has changed from the last backup. The advantage of this type of backup is that it is faster as there is not some much data to backup, however the disadvantage is that it takes longer to recover.
consistent backup This is a backup at an exact moment in time, generally you shutdown the database (or quiescent mode) then take the backup.
hot backup This type of backup is taken when the database is running, during the backup both reads and writes are not blocked
warm backup This type of backup is taken when the database is running, however reads are not blocked but writes are prohibited from making any modifications to the database.
cold backup Similar to a consistent backup as the database is shutdown before the backup begins
point-in-time restore It is a restoration of a database to a specified date and time , some databases use a full backup and recovery logs to restore to that point-in-time, others can only use the last full backup which means that data might have to be re-keyed into the system.

 Types of Backup tools:

Backup tools for MySQL
Backup method
Storage engine
Impact
Backup speed
Recovery speed
Recovery granularity
mysqldump
ALL
WARM
MEDUIM
SLOWEST
MOST FLEXIBLE
mysqldump
INNODB
HOT
MEDUIM
SLOWEST
MOST FLEXIBLE
select into outfile
ALL
WARM
SLOW
SLOW
MOST FLEXIBLE
mk-parallel-backup
ALL
WARM
MEDUIM
MEDUIM
FLEXIBLE
ibbackup
INNODB
HOT
FAST
FAST
FLEXIBLE
ibbackup
ALL
WARM
FAST
FAST
FLEXIBLE
backup command in mysqld
ALL
HOT
FAST
FAST
FLEXIBLE
filesystem (copy files)
ALL
COLD
FASTEST
FASTEST
NOT FLEXIBLE
snapshot (using LVM, ZFS, VMWare)
ALL
ALMOST HOT
FAST
FAST
LEAST FLEXIBLE
mysqlhotcopy
MyISAM
MOSTLY COLD
FAST
FAST
FLEXIBLE

MySQLdump:

     mysqldump is an effective tool to backup MySQL database. It creates a *.sql file with DROP table, CREATE table and INSERT into sql-statements of the source database. To restore the database,  execute the *.sql file on destination database.  

mysqldump # backup all databases
mysqldump --user=root --password --all-databases > backup_<date>_all.sql

# backup a specific database
mysqldump --user=root --password <database_name> > backup_<date>_<database_name>.sql
# backup multiple databases
mysqldump --user=root --password <database_name>,<database_name> > backup_<date>.sql

# backup a table from a database
mysqldump --user=root --password <database_name> <table_name> > backup_<date>_<database_name>_<table_name>.sql
# backup some specific data
mysqldump --user=root --password <database_name> <table_name> --where "last_name='VALLE' order by first_name > backup_<date>.sql
# dumping from one database to another
mysqldump --databases <database_name> | mysql -h <destination_host> <database_name>
restore a mysqldump # all databases
mysql --user=root --password < backup.sql

# specific database
mysql --user=<user> --password <database_name> < backup_<dataabse_name>.sql

SELECT INTO OUTFILE

     The SELECT INTO OUTFILE SQL statement is actually a variant of the SELECT query. It is used when you want to direct query output to a text file. This file can then be opened by a spreadsheet application, or imported into another database like Microsoft Access, Oracle, or any other software that supports delimitation.
Ex

SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
 
LOAD DATA INFILE 
 
The LOAD DATA INFILE statement reads rows from a text file into a table at a very high speed. The file name must be given as a literal string. 

Ex

LOAD DATA INFILE 'data.txt' INTO TABLE db2.my_table;

mysqlhotcopy

mysqlhotcopy is a Perl script that was originally written and contributed by Tim Bunce. It uses FLUSH TABLES, LOCK TABLES, and cp or scp to make a database backup. It is a fast way to make a backup of the database or single tables, but it can be run only on the same machine where the database directories are located. mysqlhotcopy works only for backing up MyISAM and ARCHIVE tables. 

Ex

#backup a database
mysqlhotcopy <database_name> /backups

# backup multiple databases
mysqlhotcopy <database_name> accounts /backups

#backup a database to to another server
mysqlhotcopy --method=scp <database_name> \ username@backup.server:/backup


# use pattern match to backup databases and tables
mysqlhotcopy <database_name>./^employees/ /backup


  
10 Ways to Automatically & Manually Backup MySQL Database





No comments:

Post a Comment