In the earlier post we discuss about how to take backup using mysqldump. Today will discuss about how to take backup using Percona XtraBackup. The Percona XtraBackup is a Physical Dump / Backup which consists of raw copies of data and data directories present in it. This type of backup is very useful and suitable for the databases which contains large and important data sets that need to recovered quickly when the disaster occurs.
Creating a Backup:
To create a backup, Xtrabackup script has to be called with backup, datadir and targetdir parameters.
backup - is to create the backup
datadir - which is where the MySQL data directory stored
targetdir - backup directory, where we want to save the backup
If the target directory does not exist, xtrabackup creates it. If the directory does exist and is empty, xtrabackup will succeed. xtrabackup will not overwrite existing files, it will fail with operating system error 17, file exists.
The tool changes its working directory to the data directory and performs two primary tasks to complete the backup:
After the backup is finished, the target directory will contain files such as the following, assuming you have a single InnoDB table test.tbl1 if MySQL’s innodb_file_per_table option is enabled.
Note: The backup time will purely depend on your Size of Database you have.
Creating a Backup:
To create a backup, Xtrabackup script has to be called with backup, datadir and targetdir parameters.
backup - is to create the backup
datadir - which is where the MySQL data directory stored
targetdir - backup directory, where we want to save the backup
If the target directory does not exist, xtrabackup creates it. If the directory does exist and is empty, xtrabackup will succeed. xtrabackup will not overwrite existing files, it will fail with operating system error 17, file exists.
The tool changes its working directory to the data directory and performs two primary tasks to complete the backup:
- It starts a log-copying thread in the background. This thread watches the InnoDB log files, and when they change, it copies the changed blocks to a file called xtrabackup_logfile in the backup target directory. This is necessary because the backup might take a long time, and the recovery process needs all of the log file entries from the beginning to the end of the backup.
- It copies the InnoDB data files to the target directory. This is not a simple file copy; it opens and reads the files similarly to the way InnoDB does, by reading the data dictionary and copying them a page at a time.
$ xtrabackup --backup --datadir=/var/lib/mysql/ --target-dir=/data/backups/mysql/
Sample output:
log scanned up to (3646475517369)
log scanned up to (3646475581716)
log scanned up to (3646475636841)
log scanned up to (3646475718082)
log scanned up to (3646475988095)
log scanned up to (3646476048286)
log scanned up to (3646476102877)
log scanned up to (3646476140854)
[01] Copying /usr/local/mysql/var/ibdata1
to /usr/local/mysql/Backups/2017-01-01_21-11-15/ibdata1
[01] ...done
After the backup is finished, the target directory will contain files such as the following, assuming you have a single InnoDB table test.tbl1 if MySQL’s innodb_file_per_table option is enabled.
/data/backups/mysql/ibdata1
/data/backups/mysql/test
/data/backups/mysql/test/tbl1.ibd
/data/backups/mysql/xtrabackup_checkpoints
/data/backups/mysql/xtrabackup_logfile
Comments
Post a Comment