第13章 备份和恢复

第13章备份和恢复《MySQL数据库应用教程》刘瑞新主编配套资源目录第13章备份和恢复13.1备份与恢复概述13.2使用mysqldump命令备份数据13.3数据的恢复13.4其他备份与恢复数据的方法13.5表记录的导出与导入13.1.1数据备份的原因1.系统故障2.事务故障3.介质故障4.其他灾害13.1备份与恢复概述13.1.2数据库备份的分类1.按备份时服务器是否在线划分（1）热备份（2）温备份（3）冷备份13.1备份与恢复概述2.按备份的内容划分（1）逻辑备份（2）物理备份13.1备份与恢复概述3.按备份涉及的数据范围划分（1）完整备份（2）增量备份（3）差异备份13.1备份与恢复概述13.1.3备份的周期和频率13.1.4数据恢复的方法13.1备份与恢复概述13.2.1备份数据库或表mysqldump[-h[hostname]]-uusername-p[password]db_name[tb_name1tb_name2...]>filename.sql13.2使用mysqldump命令备份数据【例13-1】使用mysqldump命令，备份studentinfo数据库中的所有表，备份文件studentinfo.sql存储在d:/db文件夹中。1）2）C:\Users\Administrator>mysqldump-uroot-pstudentinfo>d:/db/studentinfo.sql13.2使用mysqldump命令备份数据【例13-2】使用mysqldump命令备份studentinfo数据库中的class、student表，备份文件studentinfo_class_student.sql存储在D:/db文件夹中。C:\Users\Administrator>mysqldump-uroot-p123456studentinfoclassstudent>d:/db/studentinfo_class_student.sqlmysqldump:[Warning]Usingapasswordonthecommandlineinterfacecanbeinsecure.13.2使用mysqldump命令备份数据13.2.2查看备份文件13.2使用mysqldump命令备份数据13.2.3使用mysqldump命令备份多个数据库mysqldump-uusermame-p[password]--databasesdb_name1[db_name2...]>filename.sql【例13-3】使用mysqldump命令备份数据库studentinfo。C:\Users\Administrator>mysqldump-uroot-p--databasesstudentinfo>d:/db/studentinfo2.sqlEnterpassword:******13.2使用mysqldump命令备份数据【例13-3】使用mysqldump命令备份数据库studentinfo。C:\Users\Administrator>mysqldump-uroot-p--databasesstudentinfo>d:/db/studentinfo2.sqlEnterpassword:******13.2使用mysqldump命令备份数据13.2使用mysqldump命令备份数据13.2.4使用mysqldump命令备份服务器上的所有数据库mysgldump-uusername-p--all-databases>filename.sql【例13-4】使用mysqldump命令备份MySQL服务器上的所有数据库。在“命令提示符”窗口中输入下面备份服务器上全部数据库的命令：C:\Users\Administrator>mysqldump-uroot-p--all-databases>d:/db/all_mysql.sqlEnterpassword:******13.2使用mysqldump命令备份数据11.3.1使用mysql命令恢复数据使用mysgl命令恢复表mysql-uusername-pdb_name<filename.sql【例13-5】使用mysql命令恢复studentinfo数据库中的class、student表，备份文件是例13-2中使用mysqldump命令备份的脚本文件studentinfo_class_student.sql，存储在D:/db文件夹中。C:\Users\Administrator>mysql-uroot-pstudentinfo<d:/db/studentinfo_class_student.sqlEnterpassword:******13.3数据的恢复2．使用mysql命令还原单个数据库或者多个数据库mysql-uusername-p<filename.sql【例13-6】使用mysql命令还原studentinfo数据库，备份文件是例13-3中使用mysqldump命令备份的脚本文件studentinfo2.sql，存储在D:/db文件夹中。C:\Users\Administrator>mysql-uroot-pEnterpassword:******mysql>DROPDATABASEstudentinfo;mysql>QUIT13.3数据的恢复4）C:\Users\Administrator>mysql-uroot-p<d:/db/studentinfo2.sqlEnterpassword:******5）C:\Users\Administrator>mysql-uroot-pEnterpassword:******6）mysql>SHOWDATABASES;7）mysql>USEstudentinfo;mysql>SHOWTABLES;8）mysql>SELECT*FROMstudent;13.3数据的恢复11.3.2使用source语句恢复数据SOUCEfilename.sql1.恢复指定数据库中的表【例13-7】在studentinfo数据库中，删除student表中的所有记录。用source语句恢复该数据库中student表中被删除的记录。1）C:\Users\Administrator>mysqldump-uroot-pstudentinfostudent>d:/db/studentinfo_student.sqlEnterpassword:******2）C:\Users\Administrator>mysql-uroot-pEnterpassword:******13.3数据的恢复3）mysql>USEstudentinfo;Databasechangedmysql>SELECT*FROMstudent;4）mysql>DELETEFROMstudent;QueryOK,11rowsaffected(0.01sec)mysql>SELECT*FROMstudent;Emptyset(0.00sec)mysql>DROPTABLEstudent;QueryOK,0rowsaffected(0.03sec)mysql>SHOWTABLES;13.3数据的恢复5）mysql>SOURCEd:/db/studentinfo_student.sql;QueryOK,0rowsaffected(0.00sec)…QueryOK,0rowsaffected(0.00sec)QueryOK,11rowsaffected(0.01sec)Records:11Duplicates:0Warnings:0…QueryOK,0rowsaffected(0.00sec)6）mysql>SELECT*FROMstudent;13.3数据的恢复2.恢复指定数据库中的所有表【例13-8】用source语句将例13-1中生成的备份文件studentinfo.sql恢复到studentinfo数据库中。操作步骤如下。1）C:\Users\Administrator>mysql-uroot-pEnterpassword:******2）mysql>DROPDATABASEstudentinfo;QueryOK,5rowsaffected(0.07sec)13.3数据的恢复2）mysql>CREATEDATABASEstudentinfo;QueryOK,1rowaffected(0.01sec)mysql>USEstudentinfo;Databasechanged3）mysql>SOURCEd:/db/studentinfo.sql;4）mysql>USEstudentinfo;Databasechangedmysql>SELECT*FROMstudent;13.3数据的恢复1.备份数据库【例13-9】备份studentinfo数据库。13.4使用NavicatforMySQL菜单方式备份与恢复数据13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法2.恢复数据库【例13-10】恢复studentinfo数据库。13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法3.提取SQL【例13-11】从备份文件中提取SQL脚本文件。13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法4.转储SQL文件【例13-12】生成studentinfo数据库的备份脚本文件。13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法5.运行SQL文件【例13-13】从脚本文件恢复studentinfo数据库。13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法13.5表记录的导出与导入13.5.1使用SELECT...INTOOUTFILE语句导出文本文件SELECT{*|column1[,column2...]}FROMtb_name[WHEREconditions]INTOOUTFILE'filename'[options];[FIELDS[TERMIATEDBY'string'][[OPTIONALLY]ENCLOSEDBY'CHAR'][ESCAPEDBY'CHAR']][LINES[STARTINGBY'string'][TERMINATEDBY'string']]13.4其他备份与恢复数据的方法【例13-14】把studentinfo数据库中的student表中的所有记录导出到在服务器上的默认文件夹C:/ProgramData/MySQL/MySQLServer8.0/Uploads/st2022-1.txt。USEstudentinfo;SELECT*FROMstudentINTOOUTFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/st2022-1.txt';13.4其他备份与恢复数据的方法【例13-15】在studentinfo数据库中，导出student表中的所有女生记录，保存到在服务器上的默认文件夹C:/ProgramData/MySQL/MySQLServer8.0/Uploads/st2022-3.txt。其中，列值之间用“,”分隔，字符型数据用双引号括起来，每条记录以“<”开头，以“>\r\n”结尾。SQL语句如下：SELECTStudentID,StudentName,Sex,Birthday,Address,100FROMstudentWHERESex='女'ORDERBYStudentIDINTOOUTFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/st2022-3.txt'CHARACTERSETutf8mb4FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'#'"'单引号中是1个双引号

LINESSTARTINGBY'<'TERMINATEDBY'>\r\n';13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法13.5.2使用LOADDATAINFILE语句导入文本文件LOADDATA[{LOW_PRIORITY|CONCURRENT}]INFILE'filename'[{REPLACE|IGNORE}]INTOTABLEtb_name[options][IGNOREnLINES][({column|UserVariables}],…)][SETcolumn=expression,….)];13.4其他备份与恢复数据的方法【例13-16】把服务器上C:/ProgramData/MySQL/MySQLServer8.0/Uploads/st2022-1.txt文件导入到studentinfo数据库中的student表中。在NavicatforMySQL中打开studentinfo数据库，在查询窗格中运行下面SQL语句：LOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/st2022-1.txt'REPLACEINTOTABLEstudent;13.4其他备份与恢复数据的方法【例13-17】在studentinfo数据库中创建一张新表student3，使用LOADDATAINFILE语句将C:/ProgramData/MySQL/MySQLServer8.0/Uploads/st2022-3.txt中的记录导入到student3表中。1）CREATETABLEstudentinfo.student3(StudentIDCHAR(12)PRIMARYKEY,StudentNameVARCHAR(20)NOTNULL,SexCHAR(2)NOTNULL,BirthdayDATENULLDEFAULTNULL,AddressVARCHAR(30),NumberINT)ENGINE=InnoDBCHARACTERSET=utf8mb4;13.4其他备份与恢复数据的方法2）LOADDATAINFILE'C:/ProgramData/MySQL/MySQLServer8.0/Uploads/st2022-3.txt'INTOTABLEstudentinfo.student3CHARACTERSETutf8mb4FIELDSTERMINATEDBY','OPTIONALLYENCLOSEDBY'"'#'"

LINESSTARTINGBY'<'TERMINATEDBY'>\r\n';3）SELECT*FROMstudentinfo.student3;13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法13.5.3设置导出文本文件的路径1）SHOWVARIABLESLIKE'%secure%';13.4其他备份与恢复数据的方法13.4其他备份与恢复数据的方法以管理员身份用记事本打开my.ini文件，查找到secure-file-privsecure-file-priv="d:/db"13.4其他备份与恢复数据的方法