MySQL服务器学习笔记!(四) ——备份/恢复

作者: 魏延是反贼. 分类: 数据库, 系统·安全·调优

这是一步步从头学习MySQL的笔记历程,本人在学习之前只接触过少量SQL Server。

第一天历程:压榨自己的学习能力极限,每天学习新内容之MySQL服务器!(一)

第二天历程:压榨自己的学习能力极限,每天学习新内容之MySQL服务器!(二)

第三天历程:压榨自己的学习能力极限,每天学习新内容之MySQL服务器!(三)

第四天历程:压榨自己的学习能力极限,每天学习新内容之MySQL服务器!(四)

接下来每天都将更新~相信你通过笔记,一定能从零学会MySQL~

废话不多说,开始今天的学习压榨~!


 MySQL的备份和恢复

    我们知道如果把表数据放在Raid1上,一块硬盘坏了我们可以继续使用,那么是否就意味着我们可以不用备份了呢?当然不是,假设我们不小心执行了”drop database mydb”。是否可以备份,只要执行一个删除语句,看是否能将其还原回来,这才是备份。而Raid只是在硬件级别上作出的冗余备份。

    对于我们Mysql来说,最重要的就是数据本身,所以我们必须能确保数据本身不会出现任何问题。而当出现问题的时候,我们如何能将损失降到最低,尽可能的将数据找回来,此时,我们就需要用到备份。

    但是注意,备份永远解决不了数据的恢复,还原才能。很多人仅仅只是备份了,备份之后的数据,将备份的东西随手一扔,或者备份完成之后不检查自己的备份到底是否能够使用。所以备份解决不了任何问题,还原才能。而作为一个好的DBA,完全掌握备份和恢复,才是工作的重中之重。

 



数据库的日志文件:

    日志一般有两类:

    1.事物日志:实现为数据库服务器提供以下几种功能:

    →将随机IO事件转换为顺序IO(可以大大提高数据库性能),所有的写操作不是被立即放在磁盘上去的,而是先放到了在磁盘上的一段连续空间:事物空间。之后再由RDBMS控制文件内容将其同步进硬盘内。这样让硬盘在寻道的时候速度就非常快,所以理论上顺序IO比随机ION倍。

    →事件重放(撤销)的基础。能够保证数据库的事物在掉电之后依然完整,再下次开启之后依然能将数据同步到数据库中。

             

    2.事件日志:过去的一段时间内某个关键时刻发生了什么事情

    其实就是历史事件,只记录某点某时某刻做了什么事情。而事物日志则是记录了做这些事情怎么做的,里面操作了什么数据,操作了多少等等精确的内容。事务日志一般只记录写操作。

 

一:MySQL的日志分类:

错误日志 the error log

二进制日志 the binary log

普通的查询日志 the general guery log :通用查询,谁查询了什么的等

慢查日志 the slow query log :执行过程超出了某些特定时间长度的日志(比如记录超过1秒查询的那次任务)

中继日志 the relay log

默认情况下,我们的MySQL没有启动任何日志。而启动MySQL的那个脚本,帮我们开启了错误日志,错误日志的文件在data下,叫做*.err

 

二:错误日志:

    不仅仅记录了错误信息,还主要记录了MySQL进程在启动和停止的时候产生的信息,如果我们使用了主从复制的功能的话,在服务器上启动复制的进程的时候,进程信息也会记录在错误日志中。

 

    如何去启用error log呢?

    只需要在服务器的主配置文件里,定义:

    log-error = /var/log/mysqld.err

    (如果改到其他位置,请确保Mysql用户在那个位置上有写权限)

    如果没有定义这样日志的话,启动时的错误信息,都会输出到屏幕上来。所以我们的Mysql

    错误日志的命名格式:服务器名.err

    SHOW VARIABLES LIKE ‘%log%’,可以看到关于log的信息,

log_error:这个值就是我们错误日志文件的路径,我们也可以手动设置修改它的值。

       log_output:日志输出的保存格式,默认都是FILE,保存为一个文件,其实也可以保存到表中

 

三:二进制日志:

    有点类似于事物日志,它也能提供事物重放功能,但是它不能取代事物日志,它存在的主要目的,可以提供:

    1.增量备份机制

    2.基于时间点的恢复(可以手动控制,恢复到什么位置,什么时间恢复等)

    3.复制架构的基础(只要把二进制日志复制到另一台机子上,执行二进制日志中的内容,则)

    二进制日志的开启,会给服务器增加很大的压力,但是考虑到二进制日志的重要性,所以还是要开启的。

    二进制日志的定义:

    默认在数据文件目录data下叫做:xxx-bin.XXXXX

        xxx-bin.index则是一个索引文件,记录到底现在有多少二进制文件,以及正在写哪个。

        如果要自己定义的话,二进制文件路径:

        log-bin=/data/logs/binary/changelog

        二进制文件索引文件修改:

        log-bin-index=/data/logs/relay/binarylog.index

 

四:慢查日志/查询日志:

    实现定义那些执行时间超过了特定时长的查询事件。默认是10秒。默认是没有开启的。

       long_query_time =定义默认时间

       slow_query_log=是否开启慢查日志 ON OFF

       slow_query_log_file=定义慢查日志的路径及文件名

       general_log=是否开启查询日志ON/OFF

       general_log_file=查询日志的保存路径以及文件名

       log_output=将日志保存在哪

 

五:如何做日志滚动:

    为了让日志文件保持特定大小以免超出了我们服务器响应的能力。我们要做日志滚动

    flush logs命令:手动其滚动

    一般只对binary logs有用,它会将当前的二进制日志关闭,然后新生成一个新的二进制日志。对于其他的日志文件,我们完全可以手动去滚动,不需要这个命令。

 

六:二进制日志的删除方式:

   对于慢查日志和查询日志的文件,滚动过后不想要了可以直接删除,但是对于二进制文件来说,我们不能手动实用rm命令对其进行删除。而需要使用

       PURGE BINARY LOGS命令:删除一个二进制日志之前的内容

mysql> PURGE BINARY LOGS TO ‘mysql-bin.00007’;

包括00007之前的所有文件都自动给你清除。

mysql> PURGE BINARY LOGS BEFORE ‘2011-3-5 23:00:00’;

    删除这个时间点之前的所有二进制日志文件

       二进制日志文件默认是1G:

              max_binlog_size=可以定义默认大小。

 

 



日志的备份和恢复

    对于我们Mysql的属于一个在线的数据库来讲,我们在备份的时候,可能数据也在一直被写入,而如果我们强行备份的话,可能会导致备份中存入的数据的损坏。以及数据的不一致性,所以我们要采用不同的类型进行备份:

 

一:备份的类型:(从对象来分)

    逻辑备份(逻辑导出):备份的是数据库的逻辑结构,备份的一般都是各种逻辑语句。(比如CREATE DATABESESINSERT之类的)也叫logical export逻辑导出。逻辑备份的力度比较小,可以做到仅仅备份一个表甚至一个表中的某些行。

    物理备份(裸备份):复制对应数据库的数据文件。对于不同的数据库引擎,物理备份的方法也是不一样的,物理备份也叫裸备份。

 

    对于MyISAM这样的没有表空间的引擎来说,物理备份的速度要远快于逻辑备份。

    对于InnoDB这样的引擎来说,它直接创建了固定大小的文件,然后往里填充,物理备份就没有优势了。

 

    逻辑备份的优点:

    1.在备份速度上两种备份要取决于不同的存储引擎

    2.物理备份的还原速度非常快。但是物理备份的最小力度只能做到表。

    3.逻辑备份保存的结构通常都是纯ASCII的,所以我们可以使用文本处理工具来处理。

    4.逻辑备份有非常强的兼容性,而物理备份则对版本要求非常高

    5.逻辑备份也对保持数据的安全性有保证。

 

    逻辑备份的缺点:

    1.逻辑备份要对RDBMS产生额外的压力,而裸备份无压力

    2.逻辑备份的结果可能要比源文件更大。所以很多人都对备份的内容进行压缩

    3.逻辑备份可能会丢失浮点数的精度信息

 

二:备份的类型:(从数据集来分)

完全备份:备份所有数据

增量备份:只备份从上一次完全备份或增量备份以来所增加的数据(通过二进制日志来实现增量备份,依靠二进制文件)

差异备份:每次备份都备份从上一次完全备份以来所有新增的数据

       对于Mysql来讲,我们一般都是结合用,比如完全+增量1+增量2+增量3,完全+差异,不会单独使用一种备份的。

 

       DBA的小把戏:

在每一张表背后,在别人所有加入的字段后都加了一个timestamp。当有人改变数据的时候,则将timestamp修改为改变数据的时间,这样就可以在数据改变之后,备份一段时间的数据。

 

三:备份的类型:(根据服务器状态)

    冷备:干净的关闭数据库,直接备份,此时可以做各种备份。

    温备:服务不关闭,但是只允许查询操作,然后后台进行备份。

    热备:不影响读写,服务在线的备份。

 

    一般来讲非事务性的引擎都不支持热备份,比如MyISAM。但是热备通常是各个组织都非常需要的,所以非事物引擎都采用其他的概念,达到几乎是热备的概念,(比如LVM)。

    对于事务性引擎,热备就非常简单的实现。像InnoDB来说,如果想使用干净的逻辑的热备的话,只要使用官方所提供的一个工具,就能完全达到。

 

四:备份都备份什么?

    我们备份,一般备份以下几个部分:

    1.数据文件

    2.日志文件(比如事务日志,二进制日志)

    3.存储过程,存储函数,触发器(这类MySQL数据库)

    4.配置文件(十分重要,各个配置文件都要备份)

    5.用于实现数据库备份的脚本,数据库自身清理的Croutab等……

 

    由于备份的机制不同,所以我们建议千万不要在一个表中混合使用数据引擎。

 

 

五:常用的备份工具

    mysqldump:逻辑备份工具,可以用于所有的存储引擎,但是在任何一个存储引擎上只支持温备,如果使用INNODB的话可以支持热备,备份速度中速,还原速度非常非常慢,但是在实现还原的时候,具有很大的操作余地。具有很好的弹性。

    SELECT INTO OUTFILEMySQL的一个命令,是个逻辑备份,备份力度非常细,可以指定某个表的某些行,只支持到温备,备份速度慢,恢复速度慢,可操作性比较灵活。

    mk-parallel-backup:一个perl脚本,是一个人开发的,各种mk脚本,它的主要作用是模拟了mysqldump的功能,能够实现在多颗CPU上实现并行备份的能力,比mysqldump要快一些,备份速度中等,恢复速度中等。

    ibbackup:对于INNODB可以实现温备,备份和还原速度都很快,这个软件它的每服务器授权版本是5000美元……

    BACKUP command:这个基本上已经被废弃了。

    Filesystem(copy files):复制命令,只能实现冷备,物理备份。使用归档工具,cp命令,对其进行备份的,备份速度快,还原速度几乎最快,但是灵活度很低,可以跨系统,但是跨平台能力很差。

    Snapshot:基于快照(LVMZFS)的物理备份,速度非常快,几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用,所以备份速度比较快,恢复速度比较快,没有什么弹性空间,而且LVM的限制:不能对多个逻辑卷同一时间进行备份,所以数据文件和事务日志等各种文件必须放在同一个LVM上。而ZFS则非常好的可以在多逻辑卷之间备份。

    mysqlhostcopy:基本上属于冷备的范畴,而且只能用于MyISAM引擎,物理备份,速度比较快。

 

备份方式1mysqldump的使用:

    逻辑备份工具:使用SELECT语句将数据都转换出来,并将其保存成ASCII文件。

    备份使用格式:mysqldump db_name tbl_name

    备份的结果要使用输出重定向的方式,保存成一个文件比如:

    mysqldump db table > /root/tb1.sql

 还原的时候使用:

    mysql < /root/tb1.sql

    mysql> SOURCE /root/tb1.sql

它的常用选项有:

    –databases db1,db2,db3:一次备份多个数据库

    –all-databases:一次备份整个数据库上的所有数据库

    –events:备份create events创建事件

    –extended-insert:备份扩展的insert语句,恢复的时候使用一个insert语句进行插入,要比单个使用Insert速度快,默认是启用的。关闭使用 –skip-extended-insert

    –flush-logs:主要用于实现刷新日志到磁盘中去,为了保证一致性的(大多数情况下也是需要的。)

 

    –lock-all-tables:在备份之前对所有表进行加锁(这个是必须的!)

    –lock-tables tb1,tb2:锁定某些个表(一般很少用)

    –routines:同时备份存储过程存储函数

    –triggers:备份触发器

    –single-transaction:对于InnoDB的引擎提供一个一致性的视图,然后备份。这样的备份完全是一个热备,可以不锁表的

        –master-data=n:复制的,为了能够启用实现新的复制服务器。一般常用的是2.

        –no-data:偶尔有用,只备份数据库和表的定义

 

        –where “WHERE clause”只备份数据库中某些表或者某些行的数据

 

    1.备份表hellodb中的表t1

        mysqldump -uroot -p hellodb t1 > /root/t1.sql

        t1.sql中使用 “” 或者使用 “/*”都是注释信息

    2.恢复表hellodb中的表t1

        mysql -uroot -p hellodb < /root/t1.sql

    3.份份数据库hellodb

        mysqldump –databases hellpdb > /root/hellodb.sql

    4.恢复数据库hellodb

    mysql -uroot -p < /root/hellodb.sql

    真正备份的时候,要先锁定

    mysql> FLUSH TABLES;

    mysql> FLUSH TABLES WITH READ LOCK                 ##给加锁

    mysqldump –databases hellpdb > /root/hellodb.sql  ##快速备份

    mysql> UNLOCK TABLES;                              ##解锁

5.也可以自动让其加锁,并自动备份触发器:

    mysqldump –lock-all-tables –triggers –databases hellodb > /root/hellodb.sql

 

    备份出来之后完全可以将其压缩一下,比如用Gzip压缩

 

备份方式2SELECT INTO OUTFILE:命令

用于备份某些表中的某些数据,这里备份已经要保存在/tmp下,它没有关于表格式的定义,如果用它来恢复的话,必须要确定表和表格式都已经存在.

 

    备份格式:SELECT * INTO OUTFILE ‘/tmp/t1.txt’FROM t1;

   还原格式:LOAD DATA INFILE ‘/tmp/t1.txt’INTO TABLE t1;     

1.t2表中备份所有h开头的行

       mysql> SELECT * INTO OUTFILE ‘/tmp/t2.sql’FROM t2 WHERE CNAME LIKE ‘h%’

   2.还原:将表中的数据还原回来

mysql> LOAD DATA INFILE ‘/tmp/t2.sql’ INTO TABLE t3;

 

备份方式3:基于LVM来进行物理备份

    备份这类的前提:

    1.数据文件必须在独立的LV上。

    2.对数据库中的表施加读锁。

    3.立即对数据在的LV做快照;

    4.释放数据库的读锁;

    5.挂载快照卷,备份数据文件(如果是Innodb的引擎,一定要备份事物日志)

    6.删除快照卷

 

       对于Innodb数据引擎的备份还需要确保:

              Innodb的数据和事务日志在同一个LVM

sync_binlog=1

log_bin=ON

innodb_support_xa=ON

 

使用物理备份做一次全库备份:

    mysql> FLUSH TABLES WITH READ LOCK;               ##给全表加读锁

    mysql> \q                                         ##快速退出

    lvcreate -L 50M -n mysnap -s /dev/myvg/mydata     ##快速创建快照

    mysql                                             ##快速进入Mysql

    mysql> UNLOCK TABLES;                             ##快速解锁

    mysql> \q                                         ##退出

    mount /dev/myvg/mysnap /mnt                       ##挂载快照卷

    cd /mnt  

    tar -jcf /root/mysql.tar.bz2 hellodb/ ibdata1 iblogfile* mysql-bin.*  ##使用tar压缩归档备份

    umount /mnt                                       ##卸载快照卷

    lvremove –force /dev/myvg/mysnap                 ##删除快照卷

 

    假设内容已经都坏了,重新安装好mysql之后,将刚才的所有内容恢复回来。

先别启动mysqld

    cd /mydata/data                            ##进到目录内

    tar xf /root/mysql.tar.bz2 -C ./           ##解压归档文件

    service mysqld start                       ## 重启Mysqld

      

对于我们快照完之后,又新增的内容的恢复,我们就需要使用二进制日志文件来恢复了

    查看二进制日志文件:mysqlbinlog

    常用的选项:

–start-position:从哪个事件位置开始

       –end-position:从哪个事件结束

       –start-datetime:指定从哪个时间点开始

            –start-datatime=”2011-09-05 11:39:40”

       –stop-datetime:从哪个时间点结束

            –start-datatime=”2011-09-05 12:00:00”

 

    二进制日志文件内容:

    at :事件开始的位置(整个mysql二进制日志中的位置)

    年月日 时分秒 服务器id 下一个事件开始的位置  线程号  语句发生时间 执行结果 执行内容 时间戳

 

    SHOW BINLOG EVENTS,查看事件点

    查看此时正在被使用的事物日志文件:以及下一次事件开始的时候起始的位置

    SHOW MASTER STATUS;

 

当我们需要单独备份一个正在使用的事件日志的时候,

SHOW MASTER STATUS;      ##查看当前事件日志

FLUSH LOGS ;             ##手动滚动日志以让其写入新的日志

此时再进行将上一个日志文件进行备份

 

 

一次完整的物理备份+基于二进制的差异备份,以及完全恢复的过程:

    mysql> FLUSH TABLES WITH READ LOCK;             ##给全表加读锁

    mysql> SHOW MASTER STATUS;                      ##记录事件日志的日志文件和时间起始位置

    mysql> \q                                       ##快速退出

    lvcreate -L 50M -n mysnap -s -p r /dev/myvg/mydata   ##快速创建快照

    mysql                                           ##快速进入Mysql

    mysql> UNLOCK TABLES;                           ##快速解锁

    mysql> \q                                       ##退出

    mount /dev/myvg/mysnap /mnt                     ##挂载快照卷

    cd /mnt  

    tar -jcf /root/mysql.tar.bz2 hellodb/ ibdata1 iblogfile*     ##使用tar压缩归档备份

    umount /mnt                                     ##卸载快照卷

    lvremove –force /dev/myvg/mysnap               ##删除快照卷

    mysqlbinlog –start-position 301 mysql -bin.000006 > /root/mysql2.incr.sql     ##备份二进制文件

 

    此时如果数据库坏了,需要全盘恢复。

    chown -R mysql .                    ##重装mysql,先修改属主

    scripts/mysql_install_db –datadir=/mydata/data/ –user=mysql  ##重装mysql,指定我们的目录

    chown -R root .                     ##重新恢复属主

 

    cd /mydata/data/                    ##进入工作目录

    tar xf /root/mysql2.tar.bz2 -C .    ##将备份解压至工作目录

    mysql < /root/mysql2.incr.sql   ##恢复新增的二进制日志到数据库中

 

标签:, ,

返回正文

(1)条评论

  • 2B

    |

    东西很好。谢谢搂主。

    回复

您的留言是我最大的支持!