MySQL教程（第4版） 课件 第8章 数据库备份与恢复

第8章

数据库备份与恢复——SQL语句导出或导入数据01表记录导出表记录导出下列语句可把表记录导出到一个文件中。SELECT*INTOOUTFILE'文件名'导出选项其中，导出选项：[FIELDS [TERMINATEDBY'符号'] [[OPTIONALLY]ENCLOSEDBY'转义字符'] [ESCAPEDBY'转义字符']][LINESTERMINATEDBY'字符串']表记录导出说明：（1）导出文件默认在服务器主机上创建，并且文件名不能是已经存在的，否则可能将原文件覆盖，需要在文件名前加上具体的路径。（2）FIELDS子句就是表列子句，指定导出文件中数据存放的格式。（3）LINES子句TERMINATEDBY指定一行结束的标志。如果FIELDS和LINES子句都不指定，则默认声明以下子句：FIELDSTERMINATEDBY'\t'ENCLOSEDBY''ESCAPEDBY'\\'LINESTERMINATEDBY'\n'FIELDS子句与LINES子句的语法完全相同，两种子句都是可选的，但如果在LOADDATA后同时指定了两种，则FIELDS子句必须位于LINES子句之前。（4）如果使用DUMPFILE而非OUTFILE：SELECT*INTODUMPFILE'文件名'导出选项表记录导出【例】导出xscj数据库kc表的所有记录到E:\mysql5\data目录kc.txt文件中，要求列值如果是字符就用双引号标注，列值之间用逗号隔开，每行以“\r\n”为结束标志。SETGLOBALlocal_infile=ON; USExscj;SELECT*FROMkc INTOOUTFILE'E:\mysql5\\data\\kc.txt' FIELDS TERMINATEDBY',' ENCLOSEDBY'\"' ESCAPEDBY'\'' LINESTERMINATEDBY'\r\n'; 说明：（1）将local_infile这个系统全局环境设置为ON。（2）如果数据库（表）的字符集与操作系统字符集不同，导出的txt文件会显示乱码。（3）如果导出文件不能成功，先停止MySQL，查看系统配置文件my.ini。可以修改[mysqld]段secure_file_priv=E:\mysql5\data，重新启动MySQL。02导入文本数据导入文本数据MySQL的LOADDATA语句能以非常快的速度将文本文件中的数据导入到表中：LOADDATA[LOCAL]INFILE文件名INTOTABLE表名

FIELDS... LINES... 说明一：（1）“文件名”用单引号括起来，文件以普通文本文件（.txt）格式保存。（2）FIELDS声明一系列字段子句，同导出语句。（3）LINES声明一系列行子句，同导出语句。说明二：对从文本文件中导入数据的处理遵循如下规则：（1）对于数值类型数据，引用字符加不加都可以。例如，学时、学分。（2）文本文件中的行可省略一个或多个字段的数据内容，仅以分隔字符占位，被省略的字段将默认按照其表中列类型的隐式默认值填写数据。（3）对于blob类型的数据，按文本文件中实际给出的字符（不论有无引用字符）分配空间，一个字符对应一个字节的存储空间。导入文本数据【例】读取E:\mysql5\data目录文件kc.txt，将文件内容加载到课程拷贝表kc_copy中。USExscj;DROPTABLEIFEXISTSkc_copy;CREATETABLEkc_copyLIKEkc;SETGLOBALlocal_infile=ON; LOADDATALOCALINFILE'E:\mysql5\\data\\kc.txt'INTOTABLEkc_copy FIELDS TERMINATEDBY',' ENCLOSEDBY'\"' ESCAPEDBY'\'' LINES TERMINATEDBY'\r\n'; 第8章

数据库备份与恢复——数据库备份与恢复数据库备份与恢复MySQL提供三种数据库备份类型：（1）备份文件：通过SQL语句或使用客户端工具导出数据或表文件的拷贝来备份数据库。（2）日志文件：通过保存更新数据的所有语句来备份数据库。（3）主从复制：此功能建立在两个或以上的服务器之间，通过设定它们的主从关系来实现。01Windows命令行窗口备份和恢复mysqldump备份数据库及表mysql恢复数据库及表Windows命令行窗口备份和恢复1.mysqldump备份数据库及表mysqldump是MySQL内置的工具，允许用户将数据库指定不同的选项备份到文件、服务器，甚至是压缩gzip文件中。它使用灵活、快速。mysqldump备份包括下列三种写法：mysqldump[选项]数据库[表]>文件名mysqldump[选项]--databases[选项]数据库...>文件名mysqldump[选项]--all-databases[选项]>文件名说明：（1）常用选项-u：后面的用户需要具有相应权限才能备份。-p：后面跟密码不能有空格；也可不跟密码，在命令回车后再输入密码，系统显示“*”。-h：主机名。如果是本地服务器，可以省略-h参数。--databases：备份数据库。--all-databases：备份所有数据库。--tables：备份表。--where：记录筛选条件。-d：只备份数据。-help：得到mysqldump选项表及帮助信息。（2）>文件名>：导出符。表示导出到文件名指定的文件中。文件名：可指定存放文件的目录（例如：E:\mysql5\data），目录必须已经创建，如有同名文件将覆盖。Windows命令行窗口备份和恢复【例】备份数据库。在Windows命令行窗口，进入MySQL安装目录下的bin子目录：cdC:\ProgramFiles\MySQL\MySQLServer5.7\bin（1）备份xscj数据库（包括所有对象和数据）：mysqldump-uroot-p--databasesxscj>E:\MYSQL5\data\xscj.sql命令执行后，输入登录MySQL实例密码。数据库可以同时写多个，用空格分开。（2）备份OPPO主机test数据库（包括所有对象和数据）：mysqldump-uroot-p123456mm-hOPPO--databasestest>E:\MYSQL5\data\oppo_test.sql（3）备份xscj数据库xs、kc和cj表：mysqldump-uroot-p123456--databasesxscj--tablesxskccj>E:\mysql5\data\xs_kc_cj.sql直接写“123456”本机密码。（4）备份xscj数据库所有表结构：mysqldump-hlocalhost-uroot-p-dxscj>E:\mysql5\data\xscj_stru.sql（5）备份所有的数据库表结构：mysqldump-hlocalhost-uroot-p-d--all-databases>E:\mysql5\data\all_stru.sqlWindows命令行窗口备份和恢复2.mysql恢复数据库及表mysqldump备份的文件中存储的是SQL语句的集合，用户可以将这些语句还原到服务器上以恢复一个损坏的数据库。此恢复命令只能恢复数据库中被损坏的表及数据，如果删除的是整个数据库，将无法恢复，除非再建一个同名的数据库，然后往其中恢复表和数据。【例】先备份本地test数据库，再用备份文件将其恢复。（1）备份本地test数据库后删除。mysqldump-uroot-p--databasestest>E:\MYSQL5\data\test.sql删除test数据库中的表。（2）恢复test数据库mysql-uroot-p123456test<E:\mysql5\data\test.sql（3）查看test数据库中的对象02使用日志文件备份和恢复启用日志处理日志使用日志文件备份和恢复1.启用日志二进制日志可以在启动服务器的时候启用，这需要修改MySQ安装文件夹中的my.ini配置文件。log_bin[=文件名]若不指定目录，则在MySQL的data目录下自动创建二进制日志文件。由于下面使用mysqlbinlog工具处理日志时，日志必须处于bin目录下，所以日志的路径就指定为bin目录路径。（1）修改my.ini配置文件需要先停止MySQL服务：netstopMySQL服务名如显示拒绝，改用Windows“计算机管理”工具停止MySQL运行。（2）修改my.ini配置文件，例如：log_bin=C:\ProgramFiles\MySQL\MySQLServer5.7\bin\logfile保存，重启服务：netstartMySQL服务名使用日志文件备份和恢复2.处理日志从Windows命令行窗口进入MySQL安装目录\bin下。（1）使用mysqlbinlog实用工具可以查看和处理二进制日志文件mysqlbinlog[选项]日志文件...例如，运行以下命令可以查看logfile.000001的内容：mysqlbinloglogfile.000001由于二进制数据非常庞大，无法在屏幕上延伸，可以保存到文本文件中：mysqlbinlog日志文件.000001>E:\mysql5\logfile_000001.txt（2）使用日志恢复数据mysqlbinlog[选项]日志文件...|mysql[用户选项]（3）清理日志flushlogs;使用日志文件备份和恢复【例】使用日志文件备份和恢复xscj数据库。1）

备份xscj数据库（1）假设用户在星期一下午1点使用mysqldump工具进行数据库xscj的完全备份，备份文件为xscj.sql。mysqldump-uroot-p--databasesxscj>E:\MYSQL5\data\xscj.sql（2）从星期一下午1点开始用户启用日志，logfile.000001文件保存了从星期一下午1点到星期二下午1点的所有更改。（3）在星期二下午1点运行一条SQL语句：flushlogs;此时创建了logfile.000002文件。使用日志文件备份和恢复2）恢复xscj数据库（1）将数据库恢复到星期一下午1点状态：mysql-uroot-p123456xscj<E:\MYSQL5\data\xscj.sql（2）将数据库恢复到星期二下午1时的状态：mysqlbinloglogfile.000001|mysql-uroot–p123456（3）将数据库恢复到星期三下午1点时的状态：mysqlbinloglogfile.000002|mysql-uroot–p123456（4）清除所有的日志文件：由于日志文件要占用很大的硬盘资源，备份数据库后，要及时将没用的日志文件清除掉。下列SQL语句用于清除所有的日志文件：resetmaster;如果要删除部分日志文件，可以使用PURGEMASTERLOGS语句：PURGE{MASTER|BINARY}LOGSTO'文件名'或PURGE{MASTER|BINARY}LOGSBEFORE'日期'第8章

数据库备份与恢复——直接复制文件直接复制文件如果要把MyISAM类型的表直接复制到另一个服务器使用，首先要求两个服务器必须使用相同的MySQL版本，而且硬件结构必须相同或相似。在复制之前要保证数据表不被使用。保证复制完整性的最好方法是关闭MySQL服务，复制数据库目录下的所有表文件（*.frm、*.myd和*.myi文件），然后重启服务。文件