数据基础及应用 1

第8章数据备份与恢复8.4数据复制8.3数据库迁移数据备份8.2数据恢复8.5二进制日志文件CONTENTS目录

8.18.1数据备份8.1.1使用Workbench工具备份步骤：主窗口→Server→DataExport选择要备份的数据库和表，选择保存目录，单击StartExport导出对象（ObjectstoExport）：DumpStoredProceduresandFunctions：导出存储过程和函数DumpEvents：导出事件DumpTriggers：导出触发器导出选项：导出到项目目录/导出到单独文件8.1.2使用命令行程序备份——mysqldumpmysqldump命令将数据库数据备份成文本文件（.sql）1.备份单个数据库：mysqldump-uusername-pdbname[table1table2]>BackupName.sql例8-1：mysqldump-uroot-pstudentcourse>D:\student.sql2.备份多个数据库：mysqldump-uusername-p--databasesdbname1dbname2>BackupName.sqlmysqldump备份（续）例8-2：mysqldump-uroot-p--databasesstudentmysql>D:\data.sql3.备份所有数据库：mysqldump-uusername-p--all--databases>BackupName.sql例8-3：mysqldump-uroot-p--all--databases>D:\data.sql备份文件内容：CREATETABLE语句+INSERT语句文件开头注释中记录了MySQL版本、主机名和数据库名8.1.3直接复制文件备份最简单的备份方法：直接复制MySQL数据库文件特点：速度快，操作简单注意事项：操作前最好先停止MySQL服务，保证数据文件的完整性不适用于InnoDB存储引擎的表适用于MyISAM等存储引擎8.2数据恢复8.2.1使用Workbench工具恢复步骤：主窗口→Server→DataImport选择导入文件和准备导入的数据库单击StartImport按钮，导入数据8.2.2使用MySQL命令恢复使用mysql命令来恢复mysqldump备份的数据：mysql-uroot-p[dbname]<backup.sql指定dbname：恢复该数据库下的表不指定dbname：如数据库不存在则会报错例8-4：mysql-uroot-p<D:\all.sql输入密码后完成数据恢复8.3数据库迁移8.3.1相同类型MySQL数据库迁移先备份再恢复：mysqldump-hhost1-uroot-password=password1--all-databases>data.sqlmysql-hhost2-uroot-password=password2<data.sql可直接实现MySQL数据库间的迁移8.3.2不同数据库间迁移——导出文本文件不同类型数据库间迁移采用文本文件中转的方式1.用SELECT...INTOOUTFILE导出文本文件：SELECT[列名]FROMtable[WHERE条件]INTOOUTFILE'目标文件'[option];常用选项：FIELDSTERMINATEDBY（字段分隔符）FIELDSENCLOSEDBY（字段引用符）SELECTINTOOUTFILE示例例8-5导出student数据库course表，字段逗号分隔，字符型数据加双引号：SELECT*FROMstudent.courseINTOOUTFILE'd:\course.txt'FIELDSTERMINATEDBY'\,'OPTIONALLYENCLOSEDBY'\"'LINESTERMINATEDBY'\r\n';mysqldump导出文本&LOADDATA导入2.用mysqldump导出文本文件：mysqldump-uroot-pPassword-T目标目录dbnametable[option];加-xml导出XML格式；加-html导出HTML格式3.用LOADDATA导入文本文件：LOADDATALOCALINFILE'data.txt'INTOTABLEtable_name;例8-7：LOADDATALOCALINFILE'd:\course.txt'INTOTABLEcourse;8.4数据复制8.4.1MySQL数据复制介绍数据复制：主服务器（Master）→从服务器（Slave）自动同步数据复制的功能：（1）水平扩展：负载分散到多个从机，读写分离（2）高可用性：主机出错时迁移到从机（3）数据备份：从机执行备份，避免主机性能下降（4）在线分析：从机提供查询分析服务MySQL数据复制原理基础：二进制日志文件（BinaryLog）主服务器启用二进制日志后，所有操作以事件方式记录从服务器读取主服务器的二进制日志并在本地回放通过这种方式实现主从数据同步8.4.2主从复制配置（1）1.安装从数据库服务器（版本与主服务器相同）2.修改主服务器my.ini配置：[mysqld]log-bin='mysql-bin'（必须）启用二进制日志server-id=1（必须）服务器唯一ID3.修改从服务器配置：server-id=28.4.2主从复制配置（2）4.重启主从服务器上的MySQL服务5.在主服务器上建立账号sync并授权：CREATEUSER'sync'@'%'IDENTIFIEDWITHmysql_native_passwordBY'p01';GRANTREPLICATIONSLAVEON*.*TOsync;6.查看主服务器状态：SHOWMASTERSTATUS;7.配置并启动从服务器：CHANGEREPLICATIONSOURCETO...;STARTREPLICA;8.4.2主从复制配置（3）8.查看从服务器状态：SHOWSLAVESTATUS;Slave_IO和Slave_SQL进程必须都为YES状态9.复制效果测试：在主服务器插入记录，在从服务器查询确认数据已同步8.5二进制日志文件8.5.1开启日志文件在my.ini的[mysqld]下添加配置：log-bin='mysql-bin'--日志文件前缀名max_binlog_size=20M--单个日志文件大小（默认1G）binlog-do-db=student--只记录student库binlog-ignore-db=student--除student库不记录MySQL默认关闭二进制日志（开启会带来约1%的性能损耗）8.5.2管理binlog1.RESETMASTER：复位日志，删除所有日志，从0001重新开始2.PURGEMASTERLOGSTO'mysql-bin.000005';--删除000005前的日志PURGEMASTERLOGSBEFORE'2023-03-2311:00:00';--删除指定时间前的日志3.设置过期天数（my.ini）：EXPIRE_LOGS_DAYS=3--日志保存3天后自动删除使用日志恢复数据完整恢复：mysqlbinlogbin.000001|mysql-uroot-p基于时间点恢复（跳过误操作时段）：mysqlbinlog--stop-datetime='2024-02-2109:59:59'mysql-bin.000001|mysql...mysqlbinlog--start-datetime='2024-02-2110:02:01'mysql-bin.000001|mysql...基于位置恢复（精确控制起止位置）：mysqlbinlog--stop-position=462mysql-bin.000002|mysql...增量备份与恢复1.完整备份（每周日23:00）：MYSQLDUMP--SINGLE-TRANSACTION--FLUSH-LOGS--all-databases>full.sql2.增量备份（每天23:00）：mysqladminflush-logs（生成新的binlog文件，备份旧文件）3.恢复流程：先恢复完整备份，再依次导入增量binlog文件mysqlbinlogbin-log.000002bin-log.000003|mysql-uroot-p本章小结数据备份：可使用Workbench的"DataExport"功能，或使用mysqldump命令将数据库导出为SQL脚本文件或分隔文本文件。直接复制数据库文件也是一种备份方式。数据恢复：可使用Workbench的"DataImport"功能，或使用mysql命令执行SQL脚本恢复数据。恢复前需确保目标数据库已存在（或备份文件包含CREATEDATABASE语句）。数据库迁移：同类型数据库之间（如MySQL→MySQL）采用先备份再恢复的方法；不同类型数据库之间采用先导出为文本格式文件（如CSV、TXT）再导入的方法，也可借助第三方工具（如Navicat的"数据传输"）。数据复制：是MySQL实现性能扩展的重要方法。配置一台