動作

MYSQL

出自 Itsmw

MySQL簡介與管理

本篇文章, 主要是在記錄一些 MySQL 的疑難雜症



基本觀念(必讀)

  • 主設定: /etc/mysql/my.cnf
  • 開啟埠
    • 對外:TCP 3306
    • 對本機:不必開通訊埠
    • 註:若 PHP 程式碼與 MySQL Server 位於同一台機器,可以把 3306 埠關閉
  • 記錄檔: /var/log/mysql.log
  • 系統資料庫: /var/lib/mysql/mysql
    • 各 MySQL 版本的系統資料庫格式不一樣,若錯用會導致資料庫無法啟動
    • 若此一資料庫被誤刪,可用 mysql_install_db 指令建立
  • 資料庫
    • 位置: /var/lib/mysql
    • 類型:MySQL 支援多種資料庫類型,比較常用的兩種,一為支援 row lock 的 InnoDB;另一為預設的 MyISAM
  • MyISAM資料庫解說(預設)
    • /var/lib/mysql 內每個資料夾皆是一組資料庫
    • 資料夾及其檔案之擁有者為 mysql.mysql
    • 資料夾內同名字之三個檔案組成一個資料表
      *.frm --> 欄位定義
      *.MYD --> 實體資料
      *.MYI --> 資料索引
  • InnoDB 簡介
    • 支援列鎖定(Row Lock),可防止兩支程式同時修改同一筆資料,對商業運作資料之保護力較好。
    • InnoDB 資料庫
      • 資料庫格式(欄位定義): /var/lib/mysql/ 底下資料庫同名之資料夾。
      • 資料庫實體資料,存在 /var/lib/mysql 底下的 ibdata1
    • 記錄檔:/var/lib/mysql/ib_logfile*




MySQL 管理

MySQL系統管理

  • 關於 MySQL 管理幾個基本觀念
    • MySQL 有自己的帳號密碼
    • MySQL 最高權限帳號與 Linux 一樣是 root ,但密碼不同(除非設成一樣)
    • MySQL 的帳號密碼是給網頁程式碼(ex. php)連線用,使用者使用的機會,只僅限 phpmyadmin 連線時才會需要
    • OB2D 上所設定的管理密碼(ob2d-csetup-utf8)就是 MySQL 的 root 密碼


  • 啟動/關閉/重新啟動
    root@dns:~# service mysql start|stop|restart


  • 初始化系統資料庫
    root@dns:~# mysql_install_db
    註:本功能僅限 /var/lib/mysql/mysql 已被誤刪的情況下才可以使用


  • 初設 MySQL root 密碼
    root@dns:~# mysqladmin -u root -p password
    註1:輸入 Linux root 密碼後即才可繼續輸入 MySQL 密碼
    註2:OB2D 本動作不必做,因為 ob2d-csetup-utf8 己幫忙處理過了。


  • 修復資料表索引
    root@dns:~# mysqlcheck -u root -p -f 資料庫名 資料表名



忘記 MySQL 密碼

  • 方法一:執行 ob2d-csetup-utf8,重設管理密碼
    注意:它除了會幫您重設密碼外,也會額外重設許多設定,因此不太建議使用此方法。
  • 方法二:直接使用 MySQL 工具重設
  1. 停止 MySQL
    root@dns:~# service mysql stop
  2. 啟動無密碼可登入的 MySQL
    root@dns:~# mysqld_safe --skip-grant-tables &
  3. 進入 mysql 指令模式
    root@dns:~# mysql
  4. 直接修改 mysql user 表,設 root 密碼
    root@dns:~# Update `mysql`.`user` Set `password` = PASSWORD('新密碼') Where `user` = 'root';
  5. 關閉 MySQL
    root@dns:~# service mysql stop
  6. 正常啟動 MySQL
    root@dns:~# service mysql start
    註:重新啟動後 MySQL root 密碼已被重設



資料庫管理

 整個資料庫管理,建議使用 phpmyadmin 網頁工具,簡介如下:

  • phpmyadmin 安裝
    root@dns:~# apt-get update
    root@dns:~# apt-get install phpmyadmin
    註:大多數的 OB2D 多已安裝此套件


  • phpmyadmin 使用:直接使用網頁瀏覽器,連結至 phpmyadmin 網站即可
    舉例:http://your.host.name/phpmyadmin
    連線後,以 MySQL 的帳號密碼進入
    • 只有用 root 登入,才會有全部功能
    • 可以透過 GRANT 語法,建立特定帳號,而它只能掌控單一資料庫(詳見本文:建立虛擬站台段落)


  • 資料庫搬家
    • B2D 系列 Server 之 MySQL 資料庫皆在 /var/lib/mysql
    • 只要下指令把 /var/lib/mysql 底下之資料庫(夾)複製至新主機相同之位置,並修改權限擁有者為 mysql 即可完成搬移任務。以 sfs3 資料庫為例,做法如下:
    1. 複製
      root@dns:~# scp root@your.old.host:/var/lib/mysql/sfs3 /var/lib/mysql
    2. 修改擁有者
      root@dns:~# chown -R mysql.mysql /var/lib/mysql/sfs3



MySQL User 權限管理

若要限定某個 MySQL user 只能取用自己的資料庫,可使用下列之 SQL 語法。

GRANT ALL PRIVILEGES
   ON myuser.*
   TO 'myuser'@'localhost'
   IDENTIFIED BY 'myuser_pwd';



一些小問題FAQ

開啟整個 C Class 皆可遠端連線

語法示例如下:

GRANT ALL PRIVILEGES
   ON myuser.*
   TO 'myuser'@'120.116.12.%'
   IDENTIFIED BY 'myuser_pwd';



開啟遠端連線後,連接的速度很慢

修改 /etc/mysql/my.cnf
在 [mysqld] 底下新增「skip-name-resolve」,示例如下:

[mysqld] 
skip-name-resolve

注意,加上此設定後,MySQL 不會做正反解,因此允許連線的網址必須強制使用 ip address 才行。



PHP部份

phpMyAdmin

預設為繁體中文

  • 修改 config.inc.php 新增以下設定到檔尾
$cfg['Lang'] = 'zh_TW';
  • Ubuntu 官方套件 config.inc.php 位置:/etc/phpmyadmin



PHP5+MySQL5使用AdoDB5

由於 PHP5 + MySQL5 的情況下, 要改用 AdoDB 第五版以上才可以正常運作。但下載回的 adodb5 , 仍有 「同一 php code 內無法同時 connect 至兩組 database 」 的問題。要解決此一問題, 要修改 adodb5/drivers/adodb-mysql.inc.php

class ADODB_mysql extends ADOConnection {
	//加上這一行
	var $__db = array();
		:
		:
		:
	//修改以下兩組 function 成本文所述的樣子
	function SelectDB($dbName)
	{
		$this->database = $dbName;
		$this->databaseName = $dbName;
		if ($this->_connectionID) {
			@mysql_select_db($dbName,$this->_connectionID);
			$this->__db[$this->_connectionID] = $dbName;
			return $this->_connectionID;
		}
		else return false;
	}

	function _query($sql,$inputarr)
	{
	//global $ADODB_COUNTRECS;
		//if($ADODB_COUNTRECS)
		$this->SelectDB($this->__db[$this->_connectionID]);
		return mysql_query($sql, $this->_connectionID);
		//else return @mysql_unbuffered_query($sql,$this->_connectionID);
		// requires PHP >= 4.0.6
	}


同一PHP code 混用 big5 及 utf8 資料庫

UTF8 編碼的 PHP code 混用 big5 及 utf8 資料庫時,統一以 utf8 輸出網頁注意事項

  • 假設 AdoDB5
    • $conn1 連到 utf8 編碼的資料庫
    • $conn2 連到 big5 編碼的資料庫
  • 執行 SELECT 語法讀取資料庫時
    • 在執行 $conn1 的 SQL 語法前,優先執行: $conn1 -> Execute("SET NAMES 'utf8'");
    • 在執行 $conn2 的 SQL 語法前,優先執行: $conn2 -> Execute("SET NAMES 'latin1'");
    • 註:PHP5 的 libmysql 預設以 latin1 向 mysql 讀取資料, 因此, 在讀取 utf8 資料庫時, 先 Set names utf8, 否則會出現一大堆問號????
  • conn2讀出的資料記得轉成 utf8 碼
    • $row[1]=iconv("big5","utf-8",addslashes($row[1]));



SQL語法

把 A 表的某欄位複製到 B 表相對應欄位

例:把相同學校的 oldwhois.auth_date 丟到 whois.auth_date 內

UPDATE whois,oldwhois
SET whois.auth_date=oldwhois.auth_date
WHERE whois.SHN=oldwhois.SHN


使用 ADODB 抓 auto_increment 編號

有時, 我們 INSERT 一筆資料後, 想取得其序號(自動編號:auto_increment), 在 ADODB 可使用 insert_id 函式達成。

$rsSave = $conn->Execute($sqlSave);
$osn = $conn->Insert_ID();


開設 myuser 帳號限定使用 myuser 資料庫

使用 phpmyadmin 建立 MySQL 空間

  1. 建立資料庫 myuser
    在 phpmyadmin 首頁就有建立資料庫的功能,請依自己的需求建立
  2. 下 SQL 語法,建立 myuser 使用者並使其只能完全存取 myuser 資料庫
GRANT ALL PRIVILEGES
   ON myuser.*
   TO 'myuser'@'localhost'
   IDENTIFIED BY 'myuser_pwd';



Left Join 進階

  • 當我們在 Left Join 時,想篩選出左右表皆要符合某特定狀況或不存在時,不是單純右表沒有時,依左表為準全部列出,該怎麼辦?
  • Left TABLE: ec_stud_base
stud_id stud_name
1 王小明
2 李飛
3 吳水金
4 王春橋
  • Right TABLE: ec_stud_seme
stud_id seme_year seme_class seme_num
1 0992 101 1
2 0992 101 2
3 0992 101 3
4 0992 101 4
1 1001 201 1
2 1001 201 2
  • 例如:我們想選出姓「王」的學生的基本資料,並試圖把他在 100 學年第 1 學期的學籍資料列出(若沒有,以 NULL呈現)
  • 可用的語法如下
SELECT A.`stud_id`, A.`stud_name`, B.`seme_class`, B.`seme_num`
FROM  `ec_stud_base` A LEFT JOIN `ec_stud_seme` B
ON ( A.`stud_id` = B.`stud_id` AND B.`seme_year` = '1001') 
WHERE  A.`stud_name` LIKE '王%' ";
  • 這語法的重點在於 ON 的後面是可以用 AND 串接兩個條件式
  • 結果如下
stud_id stud_name seme_class seme_num
1 王小明 201 1
4 王春橋 NULL NULL




好用函式

  • 要對 IPv4 位址排序的話,可用 inet_aton 函式
    SELECT hsn, hname, ipv4, advmon, netif FROM `hosts` ORDER BY INET_ATON(ipv4)



MySQL資料庫最佳化Tips


MyISAM 格式

  • user@dns:~$ sudo -i
  • root@dns:~# vi /etc/mysql/conf.d/myisam.cnf
[mysqld]
#前三行數字要一致
bulk_insert_buffer_size = 128M
tmp_table_size          = 128M
max_heap_table_size     = 128M
join_buffer_size        = 16M
key_buffer_size         = 128M
max_allowed_packet      = 32M
query_cache_limit       = 4M
read_buffer_size        = 1M
read_rnd_buffer_size    = 2M
sort_buffer_size        = 8M
table_cache             = 128
 ## Thread settings
thread_concurrency      = 4  #recommend 2x CPU cores
thread_cache_size       = 100 #recommend 5% of max_connections
## Table cache settings
#table_cache            = 512    #5.0.x <default: 64>
table_open_cache        = 512    #5.1.x, 5.5.x <default: 64>

InnoDB格式

  • user@dns:~$ sudo -i
  • root@dns:~# vi /etc/mysql/conf.d/innodb.cnf
[mysqld]
# RAM 至少要 2G 才可以這樣設,不然省點用
innodb_buffer_pool_size=1G
innodb_flush_log_at_trx_commit=0




本文小檔案

__NORICHEDITOR__

題名: Title::MYSQL疑難雜症
作者: Creator::itsmw作者群
主題/關鍵詞: Subject::MySQL,update,select,subquery
簡述: Description::MySQL 的疑難雜症解決記錄處
出版者: Publisher::台南縣教網中心 itsmw 計畫
其他參與者: Contributor::itsmw作者群
日期: Date::2010-1-9
文件類型: Type::08伺服器軟體
Type::03問題解決
資料格式: Format::text/html
文件識別代號: Identifier::http://myip.tw/itsmw/index.php?title=MYSQL
來源: Source::http://myip.tw/itsmw/index.php?title=MYSQL
語言: Language::zh-TW
相關資源: Relation::http://myip.tw/itsmw/index.php?title=MYSQL
文件涵蓋範圍: Coverage::跨所有平台
版權規範: Rights::GNU Free Documentation License 1.2