MySQL Three kinds of backup

A) Backup classification

123456789101112

冷备：cold backup数据必须下线后备份温备：warm backup全局施加共享锁，只能读，不能写热备：hot backup数据不离线，读写都能正常进行备份的数据集完全备份：full backup部分备份：partial backup备份时的接口（是直接备份数据文件还是通过mysql服务器导出数据）物理备份：直接复制（归档）数据文件的备份方式：physical backup逻辑备份：把数据从库中提出来保存为文本文件：logical backup完全备份：full backup增量备份：incrementl backup差异备份：fidderential backup

II) Backup tool mysqldump explanation

123456789101112131415161718192021222324252627

mysqldump：逻辑备份工具InnoDB引擎热备，MyISM温备，Aria温备备份和恢复的过程比较慢，很难实现差异或增量备份恢复时，如果目标库不存在，需要事先手动创建常用选项：--all-databases：备份所有库--databases db1 db2...：备份指定的多个库注意：备份前要加锁：--lock-all-tables：请求锁定所有表之后在备份。--lock-tables：锁定指定的表。--single-transaction：能够对InnoDB存储引擎实现热备。备份代码：--events：备份事件调度器代码--routines：备份存储过程和存储函数--triggers：备份触发器备份时滚动日志：--flush-logs：备份前，请求到锁之后滚动日志。复制时的同步位置标记--master-data=【0|1|2】0表示不记录1表示记录change master语句2记录为注释的change master语句使用mysqldump备份：请求锁：--lock-all-tables或使用--singe-transaction进行innoDB热备滚动日志：--flush-logs选定要备份的库：--databases记录二进制日志文件及位置：--master-data=0|1|2

Mysqldump three kinds of backup methods:

The first type:

Backup: [[email protected] ~]# mysqldump--databases benet >/tmp/benet.sql

mysql> unlock tables; unlocking

mysql> drop database benet; Delete Library
Query OK, 1 row affected (0.70 sec)

Recovery

Second way to back up:

Mysqldump--databases benet--lock-all-tables--flush-logs >/tmp/benet.sql Direct Backup

The third way of backup: mainly for InnoDB engine backup, to achieve hot standby.

Mysqldump--databases benet--single-transactions--flush-logs >/tmp/benet.3.sql

Back up all databases

Mysqldump--all-databases >/tmp/all.sql

The Xtrabackup Backup tool uses the following:

Download location: http://www.percona.com/

Yum-y Install percona-xtrabackup-2.1.8-733.rhel6.x86_64.rpm

Yum-y Install percona-toolkit-2.2.7-1.noarch.rpm

Back Up all databases:

Innobackupex--user=root/myback

123456

[[email protected] 2014-04-14_23-39-44]# cat xtrabackup_checkpointsbackup_type = full-backupedfrom_lsn = 0to_lsn = 1619068last_lsn = 1619068compact = 0

Recovering a Database

Innobackupex--apply-log/mydata/2014-04-14_23-39-44/the data first.
Innobackupex--copy-back/mydata/2014-04-14_23-39-44/recover data based on full backup

Do incremental Backups

Innobackupex--incremental/mydata/--incremental-basedir=/mydata/2014-04-14_23-39-44/

Based on the last full preparation.

123456

[[email protected] 2014-04-14_23-45-11]# cat xtrabackup_checkpointsbackup_type = incrementalfrom_lsn = 1619068to_lsn = 1619478last_lsn = 1619478compact = 0

View is an incremental backup

Restore incremental backup, first restore, full standby, post-increment

1234

innobackupex --apply-log --redo-only /mydata/2014-04-14_23-39-44/innobackupex --apply-log --redo-only /mydata/2014-04-14_23-39-44/ --incremental-dir=/mydata/2014-04-14_23-45-11/innobackupex --apply-log --redo-only /mydata/2014-04-14_23-39-44/ --incremental-dir=/mydata/2014-04-14_23-49-01/innobackupex --copy-back /mydata/2014-04-14_23-39-44/

Lvm-snapshot: LVM-based snapshot backup

Approximate steps:

1) The transaction log must be on the same volume as the data file

2) to request a global lock on MySQL before creating the snapshot volume, release the lock after the snapshot creation is complete

3) Once the global lock is completed, log scrolling is done, and the binary log and location tag (manual) are done;

12345678910111213

[[email protected] ~]# mount/dev/mapper/vg0-root on / type ext4 (rw)proc on /proc type proc (rw)sysfs on /sys type sysfs (rw)devpts on /dev/pts type devpts (rw,gid=5,mode=620)tmpfs on /dev/shm type tmpfs (rw)/dev/sda1 on /boot type ext4 (rw)/dev/mapper/vg0-usr on /usr type ext4 (rw)/dev/mapper/vg0-varon /vartype ext4 (rw)none on /proc/sys/fs/binfmt_misc type binfmt_misc (rw)sunrpc on /var/lib/nfs/rpc_pipefs type rpc_pipefs (rw)/dev/mapper/myvg-lvmy on /mnt type ext4 (rw)/mnt下放的就是mysql数据库得数

123	[[email protected] ~]# vim /etc/my.cnfthread_concurrency = 8datadir = /mnt

When you install the database, you create a logical volume, and the installation directory is/mnt.

Request global lock, scroll log

MariaDB [(None)]> flush tables with read lock;

MariaDB [(none)]> flush logs;

Record binary Log location

Mysql-e ' Show Master status ' > Mysql.txt

Create a Snapshot

[Email protected] ~]# lvcreate-l 1g-s-N mydata-snap-p r/dev/myvg/mydata

Unlock

MariaDB [(none)]> unlock tables;

Mount a snapshot and back up

mount/dev/myvg/mysql-snap/media/

tar-jcf/tmp.mysq.tar.xz/media/*

Uninstalling and deleting snapshots

1234	[[email protected] ~]# umount /media/[[email protected] ~]# lvremove /dev/myvg/mysql-snapDo you really want to remove active logical volume mysql-snap? [y/n]: yLogical volume "mysql-snap"successfully removed

MySQL Three kinds of backup