第12章MySQL服務(wù)器Linux系統(tǒng)管理與服務(wù)器配置——基于CentOS7目錄12.1項目一：MySQL服務(wù)器的安裝與啟動 12.1.1MySQL數(shù)據(jù)庫簡介 12.1.2安裝MySQL數(shù)據(jù)庫 12.1.3管理mysqld服務(wù) 12.1.4測試登錄MySQL數(shù)據(jù)庫 12.1.5重置MySQL數(shù)據(jù)庫管理員root密碼 12.2項目二：數(shù)據(jù)庫的創(chuàng)建與使用12.2.1數(shù)據(jù)庫操作 12.2.2數(shù)據(jù)表操作 12.2.3處理表數(shù)據(jù) 12.3項目三：數(shù)據(jù)庫的權(quán)限管理與備份 12.3.1數(shù)據(jù)庫的權(quán)限管理 12.3.2數(shù)據(jù)庫的備份與恢復(fù) 本章小結(jié) 12.1項目一：MySQL數(shù)據(jù)庫的安裝與啟動某公司設(shè)計部需要在服務(wù)器上部署一個用于發(fā)布項目考核信息的網(wǎng)站，在該網(wǎng)站上實現(xiàn)員工對考核內(nèi)容的選擇和考核成績的查詢等基本操作。為此，除需要部署前臺相關(guān)Web服務(wù)外，還需要部署后臺的數(shù)據(jù)庫服務(wù)。管理員選取目前比較常用的數(shù)據(jù)庫MySQL，因此需要對MySQL服務(wù)器進(jìn)行安裝并測試。序號知識點詳見章節(jié)1了解MySQL數(shù)據(jù)庫12.1.12安裝MySQL服務(wù)器的過程12.1.23管理mysqld服務(wù)12.1.34測試MySQL服務(wù)器12.1.45重置MySQL服務(wù)器管理員root密碼12.1.512.1.1MySQL數(shù)據(jù)庫簡介MySQL由瑞典MySQLAB公司開發(fā)，是一個開放源碼的小型關(guān)系數(shù)據(jù)庫管理系統(tǒng)。關(guān)系數(shù)據(jù)庫將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個大倉庫內(nèi)，這樣就增加了對數(shù)據(jù)庫的訪問速度并提高了靈活性。由于MySQL具有體積小、速度快、擁有成本低等特點，使許多中小型網(wǎng)站為了降低網(wǎng)站總體成本而選擇MySQL作為網(wǎng)站數(shù)據(jù)庫。12.1.2安裝MySQL數(shù)據(jù)庫1.YUM方式安裝MySQL服務(wù)器需要到/downloads/repo/yum/上下載MySQL的YUM庫文件：mysql57-community-release-el7-11.noarch.rpm，然后進(jìn)行安裝，安裝命令如下：#yumlocalinstallmysql57-community-release-el7-11.noarch.rpm使用下面的命令來檢查MySQL服務(wù)器的YUM庫安裝是否正確#yumrepolistenabled|grep"mysql“執(zhí)行#yuminstallmysql-community-server命令安裝MySQL服務(wù)器，在此過程中會安裝多個軟件包，直到出現(xiàn)安裝成功提示。12.1.2安裝MySQL數(shù)據(jù)庫2.源碼包方式安裝MySQL服務(wù)器采用源碼包方式安裝MySQL服務(wù)器的方法較復(fù)雜，這里以安裝MySQL5.7版本為例，需要從官網(wǎng)上下載含boost的源碼包，下載地址為https:///downloads/mysql/5.7.html，下載位置如圖12.1.2安裝MySQL數(shù)據(jù)庫2.源碼包方式安裝MySQL服務(wù)器下載完成后，在終端中依次完成如下操作：#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數(shù)據(jù)庫2.源碼包方式安裝MySQL服務(wù)器接下來繼續(xù)對MySQL進(jìn)行配置。首先，使用vim/etc/f命令編輯/etc/f文件，文件內(nèi)容如下：[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數(shù)據(jù)庫2.源碼包方式安裝MySQL服務(wù)器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數(shù)據(jù)庫2.源碼包方式安裝MySQL服務(wù)器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數(shù)據(jù)庫2.源碼包方式安裝MySQL服務(wù)器max_allowed_packet=16M[mysql]no-auto-rehash[myisamchk]key_buffer_size=32Msort_buffer_size=768Kread_buffer=2Mwrite_buffer=2M12.1.2安裝MySQL數(shù)據(jù)庫2.源碼包方式安裝MySQL服務(wù)器然后繼續(xù)執(zhí)行以下指令：#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//復(fù)制可執(zhí)行配置文件此時已完成MySQL的配置。12.1.3管理mysqld服務(wù)#systemctlstartmysqld.service//啟動mysqld服務(wù)#systemctlstopmysqld.service//停止mysqld服務(wù)#systemctlrestartmysqld.service//重新啟動mysqld服務(wù)#systemctlstatusmysqld.service//查看mysqld服務(wù)狀態(tài)#systemctlenablemysqld.service//設(shè)置mysqld服務(wù)開機(jī)自啟動#systemctldisablemysqld.service//禁止mysqld服務(wù)開機(jī)自啟動12.1.4測試登錄MySQLMySQL5.7在安裝過程中除了創(chuàng)建數(shù)據(jù)庫超級管理員“root@localhost”，還為他生成了默認(rèn)的密碼，該密碼保存在/var/log/mysqld.log文件中。因此在測試登錄MySQL之前需要到/var/log/mysqld.log文件中查看默認(rèn)密碼。取得該密碼后，再執(zhí)行#mysql命令登錄數(shù)據(jù)庫。mysql命令位于/usr/bin目錄下，用來登錄本地或遠(yuǎn)程的MySQL服務(wù)器。在登錄過程中輸入默認(rèn)的root密碼，出現(xiàn)mysql>提示符，表示登錄成功。12.1.5重置MySQL數(shù)據(jù)庫管理員root密碼測試登錄MySQL服務(wù)器成功后，需要重置root的密碼，才能執(zhí)行其他操作。在設(shè)置密碼時，注意MySQL服務(wù)器的默認(rèn)密碼策略要求密碼必須包含大小寫字母、數(shù)字和特殊符號，并且長度不能少于8位。12.1項目一：MySQL數(shù)據(jù)庫的安裝與啟動操作過程：由于CentOSLinux7默認(rèn)的YUM庫中沒有MySQL服務(wù)資源，因此需要到/downloads/repo/yum/網(wǎng)址去下載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項目二：數(shù)據(jù)庫的創(chuàng)建與使用在12.1項目基礎(chǔ)上，管理員準(zhǔn)備在MySQL數(shù)據(jù)庫中創(chuàng)建項目考核信息發(fā)布網(wǎng)站需要的數(shù)據(jù)庫（couman）和相關(guān)數(shù)據(jù)表。數(shù)據(jù)表包括：employee（員工信息）表、exam（考核內(nèi)容）表、scores（員工考核信息）表，各個表的結(jié)構(gòu)如下。employee表，字段：eno（員工號）、ename（姓名）、sex（性別）、groups（項目組）。exam表，字段：exid（內(nèi)容序號）、cname（考核內(nèi)容）。scores表，字段：scid（序號）、eno（員工號）、exid（內(nèi)容序號）、score（成績）。數(shù)據(jù)表創(chuàng)建成功后，管理員將對數(shù)據(jù)進(jìn)行增、刪、改、查的測試。序號知識點詳見章節(jié)1數(shù)據(jù)庫創(chuàng)建12.2.12數(shù)據(jù)表的創(chuàng)建12.2.23對數(shù)據(jù)的增刪改查操作12.2.312.2.1數(shù)據(jù)庫操作對數(shù)據(jù)庫的常用操作包括：創(chuàng)建數(shù)據(jù)庫、選擇數(shù)據(jù)庫和刪除數(shù)據(jù)庫。1.創(chuàng)建數(shù)據(jù)庫登錄數(shù)據(jù)庫后，使用“createdatabase數(shù)據(jù)庫名；”完成對數(shù)據(jù)庫的創(chuàng)建。然后可以使用“showdatabases；”命令來查看目前所有數(shù)據(jù)庫的信息。MySQL數(shù)據(jù)庫默認(rèn)自帶4個數(shù)據(jù)庫，information_schema數(shù)據(jù)庫存儲相關(guān)信息，mysql數(shù)據(jù)庫存儲授權(quán)表，performance_schema數(shù)據(jù)庫存儲數(shù)據(jù)庫性能參數(shù)，sys數(shù)據(jù)庫存儲數(shù)據(jù)庫性能數(shù)據(jù)，可以用來提升數(shù)據(jù)庫的性能。12.2.1數(shù)據(jù)庫操作2.選擇數(shù)據(jù)庫當(dāng)需要對某一數(shù)據(jù)庫進(jìn)行操作時，首先需要選擇該數(shù)據(jù)庫才能操作，使用“use數(shù)據(jù)庫名字；”命令來完成。3.刪除數(shù)據(jù)庫使用“deletedatabase數(shù)據(jù)庫名；”命令刪除指定數(shù)據(jù)庫，刪除數(shù)據(jù)庫時，連同數(shù)據(jù)庫里的所有數(shù)據(jù)表(包括其中的數(shù)據(jù))和數(shù)據(jù)庫目錄都將被刪除。12.2.2數(shù)據(jù)表操作1.創(chuàng)建表創(chuàng)建表的SQL語句格式：createtable表名(字段名1字段類型[字段約束]，

字段名2字段類型[字段約束]，...，

字段名n字段類型[字段約束]，[表約束])[Type|Engine=表類型|存儲引擎]；其中字段類型，也稱作列類型，規(guī)定了某個字段所允許輸入的數(shù)據(jù)類型。字段類型說明INT整形，4個字節(jié)FLOAT數(shù)值類型，支持浮點數(shù)或小數(shù)DOUBLE數(shù)值類型，支持雙精度浮點數(shù)TIMEHH:MM:SS格式的時間字段DATEYYYYMMDD格式的日期字段CHAR字符型，最大長度為255VARCHAR字符串類型，最大長度為255YEARYYYY或YY格式的年字段12.2.2數(shù)據(jù)表操作字段約束用于進(jìn)一步約束某個字段允許輸入的數(shù)據(jù)。表約束用于確定表的主鍵、外鍵和索引等約束說明Null(或

NotNull)允許字段為空或不為空，默認(rèn)為NullDefault指定字段的默認(rèn)值A(chǔ)uto_Increment設(shè)置INT型字段能夠自動生成遞增1的整數(shù)約束說明PrimaryKey指定主鍵ForeignKey指定外鍵Index指定索引Unique指定唯一索引Fulltext指定全文索引12.2.2數(shù)據(jù)表操作2.修改表修改表的SQL語句格式：altertable表名操作1[，操作2，...]；可以對表的結(jié)構(gòu)進(jìn)行修改，包括添加、刪除或修改字段，更改表名或類型等。操作包括add、change、modify、drop和rename等。3.復(fù)制表復(fù)制表的SQL語句格式：createtable新表名like原表名；

復(fù)制的內(nèi)容包括表結(jié)構(gòu)、表中的數(shù)據(jù)和約束。4.刪除表刪除表的SQL語句格式：droptable表名1[，表名2，...];用于刪除一個或多個已存在的表，表結(jié)構(gòu)和表中的數(shù)據(jù)都將被刪除。12.2.3處理表數(shù)據(jù)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項目二：數(shù)據(jù)庫的創(chuàng)建與使用操作過程：#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項目二：數(shù)據(jù)庫的創(chuàng)建與使用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項目二：數(shù)據(jù)庫的創(chuàng)建與使用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項目三：數(shù)據(jù)庫的權(quán)限管理與備份管理員在創(chuàng)建完成couman數(shù)據(jù)庫后，為了數(shù)據(jù)庫的使用安全，對數(shù)據(jù)庫的使用權(quán)限進(jìn)行了授權(quán)，具體如下：（1）創(chuàng)建并授予dpuser0101用戶對數(shù)據(jù)庫的本地登錄、數(shù)據(jù)查詢（select）權(quán)限；（2）創(chuàng)建并授予dpuser0102用戶對數(shù)據(jù)庫的遠(yuǎn)程登錄、數(shù)據(jù)庫（couman）的所有權(quán)限；（3）創(chuàng)建并授予dpuser0201用戶對數(shù)據(jù)庫的遠(yuǎn)程登錄，數(shù)據(jù)的增、刪、改、查（INSERT、DELETE、UPDATESELECT）權(quán)限；（4）創(chuàng)建并授予dpuser0202用戶對數(shù)據(jù)庫的所有權(quán)限。同時為了保障數(shù)據(jù)庫的意外恢復(fù)，管理員對couman數(shù)據(jù)庫做了備份操作。序號知識點詳見章節(jié)1數(shù)據(jù)庫的權(quán)限管理12.3.12數(shù)據(jù)庫的備份與恢復(fù)12.3.212.3.1數(shù)據(jù)庫的權(quán)限管理1.數(shù)據(jù)庫權(quán)限介紹與設(shè)置當(dāng)首次安裝MySQL時，MySQL的安裝程序會自動創(chuàng)建一個名為mysql的數(shù)據(jù)庫，該數(shù)據(jù)庫中包含5與數(shù)據(jù)庫授權(quán)相關(guān)的表(user、db、host、tables_priv、columns_priv)，這5個授權(quán)表決定用戶連接服務(wù)器及操作的權(quán)限。表名說明user定義了允許連接MySQL服務(wù)器的用戶名、密碼和可以連接的主機(jī)db定義了連接到MySQL服務(wù)器的用戶可以使用的數(shù)據(jù)庫及在這些數(shù)據(jù)庫中能進(jìn)行的操作host如果db表中host字段為空，由host表定義可以連接到MySQL服務(wù)器的主機(jī)，并且不同的主機(jī)對指定的數(shù)據(jù)庫可以有不同的權(quán)限tables_priv定義了連接到MySQL服務(wù)器的用戶可以訪問的具體的表，以及對表可執(zhí)行的操作columents_priv定義了連接到MySQL服務(wù)器的用戶可以訪問表中的具體字段，以及對字段可執(zhí)行的操作12.3.1數(shù)據(jù)庫的權(quán)限管理MySQL數(shù)據(jù)庫提供了兩種方法修改授權(quán)表中的訪問權(quán)限：可以使用insert、update和delete等SQL語句手工修改，或者使用grant和revoke命令。對比兩種方法，后一種更加簡單而方便。grant命令用于授予權(quán)限，而revoke命令用于撤銷權(quán)限。grant命令的格式：grant權(quán)限級別[(字段名)]on數(shù)據(jù)庫名.表名to用戶名@域名或IP地址[Identifiedby'password'][withgrantoption];revoke命令的格式：revoke權(quán)限級別[(字段名)]on數(shù)據(jù)庫名.表名from用戶名@域名或IP地址;12.3.1數(shù)據(jù)庫的權(quán)限管理在管理數(shù)據(jù)庫權(quán)限時，通?？梢曰谝韵聨c來設(shè)置用戶權(quán)限。①只賦予能滿足用戶需要的最小權(quán)限，防止用戶操作不當(dāng)。比如，用戶只是需要查詢數(shù)據(jù)信息，則只賦予SELECT權(quán)限，不用給用戶賦予UPDATE、INSERT或DELETE權(quán)限。②創(chuàng)建用戶時限制用戶的登錄主機(jī)，一般限制成指定IP地址或者內(nèi)網(wǎng)IP地址段。③如果安裝完數(shù)據(jù)庫自動創(chuàng)建了無密碼用戶，則最好將這些用戶刪除。④為每個用戶設(shè)置滿足密碼復(fù)雜度的密碼。⑤定期清理不需要的用戶，回收權(quán)限或者刪除用戶。12.3.1數(shù)據(jù)庫的權(quán)限管理2.設(shè)置數(shù)據(jù)庫權(quán)限案例(1)grant命令創(chuàng)建一個只允許從本地登錄的用戶jack，并允許將權(quán)限授予別的用戶，密碼為：Jack11!!.mysql>grantallprivilegeson*.*tojack@'localhost'identifiedby"Jack11!!"withgrantoption;(2)刷新權(quán)限使用flush命令可以使權(quán)限生效，尤其是當(dāng)授權(quán)表user、db、host等做了update或者delete更新等操作時。mysql>flushprivileges;(3)查看權(quán)限查看當(dāng)前用戶的權(quán)限：mysql>showgrants;查看jack用戶的權(quán)限：mysql>showgrantsfor'jack'@'localhost';12.3.1數(shù)據(jù)庫的權(quán)限管理(4)回收權(quán)限回收jack用戶的權(quán)限：mysql>revokedeleteon*.*from'jack'@'localhost';(5)對賬戶重命名將數(shù)據(jù)庫用戶jack修改為jim：mysql>renameuser'jack'@'localhost'to'jim'@'localhost';(6)刪除用戶刪除MySQL用戶jim：mysql>dropuser'jim'@'localhost';12.3.2數(shù)據(jù)庫的備份與恢復(fù)在數(shù)據(jù)庫管理的實際工作中，數(shù)據(jù)庫的定期備份是一件至關(guān)重要的事情。通常管理員以批處理的方式進(jìn)行數(shù)據(jù)庫的自動備份，這樣一旦數(shù)據(jù)庫發(fā)生故障或者誤操作時，可以進(jìn)行數(shù)據(jù)庫的恢復(fù)來保證服務(wù)器的正常運行。mysqldump命令可以用于數(shù)據(jù)庫的備份。mysqldump命令格式：mysqldump備份數(shù)據(jù)庫的名稱--user=用戶名--password=密碼>備份的文件名例如