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