-- 备份基础知识;
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 -p --backup_dir= backup-and-apply-log
1.backup:执行备份初始阶段;
2.backup-and-apply-log:包括备份的初始阶段以及第二个阶段,即将InnoDB表放到最新的备份中,其中包括在备份运行期间对数据所做的任何更改;
-- 使用mysqlbackup恢复备份;
基本用法:mysqlbackup --backup-dir= copy-back
1.:指定备份文件的存储位置;
2.copy-back:指示mysqlbackup执行恢复操作;
1.恢复操作将的内容(包括InnoDB和MyISAM索引)以及.frm文件恢复到其原始位置(由文件定义);
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 -p --backup-image= --backup_dir= backup-to-image
2.其他情形
1.标准输出:
... --backup-dir= --backup-image= -backup-to-image
2.将现有的备份目录转换为单个文件:
... --backup-dir= --backup-image= backup-dir-to-image
-- 恢复mysqlbackup单个文件备份;
1.提取选择的文件:
mysqlbackup -u -p --backup-image= --backup_dir= image-to-backup-dir
2.其他情形:
1.列出内容:
... --backup-image= list-image
2.将现有的备份目录转换为单个文件:
... --backup-image= --src-entry= --dst-entry= extract
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 -p
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 -L ;选项-s指示lvcreate创建快照,其他选项指定新快照的名称和大小以及原始卷的位置;
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= --password= --opt db_name > backup.file
-- 与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= < backup_file.sql
1.如果备份文件不存在,则指定数据库:USE db_name;
2.如果通过调用mysqldump与--database或--all-databases选项创建转储文件,则在从转储文件重新装入时,不需要指定目标数据库名称;
2.从一个数据库复制到另一个数据库(管道技术):mysqldump -u -p
| mysql --login-path= ;
3.mysqlimport:
1.如果调用mysqldump时使用--tab(与--fields-terminated-by,--fields-enclosed-by一起使用)选项,则将生成制表符分隔的数据文件:
1.包含CREATE TABLE语句的SQL文件;
2.包含表数据的文本文件;
2.要重新装入表,可将位置更改为备份目录,通过使用mysql处理.sql文件,然后使用mysqlimport装入.tsv文件:
shell> cd
shell> mysql --login-path= < table.sql
shell> mysqlimport -u -p table.tsv
补充:
-- mysqldump的参数
1.-A,--all-databases:导出所有的数据库,跟使用--databases后面跟上所有的数据库是一样的;
2.--add-drop-database:在创建数据库前添加drop database的语句;
3.--add-drop-table:在创建表之前添加drop table语句;
4.--add-locks:在插入语句前加锁;
5.--allow-keywords:创建的列允许使用关键字;
6.-i,--comments:写入附加信息,即添加注释;
7.-c,--complete-insert:使用完全插入语句,个人觉得还是-e参数好,数据量小用-e,数据量大用-c;
8.-B,--databases:备份多个数据库,把要备份的数据库跟在参数后面即可,当前数据库也会被包涵进来;
9.--delete-master-logs:备份完成后删除主机日志,自动打开--master-data选项;
10.-e,--extended-insert:使用multiple-row INSERT语句,即一个insert语句后面有多个值的列表,这是一种更高效的插入方式;
11.-F,--flush-logs:开始备份前切换一下日志,如果你一次备份多个数据库(使用--databases或者--all-databases选项时),则在备份每个数据库前都会切换日志.当使用--lock-all-tables or --master-data时,日志只会被切换一次,因为此时所有的表都被锁住,数据库保持一致.所以当你想要备份和日志组切换同时发生时,要用--lock-all-tables or --master-data和--flush-logs一起使用;
12.-h,--host=name:连接到主机;-u,--user-name:用户名;-p,--password:用户密码;
13.--ignore-table=name:不备份指定的表,如果要指定多个表,则要数据库和表明一起指定,如:--ignore-table=database.table;
14.-x,--lock-all-tables:会锁住所有数据库的表,会在备份期间加全局只读锁,自动关闭--single-transaction和--lock-tables选项;
15.--master-data[=#]:使得二进制日志的位置和和名称被添加到输出文件中,如果等于1,会像CHANGE MASTER命令一样打印它,如果等于2,命令会以注释的形式出现.这个选项会打开--lock-all-table选项,除非--single-transaction选项也被指定(此时全局只读锁知会在开始备份时有效),可以通过show master status命令查看当前日志信息,在恢复和复制功能时有用.
16.-n,--no-create-db:不包括创建数据库的语句;
17.-t,--no-create-info:不包括创建表结构语句;
18.-d,--no-data:只包含表定义,不包含表数据;
19.--order-by-primary:使每个表中的记录按照主键排序,如果没有主键,则使用第一个唯一索引.当导出一个MyISAM表到一个InnoDB表时有用,但是会延长导出时间;
20.--quick:不缓存query,直接导出到标准输出;
21.-R,--routines:导出stored routines(存储过程和函数);
22.--single-transaction:在一个事务中创建一个一致性的快照,只在支持多版本控制的引擎中起作用,目前只有innodb引擎.当--single-transaction进程工作时,为了保持数据一致性,则不能使用ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLEY语句,此选项自动关闭--lock-tables选项;
23.--opt:与同时指定--add-drop-table, --add-locks, --create-options, --quick, --extended-insert, --lock-tables, --set-charset, and --disable-keys相同.默认开启,要关闭使用--skip-opt;
24.-w,--where=name:只导出选择的记录;
-- 如何使用mysqldump备份
1.非事务表的一致备份:mysqldump --opt --lock-all-tables --master-data=2 -B db_name1 db_name2 > /tmp/backup.sql,备份时表是只读的;
2.事务表的一致备份:mysqldump --opt --single-transaction --master-data=2 -B db_name1 db_name2 > /tmp/backup.sql,备份时表是可读写的;
3.只备份routines(存储过程和函数,-R参数,在information_schema.routines表中)和events(作业,-E参数,在information_schema.events表中)信息:mysqldump -n -t -d -R -E > /tmp/routines.sql;
4.备份表结构,视图,函数,存储过程和作业的信息:mysqldump -d -R -E -S /mysql/logs/mysqld.sock --databases db_name > /tmp/objects.sql;
5.备份数据库test,包括视图信息:mysqldump --opt --lock-all-tables -R -E --master-data=2 -B test > /tmp/test_backup.sql;(查看当前二进制日志的名称和位置:show master logs;);
-- 测验;
a
-- 备份日志和状态文件;
1.二进制日志存储在备份完成后所做的更新;
2.服务器使用的选项文件(my.cnf和my.ini文件):这些文件包含在系统崩溃后必须恢复的配置信息;
3.复制文件:
1.复制从属服务器创建一个包含连接到主服务器所需信息的master.info文件;
2.以及一个指示当前的中继日志处理进度的relay-log.info文件;
4.复制从属服务器数据文件:
1.复制从属服务器创建用于处理LOAD DATA INFILE语句的数据文件;
2.这些文件位于slave_load_tmpdir系统变量指定的目录中,在服务器启动时使用--slave-load-tmpdir选项可设置该变量;
3.如果不设置slave_load_tmpdir,则应用tmpdir系统变量的值;
4.要保护复制从属服务器数据,需要备份以SQL_LOAD-开头的文件;
5.MySQL二进制文件和库;
6.策略:
1.静态文件:使用常规系统工具在服务器运行的情况下备份;
2.动态文件:使用常规系统工具在服务器停止的情况下备份;
-- 将复制用作备份的辅助工具;
1.主服务器可以继续运行:
1.使用从属服务器生成备份,而不备份主服务器;
2.主服务器不会被中断,备份过程不会对主服务器增加处理负荷,也不要求增加硬盘空间或进行额外处理;
2.可以停止从属服务器以生成备份:
1.停止服务器:关闭mysqld进程;
2.或者发出STOP SLAVE SQL_THREAD语句以停止服务器处理其从主服务器收到的更新,必须刷新表以强制挂起对磁盘的更改;
3.备份从属服务器的数据库:
1.停止的服务器可以使用系统工具;
2.从属服务器线程停止,但仍在运行的服务器可以使用任何MySQL工具;
4.启动服务器:
1.启动停止的服务器;
2.START SLAVE SQL_THREAD;
-- 备份方法比较;
1.参看PPT的对比;
2.快照:并非所有引擎都以相同方式处理快照,例如,InnoDB表不需要FLUSH TABLES WITH READ LOCK就能启动快照,但MyISAM表却需要;
-- 备份策略;
需要关心的问题:
1.我们的系统能否承受长时间停机(停机时间)?
2.有多少数据要备份?
3.使用哪些存储引擎来存储数据(InnoDB,MyISAM或两者)?
-- 处理二进制日志内容;
1.确定在备份生成后写入哪些日志:
1.在恢复了二进制备份文件或重新装入了文本备份文件后,通过重新处理在服务器的二进制日志中记录的数据更改,完成恢复操作;
2.为此,必须确定在生成备份后写入哪些日志,然后,需要使用mysqlbinlog程序将这些二进制日志的内容转换成文本SQL语句,以便使用mysql处理结果语句;
2.使用mysqlbinlog转换内容:
1.mysqlbinlog bin.000050 bin.000051 bin.000052 | mysql
2.使用一个命令处理所有binlog;
3.恢复部分binlog:
1.--start-datetime选项:指定开始提取的日期和时间,其中选项参数采用DATETIME格式;请注意,--start-datetime的粒度仅有一秒,因此可能不够精确,不能指定开始的确切位置;
2.--start-position选项:可用于指定在给定的日志位置开始提取;
3.对应的--stop-datetime和--stop-position选项,用于指定停止提取日志内容的位置;
4.mysqlbinlog --start-position=23456 binlog.000004 | mysql
5.如果不确定日志文件中对应于处理开始点的时间戳或位置,可使用mysqlbinlog(不带mysql)显示日志内容进行检查:
shell> mysqlbinlog file_name | more
补充:
-- mysqlbinlog的参数
1.-d,--database=name:列出某一个数据库的日志,只用于本地日志;
2.-f,--force-read:如果mysqlbinlog读到它不能识别的二进制日志,会打印警告而忽略该事件并继续,如果没有该事件则停止;
3.-o,--offset=#：忽略前N个实体;
4.-R,--read-from-remote-server:从远程服务器读取二进制日志,如果没有指定此选项,则--host, --user, --password, --port, --protocal, --socket选项都被忽略;
5.-r,--result-file=name:直接输出到给定的文件;
6.--start-datetime=time:读取二进制日志的生成开始时间,可以使用任何mysql服务器的时间格式,datetime和timestamp类型,如:'YYYY-MM-DD HH24:MI:SS';
7.--stop-datetime=time:读取二进制日志的生成结束时间;
8.-j,--start-position=#:读取二进制日志的生成开始位置,是一个整型参数;
9.--stop-position=#:读取二进制日志的生成结束位置,一个整型参数;
10.-t,--to-last-log:在mysql服务器中请求的二进制日志结尾处不停止,而是继续打印直到最后一个二进制日志的结尾,如果将输出发送给同一台mysql服务器,会导导致无限循环,要与--read-from-remote-server连用;
11.-D,--disable-log-bin:禁用二进制日志,如果使用--to-last-logs选项将输出发送给同一台mysql服务器,可以避免无限循环,该选项在崩溃恢复也很有用,可以避免复制已经记录的语句;
-- 测验;
c
-- 课后练习;
补充:
-- 表结构的复制
1.第一种做法是:CREATE TABLE table_name AS SELECT * FROM tb_name;
1.可以复制表接口和表中数据,如果只想要表接口可以加一个false的过滤;
2.但是会丢失表中列上面的属性(如自增属性)和索引(主外键);
2.第二种做法是:CREATE TABLE table_name(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) SELECT * FROM table_name;
1.可以在创建表时指定所有的属性,并同步数据;
2.但是语法太麻烦;
4.第三种做法是:CREATE TABLE table_name LIKE table_name;
1.只复制表结构,而且保留列的属性和索引;
2.如果想要数据的话可以INSERT table_name SELECT * FROM table_name;