— 备份基础知识;
1.最重要的备份原因:
1.完整系统恢复:如果系统发生故障,则拥有系统的备份至关重要,因为可以恢复系统;实施怎样的备份和恢复策略取决于被恢复数据要达到的完整性和时效性;
2.审计功能:对于某些系统及关联的流程,可能需要审计或分析独立于主生产环境的环境中的数据;可以使用备份创建这样一个独立的环境;
3.常见DBA任务:在需要执行常见DBA任务(例如将数据从一个系统传输到另一个系统,根据特定的生产服务器状态创建开发服务器,或者将系统的特定部分恢复到用户出错前的某个状态)时使用备份;
2.备份类型:
1.热备份:这些动态备份在读取或修改数据的过程中进行,很少中断或者不中断传输或处理数据的功能;使用热备份时,系统仍可供读取和修改数据的操作访问;
2.冷备份:这些备份在用户不能访问数据时进行,因此无法读取或修改数据;这些脱机备份会阻止执行任何使用数据的活动,这些类型的备份不会干扰正常运行的系统的性能;但是,对于某些应用程序,会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据;
3.温备份:这些备份在读取数据时进行,但在多数情况下,在进行备份时不能修改数据本身;这种中途备份类型的优点是不必完全锁定最终用户,但是,其不足之处在于无法在进行备份时修改数据集,这可能使这种类型的备份不适用于某些应用程序;在备份过程中无法修改数据可能产生性能问题;
3.磁盘:
1.可以使用复制或RAID镜像之类流程,或者使用DRBD之类的外部应用程序,将数据直接备份到其他磁盘;
2.这些技术提供实时(或几乎实时)备份,以及快速恢复数据的方法;
4.二进制日志:
1.二进制日志记录对数据的修改;因此,二进制日志对恢复自上次完整备份以来发生的事件很有用;
2.备份二进制日志的优点是其中包含了各个时间对数据所做的所有更改的记录,而不是数据的快照;
3.可以按顺序创建多个二进制日志备份,根据修改的数据量以及完成完整备份的频率,决定要在备份之间创建的二进制日志备份的数量;
4.二进制日志的不足之处是必须恢复自序列中最后一次完整备份以来创建的所有按顺序的二进制日志;此外,从系统故障中恢复的速度可能会很慢,具体取决于必须恢复的二进制日志的数量;
5.逻辑/文本备份:
1.可以使用mysqldump进行完整数据转储,这些数据转储基于特定的时间点,但是是所有备份副本中速度最慢的;
2.使用mysqldump的优点是所创建的文件是简单的SQL脚本,其中包含可在MySQL服务器上运行的语句;
3.不足之处在于mysqldump会在转储过程中锁定表,这会阻止用户在备份过程中读写文件;
— 使用MySQL进行备份;
MySQL备份可以是下列备份之一:
1.逻辑备份:逻辑备份会产生一个文本文件,其中包含重构数据库的SQL语句;
2.物理备份:这是MySQL数据库文件的二进制副本;
3.基于快照的备份;
4.基于复制的备份;
5.增量备份:通过刷新MySQL二进制日志创建的备份;
— 测验;
b
— 逻辑(文本)备份;
1.逻辑备份:
1.将数据库和表的内容转换为SQL语句;
2.可移植:这些SQL语句包含重建MySQL数据库和表所需的全部信息,可以使用该文本文件在运行不同体系结构的其他主机上重新装入数据库;
3.要求MySQL服务器在备份期间运行:因为服务器在创建文件时要读取备份的表的结构和内容,然后将结构和数据转换为SQL语句;
4.可以备份本地和远程MySQL服务器:其他类型的备份(原始备份)只能在本地MySQL服务器上执行;
5.通常比原始(二进制)备份的速度慢:
1.因为MySQL服务器必须读取表并解释其内容,然后,将表内容转换成磁盘文件,或者将语句发送给客户机程序,由客户机程序将语句写出;
2.在恢复过程中,逻辑备份速度比原始备份慢;这是因为恢复的方法执行单个CREATE和INSERT语句来重新创建每个备份表和行;
2.逻辑备份文件的大小可能会超过备份的数据库大小;
— 物理(原始或二进制)备份;
1.物理备份:
1.生成数据库文件的完整二进制副本,这些副本以完全相同的格式保留数据库;可以使用标准命令,如tar/cp/cpio/rsync/xcopy;
2.必须恢复到同一个数据库引擎:因为原始备份是数据库文件位的完整表现形式;
3.可以在不同的计算机体系结构间恢复;
4.比逻辑备份和恢复的速度快:因为该过程是简单的文件复制,不需要了解文件的内部结构;
2.数据库文件在备份期间不能有更改:
1.实现这一点的方法取决于存储引擎;
2.对于InnoDB:需要关闭MySQL服务器;
3.对于MyISAM:锁定表以允许读取,但不允许更改;
4.可以使用快照,复制或专有方法:最大限度地减小对MySQL和应用程序的影响;
— 基于快照的备份;
1.创建数据的时间点“副本”;
2.提供一个逻辑上冻结的文件系统版本,可从中进行MySQL备份;
3.显著减少数据库和应用程序不可用的时间原始备份通常针对快照副本进行;
4.外部快照功能:
1.基于快照的备份使用MySQL外部的快照功能;
2.例如,如果MySQL数据库和二进制日志在具有相应文件系统的LVM2逻辑卷上,则可创建快照副本;
3.基于快照的备份最适合InnoDB之类的事务引擎,可以为InnoDB表执行热备份;对于其他引擎,可以执行温备份;
5.可伸缩:
1.快照备份是可伸缩的,因为执行快照所需的时间不会随数据库大小的增长而增加;
2.事实上,从应用程序的角度来看,备份期限几乎是零,但是,创建快照后,基于快照的备份几乎总是包括一个原始备份;
— 基于复制的备份;
1.MySQL复制可用于备份:
1.主服务器用于生产应用程序;
2.从属服务器用于备份目的;
2.这样可避免影响生产应用程序;
3.从属服务器的备份为逻辑备份或原始备份;
4.较高的成本:必须有另一台服务器和存储设备用于存储数据库的副本;
5.基于异步复制:
1.从属服务器相对于主服务器可能会有延迟;
2.如果在从属服务器读取二进制日志之前未清除二进制日志,这是可接受的;
— 测验;
a
— 二进制日志记录和增量备份;
1.在会话级别控制二进制日志记录,必须拥有SUPER特权:SET SQL_LOG_BIN = {0|1|ON|OFF};
2.执行逻辑备份或原始备份时刷新二进制日志:将二进制日志同步到备份;
3.逻辑备份和原始备份是完整备份:将备份所有表的所有行;
4.要执行增量备份,需复制二进制日志;
5.二进制日志可用于细粒度级恢复:可以标识导致损坏的事务并在恢复过程中跳过这些事务;
— 备份工具:概述;
1.逻辑备份的SQL语句;
2.执行SQL语句(用于锁定)与操作系统命令(用于生成二进制副本)组合的原始备份;
3.MySQL的其他原始备份工具:
1.MySQL Enterprise Backup:执行MySQL数据库热备份操作;该产品的设计目的就是为了高效且可靠地备份由InnoDB存储引擎创建的表,为完整起见,该产品还能备份其他存储引擎中的表;
2.mysqldump:该实用程序执行逻辑备份,可与任何数据库引擎一起使用;可以使用crontab(在Linux和UNIX中)和Windows任务调度程序(在Windows中)自动运行该实用程序;mysqldump没有任何跟踪或报告工具;
3.mysqlhotcopy:该实用程序执行原始备份,仅用于使用MyISAM或ARCHIVE数据库引擎的数据库;名称暗指mysqlhotcopy执行“热”备份,即不中断数据库可用性,但是,由于已对数据库进行了读取锁定,无法在备份过程中更改,因此最好将其描述为“温”备份;
4.第三方工具;
1.本课程主要是讲Oracle商业和社区工具;
2.补充Percona的Xtrabackup;
— MySQL Enterprise Backup;
1.热备份:
1.热备份是在数据库运行期间执行的,这种类型的备份不阻止正常的数据库操作,甚至能捕获备份进行期间发生的更改;
2.mysqlbackup是MySQL Enterprise Backup产品的基本命令行工具,对于InnoDB表,此工具可执行热备份操作;
2.温备份:
1.对于非InnoDB存储引擎,MySQL Enterprise Backup执行温备份;运行非InnoDB备份时,可以读取数据库表,但不能修改数据库;
3.增量备份:
1.备份自上一次备份以来有变化的数据;
2.主要用于InnoDB表或者只读或很少更新的非InnoDB表;
4.单文件备份:因为可以将单文件备份传输给其他进程(如磁带备份或scp之类的命令),因此可使用此技术将备份放在其他存储设备或服务器上,不会在原始数据库服务器上产生显著的存储开销;
— mysqlbackup;
使用mysqlbackup备份的原始文件
1.InnoDB数据:
1.ibdata*文件:共享表空间文件;
2..ibd文件:基于每个表的数据文件;
3.ib_logfile*文件:日志文件,从ib_logfile*文件提取的数据(代表在备份期间发生的更改的重做日志信息),存储在新备份文件ibbackup_logfile中;
2.要包括的数据目录中的所有文件:
1..opt文件:数据库配置信息;
2..TRG文件:触发器参数;
3..MYD文件:MyISAM数据文件;
4..MYI文件:MyISAM索引文件;
5..FRM文件:表数据字典文件;
6.TIPS:默认情况下,mysqlbackup备份数据目录中的所有文件,如果指定–only-known-file-types选项,则备份仅包括具有MySQL公认扩展名的其他文件;
3.mysqlbackup是一种易于使用的工具,适用于所有备份和恢复操作:
1.可以使用mysqlbackup联机备份InnoDB表以及生成对应于与InnoDB备份相同的binlog位置的MyISAM表的快照;
2.除了创建备份以外,mysqlbackup还可以将备份数据打包和解包,将在备份操作过程中对InnoDB表所做的任何更改应用于备份数据,以及将数据/索引和日志文件复制回其原始位置;
4.备份过程:
1.mysqlbackup打开到要执行备份的MySQL服务器的连接;
2.然后,mysqlbackup对InnoDB表执行联机备份;
3.当mysqlbackup运行几乎完成时,执行SQL命令FLUSH TABLES WITH READ LOCK,然后将非InnoDB文件(如MyISAM表和.frm文件)复制到备份目录;
1.如果此时未在数据库中长时间运行SELECT或其他查询,则MyISAM表很小,锁定阶段仅持续几秒钟;
2.否则,包括InnoDB类型表在内的整个数据库都会锁定,直到在备份之前开始的所有长时间查询完成;
4.mysqlbackup运行完成,并对表执行UNLOCK解锁;
5.基本用法:mysqlbackup -u
1.backup:执行备份初始阶段;
2.backup-and-apply-log:包括备份的初始阶段以及第二个阶段,即将InnoDB表放到最新的备份中,其中包括在备份运行期间对数据所做的任何更改;
— 使用mysqlbackup恢复备份;
基本用法:mysqlbackup –backup-dir=
1.
2.copy-back:指示mysqlbackup执行恢复操作;
1.恢复操作将
2.使用copy-back选项必须先关闭数据库服务器,然后才能使用mysqlbackup与copy-back选项;使用此
3.选项时,可将数据文件,日志及其他备份文件从备份目录复制回到其原始位置,并对其执行任何必需的后期处理;
4.在copy-back过程中,mysqlbackup无法从服务器查询其设置,因此从标准配置文件中读取datadir之类选项;
5.如果要恢复到不同的服务器,则可使用–defaults-file选项提供非标准默认设置文件;
补充:
— 使用mysqlbackup备份恢复:
1.完全备份还原:
1.备份数据库:
1.创建目录:mkdir -p /mysqlbackup; chown -Rf mysql:mysql /mysqlbackup;
2.创建备份:mysqlbackup –user=root –password= –with-timestamp –backup-dir=/mysqlbackup/ backup-and-apply-log;
2.还原数据库:
1.要把mysql服务关掉;
2.使用的备份必须是数据一致性的,即指定了–bakcup-and-apply-log参数的;
3.使用copy-back选项,这个操作会拷贝表/索引/元数据和其它恢复需要的文件恢复到原来的位置(定义在参数文件中);
4.还原数据库:mysqlbackup –defaults-file=/etc/my.cnf –backup-dir=/mysqlbackup/2015-08-25_17-20-34/ copy-back;
5.还原之后检查文件的权限(chown -Rf mysql:mysql data/ logs/),并开启mysql服务即可;
6.如果是需要还原到不同的目录的话,只需要修改–defaults-file参数指定的my.cnf文件即可;
2.增量备份还原:
1.增量备份分两种情况:
1.–incremental-base:使用这种方式,不需要知道两次备份的LSN(Log Sequence Number),只需要指定上一次备份(完全备份或者差异备份)的目录即可,mysqlbackup工具会从metadata文件中找到备份开始的位置.缺点是必须指定一系列目录的名称,可以使用应编码或者用shell实现,用–with-timestamp时需要制定规则;
2.–start-lsn:使用这种方式,就必须记录上次备份的LSN号,不需要关心上次备份的目录.缺点是需要知道上次备份的后的LSN,可以通过shell去获取,备份的目录可以使用–with-timestamp选项;
2.–incremental-base方式增量备份:mysqlbackup –defaults-file=/usr/local/mysql/my.cnf –incremental –incremental-base=dir:/mysqlbackup/2012-07-15_17-31-55/ –incremental-backup-dir=/mysqlbackup/incremental/sunday –with-timestamp backup;
3.–start-lsn方式增量备份:
1.查看上次备份结束的LSN号码:/path/meta/backup_variables.txt文件中end_lsn表示;
2.命令:mysqlbackup –defaults-file=/usr/local/mysql/my.cnf –incremental –start-lsn=xxxxx –with-timestamp –incremental-backup-dir=/mysqlbackup/incremental/sunday/ backup;
3.压缩备份功能:
1.数据压缩的功能只适用于InnoDb引擎的表;
2.压缩选项–compress只能用于完全备份,不能用于增量备份;
3.执行完全备份的命令:>mysqlbackup –compress –backup-dir=/mysqlbackup –with-timestamp backup;
然后执行apply-log选项;
4.部分备份(支持三种部分备份):
1.Leaving out files that are present in the MySQL data directory but not actually part of the MySQL instance. This operation involves the –only-known-file-types option;
2.Including certain InnoDB tables but not others. This operation involves the –include, –only-innodb, and –only-innodb-with-frm options;
3.Including certain database directories but not others. This operation involves the –databases and –databases-list-file options;
5.单文件备份:
1.可以通过指定backup-to-image参数来创建单文件备份,所有的源数据文件都必须在同一个目录下,所以尽量配置datadir, innodb_log_group_home_dir, and innodb_data_home_dir参数相同.
2.备份单一文件到绝对目录:mysqlbackup –backup-image=/backups/sales.mbi –backup-dir=/backup-tmp backup-to-image;
6.备份内存中数据:
1.mysqlbackup命令的–exec-when-locked选项可以在备份结束之前指定命令或者参数执行,而此时数据库是锁住的.这个命令可以创建或者拷贝一个附加的文件到备份目录;
2.可以用此选项来调用mysqldump命令备份MEMORY类型的表;
— mysqlbackup单文件备份;
1.基本用法:
mysqlbackup -u
2.其他情形
1.标准输出:
… –backup-dir=
2.将现有的备份目录转换为单个文件:
… –backup-dir=
— 恢复mysqlbackup单个文件备份;
1.提取选择的文件:
mysqlbackup -u
2.其他情形:
1.列出内容:
… –backup-image=
2.将现有的备份目录转换为单个文件:
… –backup-image=
1.–src-entry:确定要从单文件备份中提取的文件或目录;
2.–dst-entry:与单文件备份配合使用,将单个文件或目录提取到用户指定的路径;
— 测验;
d
— mysqlhotcopy;
1.Perl脚本:
1.备份MyISAM和ARCHIVE表;
2.使用FLUSH TABLES,LOCK TABLES以及cp或scp可以进行数据库备份;
3.在数据库目录所在的同一台计算机上运行:以便在表锁定期间复制表文件;
4.仅限Unix;
5.MySQL服务器必须处于运行状态:以便连接到服务器;
6.mysqlhotcopy的操作速度很快,因为它直接复制表文件,而不是通过网络备份表文件;
2.基本用法:mysqlhotcopy -u
3.选项:
1.–flush-log:在所有表都锁定后刷新日志;
2.–record_log_pos=db_name.tbl_name:在指定的数据库db_name和表tbl_name中记录主从服务器状态;
— 原始InnoDB备份;
1.备份过程:
1.在复制操作期间停止服务器;
2.验证服务器是否正常关闭,没有出错;
3.生成每个组件的副本:
1.每个InnoDB表一个.frm文件;
2.表空间文件;
1.系统表空间;
2.基于每个表的表空间;
3.InnoDB日志文件;
4.my.cnf文件;
4.重新启动服务器;
TIPS:所有数据库中的所有InnoDB表必须一起备份,因为InnoDB会在系统表空间中集中维护某些信息;
2.恢复:
1.要使用原始备份恢复InnoDB表,请停止服务器;
2.替换其副本在备份过程中生成的所有组件;
3.然后重新启动服务器;
TIPS:需要替换服务器上的所有现存的表空间文件,不能使用原始备份将一个表空间添加到另一个表空间;
— 原始MyISAM和ARCHIVE备份;
1.要生成MyISAM或ARCHIVE表,需复制MySQL用于代表该表的文件:
1.对于MyISAM,这些文件是.frm,.MYD和.MYI文件;
2.对于ARCHIVE表,这些文件是.frm和.ARZ文件;
3.在此复制操作过程中,其他程序(包括服务器)不能使用该表;
4.为了避免服务器交互问题,要在复制操作过程中停止服务器;
5.注:锁定表而不关闭服务器的做法在Linux系统上有效,在Windows上,文件锁定行为会导致可能无法复制被服务器锁定的表的表文件,在这种情况下,需要停止服务器后再复制表文件;
2.在服务器运行期间,锁定要复制的表:
mysql> USE mysql
mysql> FLUSH TABLES users WITH READ LOCK;
3.执行文件系统复制;
4.启动新的二进制日志文件:FLUSH LOGS;
1.新二进制日志文件包含在备份之后更改了数据的所有语句(以及所有后续的二进制日志文件);
5.在文件系统复制后解除锁定:UNLOCK TABLES;
6.恢复:要从原始备份中恢复MyISAM或ARCHIVE表,应停止服务器,将备份表文件复制到相应的数据库目录中,然后重新启动服务器;
— LVM快照;
1.在以下情况下,使用LVM快照执行原始备份:
1.主机支持LVM:例如,Linux支持LVM2;
2.包含MySQL数据目录的文件系统在逻辑卷上;
2.备份过程:
1.生成包含MySQL数据目录的逻辑卷的快照:在备份非InnoDB表时,使用FLUSH TABLES WITH READ LOCK;
2.从快照执行原始备份;
3.删除快照;
补充:LVM快照原理;
1.在支持LVM的系统(如Linux)上,可以创建要包含MySQL数据目录的逻辑卷;
2.可以创建该卷的快照,该快照的行为就像是逻辑卷的即时副本,LVM使用称为“写入时复制”(copy-on-write)的机制创建最初不含数据的快照;
3.在从新创建的快照读取文件时,LVM会从原始卷读取这些文件,当原始卷发生变化时,LVM会在原始卷上的数据发生变化之前,立即将其复制到快照,因此,在生成快照以来发生变化的任何数据都以其原始形式存储在快照中;
这样做的结果是,当从快照读取文件时,将获得在创建快照时存在的数据版本;
4.因为快照几乎是即时的,因此可以假定在生成快照过程中底层数据没有发生任何变化,这使得快照对于在不关闭服务器的情况下备份InnoDB数据库非常有用;
5.要创建快照,可使用以下语法:lvcreate -s -n
1.例如,假定有一个卷组VG_MYSQL和一个逻辑卷lv_datadir:lvcreate -s -n lv_datadirbackup -L 2G /dev/VG_MYSQL/lv_datadir;前一条语句创建快照lv_datadirbackup,其保留大小为2GB;
2.如果只是短时间需要该快照,则保留大小可以比原始卷的大小少很多,因为快照的存储仅包含在原始卷中发生 更改的数据块;
3.例如,如果要使用快照执行备份,则保留大小仅存储在执行备份以及删除快照的时间内所做的更改,可以像挂载标准卷一样挂载快照;挂载了快照后,就像处理其他任何原始备份一样,从该卷执行原始备份(例如,通过使用tar或cp);
4.从快照备份时,数据库在备份过程中不能有更改,您肯定会获得与备份时一样的一致数据文件版本,无需停止服务器;
5.随着时间的推移,快照的空间要求通常会增长到原始卷的大小,此外,对原始卷上数据块的每项初始数据更改将导致两次向卷组写入数据:请求的更改和对快照的写入时复制;这可能会影响快照保留期间的性能;
6.由于以上原因,应在执行了备份之后尽快删除快照,要删除由前一条语句创建的快照,可使用以下语句:lvremove VG_MYSQL/lv_datadirbackup;
— 原始二进制可移植性;
1.可在MySQL服务器之间复制二进制数据库:当将一台计算机上生成的二进制备份拿到具有不同体系结构的另一台计算机上时,二进制可移植性会很有用;
2.InnoDB:数据库的所有表空间和日志文件都可直接复制,源系统与目标系统上的数据库目录名称必须相同;
3.MyISAM/ARCHIVE:单个表的所有文件都可直接复制;
4.Windows兼容性:
1.在Windows系统上,MySQL服务器在内部存储小写的数据库和表名称;
2.对于区分大小写的文件系统,可使用选项文件语句:lower_case_table_names=1;
— mysqldump;
1.将表内容转储到文件:
– 所有数据库,特定数据库或特定表;
– 允许备份本地服务器或远程服务器;
– 与存储引擎无关;
– 以文本格式写入:包含用于重新创建表的CREATE TABLE和INSERT语句的SQL格式转储文件;
– 可移植;
– 卓越的复制/移动策略;
– 适用于小规模导出,但不适用于完整备份解决方案;
2.基本用法:mysqldump –user=
— 与mysqldump保持一致;
1.仅限–master-data选项:
1.在备份过程中锁定表;
2.在备份文件中记录binlog位置;
2.–master-data和–single-transaction选项一起使用:不锁定表,仅保证InnoDB表一致性;
3.–lock-all-tables:通过锁定表实现一致性;
4.–flush-logs:启动新的二进制日志;
— mysqldump输出格式选项;
1.删除选项:
1.–add-drop-database:将一条DROP DATABASE语句添加到每条CREATE DATABASE之前;
2.–add-drop-table:将一条DROP TABLE语句添加到每条CREATE TABLE语句之前;
2.创建选项:
1.–no-create-db:不生成CREATE DATABASE语句;
2.–no-create-info:不生成CREATE TABLE语句;
3.–no-data:创建数据库和表结构,但不转储数据;
4.–no-tablespaces:指示MySQL服务器不写入任何CREATE LOGFILE GROUP或CREATE TABLESPACE语句到输出;
3.MySQL编程组件:
1.–routines:从已转储的数据库中转储存储例程(过程和函数);
2.–triggers:转储每个已转储表的触发器;
4.一个选项中的最高选项(–opt):这是用于创建高效完整的备份文件的最常用选项的快捷方式;
— 恢复mysqldump备份;
1.使用mysql重新装入mysqldump备份:mysql –login-path=