服务器之家

专注于服务器技术!
当前位置:首页 > 数据库服务器 > MySQL

MySQL数据库的三种备份方式使用分析

发布时间:2017-01-06 来源:服务器之家

一)备份分类

冷备:cold backup数据必须下线后备份

温备:warm backup全局施加共享锁,只能读,不能写

热备:hot backup数据不离线,读写都能正常进行

备份的数据集

完全备份:full backup

部分备份:partial backup

备份时的接口(是直接备份数据文件还是通过mysql服务器导出数据)

物理备份:直接复制(归档)数据文件的备份方式:physical backup

逻辑备份:把数据从库中提出来保存为文本文件:logical backup

完全备份:full backup

增量备份:incrementl backup

差异备份:fidderential backup

二)备份工具mysqldump讲解

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三种备份方式:

第一种:

wKioL1NIFk3SEDI0AAKkXLygpwk752.jpg

备份:[root@station141 ~]# mysqldump --databases benet > /tmp/benet.sql

mysql> unlock tables;解锁

mysql> drop database benet;删除库
Query OK, 1 row affected (0.70 sec)

恢复

wKiom1NIFyDjIAYAAALGfdPcPvY533.jpg

第二种方式备份:

mysqldump --databases benet --lock-all-tables --flush-logs > /tmp/benet.sql直接备份的

第三种方式备份:主要是针对innoDB引擎备份,实现热备。

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

备份所有数据库

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

[root@localhost 2014-04-14_23-39-44]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 1619068
last_lsn = 1619068
compact = 0

Xtrabackup备份工具使用如下:

下载位置: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 

备份全部数据库: 

innobackupex --user=root /myback 

[root@localhost 2014-04-14_23-39-44]# cat xtrabackup_checkpoints

backup_type = full-backuped

from_lsn = 0

to_lsn = 1619068

last_lsn = 1619068

compact = 0

恢复数据库

innobackupex --apply-log /mydata/2014-04-14_23-39-44/先把数据整理出来
innobackupex --copy-back /mydata/2014-04-14_23-39-44/根据全备份恢复数据

做增量备份

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

基于上次的全备做

[root@localhost 2014-04-14_23-45-11]# cat xtrabackup_checkpoints

backup_type = incremental

from_lsn = 1619068

to_lsn = 1619478

last_lsn = 1619478

compact = 0

查看是增量备份

恢复增量备份,依次先恢复,全备,后增量

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快照备份

大致步骤:

    1)事务日志跟数据文件必须在同一个卷上

    2)创建快照卷之前,要请求mysql的全局锁,在快照创建完成之后释放锁

    3)请求全局锁完成之后,做一次日志滚动;做二进制日志及位置标记(手动进行);

[root@localhost ~]# 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-var on /var type 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数据库得数

[root@localhost ~]# vim /etc/my.cnf

thread_concurrency = 8

datadir = /mnt

安装数据库的时候先创建好个逻辑卷,安装目录为/mnt下。

请求全局锁,滚动日志

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

MariaDB [(none)]> flush logs;

记录二进制日志位置

mysql -e 'show master status' > mysql.txt

创建快照

[root@localhost ~]# lvcreate -L 1G -s -n mydata-snap -p r /dev/myvg/mydata 

解锁

MariaDB [(none)]> unlock tables;

挂载快照并且备份

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

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

卸载并删除快照

[root@localhost ~]# umount /media/

[root@localhost ~]# lvremove /dev/myvg/mysql-snap

Do you really want to remove active logical volume mysql-snap? [y/n]: y

Logical volume "mysql-snap" successfully removed