6Linux網絡服務之Mysql資料庫服務

MySQL資料庫系統

一 MySQL簡介

提供更豐富更強大的Web應用功能

參考站點：

二 MySQL服務基礎

1 MySQL是開源的關系型資料庫伺服器軟體

目前由Oracle公司開發和維護

官方站點：http://dev.mysql.com/downloads

2 MySQL的特點

多線程、多使用者

基于C/S（用戶端/伺服器）架構

簡單易用、查詢速度快

安全可靠

三 MySQL的編譯安裝

1 準備工作

1）為了避免發生端口沖突，程式沖突，建議先檢查mysql軟體的安裝情況，确認沒有使用RPM方式安裝的mysql-server、mysql軟體包，否則建議将其解除安裝。

:rpm -q mysql-server mysql //确認沒有使用RPM方式安裝mysql-server、mysql

1. 源碼安裝需要有開發環境

:rpm -q ncurses* //确認編譯的開發環境工具有沒有安裝

:yum install ncurses* //安裝開發工具的編譯環境

2 源碼編譯及安裝

1）建立運作使用者

為了加強資料庫服務的權限控制，建議使用專門的運作使用者，使用者不需要登入系統，可以不建立宿主檔案夾

:useradd -M -u 49 -s /sbin/nologin mysql

1. 解包

将下載下傳的mysql源碼安裝包解壓，釋放到/usr/src/目錄下，并切換到源碼目錄

:mount /dev/cdrom /media //挂載安裝包

:cd /media/

:tar zxf mysql-5.1.55.tar.gz -C /usr/src/ //解包

:cd /usr/src/mysql-5.1.55/

1. 配置

企業網站平台中，可能會用到多種字元集的網頁，相應的資料庫系統也應該支援不同的字元集編碼，在配置過程中，可以将預設使用的字元集設定為utf8，并添加對gbk、gb2312等其他字元集的支援

:./configure --prefix=/usr/local/mysql --with-charset=utf8 --with-collation=utf8_general_ci --with-extra-charsets=gbk,gb2312 //配置

上述指令中各選項含義如下

--prefix 指定将mysql資料庫程式安裝到哪個目錄，如/usr/src/mysql

--with-charset 指定預設使用的字元集編碼，應與校隊規則相對應，如utf8

--with-collation 指定預設使用的字元集校隊規則

--with-extra-charset 指定額外支援的其他字元集編碼

4）調整字元集編碼

額外的字元集編碼需要進行小的調整才能被完整的支援

:vim include/config.h //添加字元集的支援定義

..............

#define HAVE_CHARSET_gb2312 1

#define HAVE_CHARSET_gbk 1

1. 編譯

:make //編譯

6）安裝

:make install //安裝

3 安裝後的其他調整

1. 建立配置檔案

在mysql源碼目錄中的support-files檔案下，提供了适合不同負責資料庫的樣本配置檔案。如果不确定資料庫系統的應用規模，一般選擇my-medium.cnf檔案即可。該檔案能夠滿足大多數企業中、的中等應用需求。根據樣例建立Mysql系統中的/etc/my.cnt配置檔案

:cp support-files/my-medium.cnf /etc/my.cnf //建立配置檔案

1. 初始化資料庫

為了能夠正常使用Mysql資料庫系統，應以運作使用者mysql的身份執行初始化任務，以便建立使用者授權庫、表、以及test測試庫等。初始化腳本mysql_install_db位于安裝目錄下的bin檔案夾中，此外還應該調整位于存放資料庫内容的檔案夾歸屬，以便mysql使用者有權限對其進行讀寫操作

:cd /usr/local/mysql/bin/

: ./mysql_install_db --user=mysql //初始化資料庫

: chown -R root:mysql /usr/local/mysql/

: chown -R mysql /usr/local/mysql/var/

1. 優化執行路徑、程式庫路徑

為Mysql系統的各種執行程式添加符号連結，以友善其使用。另外建議為Mysql的庫檔案、頭檔案也添加符号連結，便于在安裝其他用的到的這些檔案的軟體包時能自動搜尋

:ln -s /usr/local/mysql/bin/* /usr/local/bin/ 連結很重要，否則登入時會出問題

:ln -s /usr/local/mysql/lib/mysql/* /usr/lib/

:ln -s /usr/local/mysql/include/mysql/* /usr/include/

4）添加為系統服務

以便通過chkconfig進行管理，将添加mysql為系統服務，這樣以後可以使用service工具來控制Mysql資料庫服務了

cd /usr/src/mysql-5.1.55/

cp support-files/mysql.server /etc/rc.d/init.d/mysqld

chmod a+x /etc/rc.d/init.d/mysqld

chkconfig --add mysqld

1. 啟動mysqld服務，并檢視運作狀态

:/etc/init.d/mysqld start 啟動

:/etc/init.d/mysqld status 檢視運作狀态

::netstat -anpt | grep mysqld 檢視TCP 3306端口号

4 通路Mysql資料庫

Mysql資料庫系統也是一個典型的C/S架構應用，需要通路Mysql資料庫需要使用專門的用戶端軟體。在windows平台可以使用Mysql Manager、Mysql Front、Navicat Lite等軟體；而在Linux系統中，最簡單、最易用的Mysql用戶端軟體是其自帶的mysql指令工具

1. 登入到mysql伺服器

mysql -u 使用者名 [-p]

提示符：mysql>

管理者賬号為root，密碼為空

: mysql -u root //空密碼登入

: mysqladmin -u root password "123456” //設定密碼

: mysql -u root -p //使用密碼登入

::mysql -h 192.168.1.100 -u root -p //使用-h來指定登入其他伺服器

2) 退出MySQL操作環境

:exit 或

:quit

四 資料庫的基本管理

1 Mysql操作語句

成功登陸後會進入到提示符為“mysql>”的資料庫操作環境，使用者可以輸入各種操作語句進行管理，每一條操作語句以分号表示結束，輸入時可不區分大小寫

2 檢視資料庫服務的日志檔案

SHOW MASTER LOG;

3 檢視資料庫結構

Mysql是一套資料庫管理系統，在每一台Mysql伺服器中，支援運作多個庫，每個庫相當于一個容器，其中存放着很多個表，表中的每一行都包含一條資料記錄

1. 檢視資料庫清單資訊

預設有三個庫，其中mysql庫中包含了使用者認證相關的表

SHOW DATABASES;

1. 檢視資料庫中的資料表資訊

在操作前，先使用“use 資料庫名”語句切換到指定庫，資料庫的資料檔案預設存放在/usr/local/mysql/var/目錄下。每一個資料庫對應一個子目錄，用于存儲資料表檔案，每一個資料表對應三個檔案，字尾名分别為“.frm”、“.MYD”、“MYI”

USE mysql;

SHOW TABLES;

1. 顯示資料表的結構

使用“DESCRIBE [資料庫名.]表名”的語句對指定表進行檢視

DESCRIBE USER; 或者

DESCRIBE mysql.user;

4 資料庫的建立和删除

1. 建立新的資料庫

使用“CREATE DATABASE 資料庫名”語句建立新庫，剛建立的資料庫是空的，不包含任何表，在/usr/local/mysql/var/目錄下會在動生成一個與庫名相同的空檔案夾

create datebase newname;

1. 建立新的資料表

CREATE TABLE 表名 (字段1名稱 類型，字段2名稱 類型......,PRIMARY KEY (主鍵名))；

user newname

create table users (user_name char(16) not null, user_passwd char(48) default ‘’, primary key (user_name)); //在name庫中建立名為users的使用者驗證表

3）删除指定的資料表

DROP TABLE [資料庫名.]表名

drop table newname users;

4）删除指定的資料庫

DROP DATABASE 資料庫名

drop database newname;

5 管理表中的資料記錄

1. 插入資料記錄

INSERT INTO 表名(字段1, 字段2, ……) VALUES (字段1的值, 字段2的值, ……)

如果這條記錄完整的包含所有字段值，則可以省略指定字段的部分

use newname;

insert into users (user_name,user_passwd) values (‘zhangsan’,password (‘123456’));

Insert into users values (‘lisi’,password (‘123456’)); //“（”前面都有空格

1. 查詢資料記錄

SELECT 字段名1,字段名2 …… FROM 表名 WHERE 條件表達式

表示所有字段時可以使用通配符“*”，若要列出所有資料記錄，則可以省略where條件句子

SELECT user_name,user_passwd FROM newname.users where user_name= 'lisi';

select user_name,user_passwd from newname.users； //檢視指定字段所有資料記錄

select * from newname.users; //檢視所有字段所有資料記錄

1. 修改資料記錄

UPDATE 表名 SET 字段名1=值1[,字段名2=值2] WHERE 條件表達式

UPDATE newname.users SET user_passwd=PASSWORD('') WHERE user_name='lisi';

在Mysql資料庫伺服器中，用于通路資料庫的各種使用者資訊都儲存在mysql庫的user表中，可以直接修改其中的資料記錄來修改使用者密碼

update mysql.user set password=password ('456789') where user='root'; //修改root密碼

flush privileges; //重新整理使用者授權資訊

1. 删除資料記錄

DELETE FROM 表名 WHERE 條件表達式

delete from newname.users； //删除表中所有資料記錄

delete from newname.users where user_name=’lisi’； //删除lisi的資料記錄

在Mysql資料庫伺服器中，預設添加了從本機通路資料庫的空使用者，基于安全性考慮，應該删除這些空使用者

select user,host,password from mysql.user where user=''; //檢視空使用者

delete from mysql.user where user=''; //删除空使用者

五 維護Mysql資料庫

1 資料庫的使用者授權

1. 設定使用者權限（使用者不存在時，則建立使用者）

GRANT 權限清單 ON 資料庫名.表名 TO 使用者名@來源位址 [ IDENTIFIED BY ‘密碼’ ]

權限清單：用于列出授權使用的各種資料庫操作，以逗号分隔。如select，insert，update等，使用all表示所有權限

庫名.表名：指定授權操作的庫和表的名稱，其中可以使用通配符來表示所有庫或指定庫中的所有表。如newname.*

使用者@來源位址：用于指定客戶名稱和允許通路的客戶機位址，即誰能連結，能從哪裡連結，來源位址可以是域名、IP、還可以使用%，表示某個區域或網段内的所有位址。如%.benet.com、192.168.1.%等

IDENTIFIED BY：若建立使用者時，省略此項，則使用者密碼為空

執行以下指令建立對表有查詢權限的名為mazi,密碼為123456的使用者，記錄會儲存到mysql庫的user、db、host、tables_priv等相關表中，無需重新整理

grant select on newname.* to 'mazi'@'localhost' identified by '123456';

1. 檢視權限

SHOW GRANTS FOR 使用者名@域名或IP

Show grants for ‘mazi’@’192.168.1.100’;

1. 撤銷權限

REVOKE 權限清單 ON 資料庫名.表名 FROM 使用者名@域名或IP

revoke all on newname.* from 'mazi'@'localhost'; //取消使用者所有權限，可以登入mysql伺服器

2 資料庫的備份與恢複

1. 備份資料庫

方法1：可直接備份目錄 /usr/local/mysql/var/

tar -jcf mysql.tar.bz2 /usr/local/mysql/var/

方法2：使用專用備份工具 mysqldump

mysqldump -u 使用者名 -p [密碼] [選項] [資料庫名] [表名] > /備份路徑/備份檔案名

1. 導出指定庫的部分表(備份mysql庫中的user表)

mysqldump -u root -p mysql user > mysql-user.sql

1. 導出一個或多個完整的庫（備份auth這個庫）

mysqldump -u root -p --database auth > auth.sql

1. 備份所有的庫（--opts可以優化執行速度）

mysqldump -u root -p --opt --all-databases > all-data.sql

D)檢視備份檔案内容

通過mysqldumo工具導出的sql腳本是文本檔案，其中‘/*……*/’或‘——’開頭的是注釋

grep -v "^--" all-data.aql | grep -v "^/" | grep -v "^$

1. 恢複資料庫

mysql -u root -p [資料庫名] < /備份路徑/備份檔案名

mysql -u root -p test < mysql-user.sql //恢複表到現有庫中

mysql -u root -p < all-data.sql //恢複資料庫