MySQL数据库应用项目教程 课件 项目6 数据库运维

MySQL数据库应用

项目教程项目六数据库运维6.1MariaDB的权限系统

6.2MariaDB的数据备份和恢复

6.3MariaDB日志管理

6.4搭建高性能数据库系统

6.1MariaDB的权限系统

6.1.1权限表MariaDB中的权限表都存放在mysql数据库中,与权限相关的数据表有user视图、db表、tables_priv表、columns_priv表、procs_priv表。1.user视图和db表(1)user视图。user视图是从global_priv表中导出的。【例1】查看mysql数据库中的global_priv表和user视图的定义信息和结构信息。SHOWCREATETABLEmysql.global_priv;DESCRIBEmysql.global_priv;SHOWCREATEVIEWmysql.user;DESCRIBEmysql.user;(2)db表。db表也是mysql数据库中非常重要的权限表,在db表中存储的是用户对某个数据库的操作权限。6.1MariaDB的权限系统6.1.1权限表2.tables_priv表、columns_priv表和procs_priv表6.1MariaDB的权限系统6.1.2MariaDB权限系统的工作原理1.合法性验证当用户连接MariaDB服务器时,MariaDB服务器会根据用户提供的信息验证用户身份,如果不能通过身份验证,则MariaDB服务器会完全拒绝该用户访问;如果能够通过身份验证,则MariaDB服务器接受连接,然后进入权限验证和分配。MariaDB使用user视图中的Host、User和Password三个字段进行身份验证。2.权限验证和分配MariaDB服务器的权限验证过程如右图：6.1MariaDB的权限系统6.1.3用户管理1.创建用户MariaDB的用户可以分为普通用户和root用户(超级管理员)。(1)使用SQL语句(GRANT和CREATEUSER)创建用户。使用GRANT语句创建新用户。语法格式:GRANTpriv_type[(column_list)][,priv_type[(column_list)]]... ON[object_type]priv_level TOuser_specification[user_options...][WITHGRANTOPTION];【例2】使用GRANT语句创建新用户test_user_1,主机名为localhost,密码为testuser,并授予所有数据表的查询权限和更新权限。GRANTSELECT,UPDATEON∗.∗TO‘test_user_1’@’localhost’IDENTIFIEDBY‘testuser’;SHOWGRANTSFOR‘test_user_1’@‘localhost’;6.1MariaDB的权限系统6.1.3用户管理1.创建用户使用CREATEUSER语句创建新用户。语法格式:CREATE[ORREPLACE]USER[IFNOTEXISTS]user_specification[,user_specification]...[WITHresource_option[resource_option]...];【例3】使用CREATEUSER语句创建新用户。创建两个新用户,test_user_2@%的密码为testuser2,test_user_3@localhost的密码为tes-tuser3。

CREATEUSERIFNOTEXISTS‘test_user_2’@‘%’IDENTIFIEDBY‘testuser2’,‘test_user_3’@‘localhost’IDENTIFIEDBY‘testuser3’;SHOWGRANTSFOR‘test_user_2’@‘%’\GSHOWGRANTSFOR‘test_user_3’@‘localhost’\G6.1MariaDB的权限系统6.1.3用户管理1.创建用户(2)利用HeidiSQL图形界面创建用户。【例4】使用HeidiSQL图形界面创建一个新用户test_user_5@127.0.0.1,密码为testuser5。运行HeidiSQL,连接MariaDB服务器。在“用户管理-未命名”对话框中进行创建。6.1MariaDB的权限系统6.1.3用户管理2.修改用户名语法格式:RENAMEUSERold_userTOnew_user[,old_userTOnew_user]...;3.修改用户密码语法格式:SETPASSWORD[FORuser]{PASSWORD(‘somepassword’)|‘encryptedpassword’};4.删除用户语法格式:DROPUSER[IFEXISTS]user_name[,user_name]..;6.1MariaDB的权限系统6.1.3用户管理【例5】进行修改用户名、用户密码和删除用户的操作。将用户名称分别修改为check_user_2和check_user_3。RENAMEUSERtest_user_2@localhostTOcheck_user_2@localhost,test_user_3@localhostTOcheck_user_3@localhost;将用户check_user_2@localhost的密码设置为checkuser2。SETPASSWORDFORcheck_user_2@localhost=PASSWORD(‘checkuser2’);使用ALTERUSER语句和CURRENT_USER()函数更改当前用户的密码为mariadb。ALTERUSERCURRENT_USER()IDENTIFIEDBY‘mariadb’;删除用户check_user_2@localhost和check_user_3@localhost。DROPUSERIFEXISTScheck_user_2@localhost,check_user_3@localhost;6.1MariaDB的权限系统6.1.4权限管理1.权限类型在MariaDB中常见的权限如右表6.1MariaDB的权限系统2.查看权限查看用户权限可以使用SQL语句、查询mysql.user视图或HeidiSQL图形界面3.授予权限在MariaDB中,可以在HeidiSQL图形界面或使用GRANT语句为用户授权。(1)权限的级别。①全局层级②数据库层级③表层级④列层级⑤子程序层级(2)授权语句GRANT。在MariaDB中,必须是拥有GRANT权限的用户才可以执行GRANT语句。4.收回权限(1)收回用户的所有权限。(2)收回用户的指定权限。(3)REVOKE语句的严格性。(4)设置root密码。6.1MariaDB的权限系统6.1.4权限管理【例6】查看用户权限信息。使用SHOWGRANTS语句显示用户test1@%、test2@localhost和test3@localhost的权限信息。SHOWGRANTSFOR‘test1’@‘%’;SHOWGRANTSFOR‘test2’@‘localhost’;SHOWGRANTSFOR‘test3’@‘localhost’;查询mysql.user视图显示用户test3@localhost的权限信息。SELECTHost,User,Select_priv,Update_privFROMmysql.userWHEREuser=‘test3’;6.1MariaDB的权限系统6.1.4权限管理6.2MariaDB的数据

备份和恢复

6.2.1数据备份和恢复1.数据损失的因素可能造成数据损失的因素如下。系统故障介质故障计算机病毒错误操作服务器崩溃自然灾害6.2MariaDB的数据备份和恢复6.2.1数据备份和恢复2.数据备份的分类(1)按备份时数据库是否离线将数据备份划分：冷备温备热备(2)按备份设计的数据范围将数据备份划分为：完全备份增量备份差分备份按需备份6.2MariaDB的数据备份和恢复6.2.2数据备份的方法1.MariaDB中的备份方式MariaDB中的数据备份方式主要有物理备份和逻辑备份。物理备份是通过复制单个数据文件或目录来执行的;逻辑备份由恢复数据所需的SQL语句组成,如CREATEDATABASE、CREATETABLE、INSERT等。2.MariaDB中的备份工具(1)mariadb-backup。(2)mariadb-dump。(3)InnoDB存储引擎的逻辑备份。(4)快照。6.2MariaDB的数据备份和恢复6.2.2数据备份的方法3.使用mariadb-dump命令备份(1)备份数据库或数据表。使用mariadb-dump命令备份数据库或数据表的语法:mariadb-dump-u<user>-p<password>-h<host><db_name><tb_name>[,<tb_name>,…]>filename.sql【例7】使用mariadb-dump命令备份单个数据库和数据表。mariadb-dump-uroot-pteaching_management>d:/bak/teaching_manage-ment20211001.sql6.2MariaDB的数据备份和恢复6.2.2数据备份的方法3.使用mariadb-dump命令备份(2)备份多个数据库。使用mariadb-dump命令备份多个数据库,需要使用--databases参数。备份多个数据库的语法格式:mariadb-dump-u<user>-p<password>-h<host>--databases<db_name>[<db_name>…]>filename.sql【例8】使用mariadb-dump命令备份多个数据库。使用mariadb-dump命令备份teaching_management数据库和mysql数据库。mariadb-dump-uroot-p--databasesteaching_managementmysql>d:/bak/mariadbbak20211001.sql6.2MariaDB的数据备份和恢复6.2.3数据恢复的方法1.使用命令恢复语法格式:mariadb-uuser-ppassword[db_name]<filename.sql;【例9】使用mariadb命令备份单个数据库和数据表。使用root用户将备份文件teaching_management20211001.sql恢复到teaching_management数据库中。mariadb-uroot-pteaching_management<d:/bak/teaching_management20211001.sql;【例10】用SOURCE命令恢复teaching_management中的student_course数据表和数据。USEteaching_management;SOURCEd:/bak/teaching_management_student_course20211001.sql;2.直接复制整个数据库目录

通过直接复制数据库目录的方法备份的数据,可以直接复制到MariaDB的数据库目录下恢复数据。6.2MariaDB的数据备份和恢复6.2.4数据迁移数据迁移(DataMigration)是指选择、准备、提取和转换数据,并将数据从一个计算机存储系统永久地传输到另一个计算机存储系统的过程。1.使用语句导出(1)使用SELECT语句导出。基本语法格式:SELECT[列名]FROMtb_name[WHERE语句]INTOOUTFILE‘目标文件’[OPTION];【例11】使用SELECT语句导出数据。使用SELECT语句将teaching_management数据库的student_info数据表中的记录导出为文本文件。SELECT∗FROMteaching_management.student_infoINTOOUTFILE‘d:/bak/student_info_20211001.txt’;6.2MariaDB的数据备份和恢复6.2.4数据迁移1.使用语句导出(2)使用mariadb-dump命令导出文本文件。基本语法格式:mariadb-dump-uroot-ppassword-hhost-Tpathdb_name[tb_name[,tb_name]][OPTIONS]【例12】使用mariadb-dump命令导出文本文件。使用mariadb-dump命令将teaching_management数据库的student_info数据表中的记录导出为文本文件。mariadb-dump-uroot-p000000-Td:/bakteaching_managementstudent_info(3)使用mariadb命令导出文本文件。基本格式:mariadb-uroot-ppassword[OPTIONS]-e|--execute=“SELECT语句”db_name>filename.txt6.2MariaDB的数据备份和恢复6.2.4数据迁移2.使用语句导入(1)用LOADDATAINFILE方式导入文件。语法格式:LOADDATA[LOCAL]INFILE‘file_name’INTOTABLEtb_name[OPTION][IGNOREnumbersLINES][字段列表][SETcolumn=exp];【例13】使用LOADDATAINFILE方式导入文件。将d:\bak\student_info_20211001.txt文件中的记录导入teaching_management数据库的student_info数据表。导入前,要将teaching_management数据库的student_info数据表中的数据全部删除。DELETEteaching_management.student_info;LOADDATAINFILE‘d:/bak/student_info_20211001.txt’INTOTABLEteaching_management.student_info;SELECT∗FROMteaching_management.student_info;6.2MariaDB的数据备份和恢复6.2.4数据迁移2.使用语句导入(2)使用mariadb-import命令导入文本文件。语法格式:mariadb-import-uroot-ppassword[LOCAL]db_namefile_name[OPTIONS]【例14】用mariadb-import命令将d:\bak\teacher_info.txt文件中的记录导入teaching_management数据库的teacher_info数据表,字段之间使用逗号间隔,字符串类型的字段使用双引号括起来,每行记录以回车换行符\r\n结尾。mariadb-import-uroot-p000000teaching_managementd:/bak/teacher_info--fields-terminated-by=,--fields-optionally-enclosed-by=\”--lines-termi-nated-by=\r\n6.2MariaDB的数据备份和恢复6.2.4数据迁移3.用HeidiSQL导出和导入启动HeidiSQL,并连接MariaDB服务器；在HeidiSQL窗口菜单栏中打开“工具”菜单,然后选择“导出数据库为SQL脚本”命令；选择需要导出的数据库或数据表;6.2MariaDB的数据备份和恢复6.3MariaDB日志管理

6.3.1二进制日志1.启动和设置二进制日志MariaDB服务器默认关闭二进制日志。如果启动二进制日志,可以通过配置文件my.ini或f中的[mysqld]或[mariadb]组中添加选项的方式启动二进制日志。选项log-bin[=path[/file_name]。选项expire_logs_days=number。选项max_binlog_size=XXM。【例15】启动MariaDB服务器的二进制日志。在my.ini配置文件中的[mysqld]组中添加如下配置信息。log-binexpire_logs_days=10 max_binlog_size=100M6.3MariaDB日志管理6.3.1二进制日志2.查看二进制日志基本语法格式:mariadb-binlog[options]log-files【例16】查看MariaDB服务器的二进制日志文件及其内容。使用SHOWBINARYLOGS语句查看MariaDB服务器的二进制日志文件。mariadb-uroot-p000000//进入MariaDB终端SHOWBINARYLOGS;QUIT;//退出MariaDB终端6.3MariaDB日志管理6.3.1二进制日志3.删除二进制日志(1)使用RESETMASTER语句删除二进制日志文件。语法格式:RESETMASTER;(2)使用PURGEBINARYLOGS语句删除日志。语法格式:PURGE{MASTER|BINARY}BINARYLOGSTO‘file_name’;或PURGE{MASTER|BINARY}BINARYLOGSBEFORE‘datetime’;6.3MariaDB日志管理6.3.1二进制日志3.删除二进制日志【例17】删除日志。使用PURGEBINARYLOGS语句删除指定序号之前的二进制日志。mariadb-uroot-p000000//进入MariaDB终端PURGEBINARYLOGSTO‘mariadb-bin.000010’;使用PURGEBINARYLOGS语句删除指定时间之前的二进制日志。PURGEBINARYLOGSBRFORE‘2021-09-3010∶08∶00’;使用RESETMASTER语句删除二进制日志文件。RESETMASTER;QUIT;//退出MariaDB终端’;6.3MariaDB日志管理6.3.1二进制日志4.使用二进制日志文件恢复数据库使用mariadb-binlog命令恢复数据。语法格式:mariadb-binlog[options]log-files|mariadb-uroot-ppassword【例18】使用mariadb-binlog命令恢复2021年10月1日23∶59∶59时MariaDB服务器中的数据库的状态。mariadb-binlog--stop-date=‘2021-10-0123∶59∶59’../data/DESKTOP-QJTDD1C-bin.000005|mariadb-uroot-p0000006.3MariaDB日志管理6.3.2错误日志1.启动和设置错误日志(1)默认配置。(2)配置错误日志文件名。(3)设置log-basename选项配置错误日志的文件名。2.查看错误日志【例19】查看MariaDB服务器的错误日志及其内容。使用SHOWVARIABLES语句查看MariaDB服务器的错误日志的存储位置。mariadb-uroot-p000000//进入MariaDB终端SHOWVARIABLESLIKE‘log_error’;QUIT;//退出MariaDB终端6.3MariaDB日志管理6.3.3通用查询日志1.启动和设置通用查询日志(1)启动通用查询日志。(2)设置通用查询日志的文件名。(3)选择通用查询日志的输出目的地。(4)关闭通用查询日志。(5)禁用通用查询日志。2.查看、删除通用查询日志(1)查看通用查询日志。(2)删除通用查询日志。6.3MariaDB日志管理6.3.4慢查询日志1.启动和设置慢查询日志(1)慢查询日志的常用参数。①slow_query_log:启用/禁用慢查询日志。②log_output:以何种方式保存日志。③slow_query_log_file:慢查询日志文件的名称。④long_query_time:定义慢查询的时间(以秒或微秒为单位)。⑤log_queries_not_using_indexes:是否记录不使用索引的查询⑥log_slow_admin_statements:是否记录某些管理语句。⑦log_slow_disabled_statements:不应记录在慢查询日志中的语句类型。⑧min_examined_row_limit:查询必须检查的最小行数⑨log_slow_rate_limit:允许记录部分慢查询。⑩log_slow_verbosity:日志中的详细信息量。6.3MariaDB日志管理6.3.4慢查询日志1.启动和设置慢查询日志(2)启动慢查询日志。①在配置文件中启动慢查询日志。②设置系统变量slow_query_log启动慢查询日志。(3)设置慢查询日志文件名。(4)选择慢查询日志的输出目的地。(5)关闭慢查询日志。2.查看、删除慢查询日志

(1)查看慢查询日志。(2)删除慢查询日志。6.3MariaDB日志管理6.3.5中继日志、事务日志1.中继日志中继日志的相关参数如下。(1)relay_log={ON|OFF|PATH}:中继日志的功能开关,定义了中继日志存放的路径和文件名称。(2)relay_log_recovery={ON|OFF}:当从服务器从宕机状态恢复后,如果中继日志损坏,会导致日志中的部分语句无法被重放或者全部不能被重放。2.事务日志(1)redolog:提供数据重做功能,实现回滚操作。(2)undolog:不仅提供数据恢复功能,实现回滚操作,而且提供了恢复操作及MVCC功能。6.3MariaDB日志管理6.4搭建高性能数据库系统

6.4.1架构读写分离数据库系统1.架构主/从数据库(1)主/从数据库系统的优势。①增加服务健壮性。②实现负载均衡。③实现数据异地备份。(2)主/从复制的原理。MariaDB主/从复制原理如右图。6.4搭建高性能数据库系统6.4.1架构读写分离数据库系统(3)搭建主/从数据库。主/从服务器信息为:主MariaDB服务器的IP地址为192.168.1.101,MariaDB服务器端口为默认的3306,主机名为master;从MariaDB服务器的IP地址为02,MariaDB服务器端口为默认的3306,主机名为slave。【例20】搭建主/从数据库。确认主/从服务器已经启动,MariaDB能正常提供服务。6.4搭建高性能数据库系统在主服务器master的配置文件中添加如下内容:[mysql]bind-address=01port=3306log-bin=/data/master/binlog/mysql-binserver-id=101datadir=/data/master/dbdata在从服务器slave的配置文件中添加如下内容:[mysql]bind-address=02port=3306log-bin=/data/slave/binlog/mysql-binserver-id=102datadir=/data/slave/dbdata6.4.1架构读写分离数据库系统2.读写分离数据库的架构读写分离数据库的基本原理是将数据库读写操作分散到不同的服务器上;服务器可以搭建成“主/从架构”的形式,主服务器负责读写操作,而从服务器只负责读操作;从服务器通过复制主服务器上的数据保证两者数据同步,每台服务器都存储了所有的业务数据;业务服务器将写操作发送给主服务器,读数据时访问从服务器。(1)读写分离能提高数据库的性能。(2)读写分离带来的问题。①主/从复制延迟。②读写分离的分配机制。(3)搭建读写分离数据库。6.4搭建高性能数据库系统6.4.1架构读写分离数据库系统(3)搭建读写分离数据库。使用MyCat作为数据库中间件构建的读写分离数据库，如图6.4搭建高性能数据库系统6.4.2搭建高可用数据库集群1.数据库集群和分布式数据库数据库集群是串联工作的,分布式数据库是并联工作的。系统结构如图。6.4搭建高性能数据库系统6.4.2搭建高可用数据库集群2.数据库集群数据库集群和主/从服务器最本质的区别就是,共享数据和不共享数据。读写分离的本质是将访问压力分散到集群中的多台服务器,但是没有分散存储压力;数据库集群则将数据库进行“分库分表”。(1)业务分库。①JOIN操作问题。②事务问题。③成本问题。(2)分表。①垂直分表。②水平分表。(3)“分库分表”的实现。“分库分表”的实现除了要判断读或写，还要判断SQL语句中具体需要操作的数据表、操作函数(如count函数)、ORDERBY、GROUPBY等。6.4搭建高性能数据库系统6.4.2搭建高可用数据库集群3.高可用数据库集群的搭建【例21】搭建高可用数据库集群。高可用数据库集群的节点规划如表6.4搭建高性能数据库系统基础环境配置。●参照表6-7配置三个节点的主机名,IP地址。●修改三个节点中的hosts文件,实现IP地址与主机名之间的映射。●关闭三个节点的SELinux和firewalld。●在三个节点上安装chrony,使用mariadb1节点作为时间服务器,将mariadb2、mariadb3节点的时间与mariadb1节点的时间同步。6.4.2搭建高可用数据库集群3.高可用数据库集群的搭建●配置第一个节点mariadb1的f文件。6.4搭建高性能数据库系统[galera]#Mandatorysettingswsrep_on=ONwsrep_provider=/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address=“gcomm://mariadb1,mariadb2,mariadb3”binlog_format=rowdefault_storage_engine=InnoDBinnodb_autoinc_lock_mode=2#Allowservertoacceptconnectionsonallinterfaces.bind-address=wsrep_cluster_name=”MariaDB_Cluster“

wsrep_node_address=”192.168.1.104“

wsrep_node_name=mariadb1wsrep_sst_method=rsync6.4.2搭建高可用数据库集群3.高可用数据库集群的搭建●配置第二个节点mariadb2的f文件。6.4搭建高性能数据库系统[galera]#Mandatorysettingswsrep_on=ONwsrep_provider=/usr/lib64/galera/libgalera_smm.sowsrep_cluster_address=“gcomm://mariadb1,mariadb2,mariadb3”binlog_format=rowdefault