《数据库系统与应用技术》课件 第9章 数据库维护

第9章数据库维护主讲:何广赢时间:2026/01/01CONTENTS目录01用户与权限管理用户管理基础CREATEUSER、DROPUSER权限控制GRANT、REVOKE语句角色管理简化多用户权限分配02数据备份与恢复逻辑备份mysqldump工具详解物理备份直接复制数据文件恢复技术mysql命令恢复03数据导入导出与迁移数据导出SELECT...INTOOUTFILE数据导入LOADDATAINFILE数据库迁移物理与逻辑迁移01用户与权限管理01DATABASEMAINTENANCEOVERVIEW数据库维护概述核心维护领域数据安全灵活的权限控制机制，支持从全局到列级的精细权限管理，确保数据机密性和完整性容灾恢复物理备份、逻辑备份和增量备份技术，快速恢复数据，最大限度减少系统停机时间性能优化数据导入导出、主从同步、定期维护确保数据库系统长期稳定高效运行MySQL维护技术框架1权限控制机制用户管理·权限分配·角色管理2备份恢复技术物理备份·逻辑备份·增量备份3数据迁移方案导入导出·数据库迁移·数据同步学习目标掌握数据库维护的核心技术，能够设计合理的备份策略实施高效的恢复操作，熟练进行数据的导入导出确保数据库系统的高效性、稳定性和安全性USERMANAGEMENT用户管理基础创建用户使用CREATEUSER语句创建新用户，并指定登录密码。新用户创建完成后，默认没有任何权限，需要管理员分配相应权限后才能正常登录。--创建用户基本语法CREATEUSER'username'@'host'IDENTIFIEDBY'password';权限分配使用GRANT命令为用户分配权限。权限可以精确控制到数据库、表、列级别。--授予所有权限GRANTALLPRIVILEGESON*.*TO'username'@'localhost';权限刷新每次调整权限后，需要执行FLUSHPRIVILEGES命令刷新权限表，使更改立即生效。--刷新权限FLUSHPRIVILEGES;删除用户当用户不再需要时，应及时删除以避免潜在的安全风险。使用DROPUSER命令彻底删除用户及其权限。--删除用户DROPUSER'username'@'localhost';PRIVILEGEMANAGEMENT权限管理核心命令GRANT授权--授权语法GRANTprivilegesONdatabase.tableTO'user'@'host'[WITHGRANTOPTION];privileges:权限列表，多个权限用逗号分隔database.table:指定数据库和表，*.*表示所有库所有表WITHGRANTOPTION:允许用户为其他用户分配权限REVOKE撤销--撤销语法REVOKEprivilegesONdatabase.tableFROM'user'@'host';注意:撤销权限时，必须确保撤销的权限和授权时的权限完全一致，否则可能会报错特殊权限:USAGE权限无法撤销，创建用户时默认赋予test库:新建用户默认拥有test数据库的全部权限，且无法撤销常用权限列表SELECT查询数据INSERT插入数据UPDATE更新数据DELETE删除数据CREATE创建库表DROP删除库表ALTER修改表结构INDEX创建索引PRIVILEGELEVELS权限层级与作用范围权限控制的五个层级服务器级*.*GRANTSELECTON*.*影响所有数据库数据库级db.*GRANTSELECTONdb.*影响单个数据库表级db.tblGRANTSELECTONdb.tbl影响单个表列级SELECT(col)GRANTSELECT(col)ONdb.tbl影响特定列过程级PROCEDUREGRANTEXECUTEONPROCEDURE影响存储过程Host值含义%匹配所有主机允许从任何IP地址连接localhost本地连接不会被解析成IP地址，直接连接MySQLTCP/IP连接通过TCP/IP协议连接，只能在本机访问::1IPv6兼容兼容支持IPv6，表示同IPv4的权限检查流程1检查user表检查全局权限，确定用户是否有全局权限2检查db表检查数据库级权限，确定用户对特定数据库的权限3检查table_priv表检查表级权限，确定用户对特定表的权限4检查columns_priv表检查列级权限，确定用户对特定列的权限PRACTICALCASE实战案例:用户创建与授权流程场景说明以tom用户为例,演示完整的用户创建、授权、登录测试、权限调整、为tom1和tom2用户分配权限的实战流程。通过这个案例可以深入理解MySQL权限管理的底层机制和常见问题解决方案。1创建用户并授权--root用户操作grantallonprod.*to'tom'@'%'identifiedby'tom'withgrantoption;创建tom用户并授予prod数据库的所有权限2创建其他用户--创建tom1和tom2createuser'tom1'identifiedby'tom1','tom2'identifiedby'tom2';由root用户创建tom1和tom2用户，准备由tom分配权限3tom用户授权--tom用户操作grantselectonprod.*totom1;grantselect,insert,updateonprod.*totom2;tom用户为tom1和tom2分配不同权限遇到的问题问题描述：tom用户无法从localhost登录，报错：Accessdeniedforuser'tom'@'localhost'原因分析：虽然授予了'tom'@'%'权限，但localhost需要单独授权解决方案--为localhost单独授权grantallonprod.*to'tom'@'localhost'identifiedby'tom'withgrantoption;验证：授权后tom用户可以从localhost正常登录，并创建表、为其他用户授权PRACTICALCASE实战案例:权限回收与密码管理权限回收--回收tom2的UPDATE权限revokeupdateonprod.*fromtom2;操作效果回收权限后，tom2用户重新登录，执行UPDATE语句时会被拒绝，报错：ERROR1142(42000):UPDATEcommanddenied验证权限--查看用户权限showgrantsfortom2;root修改密码--root用户修改tom1密码setpasswordfortom1=password('oracle');flushprivileges;效果：tom1用户使用旧密码tom1登录失败，使用新密码oracle登录成功用户修改自己密码--tom1用户修改自己的密码setpassword=password('tom1');效果：tom1用户可以自行修改密码，无需root用户干预删除用户删除用户前需要先回收其所有权限，然后使用DROPUSER命令删除用户。最后执行FLUSHPRIVILEGES刷新权限表。--完整删除流程revokeallonprod.*fromtom2;dropusertom2;flushprivileges;ROLEMANAGEMENT角色管理机制角色管理概述MySQL8.0及以上版本引入角色管理功能，通过创建角色并分配权限，可以简化多用户的权限管理流程。角色是一组权限的集合，可以将角色授予用户，用户即拥有该角色的所有权限。创建角色并授权--创建只读角色CREATEROLE'read_only_role';GRANTSELECTONinventory_db.*TO'read_only_role';创建只读角色read_only_role，授予对inventory_db数据库的只读权限角色分配与激活--分配角色给用户GRANT'read_only_role'TO'report_user'@'%';SETDEFAULTROLE'read_only_role'TO'report_user'@'%';将角色分配给用户并设置为默认角色，用户登录时自动激活角色继承--创建高级角色CREATEROLE'advanced_read_role';GRANT'read_only_role'TO'advanced_read_role';GRANTEXECUTEONPROCEDUREinventory_db.generate_reportTO'advanced_read_role';高级角色可以继承基础角色的权限，并添加额外的特殊权限简化管理权限集中管理，批量分配提高安全统一权限标准，避免遗漏方便维护权限变更只需修改角色权限审计角色权限清晰，便于审计SECURITYBESTPRACTICES安全最佳实践最小权限原则仅授予用户完成工作所必需的最小权限集合，避免授予过多权限。例如，报表用户只需要SELECT权限，业务用户只需要DML权限(SELECT、INSERT、UPDATE、DELETE)，不需要DDL权限(CREATE、DROP、ALTER)。定期审计权限--查看所有用户SELECTuser,host,authentication_stringFROMmysql.user;--查看用户权限SHOWGRANTSFOR'app_user'@'%';定期审计可以发现潜在的权限滥用问题，及时回收不必要的权限使用SSL加密--要求SSL连接GRANTUSAGEON*.*TO'secure_user'@'%'REQUIRESSL;安全优势•防止数据在传输过程中被窃取•保护敏感数据(密码、个人信息)•满足合规要求(如GDPR、等保)强密码策略使用复杂密码，定期更换限制root远程登录root用户仅限本地登录操作日志审计记录所有权限变更操作定期权限审查季度/年度权限清理02数据备份与恢复02BACKUPOVERVIEW备份概述与备份类型为什么需要备份？在数据库环境中，总会有不确定的意外情况发生：停电、软硬件故障、人为破坏、管理员误操作等都可能导致数据丢失、服务器瘫痪等严重后果。定期备份可以有效防止数据丢失，并将损失降到最低。停电硬件故障人为破坏误操作物理备份备份方式备份数据文件，直接复制数据库的物理文件到指定目录特点✓速度快备份恢复都快✓完整性好数据一致性高✗占用空间大存储成本高✗跨平台差版本兼容性差工具xtrabackup、cp、tar逻辑备份备份方式对数据库对象利用工具进行导出工作，将SQL语句汇总入备份文件特点✓占用空间小文本文件压缩高✓灵活性好可编辑、可筛选✗速度慢备份恢复都慢✗资源占用高CPU和内存消耗大工具mysqldump、SELECT...INTOOUTFILEMYSQLDUMPBACKUPmysqldump备份:数据库级备份mysqldump基本语法#基本语法格式mysqldump-u用户名称-h主机名称-p密码[选项]数据库名称[表名1表名2...]>备份文件名称.sql1备份单个数据库#备份atguigu数据库mysqldump-uroot-patguigu>atguigu.sql备份文件存储在当前目录下，包含CREATE和INSERT语句2备份多个数据库#使用--databases参数mysqldump-uroot-p--databasesatguiguatguigu12>two_database.sql使用--databases或-B参数，备份文件中会包含CREATEDATABASE语句3备份所有数据库#使用--all-databases参数mysqldump-uroot-pxxxxxx--all-databases>all_database.sql使用--all-databases或-A参数备份整个MySQL实例备份文件内容CREATE语句包含创建数据库、创建表、创建索引等DDL语句INSERT语句包含插入数据的INSERT语句，恢复时重新插入数据注释信息包含MySQL版本、主机信息、备份时间等额外信息ADVANCEDBACKUPOPTIONSmysqldump备份:表级与高级选项备份部分表#只备份book表mysqldump-uroot-patguigubook>book.sql只备份指定的表，多个表名用空格分隔备份单表部分数据#只备份id<10的数据mysqldump-uroot-patguigustudent--where="id<10">student_part.sql使用--where选项筛选需要备份的数据排除某些表#排除student表mysqldump-uroot-patguigu--ignore-table=atguigu.student>no_stu_bak.sql使用--ignore-table排除不需要备份的大表只备份结构或数据#只备份结构--no-data或-d#只备份数据--no-create-info或-t灵活选择只备份表结构或只备份数据备份存储过程和函数查看存储过程SELECTSPECIFIC_NAME,ROUTINE_TYPEFROMinformation_schema.RoutinesWHEREROUTINE_SCHEMA='atguigu';备份命令mysqldump-uroot-p-R-E--databasesatguigu>fun_atguigu_bak.sql验证备份grep-C5"rand_num"fun_atguigu_bak.sqlMYSQLDUMPOPTIONSmysqldump常用选项详解结构与数据选项--add-drop-database添加DROPDATABASE语句--add-drop-tables添加DROPTABLE语句-d,--no-data只备份结构，不备份数据-t,--no-create-info只备份数据，不备份结构锁表与并发选项-l,--lock-tables锁定所有表再备份--single-transaction适用于InnoDB的一致性备份-x,--lock-all-tables对所有库的所有表加锁--flush-logs,-F备份前刷新日志文件性能优化选项-e,--extended-insert使用多行INSERT语法--opt快速备份的速记选项(默认开启)--quick,-q快速转储，不缓存结果--compact产生少量输出，禁用注释最佳实践建议InnoDB表备份使用--single-transaction选项，确保备份一致性，不锁表MyISAM表备份使用--lock-tables选项，确保备份数据的一致性大数据库备份使用--quick选项避免内存溢出，配合--extended-insert提高恢复速度版本兼容恢复到旧版本时，避免使用--opt或-e选项DATARECOVERYmysql命令恢复数据恢复原理mysqldump备份的文件包含CREATE和INSERT语句。恢复时，mysql命令执行这些SQL语句，通过CREATE语句创建数据库和表，通过INSERT语句插入备份的数据，实现数据库数据的重现。基本恢复命令#基本语法格式mysql-uroot-p[dbname]<backup.sql1单库恢复#包含CREATEDATABASEmysql-uroot-p<atguigu.sql#不包含CREATEDATABASEmysql-uroot-patguigu4<atguigu.sql2全量备份恢复#恢复所有数据库mysql-uroot-pxxxxxx<all.sql全量备份恢复会自动创建所有数据库和表，无需手动创建3分离单库备份#从全量备份中分离useatguigu;sourceclass_structure.sql;sourceclass_data.sql;用SQL语法分离出创建表和插入数据的语句后再导出PHYSICALBACKUP物理备份:冷备份方案物理备份概述物理备份是直接复制MySQL中的数据库文件，这种方法最简单、速度最快。但为保证备份的一致性，需要停止服务器或锁定表。Windows平台C:\ProgramData\MySQL\MySQLServer8.0\DataLinux平台/var/lib/mysql/macOS平台/usr/local/mysql/data方式1:停止服务器#停止MySQL服务servicemysqldstop#或systemctlstopmysqld#复制数据文件cd/var/libcp-rpmysql//backup/#启动MySQL服务servicemysqldstart方式2:锁定表#锁定表并刷新FLUSHTABLESWITHREADLOCK;#在另一个会话中复制文件cd/var/libcp-rpmysql//backup/#解锁表UNLOCKTABLES;PHYSICALRECOVERY物理恢复与注意事项物理恢复步骤1停止服务将数据库服务器停止，确保数据文件不被占用servicemysqldstop2替换文件删除原数据文件，将备份文件复制到数据目录cp-rp/backup/mysql//var/lib/3启动服务启动MySQL服务，查询相关表数据是否恢复servicemysqldstart关键要求版本号相同必须确保备份数据的数据库和待恢复的数据库的主版本号相同(如都是8.x)，因为只有主版本号相同，文件格式才相同存储引擎限制这种方式对MyISAM表比较有效，对于InnoDB表则不可用，因为InnoDB表的表空间不能直接复制Linux权限设置在Linux操作系统下，复制到数据库目录后，一定要将数据库的用户和组变成mysql：#修改文件权限chown-Rmysql.mysql/var/lib/mysql/dbname说明：Linux权限设置严格，MySQL数据库只有root和mysql用户组可以访问DATAEXPORT数据导出:SELECT...INTOOUTFILE基本语法--导出表数据到文本文件SELECT*FROMaccountINTOOUTFILE'/var/lib/mysql-files/account.txt';secure_file_priv参数MySQL默认对导出的目录有权限限制，需要查看secure_file_priv变量的值来确定允许导出的目录：--查看secure_file_priv值SHOWGLOBALVARIABLESLIKE'%secure%';值为empty不限制文件生成的位置，不安全值为路径字符串文件只能放在指定目录或其子目录值为NULL禁止执行SELECT...INTOOUTFILE操作导出示例--导出account表SELECT*FROMaccountINTOOUTFILE'/var/lib/mysql-files/account.txt';--查看导出文件cat/var/lib/mysql-files/account.txt特点说明导出的文本文件默认使用Tab分隔字段，换行符分隔记录可以通过FIELDS和LINES选项自定义分隔符导出文件不能有同名文件存在，否则会报错DATAEXPORT数据导出:SELECT...INTOOUTFILE基本语法--导出表数据到文本文件SELECT*FROMaccountINTOOUTFILE'/var/lib/mysql-files/account.txt';导出示例--导出account表SELECT*FROMaccountINTOOUTFILE'/var/lib/mysql-files/account.txt';--查看导出文件cat/var/lib/mysql-files/account.txt特点说明导出的文本文件默认使用Tab分隔字段，换行符分隔记录可以通过FIELDS和LINES选项自定义分隔符导出文件不能有同名文件存在，否则会报错ADVANCEDEXPORT数据导出:mysqldump与mysql命令使用mysqldump导出文本文件#基本导出mysqldump-uroot-p-T"/var/lib/mysql-files/"atguiguaccount使用-T选项指定导出目录，会生成account.sql(建表语句)和account.txt(数据文件)#自定义格式mysqldump-uroot-p-T"/var/lib/mysql-files/"atguiguaccount--fields-terminated-by=','--fields-optionally-enclosed-by='"'使用FIELDS选项自定义字段分隔符和包围符，生成CSV格式的文件使用mysql命令导出基本导出mysql-uroot-p--execute="SELECT*FROMaccount;"atguigu>"/var/lib/mysql-files/account.txt"垂直显示mysql-uroot-p--vertical--execute="SELECT*FROMaccount;"atguigu>account_1.txtXML格式mysql-uroot-p--xml--execute="SELECT*FROMaccount;"atguigu>account_3.xmlADVANCEDEXPORT数据导出与数据库备份比较导出方式对比SELECT...INTOOUTFILE•速度快•格式简单•目录限制严格mysqldump-T•生成两个文件•支持格式自定义•适合数据交换mysql--execute•格式灵活•支持多种输出•适合自动化脚本DATAIMPORT数据导入:LOADDATAINFILE基本语法--从文本文件导入数据LOADDATAINFILE'/var/lib/mysql-files/account_0.txt'INTOTABLEatguigu.account;完整导入流程--1.导出数据SELECT*FROMatguigu.accountINTOOUTFILE'/var/lib/mysql-files/account_0.txt';--2.删除数据(测试用)DELETEFROMatguigu.account;--3.导入数据LOADDATAINFILE'/var/lib/mysql-files/account_0.txt'INTOTABLEatguigu.account;高级导入选项--指定字段分隔符和包围符LOADDATAINFILE'/var/lib/mysql-files/account_1.txt'INTOTABLEatguigu.accountFIELDSTERMINATEDBY','ENCLOSEDBY'"';选项说明•FIELDSTERMINATEDBY：指定字段分隔符•ENCLOSEDBY：指定字段包围符•LINESTERMINATEDBY：指定行分隔符MYSQLIMPORTUTILITY数据导入:mysqlimport方式mysqlimport基本语法#基本语法格式mysqlimport[options]databasetextfile...完整示例流程1.导出数据SELECT*FROMatguigu.accountINTOOUTFILE'/var/lib/mysql-files/account.txt'FIELDSTERMINATEDBY','ENCLOSEDBY'"';2.使用mysqlimport导入mysqlimport-uroot-patguigu'/var/lib/mysql-files/account.txt'--fields-terminated-by=','--fields-optionally-enclosed-by='"'MYSQLIMPORTUTILITY数据导入:mysqlimport方式完整示例流程1.导出数据SELECT*FROMatguigu.accountINTOOUTFILE'/var/lib/mysql-files/account.txt'FIELDSTERMINATEDBY','ENCLOSEDBY'"';2.使用mysqlimport导入mysqlimport-uroot-patguigu'/var/lib/mysql-files/account.txt'--fields-terminated-by=','--fields-optionally-enclosed-by='"'常用选项--fields-terminated-by指定字段分隔符，如逗号、制表符--fields-optionally-enclosed-by指定字段包围符，如双引号--lines-terminated-by指定行分隔符，如换行符--ignore-lines=n忽略文件前n行与LOADDATAINFILE对比LOADDATAINFILE•在MySQL客户端内执行•需要FILE权限•速度快，直接在服务器端操作mysqlimport•在命令行执行•不需要登录MySQL•适合脚本自动化DATABASEMIGRATION数据库迁移方案数据迁移概述数据迁移是指选择、准备、提取和转换数据，并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。包括验证迁移数据的完整性和退役原来旧的计算机存储系统。服务器更换维护或升级应用程序迁移网站集成灾难恢复物理迁移适用场景适用于大数据量下的整体迁移，需要快速迁移的场景✓快速迁移速度快✗需停机需要停机迁移✗版本要求版本配置需相同✗可能出错可能引起未知问题实现方式•拷贝数据文件•使用XtraBackup备份工具逻辑迁移适用场景适用范围更广，无论是部分迁移还是全量迁移都可以使用✓灵活适用范围广✓兼容好版本兼容性好✗速度慢迁移速度较慢✗资源占用高CPU和内存消耗大实现方式•mysqldump导出导入•SELECT...INTOOUTFILE+LOADDATAINFILE•第三方工具(MySQLWorkbench、Navicat)MIGRATIONSCENARIOS不同场景下的迁移注意点相同版本的数据库之间迁移指在主版本号相同的MySQL数据库之间进行数据库移动(如MySQL8.0.25→8.0.32)。方式1:复制数据库目录因为迁移前后MySQL数据库的主版本号相同，可以通过复制数据库目录来实现迁移，但只适用于MyISAM引擎的表。InnoDB表不能用直接复制文件的方式备份方式2:使用mysqldump(推荐)最常见和最安全的方式是使用mysqldump命令导出数据，然后在目标数据库服务器中使用mysql命令导入。mysqldump-hhost1-uroot-p--all-databases|mysql-hhost2-uroot-p不同版本迁移字符集问题旧版本可能使用latin1，新版本使用utf8mb4。如果数据库中有中文数据，迁移过程中需要修改默认字符集，否则可能无法正常显示数据。高版本的MySQL通常兼容低版本，可以从低版本迁移到高版本不同数据库迁移架构差异不同数据库的架构、数据类型、SQL语法有差异。例如：MySQL的DATE和TIMEvsOracle的DATE；MySQL的ENUM和SETvsSQLServer的ntext、Image。迁移工具•MyODBC(MySQL↔SQLServer)•MySQLMigrationToolkit•手动更改SQL文件DATARECOVERY误删数据应对方案误删数据分类传统的高可用架构不能预防误删数据，因为主库的droptable命令会通过binlog传给所有从库，导致整个集群执行该命令。需要根据误删类型采取不同的恢复措施。DELETE误删行DROP/TRUNCATE误删表DROPDATABASE误删库rm误删实例DELETE误删行数据恢复使用Flashback工具恢复数据。原理：修改binlog内容，将原数据库重新放回。需要binlog_format=row和binlog_row_image=FULL。预防措施•代码上线前SQL审查、审计•打开安全模式(sql_safe_updates=on)•强制要求加where条件且是索引字段DROP/TRUNCATE误删库/表恢复方案使用全量备份与增量日志结合的方式。前提：有定期全量备份，并且实时备份binlog。#恢复步骤：1.取最近一次全量备份2.用备份恢复临时库3.取出之后的binlog日志4.剔除误删除语句后应用到临时库5.恢复到主数据库PREVENTIONSTRATEGIES预防误删数据策略权限分离核心数据库权限控制核心数据库不能随便分配写权限，需要审批。只给业务开发人员DML权限，不给truncate/drop权限。DBA操作规范DBA团队成员日常只使用只读账号，必要时才使用有更新权限的账号。账号隔离不同账号、不同数据之间进行权限分离，避免一个账号可以删除所有库。操作规范删除前改名删除数据表之前，必须先对表做改名操作(如加_to_be_deleted)，观察一段时间确保对业务无影响后再删除。代码审查代码上线前必须进行SQL审查和审计，确保SQL语句的安全性。安全模式打开sql_safe_updates参数，强制要求加where条件且where后需要是索引字段。延迟复制备库原理设置固定的延迟时间(如1小时)，让从库落后主库。出现误删除操作后，1小时内到备库执行stopslave，跳过误操作命令恢复数据。CHANGEMASTERTOMASTER_DELAY=3600;--1小时其他用途•延迟测试•老数据查询•历史数据分析数据安全层次模型权限控制最小权限原则，权限分离操作规范流程化操作，代码审查备份机制定期备份，延迟复制审计监控操作日志，异常告警PRACTICALCASE备份恢复实战案例案例说明通过具体案例展示冷备份(物理备份)和热备份(逻辑备份)的完整流