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

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

一、相关概念

备份：副本，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  binlog与innodb的redo 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  （即时点还原）

 

3、innobackupex导入导出单张表：

前提：启用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.ibd和mytable.exp两个文件复制到导入表server-side的数据目录中，然后再执行这条语句）。

 

 

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