簡介
- MySQL 是一個開放原始碼的關聯式資料庫管理系統
- 2008年被昇陽微系統（Sun Microsystems）收購。2009年，甲骨文公司（Oracle）收購昇陽微系統。
- 可以使用命令列工具管理MySQL資料庫（命令mysql 和 mysqladmin）
- phpMyAdmin是由PHP寫成的MySQL資料庫系統管理程式，讓管理者可用Web介面管理MySQL資料庫。
- 應用程式可透過ODBC或ADO方式，經由使用MyODBC與MySQL資料庫連接。
- 衍生版本有Drizzle，MariaDB，Percona Server及OurDelta等。
MySQL安裝
- centos 6.4 x86_64 安裝，直接使用 yum 系統安裝軟體。
[mtchang@powerful ~]$ sudo yum install mysql-server -y
- mysql-server 伺服器端的程式
- mysql command line 的 mysql 連接工具及一些常用的工具程式
================================================================================
Package Arch Version Repository Size
================================================================================
Installing:
mysql-server x86_64 5.1.67-1.el6_3 updates 8.6 M
Installing for dependencies:
mysql x86_64 5.1.67-1.el6_3 updates 886 k
perl-DBD-MySQL x86_64 4.013-3.el6 base 134 k
Transaction Summary
================================================================================
Install 3 Package(s)
Total download size: 9.6 M
- 第一次啟動 mysql server ，會出現訊息提出一些說明第一次需要設定 mysql root 使用者的密碼，預設沒有設定密碼。
[mtchang@powerful ~]$ sudo /etc/init.d/mysqld start
正在初始化 MySQL 資料庫： Installing MySQL system tables...
OK
Filling help tables...
OK
To start mysqld at boot time you have to copy
support-files/mysql.server to the right place for your system
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
(你可以用下面的命令改變 root 預設的密碼)
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h powerful.xx.jangmt.com password 'new-password'
Alternatively you can run:
/usr/bin/mysql_secure_installation
which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.
See the manual for more instructions.
You can start the MySQL daemon with:
cd /usr ; /usr/bin/mysqld_safe &
You can test the MySQL daemon with mysql-test-run.pl
cd /usr/mysql-test ; perl mysql-test-run.pl
Please report any problems with the /usr/bin/mysqlbug script!
正在啟動 mysqld： [ 確定 ]
- 執行改變 root 預設密碼, mysqladmin 是一隻管理 mysql server 的工具程式
[mtchang@powerful ~]$ /usr/bin/mysqladmin -u root password '12345678'
- [option]如果需要重新啟動程式，可以執行下面的指令 /etc/init.d/mysqld restart
[mtchang@powerful ~]$ sudo /etc/init.d/mysqld restart
正在停止 mysqld： [ 確定 ]
正在啟動 mysqld： [ 確定 ]
[mtchang@powerful ~]$ sudo chkconfig mysqld on
- [option]mysql 的資料目錄存放於 /var/lib/mysql 內，每個資料庫都以一個目錄存放。 例如: test 目錄對應 test 資料庫。
[mtchang@powerful ~]$ ls /var/lib/mysql/ -l
總計 20488
-rw-rw----. 1 mysql mysql 10485760 2013-04-07 10:38 ibdata1
-rw-rw----. 1 mysql mysql 5242880 2013-04-07 10:38 ib_logfile0
-rw-rw----. 1 mysql mysql 5242880 2013-04-07 10:30 ib_logfile1
drwx------. 2 mysql mysql 4096 2013-04-07 10:30 mysql
srwxrwxrwx. 1 mysql mysql 0 2013-04-07 10:38 mysql.sock
drwx------. 2 mysql mysql 4096 2013-04-07 10:30 test
# SET PASSWORD FOR 為設定帳號的密碼
# FLUSH PRIVILEGES; 為更新帳號權限資料表
[mtchang@powerful mysql]$ echo "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('********');
FLUSH PRIVILEGES;" | mysql -h localhost -u root -p12345678
# 驗證，進不去了
[mtchang@powerful mysql]$ mysql -h localhost -u root -p12345678
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
常用資料庫命令列工具
mysql 指令
- mysql 為 mysql 客戶端的命列列工具,可以執行 sql 敘述。
- 語法：mysql -h 主機IP -u 登入帳號 -p[密碼]
# 登入 mysql 使用 root 帳號, 主機為 localhost ,密碼提示詢問
[mtchang@powerful ~]$ mysql -h localhost -u root -p
Enter password: (輸入密碼)
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.1.67 Source distribution
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
# 執行 show databases; SQL 敘述式,結尾記得加上 ; 結尾。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| test |
+--------------------+
3 rows in set (0.00 sec)
# 離開 mysql client,結尾記得加上 ; 結尾。
mysql> exit;
Bye
# 可以配合 pipe 管線指令執行 sql 敘述
[mtchang@powerful ~]$ echo 'show databases;' | mysql -h localhost -u root -p12345678
Database
information_schema
mysql
test
mysql 指令練習
- 使用 mysql 客戶端工具應用於建立一個使用者帳號及資料庫，底下為即將建立的使用者資訊。
- 建立的使用者及資料庫資訊如下：
- 資料庫連線主機名稱：localhost
- 資料庫使用者帳號：sample
- 資料庫使用者密碼：******
- 資料庫名稱：sample
- SQL 敘述語法說明， -- 開頭的為 sql 敘述的註解。
-- 建立一個 mysql 使用者 sample 只允許在 localhost 連入,賦予密碼 ******
CREATE USER 'sample'@'localhost' IDENTIFIED BY '******';
-- 建立一個資料庫 sample ,如果 sample 不存在的話
CREATE DATABASE IF NOT EXISTS `sample` ;
-- 將資料庫 sample 賦予所有的存取權限給 sampel@localhost 這使用者
GRANT ALL PRIVILEGES ON `sample` . * TO 'sample'@'localhost';
- 以上為單一使用者及資料庫，SQL 敘述式直接建立法。將上面的資訊存成文字檔，並轉給 mysql root@localhost 執行。
# 建立 sql.txt 的檔案 , 開頭為 -- 的為 sql 敘述內的註解不會執行
[mtchang@powerful mysql]$ cat sql.txt
-- 建立一個 mysql 使用者 sample 只允許在 localhost 連入,賦予密碼 ******
CREATE USER 'sample'@'localhost' IDENTIFIED BY '******';
-- 建立一個資料庫 sample ,如果 sample 不存在的話
CREATE DATABASE IF NOT EXISTS `sample` ;
-- 將資料庫 sample 賦予所有的存取權限給 sampel@localhost 這使用者
GRANT ALL PRIVILEGES ON `sample` . * TO 'sample'@'localhost';
# 使用 root 帳號建立
[mtchang@powerful mysql]$ cat sql.txt | mysql -h localhost -u root -p12345678
- 驗證資料庫(database) sample 是否有建立成功
[mtchang@powerful mysql]$ echo 'show databases;' | mysql -h localhost -u sample -p******
Database
information_schema
sample
test
- 驗證使用者帳號 sample 登入來源為 localhost 的使用者是否有建立成功，且他的權限是如何？
[mtchang@powerful mysql]$ echo 'show grants;' | mysql -h localhost -u sample -p******
Grants for sample@localhost
GRANT USAGE ON *.* TO 'sample'@'localhost' IDENTIFIED BY PASSWORD '*AF8CC607D7356D518FE99B430EF3EE5D807CB42D'
GRANT ALL PRIVILEGES ON `sample`.* TO 'sample'@'localhost'
mysql 編碼問題
- 先觀看一下目前 mysql 系統執行的預設編碼，通常這是中文顯示亂碼的來源....
- 底下 latin1 這個編碼是預設的 mysql 編碼，如果中文用這個編碼會有後續很多問題發生...
[mtchang@powerful mysql]$ echo "SHOW VARIABLES LIKE 'character%';" | mysql -h localhost -u sample -p******
Variable_name Value
character_set_client latin1
character_set_connection latin1
character_set_database latin1
character_set_filesystem binary
character_set_results latin1
character_set_server latin1
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
- 可以修改系統的預設值 /etc/my.cnf 將編碼設定為 utf8 編碼輸出
[mtchang@powerful mysql]$ sudo vim /etc/my.cnf
[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci
init-connect='SET NAMES utf8'
[client]
default-character-set=utf8
# 以上有部份省略，其餘請依照原本的設定值執行
# 重新啟動
[mtchang@powerful mysql]$ sudo /etc/init.d/mysqld restart
正在停止 mysqld： [ 確定 ]
正在啟動 mysqld： [ 確定 ]
[mtchang@powerful mysql]$ echo "SHOW VARIABLES LIKE 'character%';" | mysql -h localhost -u sample -p******
Variable_name Value
character_set_client utf8
character_set_connection utf8
character_set_database utf8
character_set_filesystem binary
character_set_results utf8
character_set_server utf8
character_set_system utf8
character_sets_dir /usr/share/mysql/charsets/
- 等等，好像怪怪的...剛剛不是有建立了一個資料庫 sample 嗎？那他的編碼是?? 讓我們驗證看看，他是 latin1 編碼沒錯。
[mtchang@powerful mysql]$ echo "SHOW CREATE DATABASE sample;" | mysql -h localhost -u root -p12345678
Database Create Database
sample CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET latin1 */
[mtchang@powerful mysql]$ echo "ALTER DATABASE sample DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;" | mysql -h localhost -u root -p12345678
[mtchang@powerful mysql]$ echo "SHOW CREATE DATABASE sample;" | mysql -h localhost -u root -p12345678
Database Create Database
sample CREATE DATABASE `sample` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */
mysqldump 指令
- 用途：mysqldump 資料庫整批匯出，通常使用再備份資料庫的時候
- 語法：mysql -h 主機 -u 使用者 -p密碼 資料庫
[mtchang@powerful mysql]$ mysqldump --help
mysqldump Ver 10.13 Distrib 5.1.67, for redhat-linux-gnu (x86_64)
Copyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Dumping structure and contents of MySQL databases and tables.
Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
# 使用 root 帳號可以配合 --all-databases 匯出所有的資料
-A, --all-databases Dump all the databases. This will be same as --databases
with all databases selected.
# 設定預設編碼，再中文領域通常會因為 latin1 的中文編碼問題而使用到
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
--------------------------------- -----------------------------
default-character-set utf8
- 範例資料 data.txt 檔案(請將底下資料存成 data.txt 使用) -- 資料來源說明
-- 加入這行，使用上面說明建立的資料庫
use sample;
--
-- 資料表格式： `CUSTOMER`
--
CREATE TABLE IF NOT EXISTS `CUSTOMER` (
`客戶寶號` varchar(31) DEFAULT NULL,
`客戶代號` varchar(9) DEFAULT NULL,
`縣市` varchar(9) DEFAULT NULL,
`地址` varchar(39) DEFAULT NULL,
`郵遞區號` varchar(4) DEFAULT NULL,
`聯絡人` varchar(9) DEFAULT NULL,
`職稱` varchar(15) DEFAULT NULL,
`電話` varchar(12) DEFAULT NULL,
`行業別` varchar(9) DEFAULT NULL,
`統一編號` varchar(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
--
-- 列出以下資料庫的數據： `CUSTOMER`
--
INSERT INTO `CUSTOMER` VALUES('洽興金屬工業股份有限公司', 'A0001', '台中市', '西屯區工業區12路5號', '407', '陳勳森', '採購專員', '04-7081881', '機械', '86281373');
INSERT INTO `CUSTOMER` VALUES('新益機械工廠股份有限公司', 'A0002', '台北市', '北投區承德路7段371-1號', '100', '謝裕民', '行政專員', '02-7752450', '機械', '04675569');
INSERT INTO `CUSTOMER` VALUES('天源義記機械股份有限公司', 'A0003', '台北市', '松山區敦化北路122號3樓', '105', '翁崇銘', '工程師', '02-9015105', '機械', '22039731');
INSERT INTO `CUSTOMER` VALUES('家鄉事業股份有限公司', 'A0004', '台北市', '復興北路57號5樓', '108', '郭淑玲', '行政專員', '02-1879991', '食品', '85800192');
INSERT INTO `CUSTOMER` VALUES('四維企業(股)公司', 'A0005', '台南縣', '歸仁鄉南興村中山路851號', '720', '廖述宏', '工程師', '06-8792123', '建築', '12231596');
INSERT INTO `CUSTOMER` VALUES('永輝興電機工業股份有限公司', 'A0006', '台南縣', '麻豆鎮小埤里苓子林8-12號', '710', '黃清吉', '總務主任', '06-4588455', '電機', '04735004');
INSERT INTO `CUSTOMER` VALUES('溪泉電器工廠股份有限公司', 'A0007', '台中市', '西屯區台中工業區工業五路3號', '407', '林慶文', '工程師', '04-7613571', '電子', '07406808');
INSERT INTO `CUSTOMER` VALUES('善品精機股份有限公司', 'A0008', '台中縣', '烏日鄉中山路三段6號', '414', '張君暉', '工程師', '04-2552171', '機械', '11222009');
INSERT INTO `CUSTOMER` VALUES('佳樂電子股份有限公司', 'A0009', '台北市', '大安區復興南路一段390號5樓之3', '106', '蔣清池', '總務主任', '02-2301212', '電子', '61781463');
INSERT INTO `CUSTOMER` VALUES('科隆實業股份有限公司', 'A0010', '台北市', '大安區敦化南路一段223號8樓', '106', '劉瑞復', '工程師', '02-7721320', '貿易', '97513218');
INSERT INTO `CUSTOMER` VALUES('永光壓鑄企業公司', 'A0011', '台北市', '中山北路三段22號', '104', '梁文雄', '工程師', '02-2514474', '機械', '52003703');
- 匯入 data.txt 到 sample 資料庫中，使用 sample@localhost 使用者匯入。
[mtchang@powerful mysql]$ cat data.txt | mysql -h localhost -u sample -p******
- 驗證 sample 內的資料 ，使用 select sql 將 「客戶寶號」欄位的資訊列出。
[mtchang@powerful mysql]$ echo 'use sample; select 客戶寶號 from CUSTOMER;' | mysql -h localhost -u sample -p******
客戶寶號
洽興金屬工業股份有限公司
新益機械工廠股份有限公司
天源義記機械股份有限公司
家鄉事業股份有限公司
四維企業(股)公司
永輝興電機工業股份有限公司
溪泉電器工廠股份有限公司
善品精機股份有限公司
佳樂電子股份有限公司
科隆實業股份有限公司
永光壓鑄企業公司
- 將 sample 資料庫匯出儲存為 sql_bak.txt
[mtchang@powerful mysql]$ mysqldump -h localhost -u sample -p****** sample > sql_bak.txt
[mtchang@powerful mysql]$ cat sql_bak.txt
-- MySQL dump 10.13 Distrib 5.1.67, for redhat-linux-gnu (x86_64)
--
-- Host: localhost Database: sample
-- ------------------------------------------------------
-- Server version 5.1.67
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `CUSTOMER`
--
DROP TABLE IF EXISTS `CUSTOMER`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `CUSTOMER` (
`客戶寶號` varchar(31) DEFAULT NULL,
`客戶代號` varchar(9) DEFAULT NULL,
`縣市` varchar(9) DEFAULT NULL,
`地址` varchar(39) DEFAULT NULL,
`郵遞區號` varchar(4) DEFAULT NULL,
`聯絡人` varchar(9) DEFAULT NULL,
`職稱` varchar(15) DEFAULT NULL,
`電話` varchar(12) DEFAULT NULL,
`行業別` varchar(9) DEFAULT NULL,
`統一編號` varchar(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `CUSTOMER`
--
LOCK TABLES `CUSTOMER` WRITE;
/*!40000 ALTER TABLE `CUSTOMER` DISABLE KEYS */;
... 略 ....
- 刪除一些資料，再透過上面的備份資料匯入將資料還原。
# 刪除一些公司
[mtchang@powerful mysql]$ echo 'use sample; DELETE FROM CUSTOMER where 客戶寶號 like "%股份有限公司";' | mysql -h localhost -u sample -p******
# 驗證,是否已經刪除
[mtchang@powerful mysql]$ echo 'use sample; select 客戶寶號 from CUSTOMER;' | mysql -h localhost -u sample -p******客戶寶號
四維企業(股)公司
永光壓鑄企業公司
# 將剛剛的備份檔 sql_bak.txt 倒回去
[mtchang@powerful mysql]$ mysql -h localhost -u sample -p****** < sql_bak.txt
# 驗證,公司資料已經全部回復，剛剛已經存在的資料也被覆蓋了
[mtchang@powerful mysql]$ echo 'use sample; select 客戶寶號 from CUSTOMER;' | mysql -h localhost -u sample -p******客戶寶號
洽興金屬工業股份有限公司
新益機械工廠股份有限公司
天源義記機械股份有限公司
家鄉事業股份有限公司
四維企業(股)公司
永輝興電機工業股份有限公司
溪泉電器工廠股份有限公司
善品精機股份有限公司
佳樂電子股份有限公司
科隆實業股份有限公司
永光壓鑄企業公司
latin1 編碼的中文問題透過 mysqldump 修正
- latin1 編碼的問題再 mysql 的使用過程中很常犯這個錯誤，修正方式大抵上流程如下：
- 將 latin1 的資料庫使用 mysqldump --default-character-set=latin1 匯出
- 修正資料表的 DEFAULT CHARSET=utf8;
- 驗證一下中文的資料是否還是亂碼，配合 console 的環境觀看是 big5 or utf8 編碼
- 轉換編碼
- 修正後的資料轉存原資料庫
- 將 latin1 的資料庫使用 mysqldump --default-character-set=latin1 匯出
mysqldump 資料庫 -h 主機 -u 帳號 --password=密碼 --default-character-set=latin1 > 備份資料.sql
- 每個表格後面加上 DEFAULT CHARSET=utf8; 且為了避免悲劇再次重現，請參考上面的編碼設定為 utf8 mysql server 預設編碼。
CREATE TABLE `CONDUCT` (
`班級座號` varchar(9) DEFAULT NULL,
`導師評分` decimal(15,5) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
- 驗證一下中文的資料是否還是亂碼，配合 console 的環境觀看是 big5 or utf8 編碼，再來做編碼轉換
# 將資料轉換為 big5 編碼
piconv -f utf8 -t big5 備份資料.sql > b5.sql
# 再將 big5 編碼轉換為 utf8 編碼
piconv -f big5 -t utf8 b5.sql > utf8.sql
- 重新倒回原本的資料庫(建議先用 test 資料庫測試)
mysqldump 資料庫 -h 主機 -u 帳號 --password=密碼 < utf8.sql
mysqlshow
- mysqlshow 列出目前資料庫的結構及使用狀況 (databases, tables, and columns).
[mtchang@powerful mysql]$ mysqlshow -h localhost -u root -p******** --count
+--------------------+--------+--------------+
| Databases | Tables | Total Rows |
+--------------------+--------+--------------+
| information_schema | 28 | 2341 |
| mysql | 23 | 1995 |
| sample | 1 | 11 |
| test | 0 | 0 |
+--------------------+--------+--------------+
4 rows in set.
練習
- 請安裝 mysql server 於你的機器中，並設定下面的內容：
- 將 root 密碼設定為 linux 。
- 建立一個使用者 a01 密碼為 linux
- 建立資料庫 a01 及 a01_tmp 可以為 a01 使用者使用
- 請參考 結構 及 內容將資料匯入 a01 資料庫內。
- 驗證匯入的資料請使用 SQL 「use a01;select * from STUDENTS;」，看是否資料都已經全部匯入？
- 使用 mysqldump 將資料匯出為 a01.sql ，並使用壓縮程式壓縮為 a01.sql.bz2
- 將 a01.sql.bz2 解開匯出回 a01_tmp 資料庫中。
- 驗證匯入的資料請使用 SQL 「use a01_tmp;select * from STUDENTS;」，看是否資料都已經全部匯入？
- 使用 mysqlshow 驗證資料庫的行數是否正確？
沒有留言:
張貼留言