Linux运维 第四阶段 (六)MySQL备份&&还原(mysqldump、LV’s snapshot、xtrabackup)

  1. 云栖社区>
  2. 博客>
  3. 正文

Linux运维 第四阶段 (六)MySQL备份&&还原(mysqldump、LV’s snapshot、xtrabackup)

技术小阿哥 2017-11-26 15:13:00 浏览674
展开阅读全文

Linux运维 第四阶段 (六)MySQL备份&&还原(mysqldumpLV’s snapshotxtrabackup

一、相关概念

备份:副本,mysql-database备份不同于RAID(RAID是保证硬件损坏而不会业务终止)

备份内容:数据、配置文件、二进制日志、事务日志

1、备份类型:

》热备份、温备份、冷备份

热备份:读写不受影响,复杂度高,InnoDB(xtrabackup,mysqldump),lvm快照功能可实现几乎热备;

温备份:仅可执行读操作,MyISAM借助LVM可实现温备;

冷备份:离线操作,读写操作均终止。

》物理备份、逻辑备份

物理备份:复制数据文件,速度快,mysqlhotcopy;

逻辑备份:将数据导出至文本文件中,需mysql进程参与,速度慢,丢失浮点数精度,方便使用文本处理工具对其直接处理,可移植性强,跨版本;

》完全备份full、增量备份increment、差异备份difference

full:备份全部数据;

increment:仅备份上次完全备份或增量备份以后变化的数据;

difference:仅备份上次完全备份以来变化的数据。

2、备份工具:

mysqldump:逻辑备份工具,MyISAM温备,InnoDB热备,备份出的数据比原数据占用空间大,但压缩后可大大节省空间,不适合对大数据库做完全备份,速度慢且消耗系统资源,使浮点数丢失精度;

mysqlhotcopy:It’s a perl script,物理备份工具;

cp:冷备份;

lv快照功能:逻辑卷的快照功能,可实现几乎热备;

ibbackup:商业工具;

xtrabackup:用于事务存储引擎,opensource;

SELECT INTO  OUTFILE:通常做单张表的备份。

3、还原:要经常进行还原演练及测试备份文件是否可用。

4、innodb_support_xa  = 1  binloginnodbredo log保持一致性

Sync_binlog =  1  事务安全性增强,但会消耗性能

 

 

二、mysqldump的使用:

1、语法:

#mysqldump [options] [db_name [tbl_name ...]]

options:

--all-databases 所有库

--databases 指定库

--lock-all-tables  备份前先锁定所有表

--flush-logs 滚动日志

--master-data=0|1|2   0不记录二进制日志文件及事件位置,1以CHANGE MASTER TO的方式记录位置,可用于恢复后直接启动从服务器,2以CHANGE MASTER TO的方式记录位置,默认被注释

--single-transaction  若指定库中的表类型为InnoDB存储类型,可用此项启动热备,注意此项不能与—lock-all-tables共用

--events 事件

--routines 存储过程、存储函数

--triggers 触发器

 

例:

>LOCK TABLES;

>FLUSH  TABLES; (备份前应锁定表,也可将此两句合并为>FLUSH  TABLES  WITH READ LOCK;备份完成后记得解锁>UNLOCK;)

>FLUSH LOGS;  (滚动日志)

注:以上三句也可用选项替代,--lock-all-tables --flush-logs,使用选项则不必考虑解锁。

#mysqldump -uroot  -p  jiaowu >  /root/jiaowu.sql   (备份库jiaowu,此种方式备份在还原时必须先指定库或创建此库才能还原)

#mysql  -uroot  -p  test <  /root/jiaowu.sql  (将备份的库中的所有表还原到test库中;或创建新库>CREATE  DATABASE jiaowu;还原#mysql –uroot  -p jiaowu <  /root/jiaowu.sql)

#mysqldump -uroot  -p  jiaowu stu  >  /root/stu.sql (将库中的表stu备份,还原时必须指定库)

#mysql -uroot  -p  jiaowu <  /root/stu.sql  (将表还原至jiaowu库中)

#mysqldump -uroot  -p  --databases mysql  jiaowu  > /root/mj.sql  (将两个库备份)

#mysql -uroot  -p  < /root/mj.sql  (还原时不需指定库,直接还原)

>UNLOCK; (在mysql命令行下解锁)

 

 

2、实例:

此例,适用于小型数据库备份还原。

每周完全(mysqldump)+每日增量(复制二进制日志文件)+即时点还原

 

完全备份如下:

#msyqldump -uroot  -p  --lock-all-tables  --flush-logs --master-data=2  --all-databases  > /backup/alldatabases.`date +%F`.sql

#mysql -uroot  -p  -e  ‘SHOW  MASTER STATUS;’  >  /backup/all.`date  +%F`.info (记录当前正在使用的二进制日志,目的增量备份时,知道从第几个二进制日志开始备份。此步也可没有,因为上句已有--master-data选项,可直接打开备份的.sql文件查看位置,不过一般生产中完全备份后的文件比较大,打开浪费系统资源)

 

每天增量备份如下:

#mysqladmin -uroot  -p  flush-logs

#mysql -uroot  -p  -e  ‘SHOW  MASTER STATUS;’ >  /backup/increment.`date+%F`.info 

#mysqlbinlog mysql-bin.0000XX  >  /backup/mon-incre.`date  +%F`.sql (比较完全备份后记录的二进制日志all.XXXX-XX-XX.info与当前使用的二进制日志之间生成了几个二进制日志文件,此步就备份几个,若需要备份的文件>1个,跨文件,用时间点导出,例如:#mysqlbinlog  --start-datetime=’2015-08-12  14:16:43’ mysql-bin.000010           mysql-bin.000012  > /backup/mon-incre.`date  +%F`.sql表示从一个时间点一直到指定文件的结束)

 

还原如下:

>SELECT @@sql_log_bin;

>SET sql_log_bin=0;  (临时禁止往二进制日志里写入)

>\.  /backup/alldatabases.XXXX-XX-XX.sql  (还原完全备份的,也可用>source  /backup/*)

>\.  /backup/mon-incre1.XXXX-XX-XX.sql  (还原每天增量备份的)

#mysqlbinlog mysql-bin.0000XX  >  /tmp/temp.sql (将最后一次增量备份之后到现在生成的二进制日志导出,同样若有多个文件,选项用时间点指定)

#mysql -u  root  -p <  /tmp/temp.sql  (即时点还原)

>SET sql_log_bin=1;

到此数据库已还原到最新状态。

 

 

三、SELECT  INTO  OUTFILE:通常做单张表的备份

1、语法:

备份:>SELECT  col1,col2  INTO OUTFILE  ‘/path/to/file’ FROM  tb_name [WHERE  condition];

还原:>LOAD  DATA  INFILE ‘/path/to/file’  INTO  TABLE tb_name;  (还原时要先把表创建好才能还原)

 

例:>SELECT  *  INTO OUTFILE  ‘/tmp/tutors.txt’  FROM tutors  WHERE  Age>20;

>DROP TABLE  tutors;

>\! less  /tmp/tutors.txt

>CREATE TABLE  tutors  (

SID INT  UNSIGNED  NOT NULL  AUTO_INCREMENT  PRIMARY KEY,

Name CHAR(20));

>DESC tutors;

>SELECT *  FROM  tutors;

>LOAD DATA  INFILE  ‘/tmp/tutors/txt’  INTO TABLE  tutors;

>SELECT *  FROM  tutors;

 

 

四、用LV的snapshot实现几乎热备:

 

前提:

数据文件要在逻辑卷上;

此逻辑卷所在卷组必须有足够空间使用快照卷;

数据文件和事务日志要在同一个卷上。

 

实例:完全+增量+即时点还原

 

完全备份如下:

1、>FLUSH  TABLES WITH  READ  LOCK; 

>FLUSH LOGS;  (锁表并滚动二进制日志)

2、#mysql  -uroot -p  -e  ‘SHOW  MASTER  STATUS;’ > /backup/master.`date +%F`.info  (导出正在使用的二进制日志状态)

3、#lvcreate  -L 50M  -s  -p r  -n  mydata_snap /dev/myvg/mydata  (创建快照卷,其中-s,--snapshot;-p,--permission r|rw)

4、>UNLOCK  TABLES;

5、#mount  /dev/myvg/mydata_snap  /mnt

#cd /mnt;ls

#cp -a  ./*  /backup/fullbackup.`date  +%F`/

#umount /mnt

#lvremove --force  /dev/myvg/mydata_snap

 

增量备份如下:

#mysqladmin flush-logs

#mysql -u  root  -p -e  ‘SHOW  MASTER STATUS;’  >  /backup/mon.incre.`date  +%F`.info

#cat /backup/master.*.info

#cat  /backup/mon.incre.*.info  (根据这两个文件,判断需要备份几个二进制日志文件,并且时间点从什么时候开始#mysql  mysql-bin.000010)

#mysqlbinlog  --start-datetime=’2015-08-14 16:59:00’mysql-bin.000010  mysql-bin.000018  > /backup/mon.incre.`date  +%F`.sql

 

还原如下:

#service mysqld  stop

#cp -a  /backup/fullbackup.*/*  /mydata/data/

#service mysqld  start

>SET sql_log_bin=0;

>source /backup/mon.incre.*.sql

#mysqlbinlog mysql-bin.0000XX  >  /tmp/temp.sql (将最后一次增量备份之后到现在生成的二进制日志导出,同样若有多个文件,用时间点指定)

>source /tmp/temp.sql  (即时点还原)

>SET  sql_log_bin=1;

 

 

五、xtrabackup的使用:

1、属percona公司,开源工具,xtra引擎(innodb的增强);

Innobackupex(脚本,自动封装了一些工具,使得使用简化,隐藏了不同版本的细节);

备份时,需要mysql在线,还原时则不必;

还原后,要将数据所在目录改属主属组;

 

#innobackupex --help

#innobackupex --user=USERNAME /path/to/backup-dir  (完全备份)

#innobackupex --incremental /path/to/incre1-backup-dir --incremental-basedir=/path/to/backup-dir  (第一次增量备份,注意两个目录的指向,前一个是第一次增量备份目录,后一个是指向完全备份的目录)

#innobackupex --incremental /path/to/incre2-backup-dir --incremental-basedir=/path/to/incre1-backup-dir  (第二次增量备份,注意两个目录指向,前一个是本次增量备份目录,后一个指向第一次增量备份目录)

#innobackupex --apply-log  --redo-only  /path/to/backup-dir  (准备完全备份,只有准备的数据才能用于还原)

#innobackupex  --apply-log --redo-only  /path/to/backup-dir--incremental-dir=/path/to/incre1-backup-dir (准备第一次增量备份,注意指向完全备份)

#innobackupex --apply-log  --redo-only  /path/to/backup-dir  --incremental-dir=/path/to/incre2-backup-dir  (准备第二次增量备份,注意选项使用的是dir,两次增量备份的准备阶段都将数据合并到准备的完全备份上,所以还原只需还原准备的完全备份)

#rm -rf  /mydata/data/*  (清空数据目录再还原,否则会报错,Error:Original data directory '/mydata/data' is not empty!)

#innobackupex --copy-back /path/to/backup-dir  (若之后数据有变化,通过二进制日志的即时点还原)

#chown -R  mysql.mysql  /mydata/data (还原后的数据注意改属主属组)

 

 

2、实例:

xtrabackup full +  xtrabackup  increment +  binlog

 

完全备份与增量备份:

#innobackupex --user=root  /backup  (完全备份)

#innobackupex --incremental  /backup --incremental-basedir=/backup/2015-08-15_18-37-22/  (第一次增量备份)

#innobackupex --incremental  /backup --incremental-basedir=/backup/2015-08-15_18-39-35/  (第二次增量备份)

#innobackupex --apply-log  --redo-only  /backup/2015-08-15_18-37-22/  (准备完全备份)

#innobackupex --apply-log  --redo-only  /backup/2015-08-15_18-37-22  --incremental-dir=/backup/2015-08-15_18-39-35/  (准备第一次增量备份)

#innobackupex --apply-log  --redo-only  /backup/2015-08-15_18-37-22  --incremental-dir=/backup/2015-08-15_18-41-32/  (准备第二次增量备份)

 

还原:

#service mysqld  stop

#cp /mydata/data/mysql-bin.* /root/  (将所有二进制日志导出)

#rm -rf  /mydata/data/*

#innobackupex --copy-back /backup/2015-08-15_18-37-22/

#chown -R  mysql.mysql  /mydata/data/*

#ls /root/ 

#cat /backup/2015-08-15_18-41-32/xtrabackup_binlog_info  (在最后一次增量备份目录中查看最后在哪一个二进制日志,与当前生成的二进制日志比较,看需要即时点还原几个二进制日志)

#mysqlbinlog /root/mysql-bin.000015  (记录最后一次增量备份日志的开始时间点)

#mysqlbinlog --start-date=’2015-08-13  19:32:11’  mysql-bin.000015  mysql-bin.000023  > /root/backup/now.sql  (导出最后一次增量备份到现在生成的二进制日志)

#mysql -uroot  -p  < /root/backup/now.sql  (即时点还原)

 

3innobackupex导入导出单张表:

前提:启用innodb_file_per_table(导出表的server-side可只打开此项)、innodb_expand_import(导入表的server-side两项都需开启)

导出表:

#innobackupex  --apply-log --export  /path/to/backup  (在完全备份的准备阶段用选项—export将表导出,此步会为每个innoDB的表空间创建一个以.exp结尾的文件,这些.exp文件可用来导入到其它server

导入表:

>CREATE TABLE  mytable  (

id  INT  UNSIGNED AUTO_INCREMENT  PRIMARY  KEY,

name char(20))  ENGINE=InnoDB;  (要事先创建表结构并且要与原表一致,存储引擎要为InnoDB

>ALTER TABLE  mydatabase.mytable  DISCARD TABLESPACE;  (删除表空间文件)

>ALTER TABLE  mydatabase.mytable  IMPORT TABLESPACE;  (在执行此操作前,将导出表server-side上的mytable.ibdmytable.exp两个文件复制到导入表server-side的数据目录中,然后再执行这条语句)。

 

 

 本文转自 chaijowin 51CTO博客,原文链接:http://blog.51cto.com/jowin/1685430,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
技术小阿哥
+ 关注