Linux系统管理与服务器配置-基于CentOS 7（第2版） 课件 第12章MySQL数据库服务器

第12章MySQL服务器Linux系统管理与服务器配置——基于CentOS7目录12.1项目一：MySQL服务器的安装与启动 12.1.1MySQL数据库简介 12.1.2安装MySQL数据库 12.1.3管理mysqld服务 12.1.4测试登录MySQL数据库 12.1.5重置MySQL数据库管理员root密码 12.2项目二：数据库的创建与使用12.2.1数据库操作 12.2.2数据表操作 12.2.3处理表数据 12.3项目三：数据库的权限管理与备份 12.3.1数据库的权限管理 12.3.2数据库的备份与恢复 本章小结 12.1项目一：MySQL数据库的安装与启动某公司设计部需要在服务器上部署一个用于发布项目考核信息的网站，在该网站上实现员工对考核内容的选择和考核成绩的查询等基本操作。为此，除需要部署前台相关Web服务外，还需要部署后台的数据库服务。管理员选取目前比较常用的数据库MySQL，因此需要对MySQL服务器进行安装并测试。序号知识点详见章节1了解MySQL数据库12.1.12安装MySQL服务器的过程12.1.23管理mysqld服务12.1.34测试MySQL服务器12.1.45重置MySQL服务器管理员root密码12.1.512.1.1MySQL数据库简介MySQL由瑞典MySQLAB公司开发，是一个开放源码的小型关系数据库管理系统。关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了对数据库的访问速度并提高了灵活性。由于MySQL具有体积小、速度快、拥有成本低等特点，使许多中小型网站为了降低网站总体成本而选择MySQL作为网站数据库。12.1.2安装MySQL数据库1.YUM方式安装MySQL服务器需要到/downloads/repo/yum/上下载MySQL的YUM库文件：mysql57-community-release-el7-11.noarch.rpm，然后进行安装，安装命令如下：#yumlocalinstallmysql57-community-release-el7-11.noarch.rpm使用下面的命令来检查MySQL服务器的YUM库安装是否正确#yumrepolistenabled|grep"mysql“执行#yuminstallmysql-community-server命令安装MySQL服务器，在此过程中会安装多个软件包，直到出现安装成功提示。12.1.2安装MySQL数据库2.源码包方式安装MySQL服务器采用源码包方式安装MySQL服务器的方法较复杂，这里以安装MySQL5.7版本为例，需要从官网上下载含boost的源码包，下载地址为https:///downloads/mysql/5.7.html，下载位置如图12.1.2安装MySQL数据库2.源码包方式安装MySQL服务器下载完成后，在终端中依次完成如下操作：#useradd-s/sbin/nologinmysql//添加用户#mkdir-p/data/mysql/data//建立所需目录#chown-Rmysql:mysql/data/mysql//更改所有者为mysql#mkdir/usr/local/mysql//建立所需安装目录#tar-xzvfmysql-boost-5.7.37.tar.gz-C/usr/local/mysql//将下载好的mysql解压到/usr/local/mysql目录下#cd/usr/local/mysql/mysql-5.7.37///切换到/usr/local/mysql目录下#yuminstall-ygccgcc-c++cmakencursesncurses-develbiso//安装所需的依赖包#yuminstall–yopensslopenssl-develncursesncurses-devel//安装所需的依赖包#cmake-DDEFAULT_CHARSET=utf8-DDEFAULT_COLLATION=utf8_general_ci-DWITH_BOOST=boost//配置#make&&makeinstall//编译安装12.1.2安装MySQL数据库2.源码包方式安装MySQL服务器接下来继续对MySQL进行配置。首先，使用vim/etc/f命令编辑/etc/f文件，文件内容如下：[client]port=3306socket=/tmp/mysql.sock[mysqld]port=3306socket=/tmp/mysql.sockuser=mysqlbasedir=/usr/local/mysqldatadir=/data/mysql/datapid-file=/data/mysql/mysql.pidlog_error=/data/mysql/mysql-error.logslow_query_log=1long_query_time=112.1.2安装MySQL数据库2.源码包方式安装MySQL服务器slow_query_log_file=/data/mysql/mysql-slow.logskip-external-lockingkey_buffer_size=32Mmax_allowed_packet=1024Mtable_open_cache=128sort_buffer_size=768Knet_buffer_length=8Kread_buffer_size=768Kread_rnd_buffer_size=512Kmyisam_sort_buffer_size=8Mthread_cache_size=16query_cache_size=16Mtmp_table_size=32Mperformance_schema_max_table_instances=1000explicit_defaults_for_timestamp=true#skip-networking12.1.2安装MySQL数据库2.源码包方式安装MySQL服务器max_connections=500max_connect_errors=100open_files_limit=65535log_bin=mysql-binbinlog_format=mixedserver_id=232expire_logs_days=10early-plugin-load=""default_storage_engine=InnoDBinnodb_file_per_table=1innodb_buffer_pool_size=128Minnodb_log_file_size=32Minnodb_log_buffer_size=8Minnodb_flush_log_at_trx_commit=1innodb_lock_wait_timeout=50[mysqldump]quick12.1.2安装MySQL数据库2.源码包方式安装MySQL服务器max_allowed_packet=16M[mysql]no-auto-rehash[myisamchk]key_buffer_size=32Msort_buffer_size=768Kread_buffer=2Mwrite_buffer=2M12.1.2安装MySQL数据库2.源码包方式安装MySQL服务器然后继续执行以下指令：#chown-Rmysql:mysql/usr/local/mysql//把安装目录用户和组更改为mysql#cd../bin//切换到bin目录#./mysqld--initialize-insecure--user=mysql--basedir=/usr/local/mysql--datadir=/data/mysql/data//初始化mysql#cd../support-files///切换到support-files目录#cpmysql.server/etc/init.d/mysqld//复制可执行配置文件此时已完成MySQL的配置。12.1.3管理mysqld服务#systemctlstartmysqld.service//启动mysqld服务#systemctlstopmysqld.service//停止mysqld服务#systemctlrestartmysqld.service//重新启动mysqld服务#systemctlstatusmysqld.service//查看mysqld服务状态#systemctlenablemysqld.service//设置mysqld服务开机自启动#systemctldisablemysqld.service//禁止mysqld服务开机自启动12.1.4测试登录MySQLMySQL5.7在安装过程中除了创建数据库超级管理员“root@localhost”，还为他生成了默认的密码，该密码保存在/var/log/mysqld.log文件中。因此在测试登录MySQL之前需要到/var/log/mysqld.log文件中查看默认密码。取得该密码后，再执行#mysql命令登录数据库。mysql命令位于/usr/bin目录下，用来登录本地或远程的MySQL服务器。在登录过程中输入默认的root密码，出现mysql>提示符，表示登录成功。12.1.5重置MySQL数据库管理员root密码测试登录MySQL服务器成功后，需要重置root的密码，才能执行其他操作。在设置密码时，注意MySQL服务器的默认密码策略要求密码必须包含大小写字母、数字和特殊符号，并且长度不能少于8位。12.1项目一：MySQL数据库的安装与启动操作过程：由于CentOSLinux7默认的YUM库中没有MySQL服务资源，因此需要到/downloads/repo/yum/网址去下载MySQL的YUM库文件：mysql57-community-release-el7-11.noarch.rpm。/downloads/repo/yum/#yumlocalinstallmysql57-community-release-el7-11.noarch.rpm#yuminstallmysql-community-server#systemctlstartmysqld.service#systemctlenablemysqld.service#cat/var/log/mysqld.log|greppassword#mysql-uroot-pmysql>setpasswordforroot@localhost=password('MYsql123!');12.2项目二：数据库的创建与使用在12.1项目基础上，管理员准备在MySQL数据库中创建项目考核信息发布网站需要的数据库（couman）和相关数据表。数据表包括：employee（员工信息）表、exam（考核内容）表、scores（员工考核信息）表，各个表的结构如下。employee表，字段：eno（员工号）、ename（姓名）、sex（性别）、groups（项目组）。exam表，字段：exid（内容序号）、cname（考核内容）。scores表，字段：scid（序号）、eno（员工号）、exid（内容序号）、score（成绩）。数据表创建成功后，管理员将对数据进行增、删、改、查的测试。序号知识点详见章节1数据库创建12.2.12数据表的创建12.2.23对数据的增删改查操作12.2.312.2.1数据库操作对数据库的常用操作包括：创建数据库、选择数据库和删除数据库。1.创建数据库登录数据库后，使用“createdatabase数据库名；”完成对数据库的创建。然后可以使用“showdatabases；”命令来查看目前所有数据库的信息。MySQL数据库默认自带4个数据库，information_schema数据库存储相关信息，mysql数据库存储授权表，performance_schema数据库存储数据库性能参数，sys数据库存储数据库性能数据，可以用来提升数据库的性能。12.2.1数据库操作2.选择数据库当需要对某一数据库进行操作时，首先需要选择该数据库才能操作，使用“use数据库名字；”命令来完成。3.删除数据库使用“deletedatabase数据库名；”命令删除指定数据库，删除数据库时，连同数据库里的所有数据表(包括其中的数据)和数据库目录都将被删除。12.2.2数据表操作1.创建表创建表的SQL语句格式：createtable表名(字段名1字段类型[字段约束]，

字段名2字段类型[字段约束]，...，

字段名n字段类型[字段约束]，[表约束])[Type|Engine=表类型|存储引擎]；其中字段类型，也称作列类型，规定了某个字段所允许输入的数据类型。字段类型说明INT整形，4个字节FLOAT数值类型，支持浮点数或小数DOUBLE数值类型，支持双精度浮点数TIMEHH:MM:SS格式的时间字段DATEYYYYMMDD格式的日期字段CHAR字符型，最大长度为255VARCHAR字符串类型，最大长度为255YEARYYYY或YY格式的年字段12.2.2数据表操作字段约束用于进一步约束某个字段允许输入的数据。表约束用于确定表的主键、外键和索引等约束说明Null(或

NotNull)允许字段为空或不为空，默认为NullDefault指定字段的默认值Auto_Increment设置INT型字段能够自动生成递增1的整数约束说明PrimaryKey指定主键ForeignKey指定外键Index指定索引Unique指定唯一索引Fulltext指定全文索引12.2.2数据表操作2.修改表修改表的SQL语句格式：altertable表名操作1[，操作2，...]；可以对表的结构进行修改，包括添加、删除或修改字段，更改表名或类型等。操作包括add、change、modify、drop和rename等。3.复制表复制表的SQL语句格式：createtable新表名like原表名；

复制的内容包括表结构、表中的数据和约束。4.删除表删除表的SQL语句格式：droptable表名1[，表名2，...];用于删除一个或多个已存在的表，表结构和表中的数据都将被删除。12.2.3处理表数据1.插入记录插入记录的SQL语句格式：insertinto表名(字段1，字段2，...，字段n)values(字段1的值，字段2的值，...，字段n的值)；2.更新记录更新记录的SQL语句格式：update表名set字段名1=值，字段名2=值，...，字段名n=值，where匹配条件；3.查询记录查询记录的SQL语句格式：select字段名from表名where匹配条件；4.删除记录删除记录的SQL语句格式：deletefrom表名where匹配条件；12.2项目二：数据库的创建与使用操作过程：#mysql-uroot-pmysql>createdatabasecouman;mysql>showdatabases;mysql>usecouman;mysql>createtableemployee(enovarchar(10)notnull,enamevarchar(30)notnull,sexint(5)default0,groupsvarchar(20),primarykey(eno));mysql>describeemployee;mysql>createtableexam(exidvarchar(10)notnull,exnamevarchar(50)notnull,primarykey(exid));mysql>describeexam;12.2项目二：数据库的创建与使用mysql>createtablescore(scidint(10)notnullauto_increment,enovarchar(10)notnull,exidvarchar(10)notnull,scoreint(5),primarykey(scid),constraintforeignkey(exid)referencesexam(exid),constraintforeignkey(eno)referencesemployee(eno));mysql>describescore;mysql>showtables;mysql>insertintoemployee(eno,ename,sex,groups)values(1001,'wangli',1,'dgroup01');mysql>insertintoemployee(eno,ename,sex,groups)values(1002,'liuning',1,'dgroup01');mysql>insertintoemployee(eno,ename,sex,groups)values(1003,'zhangqi',0,'dgroup02');mysql>select*fromemployee;12.2项目二：数据库的创建与使用mysql>insertintoexam(exid,exname)values(10001,'test');mysql>insertintoexam(exid,exname)values(10002,'program');mysql>insertintoexam(exid,exname)values(10003,'english');mysql>select*fromexam；mysql>insertintoscore(eno,exid,score)values(1001,10001,87);mysql>insertintoscore(eno,exid,score)values(1001,10002,94);mysql>insertintoscore(eno,exid,score)values(1002,10003,72);mysql>insertintoscore(eno,exid,score)values(1003,10001,85);mysql>select*fromscore;12.3项目三：数据库的权限管理与备份管理员在创建完成couman数据库后，为了数据库的使用安全，对数据库的使用权限进行了授权，具体如下：（1）创建并授予dpuser0101用户对数据库的本地登录、数据查询（select）权限；（2）创建并授予dpuser0102用户对数据库的远程登录、数据库（couman）的所有权限；（3）创建并授予dpuser0201用户对数据库的远程登录，数据的增、删、改、查（INSERT、DELETE、UPDATESELECT）权限；（4）创建并授予dpuser0202用户对数据库的所有权限。同时为了保障数据库的意外恢复，管理员对couman数据库做了备份操作。序号知识点详见章节1数据库的权限管理12.3.12数据库的备份与恢复12.3.212.3.1数据库的权限管理1.数据库权限介绍与设置当首次安装MySQL时，MySQL的安装程序会自动创建一个名为mysql的数据库，该数据库中包含5与数据库授权相关的表(user、db、host、tables_priv、columns_priv)，这5个授权表决定用户连接服务器及操作的权限。表名说明user定义了允许连接MySQL服务器的用户名、密码和可以连接的主机db定义了连接到MySQL服务器的用户可以使用的数据库及在这些数据库中能进行的操作host如果db表中host字段为空，由host表定义可以连接到MySQL服务器的主机，并且不同的主机对指定的数据库可以有不同的权限tables_priv定义了连接到MySQL服务器的用户可以访问的具体的表，以及对表可执行的操作columents_priv定义了连接到MySQL服务器的用户可以访问表中的具体字段，以及对字段可执行的操作12.3.1数据库的权限管理MySQL数据库提供了两种方法修改授权表中的访问权限：可以使用insert、update和delete等SQL语句手工修改，或者使用grant和revoke命令。对比两种方法，后一种更加简单而方便。grant命令用于授予权限，而revoke命令用于撤销权限。grant命令的格式：grant权限级别[(字段名)]on数据库名.表名to用户名@域名或IP地址[Identifiedby'password'][withgrantoption];revoke命令的格式：revoke权限级别[(字段名)]on数据库名.表名from用户名@域名或IP地址;12.3.1数据库的权限管理在管理数据库权限时，通常可以基于以下几点来设置用户权限。①只赋予能满足用户需要的最小权限，防止用户操作不当。比如，用户只是需要查询数据信息，则只赋予SELECT权限，不用给用户赋予UPDATE、INSERT或DELETE权限。②创建用户时限制用户的登录主机，一般限制成指定IP地址或者内网IP地址段。③如果安装完数据库自动创建了无密码用户，则最好将这些用户删除。④为每个用户设置满足密码复杂度的密码。⑤定期清理不需要的用户，回收权限或者删除用户。12.3.1数据库的权限管理2.设置数据库权限案例(1)grant命令创建一个只允许从本地登录的用户jack，并允许将权限授予别的用户，密码为：Jack11!!.mysql>grantallprivilegeson*.*tojack@'localhost'identifiedby"Jack11!!"withgrantoption;(2)刷新权限使用flush命令可以使权限生效，尤其是当授权表user、db、host等做了update或者delete更新等操作时。mysql>flushprivileges;(3)查看权限查看当前用户的权限：mysql>showgrants;查看jack用户的权限：mysql>showgrantsfor'jack'@'localhost';12.3.1数据库的权限管理(4)回收权限回收jack用户的权限：mysql>revokedeleteon*.*from'jack'@'localhost';(5)对账户重命名将数据库用户jack修改为jim：mysql>renameuser'jack'@'localhost'to'jim'@'localhost';(6)删除用户删除MySQL用户jim：mysql>dropuser'jim'@'localhost';12.3.2数据库的备份与恢复在数据库管理的实际工作中，数据库的定期备份是一件至关重要的事情。通常管理员以批处理的方式进行数据库的自动备份，这样一旦数据库发生故障或者误操作时，可以进行数据库的恢复来保证服务器的正常运行。mysqldump命令可以用于数据库的备份。mysqldump命令格式：mysqldump备份数据库的名称--user=用户名--password=密码>备份的文件名例如