第12章MySQL服務器Linux系統(tǒng)管理與服務器配置——基于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項目二：數據庫的創(chuàng)建與使用12.2.1數據庫操作 12.2.2數據表操作 12.2.3處理表數據 12.3項目三：數據庫的權限管理與備份 12.3.1數據庫的權限管理 12.3.2數據庫的備份與恢復 本章小結 12.1項目一：MySQL數據庫的安裝與啟動某公司設計部需要在服務器上部署一個用于發(fā)布項目考核信息的網站，在該網站上實現員工對考核內容的選擇和考核成績的查詢等基本操作。為此，除需要部署前臺相關Web服務外，還需要部署后臺的數據庫服務。管理員選取目前比較常用的數據庫MySQL，因此需要對MySQL服務器進行安裝并測試。序號知識點詳見章節(jié)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公司開發(fā)，是一個開放源碼的小型關系數據庫管理系統(tǒng)。關系數據庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了對數據庫的訪問速度并提高了靈活性。由于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“執(zhí)行#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服務器接下來繼續(xù)對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服務器然后繼續(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//復制可執(zhí)行配置文件此時已完成MySQL的配置。12.1.3管理mysqld服務#systemctlstartmysqld.service//啟動mysqld服務#systemctlstopmysqld.service//停止mysqld服務#systemctlrestartmysqld.service//重新啟動mysqld服務#systemctlstatusmysqld.service//查看mysqld服務狀態(tài)#systemctlenablemysqld.service//設置mysqld服務開機自啟動#systemctldisablemysqld.service//禁止mysqld服務開機自啟動12.1.4測試登錄MySQLMySQL5.7在安裝過程中除了創(chuàng)建數據庫超級管理員“root@localhost”，還為他生成了默認的密碼，該密碼保存在/var/log/mysqld.log文件中。因此在測試登錄MySQL之前需要到/var/log/mysqld.log文件中查看默認密碼。取得該密碼后，再執(zhí)行#mysql命令登錄數據庫。mysql命令位于/usr/bin目錄下，用來登錄本地或遠程的MySQL服務器。在登錄過程中輸入默認的root密碼，出現mysql>提示符，表示登錄成功。12.1.5重置MySQL數據庫管理員root密碼測試登錄MySQL服務器成功后，需要重置root的密碼，才能執(zhí)行其他操作。在設置密碼時，注意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項目二：數據庫的創(chuàng)建與使用在12.1項目基礎上，管理員準備在MySQL數據庫中創(chuàng)建項目考核信息發(fā)布網站需要的數據庫（couman）和相關數據表。數據表包括：employee（員工信息）表、exam（考核內容）表、scores（員工考核信息）表，各個表的結構如下。employee表，字段：eno（員工號）、ename（姓名）、sex（性別）、groups（項目組）。exam表，字段：exid（內容序號）、cname（考核內容）。scores表，字段：scid（序號）、eno（員工號）、exid（內容序號）、score（成績）。數據表創(chuàng)建成功后，管理員將對數據進行增、刪、改、查的測試。序號知識點詳見章節(jié)1數據庫創(chuàng)建12.2.12數據表的創(chuàng)建12.2.23對數據的增刪改查操作12.2.312.2.1數據庫操作對數據庫的常用操作包括：創(chuàng)建數據庫、選擇數據庫和刪除數據庫。1.創(chuàng)建數據庫登錄數據庫后，使用“createdatabase數據庫名；”完成對數據庫的創(chuàng)建。然后可以使用“showdatabases；”命令來查看目前所有數據庫的信息。MySQL數據庫默認自帶4個數據庫，information_schema數據庫存儲相關信息，mysql數據庫存儲授權表，performance_schema數據庫存儲數據庫性能參數，sys數據庫存儲數據庫性能數據，可以用來提升數據庫的性能。12.2.1數據庫操作2.選擇數據庫當需要對某一數據庫進行操作時，首先需要選擇該數據庫才能操作，使用“use數據庫名字；”命令來完成。3.刪除數據庫使用“deletedatabase數據庫名；”命令刪除指定數據庫，刪除數據庫時，連同數據庫里的所有數據表(包括其中的數據)和數據庫目錄都將被刪除。12.2.2數據表操作1.創(chuàng)建表創(chuàng)建表的SQL語句格式：createtable表名(字段名1字段類型[字段約束]，

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

字段名n字段類型[字段約束]，[表約束])[Type|Engine=表類型|存儲引擎]；其中字段類型，也稱作列類型，規(guī)定了某個字段所允許輸入的數據類型。字段類型說明INT整形，4個字節(jié)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項目二：數據庫的創(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項目二：數據庫的創(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項目二：數據庫的創(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項目三：數據庫的權限管理與備份管理員在創(chuàng)建完成couman數據庫后，為了數據庫的使用安全，對數據庫的使用權限進行了授權，具體如下：（1）創(chuàng)建并授予dpuser0101用戶對數據庫的本地登錄、數據查詢（select）權限；（2）創(chuàng)建并授予dpuser0102用戶對數據庫的遠程登錄、數據庫（couman）的所有權限；（3）創(chuàng)建并授予dpuser0201用戶對數據庫的遠程登錄，數據的增、刪、改、查（INSERT、DELETE、UPDATESELECT）權限；（4）創(chuàng)建并授予dpuser0202用戶對數據庫的所有權限。同時為了保障數據庫的意外恢復，管理員對couman數據庫做了備份操作。序號知識點詳見章節(jié)1數據庫的權限管理12.3.12數據庫的備份與恢復12.3.212.3.1數據庫的權限管理1.數據庫權限介紹與設置當首次安裝MySQL時，MySQL的安裝程序會自動創(chuàng)建一個名為mysql的數據庫，該數據庫中包含5與數據庫授權相關的表(user、db、host、tables_priv、columns_priv)，這5個授權表決定用戶連接服務器及操作的權限。表名說明user定義了允許連接MySQL服務器的用戶名、密碼和可以連接的主機db定義了連接到MySQL服務器的用戶可以使用的數據庫及在這些數據庫中能進行的操作host如果db表中host字段為空，由host表定義可以連接到MySQL服務器的主機，并且不同的主機對指定的數據庫可以有不同的權限tables_priv定義了連接到MySQL服務器的用戶可以訪問的具體的表，以及對表可執(zhí)行的操作columents_priv定義了連接到MySQL服務器的用戶可以訪問表中的具體字段，以及對字段可執(zhí)行的操作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數據庫的權限管理在管理數據庫權限時，通?？梢曰谝韵聨c來設置用戶權限。①只賦予能滿足用戶需要的最小權限，防止用戶操作不當。比如，用戶只是需要查詢數據信息，則只賦予SELECT權限，不用給用戶賦予UPDATE、INSERT或DELETE權限。②創(chuàng)建用戶時限制用戶的登錄主機，一般限制成指定IP地址或者內網IP地址段。③如果安裝完數據庫自動創(chuàng)建了無密碼用戶，則最好將這些用戶刪除。④為每個用戶設置滿足密碼復雜度的密碼。⑤定期清理不需要的用戶，回收權限或者刪除用戶。12.3.1數據庫的權限管理2.設置數據庫權限案例(1)grant命令創(chuàng)建一個只允許從本地登錄的用戶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數據庫的備份與恢復在數據庫管理的實際工作中，數據庫的定期備份是一件至關重要的事情。通常管理員以批處理的方式進行數據庫的自動備份，這樣一旦數據庫發(fā)生故障或者誤操作時，可以進行數據庫的恢復來保證服務器的正常運行。mysqldump命令可以用于數據庫的備份。mysqldump命令格式：mysqldump備份數據庫的名稱--user=用戶名--password=密碼>備份的文件名例如