SQL基础数据教程 7

任务10备份和还原学生选课系统数据库--数据库的管理和维护目录Contents任务与学习目标10.1任务描述10.2知识准备10.2.1数据备份与还原10.2.2用户管理10.2.3权限管理10.2.4MySQL优化10.3任务实现

任务

备份和还原数据库和数据表在管理数据库的过程中，难免会发生一些意外，比如，突然停电、设备故障、操作事物等可能造成数据的丢失。为了确保数据的安全，需要定期对数据库进行备份。当数据丢失后，可通过还原来降低损失。同时，为了确保数据库中数据的安全，数据库管理员会对操作数据库的人员分配用户名、密码以及权限，使其只能在权限范围内进行操作。最后，为了合理安排资源，可通过数据库优化，使得MySQL运行更快。数据库的管理和维护学习目标：

掌握数据库和数据表的备份；掌握创建与删除用户、修改用户密码掌握授予、查看、删除用户权限数据库的管理和维护数据库的管理和维护10.1任务描述10.2知识准备10.2.1数据备份与还原1.使用mysqldump备份数据库mysqldump是MySQL数据库中的备份工具，可以将数据备份成一个SQL脚本，该脚本中包括表结构和表数据等信息，执行该可以导入备份的数据，其语法格式如下。mysqldump-u用户名-p登录密码[options]数据库名1[数据库名2…]>[文件存储路径/]db_name.sql数据库的管理和维护10.2.1数据备份与还原2.使用mysqldump备份数据表在MySQL数据库管理中，借助mysqldump工具能够对数据表进行备份操作。其语法格式如下mysqldump-u用户名-p[options]数据库名

表名1[表名2…]>[path/]tb_name.sql注意：在上述语法中，若备份多张表，多张表之间需要用空格分隔。数据库的管理和维护10.2.1数据备份与还原3.使用MySQL命令恢复数据当数据丢失或遭到意外破坏时，可以使用备份的数据进行恢复以减少损失，其语法格式如下mysql-u用户名-p密码

数据库名<[存储路径/]db_name.sql数据库的管理和维护10.2.2用户管理1.使用CREATEUSER语句创建新用户要使用CREATEUSER语句创建一个新用户，就会在mysql.user表中添加一条记录。该语句创建的用户，默认是无权限的，需要用GRANT语句进行授权，其语法格式如下。CREATEUSER用户名[密码];数据库的管理和维护10.2.2用户管理2.删除用户在MySQL中，可以使用DROPUSER语句删除用户，其语法格式如下。DROPUSER‘user’@’host’[,‘user’@’host’];注意：在上述语法中，可以删除一个用户，也可以同时删除多个用户。数据库的管理和维护10.2.2用户管理3.用户修改自身密码在MySQL中，对用户进行管理的时候，除了可以设置密码，还可以修改用户密码，其语法格式如下。ALTERUSERUSER()IDENTIFIEDBY’新密码’;注意：在上述语法中，通过USER()函数更改root用户密码。数据库的管理和维护10.2.3权限管理在MySQL中，权限信息存储在4个权限控制的表格中，分别是tables_priv、columns_priv、procs_priv和proxies_priv，其中tables_priv保存的是用户被授予的表权限，columns_priv保存的是用户被授予的列权限，procs_priv保存的是用户被授予的存储过程权限，proxies_priv保存的是用户被授予的代理权限。创建好用户后，可以通过SHOWGRANTS语句查看账号的权限信息，语法格式如下。SHOWGRANTSFOR'user'@'host';注意：在上述语法中，'user'表示用户名，'host'表示登录的主机名或IP地址。数据库的管理和维护10.2.3权限管理给用户授权可以保证数据库的安全，在MySQL中可以使用GRANT语句为用户授权，其语法格式如下。GRANTpriv_type[,priv_type...]ON数据表TOuser@host[WITHwith_option[with_option]...];注意：在上述语法中，priv_type表示用户的权限类型，*.*表示数据库的所有表，with_option表示账号资源限制，它的取值有：数据库的管理和维护10.2.3权限管理MAX_QUERIES_PER_HOURn：设置每小时可以执行n次查询。MAX_UPDATES_PER_HOURn：设置每小时可以执行n次更新。MAX_CONNECTIONS_PER_HOURn：设置每小时可以建立n个连接。MAX_USER_CONNECTIONSn：设置单个用户可以同时建立n个连接。数据库的管理和维护10.2.3权限管理收回用户不必要的权限可以在一定程度上保证系统的安全。在MySQL中使用REVOKE语句取消用户部分权限，它的基本其语法格式如下。REVOKEpriv_type[,priv_type]...ONdb.tableFROM'user'@'host'[,'user'@'host'...]使用REVOKE语句收回用户所有权限，它的基本其语法格式如下。REVOKEALLPRIVILEGES,GRANTOPTIONFROM'user'@'host'[,'user'@'host'...]数据库的管理和维护10.2.4MySQL优化1.存储引擎存储引擎本身是MySQL数据库服务器的底层组件之一，可以看作是数据表存储数据的一种格式，不同格式具有的特性不同。在创建数据表时，选择合适的MySQL存储引擎，则会将系统的效率和性能上得到一个很大的提升。数据库的管理和维护10.2.4MySQL优化1.存储引擎数据库的管理和维护10.2.4MySQL优化1.存储引擎为了便于理解，下面将以上存储引擎分别进行介绍。（1）MEMORY存储引擎，它的表中所有数据都保存在内存中，一旦程序出错或服务器断电都会导致数据的丢失。（2）MRG_MYISAM存储引擎，相同MYISAM存储引擎表的集合，所有合并的表必须具有相同顺序的字段与索引的应用。（3）CSV存储引擎，是采用文本方式存储数据的一种存储引擎，数据在文件中通过逗号分隔保存。数据库的管理和维护10.2.4MySQL优化1.存储引擎为了便于理解，下面将以上存储引擎分别进行介绍。（4）FEDERATED存储引擎，在MySQL中不可用。（5）PERFORMANCE_SCHEMA存储引擎，主要用于收集数据库服务器性能参数，用户不能为数据表创建此类型的存储引擎。（6）MyISAM存储引擎，采用“堆组织”的方式存储数据，数据在MyISAM表中的保存顺序与插入顺序完全相同。数据库的管理和维护10.2.4MySQL优化1.存储引擎为了便于理解，下面将以上存储引擎分别进行介绍。（7）InnoDB存储引擎，采用“索引组织”方式存储数据，是MySQL中第一个提供外键约束的，并对事务处理的能力是最高的。在MySQL5.7版本中，被指定为默认的存储引擎。（8）BLACKHOLE存储引擎，“黑洞”存储引擎，写入的数据都会消失，就像被“黑洞”吞噬了一样，用此特性可以将其作为转发器或过滤器。（9）ARCHIVE存储引擎，数据存储时会通过zlib压缩库压缩，在记录被请求时会实时进行解压。数据库的管理和维护10.2.4MySQL优化2.锁机制锁机制是为了保证多用户并发操作时，能使被操作的数据资源保持一致性的设计规则。举个例子，在不添加锁机制的情况下，用户A和B同时获取goods表中的num值，若用户A获取num的值并进行修改，但没提交。这时候，用户B也获取num的值，就发现num的值是未修改前的，但用户A提交后，那num的值就是修改后的。整个操作会出现两个大问题，一是用户B第一次查询的num值与用户A的不同，二是用户B前后两次读取的num不同。为了避免此情况出现，我们可以先将A要操作的资源锁住，同时让用户B等待，直到A操作完成后，再让B进行操作。数据库的管理和维护10.2.4MySQL优化2.锁机制根据存储引擎的不同，MySQL常见的锁机制有两种，分别是表级锁和行级锁。MEMORY和MyISAM存储引擎中有表级锁，InnoDB中有行级锁。表级锁是MySQL中锁的作用范围最大的一种锁，它锁定的资源是所在的数据表。行级锁是MySQL中锁的作用范围最小的一种锁，它锁定范围是用户操作的数据。下面介绍一下表级锁。数据库的管理和维护10.2.4MySQL优化2.锁机制根据操作的不同，表级锁可以分为读锁和写锁。读锁：当用户读取（如SELECT查询）数据资源时添加的锁，其他用户不可修改，但可以读取该数据资源，因此读锁也可称为共享锁。写锁：当用户对数据资源执行写（如INSERT、UPDATE、DELETE等）操作时添加的锁，当前用户操作可对数据资源进行操作，其他用户不能进行读/写操作，因此写锁也可以称为排他锁或独占锁。数据库的管理和维护10.2.4MySQL优化2.锁机制在MySQL中，锁状态分为“隐式”和“显式”。“隐式”锁由服务器自动执行和自动解锁。“显式”锁是用户根据实际需求添加的锁，数据操作完成后，需要用户对其进行解锁。“隐式”读的表级锁：当用户执行SELECT查询操作前，服务器会“自动”地为其添加一个表级的读锁。“隐式”写的表级锁：当用户执行INSERT、UPDATE、DELETE等写操作前，服务器会“自动”地为其添加一个表级的写锁。数据库的管理和维护10.2.4MySQL优化2.锁机制若要对数据表进行“显示”地添加表级锁，其语法格式如下。LOCKTABLES数据表名READ[LOCAL]|WRITE,…注意：在上述语法中，LOCAL关键字表示未添加此锁的其他用户可以在表的末尾实现并发插入数据的功能。释放“显示”表级锁，其语法格式如下。UNLOCKTABLES;数据库的管理和维护10.2.4MySQL优化2.锁机制InnoDB存储引擎具有行级锁和表级锁，表级锁同MyISAM存储引擎。InnoDB存储引擎只有通过索引条件检索才会使用行级锁，否则将使用表级锁。InnoDB的行级锁根据操作的不同也分为共享锁和排他锁。“隐式”行级排他锁：当用户对InnoDB存储引擎表执行INSERT、UPDATE、DELETE等写操作前，服务器会“自动”地为通过索引条件检索的记录添加行级排他锁，直到操作语句执行完毕，服务器再“自动”地为其解锁。数据库的管理和维护10.2.4MySQL优化3.分表技术在MySQL数据库中，数据会随着时间的推移会越来越多，而单张数据表存储的数据有限，当其达到一定量时，执行查询操作会变慢，特别是在并发操作时，会增加单表的访问压力。分表技术是根据不同的需求将单张数据表进行拆分，从而达到分散单表压力的目的，提升数据库的访问性能。在MySQL中，常用的分表技术有两种，水平分表和垂直分表。数据库的管理和维护10.2.4MySQL优化3.分表技术水平分表是将一张数据表中的全部记录分别存储到多张数据表中，创建水平分表时必须保证各数据表的字段全部相同。它是用户将记录分别存储到各个分表中，每张数据表的字段相同，但是内容不同。数据库的管理和维护10.2.4MySQL优化3.分表技术水平分表的拆分算法最常使用的是：利用记录数与分表个数取余获取分表的编号，然后再对获取的分表执行指定的操作。垂直分表是将同一个业务的不同字段分别存储到多张数据表中，创建垂直分表时，各数据表通过一个字段进行连接，其他字段都不相同。数据库的管理和维护10.2.4MySQL优化4.分区技术对于单表数据量过大的问题，除了可以使用分表技术，还可以使用MySQL本身支持的分区技术提高数据库的整体性能。分区技术就是根据给定的算法在操作数据表时，将数据在逻辑上分到多个区域中存储。此外，在分区中可设置子分区，将数据存放到具体的区域中。例如，大量的蔬菜，可以分别存储在多个仓库中，在仓库中可以划分多个区域来存放不同种类的蔬菜。数据库的管理和维护10.2.4MySQL优化4.分区技术在创建数据表时，可同时创建分区，其语法格式如下。CREATETABLE数据表名称[(字段与索引列表)][表选项]PARTITIONBY分区算法(分区字段)[PARTITIONS分区数量][SUBPARTITIONBY子分区算法(子分区字段)[SUBPARTITIONS子分区数量]][(PARTITION分区名[VALUES值][其他选项][(SUBPARTITION子分区名[其他选项])],…)];数据库的管理和维护10.2.4MySQL优化4.分区技术分区可通过关键字PARTITIONBY关键字来实现。分区算法有4种，分别为LIST、RANGE、HASH和KEY，子分区算法仅有HASH和KEY。每种分区算法对应的分区字段不同，具体的语法如