First lets start with a few terms associated with backups
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: 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.
Taking Backup:
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
The mysqldump program has been around a long time, it provides a logical backup of the entire database, individual databases, individual tables or even subsets of data using the –where option, it is often called a data dump. The output is in ascii format which means that you can open it in vi or notepad and change the contains if desired. I am not going to detail all options of the mysqldump command but show you a few examples
mysqldump
backup all databases
mysqldump –user=root –password –all-databases > backup__all.sql
backup a specific database
mysqldump –user=root –password > backup__.sql
backup multiple databases
mysqldump –user=root –password , > backup_.sql
backup a table from a database
mysqldump –user=root –password > backup_.sql
backup some specific data
mysqldump –user=root –password –where “last_name=’VALLE’ order by first_name > backup_.sql
dumping from one database to another
mysqldump –databases | mysql -h
restore a mysqldump ## all databases
mysql –user=root –password < backup.sql
specific database
mysql –user= –password < backup_.sql
—You can use the into outfile clause of the select statement to backup individual tables, the command used to load the dump created is load data infile
select into outfile / load data infile ## dump of the accounts table
select * into outfile ‘/tmp/accounts.txt’ from accounts;
load the dump
load data infile ‘/tmp/accounts.txt’ into table accounts;
Note:
if you have a 16 core server and you are dumping 32 tables, the script will start up 16 separate copies of mysqldump with each process dumping a separate table.
—-The mysqlhotcopy is a perl script written to provide a consistent backup of MyISAM and ARCHIVE tables, it does some limitations one of which when run it uses the lock tables command to create read locks on the tables being backed up, this allows for a consistent backup. again there are a number of options that you can use so have a look at the man page, here are a few examples
mysqlhotcopy
backup a database
mysqlhotcopy /backups
backup multiple databases
mysqlhotcopy accounts /backups
backup a database to to another server
mysqlhotcopy –method=scp \ [email protected]:/backup
use pattern match to backup databases and tables
mysqlhotcopy ./^employees/ /backup
—Recovering from Crashes
Most often you have to recover to a point-in-time after the last backup, the normal procedure is as follows
restore the latest backup
recovery the data to a point-in-time using recovery log files
————MySQL server uses a binary format for the log files to save space, this means that you cannot view these files directly, a utility called mysqlbinlog is supplied to convert these log files into a text format that you can view. So the process for performing a point-in-time restore for MySQL is
restore the database using the last backup
determine the first binary log and starting position needed
determine the last binary log needed
convert the binary log to text format with the mysqlbinlog utility using options to specify the start and stop time
check the text file to make sure it’s what you need
import the converted binary log(s)
convert the log files
convert to a specific binary log file
mysqlbinlog mysql-bin.010310 > mysql-bin.010310.sql
use a date to end at a specific time
mysqlbinlog –stop-datetime=’201204-29 17:00:00′ mysql-bin.010312 > mysql-bin.010312.sql
other options are
–stop-datetime
–start-datatime
–start-position
–stop-position
restore the converted file mysql –user=root -password < mysql-bin.010310.sql
