05数据库安全及性能优化电子课件 MySQL数据库应用技术.pptx

数据库安全及性能优化模块五模块五学习目标了解MySQL日志维护、数据库优化的相关技术。掌握安全管理、权限管理的相关知识。掌握数据库备份和恢复的相关技术。知识重点创建、删除用户账号。设置访问权限。备份、恢复数据。查看日志。知识难点管理用户账号。设置访问权限。备份、恢复数据。5.1用户与权限管理在MySQL中有许多系统数据库，MySQL数据库中存储的是关于权限的表，有三个重要的表，分别为mysql.user、mysql.db和mysql.host。其中，mysql.user是一张重要的权限表。如果要查看用户，需要进入mysql.user表查看，其SQL语句如下。select*from user；执行上述语句，效果如图5-1所示。图5-1 user表数据5.1用户与权限管理通过create user语句创建用户，其基本语法如下。create user username identified by password password,username identified by password password 其中，关键字user用来设置用户账号的名字，关键字identified by用来设置用户账号的密码。值username表示新建用户账户名，由user和host构成，形式为userhost。5.1.1 创建用户账号1.create user语句5.1用户与权限管理执行SQL语句create user，创建用户名为test1、主机名为localhost、密码为123456的用户账户。其SQL语句如下。执行上述语句，其结果如图所示。5.1.1 创建用户账号1.create user语句【例5-1】create user test1 localhost identified by 123456 ；create user创建出来的用户只有连接数据库的权限，需要后续继续授权。5.1用户与权限管理执行SQL语句create user，创建用户名为test2、允许远程登录的用户账户，密码为123456。其SQL语句如下。执行上述语句，其结果如图5-3所示。5.1.1 创建用户账号1.create user语句【例5-2】create user test2 identified by 123456 ；图5-3 创建test2用户账户MySQL 5.7如果创建远程登录用户账户，%可以直接省略。5.1用户与权限管理执行SQL语句create user，创建用户名为test3、主机名为localhost、没有密码的用户账户。其SQL语句如下。执行上述语句，其结果如图5-4所示。5.1.1 创建用户账号1.create user语句【例5-3】create user test3 localhost；图5-4 创建test3用户账户5.1用户与权限管理create user 语句虽然可以创建普通用户账户，但不便于为用户账号赋权限。而grant语句不仅可以创建用户账户，还可以对其进行赋权限，其基本语法如下。5.1.1 创建用户账号2.grant语句grant priv_type on database.table to username identified by password password ,username identified by password password 其中，priv_type表示新创建用户的权限。database.table表示新创建用户的权限范围，即只能在指定的数据库和表上使用权限。其他部分同create user 语句一致。5.1用户与权限管理5.1.1 创建用户账号2.grant语句执行SQL语句grant，创建用户名为test4、主机名为localhost、密码为123456的用户账户，同时设置只对chapter5数据库emp表有select权限。其SQL语句如下。【例5-4】grant select on chapter5.emp to test4 localhost identified by 123456 ；执行上述语句，其结果如图5-5所示。5.1用户与权限管理5.1.1 创建用户账号2.grant语句执行SQL语句grant，创建用户名为test4、主机名为localhost、密码为123456的用户账户，同时设置只对chapter5数据库emp表有select权限。其SQL语句如下。【例5-4】grant select on chapter5.emp to test4 localhost identified by 123456 ；执行上述语句，其结果如图5-5所示。图5-5 执行grant命令后的结果5.1用户与权限管理5.1.1 创建用户账号2.grant语句【例5-4】查看test4的权限，SQL语句为图5-6 test4用户的权限show grants for test4 localhost ；查看test4用户权限的结果如图5-6所示。grant的功能非常强大，后续内容会继续学习。5.1用户与权限管理5.1.1 创建用户账号2.grant语句【例5-5】执行SQL语句grant，创建用户名为test5、主机名为localhost、密码为123456的用户账户，同时设置对数据库所有的表都有select、update、delete权限。其SQL语句如下。grant select,update,delete on*.*to test5 localhost identified by 123456 ；show grants for test5 localhost ；5.1用户与权限管理5.1.1 创建用户账号2.grant语句【例5-5】执行上述语句，结果如图5-7所示。图5-7 test5用户的权限当database.table取值为*.*时，代表数据库中所有的表。而chapter5.*则代表chapter5数据库中所有的表，*.emp代表数据库中所有的emp表。5.1用户与权限管理5.1.1 创建用户账号3.向mysql.user表中插入记录系统权限表mysql.user中存储了关于用户账户的信息，可以使用insert语句直接将用户信息添加到 mysql.user表 中，一 般 需 要 指 定 host、user和 authentication_string（MySQL 5.7以 后mysql.user表中没有了password字段，而是使用authentication_string来代替）这三个字段，其SQL语句如下。insert into user(host,user,authentication_string)values(hostname ,username ,password(password )；对于字段password的值，要使用函数password()进行加密。在具体创建用户账户时，由于user表中字段ssl_cipher、x509_issuer、x509_subject没有默认值，所以还需要设置这三个字段的值。5.1用户与权限管理5.1.1 创建用户账号3.向mysql.user表中插入记录【例5-6】执行SQL语句insert，创建用户名为test6、主机名为localhost、密码为123456的用户账户。其SQL语句如下。insert into user(host,user,authentication_string,ssl_cipher,x509_issuer,x509_subject)values(localhost ,test6 ,password(123456 ),)；select*from user；5.1用户与权限管理5.1.1 创建用户账号3.向mysql.user表中插入记录【例5-6】执行上述语句，结果如图5-8所示。图5-8 创建test6用户账户5.1用户与权限管理5.1.2 删除用户账号1.drop user语句drop user语句删除普通用户的基本语法如下。drop user user1,user2其中，user1是指所要删除的用户，由用户名和主机名构成。5.1用户与权限管理5.1.2 删除用户账号1.drop user语句执行SQL语句drop user，删除用户名为test6、主机名为localhost的用户账户。其SQL语句如下。drop user test6 localhost ；执行上述语句，结果如图5-9所示。【例5-7】图5-9 执行drop user语句的结果5.1用户与权限管理5.1.2 删除用户账号1.drop user语句【例5-7】图5-10 用drop user语句删除test6的结果通过select语句查询user表中的数据，再次确认test6是否删除成功，如图5-10所示。由执行结果可知，不再显示用户test6，说明该用户已被成功删除。5.1用户与权限管理5.1.2 删除用户账号2.delete语句可以使用delete语句直接将mysql.user表中用户账户的信息删除。使用该方法时，要保证登录用户账户拥有delete权限，其基本语法如下。delete from user where user=username and host=hostname ；其中，user为关键字；where子句后须跟两个条件，只有同时指定用户名和主机名才能唯一确定用户账户。5.1用户与权限管理5.1.2 删除用户账号2.delete语句执行SQL语句delete，删除用户名为test5、主机名为localhost的用户账户。其SQL语句如下。delete from userwhere user=test5 and host=localhost ；执行上述语句后，通过select语句查询user表中的数据，再次确认test5是否删除成功，如图5-11所示。【例5-8】5.1用户与权限管理5.1.2 删除用户账号2.delete语句由执行结果可知，不再显示用户test5，说明该用户已被成功删除。【例5-8】图5-11 用delete语句删除test5的结果5.1用户与权限管理5.1.3 设置访问权限1.分配权限MySQL中使用grant为用户分配权限，其基本语法如下。grant priv_type on database.table to username identified by password password ,username identified by password password with grant option grant option其中，priv_type表示新创建用户的权限。database.table表示新创建用户的权限范围，即只能在指定的数据库和表上使用自己的权限。priv_type表示新创建用户的权限，可以是select、update等权限。可以使用grant命令重复给用户添加权限，权限叠加。例如，先给用户添加一个select权限，然后给用户添加一个insert权限，那么该用户就同时拥有了select和insert权限。5.1用户与权限管理5.1.3 设置访问权限1.分配权限执行SQL语句grant，创建用户名为test5，主机名为localhost，密码为123456的用户账户，同时设置对数据库所有的表都有select和delete权限，且加上with grant option子句，其SQL语句如下。【例5-9】grant select,delete on*.*to test5localhost identified by 123456 with grant option；5.1用户与权限管理5.1.3 设置访问权限1.分配权限执行上述语句，通过“show grants for test5localhost”查看该用户的权限，结果如图5-12所示。【例5-9】图5-12 test5用户的权限5.1用户与权限管理5.1.3 设置访问权限1.分配权限通过select语句查询user表中的数据，再次确认test5是否授权成功，如图5-13所示。【例5-9】图5-13 test5用户权限的查看从图5-13中可以看到，已经为test5用户赋予了select和delete权限。5.1用户与权限管理5.1.3 设置访问权限1.分配权限执行SQL语句grant，为用户名为test4，主机名为localhost，密码为123456的用户账户赋予超级管理员的权限。其SQL语句如下。【例5-10】执行上述语句，通过“show grants for test4 localhost ”查看该用户的权限，结果如图5-14所示。grant all privileges on*.*to test4 localhost identified by 123456；图5-14 test4用户的权限5.1用户与权限管理5.1.3 设置访问权限1.分配权限通过select语句查询user表中的数据，再次确认test4用户是否授权成功，如图5-15所示。【例5-10】图5-15 test4用户权限的查看从图5-15中可以看到，已经为test4用户赋予超级管理员的权限。5.1用户与权限管理5.1.3 设置访问权限1.分配权限执行SQL语句grant，为用户名为test3，主机名为localhost，密码为123456的用户账户，在chapter5数据库emp表中的id、name、salary列赋予select、delete和update的权限。其SQL语句如下。【例5-11】grant select,delete,update on chapter5.emp to test3 localhost identified by 123456；5.1用户与权限管理5.1.3 设置访问权限1.分配权限执行上述语句，通过“show grants for test3localhost”查看该用户的权限，结果如图5-16所示。【例5-11】图5-16 test3用户的权限由执行结果可知，使用grant命令分配权限时，可以在多个层次上分配，不仅可以针对所有数据表、单个数据表，也可以针对表中的某几列。5.1用户与权限管理5.1.3 设置访问权限2.刷新权限使用刷新命令使赋予的权限生效，尤其是对权限表user、db、host等做了update或者delete更新时。其SQL语句如下。flush privileges；只要对权限做了更改就使用flush privileges命令来刷新权限。5.1用户与权限管理5.1.3 设置访问权限3.查看权限在MySQL中，可以通过select语句查询user表中各用户的权限，user表中的权限列是以“_priv”结尾的，如果支持有限个权限，列值为Y,如果不支持有限个权限，列值为N。执行如下SQL语句。select *from user；运行结果如图5-17所示。图5-17 使用select语句查看当前用户的权限5.1用户与权限管理5.1.3 设置访问权限3.查看权限 查看用户名为test4，主机名为localhost的账号的权限，SQL语句如下。【例5-12】show grants for test4 localhost ；运行结果如图5-18所示。图5-18 使用show语句查看test用户的权限5.1用户与权限管理5.1.3 设置访问权限3.查看权限 如果关键字for省略，使用show grants语句可查看当前用户的权限。执行如下SQL语句。【例5-12】show grants；运行结果如图5-19所示。图5-19 使用show grants语句的结果5.1用户与权限管理5.1.3 设置访问权限4.取消权限 执行SQL语句revoke，回收用户名为test3，主机名为localhost的用户账户的update权限。其SQL语句如下。revoke update on chapter5.emp from test3 localhost ；图5-20 使用revoke语句回收test3的权限运行结果如图5-20所示。【例5-13】5.1用户与权限管理5.1.3 设置访问权限4.取消权限 执行SQL语句revoke，回收用户名为test4，主机名为localhost的用户账户的所有权限。通过select查看test4用户当前拥有的权限，如图5-21所示。图5-21 查看test4用户当前拥有的权限【例5-14】执行如下revoke语句。revoke all privileges,grant option from test4 localhost ；5.1用户与权限管理5.1.3 设置访问权限4.取消权限 执行上述语句，通过select再次查看test4用户当前拥有的权限，如图5-22所示。图5-22 使用revoke all 语句回收test4用户的权限【例5-14】由图5-22可以看出，该用户的权限已被取消，值由Y变为N。5.2数据库备份与恢复5.2.1 数据备份备份数据是数据库管理中最常用的操作。为了保证数据库中数据的安全，数据库管理员需要定期进行数据库备份。一旦数据遭到破坏，即需要通过备份的文件来还原数据库。因此，数据备份是很重要的工作。本节将介绍数据备份的两种工具，分别是mysqlpump备份工具和Navicat工具。5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份1）mysqlpump的特点和优势、缺点mysqlpump的主要特点如下。（1）并行备份数据库和数据库中的对象，加快备份过程。（2）更好地控制数据库和数据库对象（表、存储过程和用户账户）的备份。（3）备份用户账号作为账户管理语句，而不是直接插入MySQL的系统数据库。（4）备份出来直接生成压缩后的备份文件。（5）备份进度指示（估计值）。（6）重新加载（还原）备份文件。（7）备份可以排除或指定数据库。5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份1）mysqlpump的特点和优势、缺点（1）支持基于表的并行导出功能（2）导出时带有进度条。（3）支持直接压缩导出导入。（4）重新加载（还原）备份文件，先建表后插入数据最后建立索引，减少了索引维护开销，加快了还原速度。（5）更好地控制数据库和数据库对象（表、存储过程和用户账户）的备份。优势5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份1）mysqlpump的特点和优势、缺点（1）只能并行到表级别。（2）无法获取当前备份对应的binlog位置。（3）MySQL 5.7.11之前的版本不要使用，并行导出与single-transaction是互斥的。缺点mysqlpump不是SQL语句，mysqlpump是MySQL用于转存储数据库的实用程序，所以是在命令行中执行的，而不是在SQL中输入的。5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份2）备份一个数据库 使用mysqlpump命令备份一个数据库的基本语法如下。mysqlpump-u username-p password dbname table1 table2backups.sql其中，username参数表示数据库用户名，password参数表示数据库密码，dbname参数表示数据库的名称；table1、table2参数表示表的名称，backups1.sql参数表示备份文件的名称，文件名前面可以加上一个绝对路径。通常将数据库备份成一个后缀名为.sql的文件。5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份 使用root用户备份chapter5数据库下的test1表，命令如下。【例5-15】mysqlpump-u root-p*chapter5 test1E:backups.sql命令执行完成后，可以在E:下 找 到 backups1.sql文 件。backups.sql文件中的备份内容如图5-23所示。图5-23 备份内容5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份3）备份多个数据库mysqlpump命令备份多个数据库的语法如下。mysqlpump-u username-p passworddatabases dbname1 dbname2backups.sql这里要加上“databases”选项，后面跟多个数据库的名称。5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份 使用root用户备份chapter3数据库和chapter5数据库，命令如下。【例5-16】mysqlpump-u root-p*-databases chapter3 chapter5E:backups2.sql执行完上述命令后，可以在E:下看到名为backups2.sql的文件。该文件中存储着这两个数据库的所有信息。5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份4）备份所有数据库mysqlpump命令备份所有数据库的语法如下。mysqlpump-u username-p password-all-databasesbackups.sql使用“all-databases”选项就可以备份所有数据库了。5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份 使用root用户备份所有数据库，命令如下。【例5-17】mysqlpump-u root-p*-all-databasesE:backups3.sql 执行完上述命令后，可以在E:下看到名为backups3.sql的文件。该文件中存储着所有数据库的所有信息。mysqlpump更多参数可以参考表5-1。5.2数据库备份与恢复5.2.1 数据备份1.mysqlpump备份【例5-17】表5-1 mysqlpump参数5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份 使用Navicat工具备份的方法非常简单，可以分为手动备份和自动备份。在备份之前首先设置备份保存路径：进入Navicat客户端后，执行“工具”“选项”命令，打开“选项”对话框，然后选择“其他”选项卡，选择日志文件保存路径，如图5-24和图5-25所示。图5-24 “选项”命令5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份图5-25 设置保存路径5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份 1）手动备份图5-26 选择存储方式以SQL文件保存（1）其具体步骤如下：选中要备份的数据库，右击并选择“转储SQL文件”命令，可以选择“仅结构”或“结构和数据”，本例选择的转储方式是“结构和数据”，如图5-26所示。在弹出的“另存为”对话框中选择保存位置和文件名，如图5-27所示。单击“保存”按钮，在弹出的对话框单击“开始”按钮，如图5-28所示，转储成功后，单击“关闭”按钮。5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份 1）手动备份图5-27 选择文件位置图5-28 保存成功5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份 1）手动备份图5-29 新建备份c保存为备份文件（2）其具体步骤如下：选择要备份的数据库chapter5，右击，在弹出的快捷菜单中执行“备份”“新建备份”命令，如图5-29所示。在弹出的“新建备份”对话框中切换到“对象选择”选项卡，如图5-30所示。在“对象”列表中选择想要备份的数据，单击“开始”按钮，如图5-31所示。备份完成之后会生成一个.psc格式的文件，如图5-32所示。图5-30 “对象选择”选项卡5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份 1）手动备份图5-32 备份完成图5-31 开始备份5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份2）自动备份图5-33 新建批处理作业打开Navicat客户端，连上MySQL后，单击选中要备份的数据库chapter5。单击“计划”按钮，再单击“新建批处理作业”按钮，如图5-33所示。5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份2）自动备份图5-34 选择要备份的数据库在弹出的“批处理作业”对话框中，发现chapter5数据库已添加到可用任务列表里了，代表已选择了这个任务，如图5-34所示。5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份2）自动备份图5-35 “设置文件名”对话框单击“保存”按钮，在弹出的“设置文件名”对话框中给该任务取个名字，单击“确定”按钮，如图5-35所示。5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份2）自动备份图5-36 新建备份计划在“批处理作业”对话框中单击“设置计划任务”按钮，如图5-36所示。5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份2）自动备份图5-37 “高级计划选项”对话框如果想提高备份频率，设置备份日期或设置备份截止日期，单击图5-36中的“高级”按钮，打开“高级计划选项”对话框，如图5-37所示。5.2数据库备份与恢复5.2.1 数据备份2.使用Navicat工具备份2）自动备份图5-38 “设置账户信息”对话框单击“确定”按钮，如果有密码，输入计算机的账户和密码并单击“确定”按钮，则自动备份设置成功，如图5-38所示。5.2数据库备份与恢复5.2.2 恢复数据1.使用mysql命令行恢复1）在cmd命令行中使用source命令在cmd命令行中使用source命令的基本语法如下。C:Userslenovomysql-u username-p mysql-u root-p source 备份文件.sql；2）在mysql命令行中使用source命令在mysql命令行中使用source命令导入备份文件，基本语法如下。mysql source E:backups2.sql图5-39 恢复成功5.2数据库备份与恢复5.2.2 恢复数据2.使用Navicat工具恢复使用Navicat导入SQL文件，建议删除所有表或重新建数据库，同导出一样，右击数据库，在弹出的快捷菜单中选择“运行 SQL文件”命令，打开“运行SQL文件”对话框，选择文件，单击“开始”按钮，如图5-40所示。图5-40 选择SQL文件恢复数据5.2数据库备份与恢复5.2.2 恢复数据2.使用Navicat工具恢复使用Navicat工具还原备份文件，选中要恢复的数据库，依次单击“备份”“还原备份”按钮，如图5-41所示。在弹出的对话框中可以选择想要恢复的数据，单击“开始”按钮，在弹出的对话框中单击“确定”按钮，如图5-42所示，等待几秒恢复成功，完成后右击数据库并选择“刷新”命令，即可看到恢复的数据。图5-41 选择还原备份图5-42 开始恢复5.2数据库备份与恢复5.2.3 数据库的维护与诊断1.备份数据库对于数据库管理员来说，制定合理的备份策略无疑是很重要的，以下是在进行备份或恢复操作时需要考虑的一些因素。（1）确定要备份的表的存储引擎是事务型还是非事务型，两种不同的存储引擎备份方式在处理数据一致性方面是不太一样的。（2）确定使用全备份还是增量备份。全备份的优点是备份保持最新备份，恢复时可以花费更少的时间；缺点是如果数据量大，将会花费很多的时间，并对系统造成较长时间的压力。（3）可以考虑采取复制的方法来做异地备份，但是复制不能代替备份，它对数据库的误操作也无能为力。5.2数据库备份与恢复5.2.3 数据库的维护与诊断1.备份数据库（4）要定期做备份，备份的周期要充分考虑系统可以承受的恢复时间。备份要在系统负载较小时进行。（5）确保MySQL打开log-bin选项，有了binlog，MySQL 才可以在必要时做完整恢复、基于时间点的恢复或基于位置的恢复。（6）要经常做备份恢复测试，确保备份是有效的，并且是可以恢复的。5.2数据库备份与恢复5.2.3 数据库的维护与诊断2.MySQL权限与安全MySQL的权限系统主要用来对连接到数据库的用户进行权限的验证，以此来判断此用户是否属于合法用户，如果是合法用户则赋予相应的数据库权限。权限系统的工作原理 1）MySQL权限系统通过下面两个阶段进行认证。对连接的用户进行身份认证，合法的用户通过认证，不合法的用户拒绝连接。对于身份的认证，MySQL是通过IP地址和用户名联合进行确认的。5.2数据库备份与恢复5.2.3 数据库的维护与诊断2.MySQL权限与安全MySQL权限列表见表5-2。表5-2 MySQL权限列表5.2数据库备份与恢复5.2.3 数据库的维护与诊断2.MySQL权限与安全MySQL权限列表见表5-2。表5-2 MySQL权限列表5.2数据库备份与恢复5.2.3 数据库的维护与诊断2.MySQL权限与安全 监控方式2）MySQL常见的监控方式主要有三种：写程序或脚本、采用商业解决方案和开源软件方案。在第一种方式中，当机器很少时，可以通过写程序或者编写脚本的方式监控线上的服务器，但是随着业务量的增大，业务上需要监控的点变得越来越细化，需要部署的脚本也越来越多，通过脚本进行监控的方法基本上无法满足业务的需求，并且脚本的后期维护成本也很大。5.2数据库备份与恢复5.2.3 数据库的维护与诊断2.MySQL权限与安全 监控方式2）在第二种方式中，选择一个商业的解决方案，通过第三方为企业实现一套完整的监控系统。采用这种方案的优点是能在短时间内搭建一套监控平台，并且平台有很受欢迎的展现方式。在第三种方式中，选择一套已有的开源工具，通过开源工具对企业的生产系统进行监控，选择开源工具的优势包括完全免费、定制能力强、完全可控、集中化管理、可视性好。但是开源工具也有一定的缺点，如需要花费大量的时间阅读相关文档。5.2数据库备份与恢复5.2.3 数据库的维护与诊断2.MySQL权限与安全 常见错误诊断3）（1）Can t connect to MySQL server on localhost(10061)。（2）Unknown MySQL ServerHost localhost (11001)（3）Access denied for user:rootalocalhost (Using password:YES)。5.2数据库备份与恢复5.2.3 数据库的维护与诊断2.MySQL权限与安全 常见错误诊断3）（4）Access denied for user:test7localhost to database chapter5。（5）Unknown column column_name in field list。（6）You have an error in your SQL syntax。（7）Duplicate entry for key 1。5.2数据库备份与恢复5.2.4 查看日志1.错误日志错误日志记录着服务器启动和关闭的情况，还记录着关于故障或异常状况的消息，如果服务器无法启动，首先应该查看一下该日志。在意外发生时，服务器会在结束运行前把一条消息写入出错日志以表明出现了什么问题。5.2数据库备份与恢复5.2.4 查看日志2.通用查询日志通用查询日志又称为常规查询日志或普通查询日志，它包含用户连接的记录、来自客户的SQL查询和其他各种事件。这有助于监视服务器的活动：谁在连接，从何处连接和他们在做什么。当维护人员想要确定用户发送到服务器的是什么查询时，这是最方便使用的日志，对故障诊断或调试十分有用。5.2数据库备份与恢复5.2.4 查看日志3.慢查询日志慢查询日志的用途是为了改善性能，帮助开发者区别重写所需要的语句。服务器维护定义为“慢”查询(默认为10 s)的long-quer-time变量。如果查询花费的秒数过多，那么会认为是慢的，并记录在慢查询日志中。另外，慢查询也用于记录不用索引的查询。5.2数据库备份与恢复5.2.4 查看日志4.二进制日志本节主要讲解如何通过Navicat工具的相关参数设置来查看MySQL日志。（1）在Navicat主窗口中依次单击“查询”“新建查询”按钮，在查询编辑器窗口中输入查看日志状态命令，其SQL语句如下。show global variables like%general%；执行上述SQL语句后，查看当前的日志状态，如图5-43所示。5.2数据库备份与恢复5.2.4 查看日志4.二进制日志由图5-43可以看出，general_log的Value值为OFF，说明当前日志状态为关闭，且在日志所在的路径“D:MySQLmysql-5.7.23-winx64data”中没有找到general_log文件，如图5-44所示。图5-43 查看当前的日志状态OFF5.2数据库备份与恢复5.2.4 查看日志4.二进制日志图5-44 日志所在的路径5.2数据库备份与恢复5.2.4 查看日志4.二进制日志（2）把日志状态设置为“打开”。在查询编辑器窗口中输入如下SQL语句。set global general_log=on ；再次查看日志状态，其结果如图5-45所示。图5-45 查看当前的日志状态ON5.2数据库备份与恢复5.2.4 查看日志4.二进制日志由 图 5-45可 以 看 出，general_log的 Value值 为 ON，说明当前日志状态为打开。在日志所在的路径中找到了general_log文件，如图5-46所示。图5-46 general_log文件5.2数据库备份与恢复5.2.4 查看日志4.二进制日志（3）在查询编辑器窗口中输入如下SQL语句。select *from test1；执 行 上 述 语 句 后，打 开general_log文件，如图5-47所示。图5-47 查看日志记录5.2数据库备份与恢复5.2.4 查看日志4.二进制日志（3）在查询编辑器窗口中输入如下SQL语句。记录图中画线的部分就是查询日志。如果想关闭日志状态，需输入如下SQL语句。set global general_log=OFF ；5.3数据库性能优化1.数据库优化的目的（1）避免出现页面访问错误。（2）避免由于数据库连接timeout产生页面5的错误。（3）避免由于慢查询造成页面无法加载。（4）避免由于阻塞造成数据无法提交。（5）增加数据库的稳定性。（6）提高数据库的查询效率。（7）优化用户体验。（8）使页面的访问流畅。（9）提供良好的网站功能体验。5.3数据库性能优化2.数据库优化的内容SQL查询优化的目的：减少查询所消耗的时间，加快查询的响应速度。分析查询语句，可以了解查询语句的执行情况。在MySQL中，可以使用explain语句和describe语句来分析查询语句。索引可以快速定位表中的某条记录。使用索引可以提高数据库查询的速度，从而提高数据库的性能。（1）优化查询5.3数据库性能优化2.数据库优化的内容将字段很多的表分成多个表。有些表在设计时设置了很多的字段。表中有些字段的使用频率很低。当表的数据量很大时，查询数据的速度就会很慢。增加中间表。有时需要经常查询某两个表中的几个字段。如果经常进行联表查询，会降低MySQL数据库的查询速度。增加冗余字段。设计数据库表时尽量让表达到三范式。（2）数据库表结构5.3数据库性能优化2.数据库优化的内容内存中会为MySQL保留部分的缓存区。这些缓存区可以提高MySQL数据库的处理速度。缓存区的大小都是在MySQL的配置文件中进行设置的。MySQL中比较重要的配置参数都在f或者my.ini文件的mysqld组中。读者可以自行查阅相关资料，合理地配置完参数后可以提高MySQL服务器的性能，配置完参数后，需要重新启动MySQL服务器才会生效。（3）系统配置5.3数据库性能优化2.数据库优化的内容CPU选择。根据MySQL的一些特性来说，因为MySQL有一些工作只能使用到单核CPU，建议选择一些单核的频率更快的CPU。内存。随着硬件技术的成熟，硬件的价格也随之降低。可以在内存中为MySQL设置更多的缓存区，这样可以提高MySQL访问的速度。如果将查询频率很高的记录存储在