mysql备份还原

一、前言

数据对于一个公司来说有多重要这点不说大家也知道,数据还在,服务崩溃还可重建。为什么要备份?很简单,天灾人祸。比如硬件故障,软件故障,自然灾害,黑客攻击或自身误操作等因素。这时候备份,恢复数据就显得至关重要了。

二、基础概念

由于备份与恢复需要用到一些必要的知识,故在此先讲解下

2.1二进制日志

myql日志有多种,其中二进制日志文件对数据恢复有很大作用。那么什么是二进制日志?二进制日志记录了所有对数据库执行的更改操作,只有对数据能产生更改的操作才会记录下来。

日志格式:

blob.png

2.2备份类型

备份按照不同的划分方式可以划分为多种类型

如 :完全备份、增量备份、差异备份

完全备份:一个或多个数据库实例完全备份下来

增量备份:备份从完全备份或上次增量备份到现在改变了的数据部分

差异备份:备份从完全备份到现在改变了的数据

热备、温备、冷备

热备:在线备份,不需关闭mysql服务,业务不受影响

温备:在线备份,只支持读操作,

冷备:离线备份,需要关闭mysql服务

物理备份、逻辑备份

物理备份:直接复制数据文件进行备份

逻辑备份:从数据库中导出数据另存进行备份

2.3备份对象

数据、配置文件、二进制日志、存储过程、触发器

2.4备份方案

完全备份+增量备份+二进制日志

2.5数据恢复

有大概如下步骤

1、停止MySQL服务器;

2、记录服务器配置和文件权限;

3、将备份恢复到MySQL数据目录;此步骤依赖具体的备份工具;

4、改变配置和文件权限;

5、以限制方式启动MySQL服务器:比如通过网络访问;

[mysqld]

skip-networking 

socket=/tmp/mysql-recovery.sock

6、载入额外的逻辑备份;而检查和重放二进制日志;

7、检查已经还原的数据;

8、以完全访问模式重启服务器;

三、备份与恢复

3.1 mysqldump+二进制日志

备份

mysql> insert into classes(Class,NumOfStu) values('yue jiao','4');    #插入数据
Query OK, 1 row affected (0.03 sec)

mysql> show master logs;                        #查看二进制文件
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     16274 |
| master-bin.000002 |       120 |
+-------------------+-----------+
2 rows in set (0.00 sec)

[root@vm ~]# mysqldump --databases test -E -R --triggers --master-data=2 > /tmp/test_backup.mysql    #-E表示事件,-R表示存储过程,--triggers表示触发器
[root@vm ~]# ls /tmp/test_backup.mysql -l
-rw-r--r--. 1 root root 8057 Nov 19 19:36 /tmp/test_backup.mysql
[root@vm ~]# cp /etc/my.cnf /tmp/my.cnf.backup                            #备份配置文件
 [root@vm ~]# ls /tmp/my.cnf.backup -l
-rw-r--r--. 1 root root 1171 Nov 19 19:37 /tmp/my.cnf.backup
[root@vm ~]# ls -l /data/mysql/binlogs/ 
total 24
-rw-rw----. 1 mysql mysql 16274 Nov 19 17:51 master-bin.000001
-rw-rw----. 1 mysql mysql   667 Nov 19 19:41 master-bin.000002
-rw-rw----. 1 mysql mysql    76 Nov 19 17:51 master-bin.index
[root@vm ~]# cp /data/mysql/binlogs/master-bin.* /tmp/                #备份二进制日志文件
[root@vm ~]# ls /tmp/master-bin.*
/tmp/master-bin.000001  /tmp/master-bin.000002  /tmp/master-bin.index

还原

[root@vm ~]# cp /tmp/my.cnf.backup /etc/my.cnf        #配置文件还原
skip-networking
[root@vm ~]# service mysqld start
Starting MySQL...... SUCCESS!


[root@vm ~]# mysqlbinlog --start-position=120 --database=test /tmp/master-bin.000002 >/tmp/new_log.sql    #指定从什么position开始查找

mysql> set session sql_log_bin=0;                #临时关闭二进制日志功能,反正把还原过程也写进日志文件
Query OK, 0 rows affected (0.04 sec)
mysql> source /tmp/test_backup.mysql            #执行完全备份的数据文件

mysql> source /tmp/new_log.sql                  #执行增量还原

mysql> select * from classes where Class='mo jiao';
+---------+---------+----------+
| ClassID | Class   | NumOfStu |
+---------+---------+----------+
|      10 | mo jiao |        3 |
+---------+---------+----------+
1 row in set (0.02 sec)

mysql> set sql_log_bin=1;                    #打开二进制文件功能
Query OK, 0 rows affected (0.00 sec)

[root@vm ~]# sed -in /skip-networking/d /etc/my.cnf    #此操作使用户能远程访问数据库

[root@vm ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL...... SUCCESS!

3.2 lvm+二进制日志

这种备份要求mysql的data目录放在逻辑卷组中。

本实验中mysql的data目录放在逻辑卷组lv1中

mysql> show master logs;                #查看二进制文件及position
+-------------------+-----------+
| Log_name          | File_size |
+-------------------+-----------+
| master-bin.000001 |     16274 |
| master-bin.000002 |       690 |
| master-bin.000003 |       143 |
| master-bin.000004 |       143 |
| master-bin.000005 |       143 |
| master-bin.000006 |       143 |
| master-bin.000007 |       417 |
| master-bin.000008 |       143 |
| master-bin.000009 |       143 |
| master-bin.000010 |      2857 |
| master-bin.000011 |     10293 |
+-------------------+-----------+
11 rows in set (0.03 sec)

mysql> flush tables with read lock;        #全库锁表
Query OK, 0 rows affected (0.06 sec)

mysql> set sql_log_bin=0;            #关闭二进制日志功能
Query OK, 0 rows affected (0.01 sec)



[root@vm ~]# rm -rf /mydata/test/    #模拟破坏数据库操作

[root@vm ~]# lvcreate -s  -n backup -L 3G /dev/mapper/vg-lv1     #建立快照
  Logical volume "backup" created


[root@vm ~]# cp -r /data/mysql/binlogs /tmp/    #备份二进制日志文件


mysql> set sql_log_bin=1;                    #打开二进制日志功能
Query OK, 0 rows affected (0.18 sec)

mysql> unlock tables;                        #解锁
Query OK, 0 rows affected (0.00 sec)

mysql> insert into classes(Class,NumOfStu) values('yue jiao','4');    #插入数据
Query OK, 1 row affected (0.09 sec)

还原

[root@vm ~]# service mysqld stop        #停止数据库服务
Shutting down MySQL.. SUCCESS!

 [root@vm ~]# mount /dev/mapper/vg-backup /mnt/backup/        #挂载快照

[root@vm ~]# cp -r /mnt/backup/test /mydata/                #还原数据文件
[root@vm ~]# chown mysql.mysql /mydata/test –R            #更改文件属性
[root@vm ~]# mysqlbinlog --start-position=10293--database=test /tmp/master-bin.000011 >/tmp/new_log.sql     #提取从数据库备份那时的位置到最后的位置的二进制日志信息

[root@vm ~]# service mysqld start
Starting MySQL..... SUCCESS!

mysql> set sql_log_bin=0;            #关闭二进制日志功能
Query OK, 0 rows affected (0.00 sec)

mysql> source /tmp/new_log.sql        #增量还原

3.3 Xtrabackup 备份

使用innobakupex备份时,其会调用xtrabackup备份所有的InnoDB表,复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件,同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中。

在备份的同时,innobackupex还会在备份目录中创建如下文件:

(1)xtrabackup_checkpoints —— 备份类型(如完全或增量)、备份状态(如是否已经为prepared状态)和LSN(日志序列号)范围信息;

每个InnoDB页(通常为16k大小)都会包含一个日志序列号,即LSN。LSN是整个数据库系统的系统版本号,每个页面相关的LSN能够表明此页面最近是如何发生改变的。

(2)xtrabackup_binlog_info —— mysql服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置。

(3)xtrabackup_binlog_pos_innodb —— 二进制日志文件及用于InnoDB或XtraDB表的二进制日志文件的当前position。

(4)xtrabackup_binary —— 备份中用到的xtrabackup的可执行文件;

(5)backup-my.cnf —— 备份命令用到的配置选项信息;

在使用innobackupex进行备份时,还可以使用–no-timestamp选项来阻止命令自动创建一个以时间命名的目录;如此一来,innobackupex命令将会创建一个BACKUP-DIR目录来存储备份数据。

从http://www.percona.com/software/percona-xtrabackup/ 下载

语法

innobackupex [options]...

创建最小权限用户用于备份

mysql> grant reload,lock tables,replication client on *.* to 'xtrabackup'@'localhost' identified by 'xtrabackup';
Query OK, 0 rows affected (0.23 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

安装xtrabackup

[root@vm ~]# yum install -y percona-xtrabackup-2.2.3-4982.el6.x86_64.rpm

完全备份一个数据库

[root@vm ~]# innobackupex --user=xtrabackup --password=xtrabackup /tmp/backup/
MariaDB [test]>  insert into classes (Class,NumOfstu) values ('mou pai','50');        #插入数据
Query OK, 1 row affected (0.00 sec)
[root@vm backup]# innobackupex --incremental /tmp/backup/ --incremental-basedir=/tmp/backup/2015-11-27_06-11-01/    #增量备份一个数据库
MariaDB [test]>  insert into classes (Class,NumOfstu) values ('huo pai','51');        #继续插入数据
Query OK, 1 row affected (0.00 sec)
[root@vm backup]# innobackupex --incremental /tmp/backup/ --incremental-basedir=/tmp/backup/2015-11-27_06-14-53/    #第二次增量备份一个数据库

备份还原

[root@vm backup]# innobackupex --apply-log --redo-only /tmp/backup/2015-11-27_06-11-01/    #整理备份文件
[root@vm backup]# innobackupex --apply-log --redo-only /tmp/backup/2015-11-27_06-11-01/ --incremental-dir=/tmp/backup/2015-11-27_06-14-53/    #整理第一个备份文件到完全备份中
[root@vm backup]# innobackupex --apply-log --redo-only /tmp/backup/2015-11-27_06-11-01/ --incremental-dir=/tmp/backup/2015-11-27_06-16-19/    #整理第二个备份文件到完全备份中
[root@vm backup]# rm -rf /mydata/*
[root@vm backup]# innobackupex --copy-back --user=xtrabackup --password=xtrabackup /tmp/backup/2015-11-27_06-11-01/    #还原数据库
[root@vm backup]# chown mysql.mysl -R /mydata
[root@vm backup]# service mysqld start

blob.png

四、总结

备份和还原十分重要,这点就不啰嗦了,备份的时候注意不要让用户对数据库进行写操作,还原的时候需要注意关闭日志文件功能,反正还原的时候还原日志也写进二进制日志中。还需禁止数据库远程登录而进行读写操作。可以通过防火墙或者配置文件来实现。


原创文章,作者:成吉思汗,如若转载,请注明出处:http://www.178linux.com/9534

(0)
成吉思汗成吉思汗
上一篇 2015-11-28
下一篇 2015-12-03

相关推荐

  • 马哥网络班21期,第三周作业

    1、列出当前系统上所有已经登录的用户的用户名,注意:同一个用户登录多次,则只显示一次即可。     who | cut -d " " -f1 | sort -u     who:查看当前系统所有用户的会话     cut:剪切命令       cut…

    Linux干货 2016-07-26
  • 马哥教育网络班21期第8周课程练习

    1、请描述网桥、集线器、二层交换机、三层交换机、路由器的功能、使用场景与区别。 网桥也叫桥接器,是连接两个局域网的一种存储/转发设备,用来连接不同网段。 集线器称为“Hub”,主要功能是对接收到的信号进行再生整形放大,以扩大网络的传输距离,同时把所有节点集中在以它为中心的节点上。 二层交换机工作于OSI模型的第2层(数据链路层),可识别数据包中的MAC地址信…

    Linux干货 2016-09-19
  • LVM逻辑卷管理

    前言     通常情况下,在操作系统上新建了一个分区并在此分区创建文件系统后,该文件系统的大小就固定了。假如要增加此文件系统的大小,我们不得不添加一块硬盘并重新分区,创建文件系统,然后把原文件系统的数据完整复制过来。如果第二次分区时分配的空间太大,用不完又浪费该怎么办呢?重复此前的流程又将花费大量的时间,有没有更便捷的…

    Linux干货 2015-05-04
  • 正则表达式

    1、用正则表达式表示IP地址 扩展正表达式表示的: (\<([0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])\>.){3}\<([0-9]|[1-9][0-9]|1[0-9][0-9]|2[0-4][0-9]|25[0-5])\> 2、用正则表达式表示手机号 "^1[3578…

    Linux干货 2016-08-07
  • Mozart的剑(文本处理工具)——叁剑(grep、正则表达式)

    之前的句子 烟锁池塘柳 炮镇海城楼 因荷而得藕 有杏不需梅 认识于小林寒风的一篇小说《凤山遗梦》 grep grep是一种强大的文本搜索工具,它能使用正则表达式搜索文本,并把匹配的行打印出来。 作用:文本搜索工具,根据用户指定的“模式”对目标文本逐行进行匹配检查;打印匹配到的行。模式:由正则表达式字符及文本字符所编写的过滤条件 用法: grep [OPTIO…

    2017-08-03
  • NET25第8周作业

    1、写一个脚本,使用ping命令探测172.16.250.1-172.16.250.254之间的所有主机的在线状态;     在线的主机使用绿色显示;     不在线的主使用红色显示; #!/bin/bashtrap “echo ‘crtrl+c’;exi…

    Linux干货 2017-03-15

评论列表(1条)

  • stanley
    stanley 2015-11-30 22:15

    眼前一亮,好久没见这么标准的文章和写标签意识的人了