2013/04/14

CentOS 6.4上面的 Nginx + php-fpm + SSL + VirtualHost 設定



最新版本在:http://jangmt.com/wiki/index.php/Nginx
簡介

  • Nginx是一款效能設計導向的HTTP伺服器
說明
  • 環境 centos 6.4
  • SELINUX 預設啟動 enforcing
安裝 nignx 及移除 apache
  • 可以移除原有的 httpd 及 php
yum remove httpd php
  • 安裝 nginx + php-fpm 程式
[root@c6 html]# yum install nginx php-fpm
... 略 ...
==================================================================================
Package Arch Version Repository Size
==================================================================================
Installing:
nginx x86_64 1.0.15-4.el6 epel 379 k
php-fpm x86_64 5.3.3-22.el6 base 1.1 M
Installing for dependencies:
GeoIP x86_64 1.4.8-1.el6 epel 620 k
Transaction Summary
==================================================================================
Install 3 Package(s)
... 略 ...

  • 啟動及驗證 port
[root@c6 html]# /etc/init.d/nginx restart
Stopping nginx: [FAILED]
Starting nginx: [ OK ]
[root@c6 html]# /etc/init.d/php-fpm restart
Stopping php-fpm: [FAILED]
Starting php-fpm: [ OK ]
[root@c6 html]# netstat -anp | grep nginx
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 28731/nginx
unix 3 [ ] STREAM CONNECTED 1647440 28731/nginx
unix 3 [ ] STREAM CONNECTED 1647439 28731/nginx
[root@c6 html]# netstat -anp | grep php-fpm
tcp 0 0 127.0.0.1:9000 0.0.0.0:* LISTEN 28743/php-fpm
unix 3 [ ] STREAM CONNECTED 1647522 28743/php-fpm
unix 3 [ ] STREAM CONNECTED 1647521 28743/php-fpm

PHP-fpm 可以工作
  • 設定 PHP-fpm ,讓他可以和 nginx 協同工作,也就是說需要 PHP 時丟給 127.0.0.1:9000 去執行
# 以下這個設定檔是用原本 epel 的範本修改的
[root@c6 conf.d]# cat /etc/nginx/conf.d/default.conf
#
# The default server
#
server {
listen 80 default_server;
server_name _;
#charset koi8-r;
#access_log logs/host.access.log main;
location / {
root /usr/share/nginx/html;
index index.html index.htm;
}
error_page 404 /404.html;
location = /404.html {
root /usr/share/nginx/html;
}
# redirect server error pages to the static page /50x.html
#
error_page 500 502 503 504 /50x.html;
location = /50x.html {
root /usr/share/nginx/html;
}
# proxy the PHP scripts to Apache listening on 127.0.0.1:80
#
#location ~ \.php$ {
# proxy_pass http://127.0.0.1;
#}
# pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
#
location ~ \.php$ {
# root html;
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
#fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
# deny access to .htaccess files, if Apache's document root
# concurs with nginx's one
#
#location ~ /\.ht {
# deny all;
#}
}

  • 設定的點在這一段,這段原本全部都是註解掉的,因為原本預設只支援 html 靜態網頁。
location ~ \.php$ {
# 用系統預設的 /usr/share/nginx/html 目錄即可
# 請註解底下這一行,這是設定 nginx 的網頁 root 目錄的位置
#root html;
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
# 請註解掉底下這一行,他的路徑寫的有問題
#fastcgi_param SCRIPT_FILENAME /scripts$fastcgi_script_name;
# SCRIPT_FILENAME 指的是 php 的來源程式,套入到後面描述的 $fastcgi_script_name 資源
# 加入底下這一行,其中 $document_root 是一個變數,會將根目錄帶入底下這一行
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}

  • 重新啟動 nginx
[root@c6 html]# /etc/init.d/nginx restart
Stopping nginx: [ OK ]
Starting nginx: [ OK ]

  • 測試 PHP 可以工作時間並給予一個時間結果 , nginx 的根目錄在 /usr/share/nginx/html/
[root@c6 html]# cat /usr/share/nginx/html/index.php
  • 可以顯示時間及 phpinfo() 資訊即是正確。
  • 所以這種結果會準嗎?? 這值得討論....
Nginx NameBase虛擬主機
  • nginx 的虛擬主機設定
[root@c6 conf.d]# cat /etc/nginx/conf.d/c6.conf
# the c6.jangmt.com
server {
#listen 80;
server_name c6.jangmt.com;
root /home/mtchang/public_html;
index index.html index.htm index.php;
location ~ \.php$ {
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
}
[root@c6 conf.d]# /etc/init.d/nginx restart
Stopping nginx: [ OK ]
Starting nginx: [ OK ]

[mtchang@c6 ~]$ cat /home/mtchang/public_html/index.php
/home/mtchang/public_html
NGINX 的 SSL

  • 設定檔
[root@c6 conf.d]# cat /etc/nginx/conf.d/c6.conf
# the c6.jangmt.com
server {
#listen 80;
server_name c6.jangmt.com;
listen 443;
ssl on;
ssl_certificate /etc/nginx/certs/server.crt;
ssl_certificate_key /etc/nginx/certs/server.key;
root /home/mtchang/public_html;
index index.html index.htm index.php;
location ~ \.php$ {
fastcgi_pass 127.0.0.1:9000;
fastcgi_index index.php;
fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
include fastcgi_params;
}
}

  • 產生憑證 in /etc/nginx/certs 目錄內
[root@c6 certs]# openssl genrsa -des3 -out server.key 1024
Generating RSA private key, 1024 bit long modulus
..........++++++
.....++++++
e is 65537 (0x10001)
Enter pass phrase for server.key:
Verifying - Enter pass phrase for server.key:
[root@c6 certs]# openssl req -new -key server.key -out server.csr
Enter pass phrase for server.key:
You are about to be asked to enter information that will be incorporated
into your certificate request.
What you are about to enter is what is called a Distinguished Name or a DN.
There are quite a few fields but you can leave some blank
For some fields there will be a default value,
If you enter '.', the field will be left blank.
-----
Country Name (2 letter code) [XX]:TW
State or Province Name (full name) []:kaohsiung
Locality Name (eg, city) [Default City]:taiwan
Organization Name (eg, company) [Default Company Ltd]:jangmt
Organizational Unit Name (eg, section) []:com
Common Name (eg, your name or your server's hostname) []:mtchang
Email Address []:mtchang.tw@gmail.com
Please enter the following 'extra' attributes
to be sent with your certificate request
A challenge password []:
An optional company name []:
[root@c6 certs]# cp server.key server.key.org
[root@c6 certs]# openssl rsa -in server.key.org -out server.key
Enter pass phrase for server.key.org:
writing RSA key
[root@c6 certs]# openssl x509 -req -days 365 -in server.csr -signkey server.key -out server.crt
Signature ok
subject=/C=TW/ST=kaohsiung/L=taiwan/O=jangmt/OU=com/CN=mtchang/emailAddress=mtchang.tw@gmail.com
Getting Private key
[root@c6 certs]# pwd
/etc/nginx/certs
[root@c6 certs]# ls -la
total 24
drwxr-xr-x. 2 root root 4096 Apr 14 00:11 .
drwxr-xr-x. 4 root root 4096 Apr 14 00:06 ..
-rw-r--r--. 1 root root 940 Apr 14 00:06 server.crt
-rw-r--r--. 1 root root 696 Apr 14 00:06 server.csr
-rw-r--r--. 1 root root 887 Apr 14 00:06 server.key
-rw-r--r--. 1 root root 963 Apr 14 00:06 server.key.org

  • 重新啟動及測試
[root@c6 certs]# /etc/init.d/nginx restart
Stopping nginx: [FAILED]
Starting nginx: [ OK ]
[root@c6 certs]# netstat -anp | grep nginx
tcp 0 0 0.0.0.0:80 0.0.0.0:* LISTEN 30610/nginx
tcp 0 0 0.0.0.0:443 0.0.0.0:* LISTEN 30610/nginx
unix 3 [ ] STREAM CONNECTED 1702006 30610/nginx
unix 3 [ ] STREAM CONNECTED 1702005 30610/nginx
... skip ...

測試效能
  • 測試工具: ab 指令
  • ab使用範例:
  • 要執行 100 次的 connection, 20 次的 concurrent (並行, 同時):
  • 結果解釋:
  • Time taken for tests: 總共執行花了多久的時間.(以上 100 次共多久)
  • Requests per second: 每秒平均可以處理多少個 connection.
  • 內容:index.php
[mtchang@c6 public_html]$ cat index.php
/home/mtchang/public_html/index.php

  • 內容:index.html
[mtchang@c6 public_html]$ cat index.html
/home/mtchang/public_html

  • 機器規格描述
[mtchang@c6 ~]$ cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 58
model name : Intel(R) Core(TM) i5-3450 CPU @ 3.10GHz
stepping : 9
cpu MHz : 1600.000
cache size : 6144 KB
physical id : 0
siblings : 4
core id : 0
cpu cores : 4
apicid : 0
initial apicid : 0
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts
acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx rdtscp lm constant_tsc arch_perfmon pebs bts rep_good
xtopology nonstop_tsc aperfmperf pni pclmulqdq dtes64 monitor ds_cpl vmx est tm2 ssse3 cx16 xtpr pdcm
pcid sse4_1 sse4_2 popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm ida arat epb xsaveopt
pln pts dts tpr_shadow vnmi flexpriority ept vpid fsgsbase smep erms
bogomips : 6186.06
clflush size : 64
cache_alignment : 64
address sizes : 36 bits physical, 48 bits virtual
power management:
[mtchang@c6 ~]$ free
total used free shared buffers cached
Mem: 3599640 1660092 1939548 0 183164 825952
-/+ buffers/cache: 650976 2948664
Swap: 4194296 0 4194296
[mtchang@c6 ~]$ sudo hdparm -tT /dev/sda5
/dev/sda5:
Timing cached reads: 24092 MB in 2.00 seconds = 12065.08 MB/sec
Timing buffered disk reads: 300 MB in 3.00 seconds = 99.94 MB/sec

  • apache 的 ab 做壓力測試的狀況(靜態 html)
[root@lab html]# ab -n 10000 -c 10 http://c6.jangmt.com/index.html
This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/
Benchmarking c6.jangmt.com (be patient)
Completed 1000 requests
Completed 2000 requests
Completed 3000 requests
Completed 4000 requests
Completed 5000 requests
Completed 6000 requests
Completed 7000 requests
Completed 8000 requests
Completed 9000 requests
Finished 10000 requests
Server Software: nginx/1.0.15
Server Hostname: c6.jangmt.com
Server Port: 80
Document Path: /index.html
Document Length: 3698 bytes
Concurrency Level: 10
Time taken for tests: 3.654708 seconds
Complete requests: 10000
Failed requests: 0
Write errors: 0
Total transferred: 39100212 bytes
HTML transferred: 36980000 bytes
Requests per second: 2736.20 [#/sec] (mean)
Time per request: 3.655 [ms] (mean)
Time per request: 0.365 [ms] (mean, across all concurrent requests)
Transfer rate: 10447.62 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 1 0.5 1 3
Processing: 0 1 1.0 2 4
Waiting: 0 1 0.5 1 3
Total: 1 3 0.4 3 6
Percentage of the requests served within a certain time (ms)
50% 3
66% 3
75% 3
80% 3
90% 3
95% 4
98% 4
99% 4
100% 6 (longest request)

  • 用 apache 的 ab 做壓力測試的狀況(此範例為 php 的範例)底下是在沒有 fail 的狀況下的數據。
[root@lab html]# ab -n 10 -c 2 http://c6.jangmt.com/index.php
This is ApacheBench, Version 2.0.40-dev <$Revision: 1.146 $> apache-2.0
Copyright 1996 Adam Twiss, Zeus Technology Ltd, http://www.zeustech.net/
Copyright 2006 The Apache Software Foundation, http://www.apache.org/
Benchmarking c6.jangmt.com (be patient).....done
Server Software: nginx/1.0.15
Server Hostname: c6.jangmt.com
Server Port: 80
Document Path: /index.php
Document Length: 52564 bytes
Concurrency Level: 2
Time taken for tests: 0.49866 seconds
Complete requests: 10
Failed requests: 0
Write errors: 0
Total transferred: 527110 bytes
HTML transferred: 525640 bytes
Requests per second: 200.54 [#/sec] (mean)
Time per request: 9.973 [ms] (mean)
Time per request: 4.987 [ms] (mean, across all concurrent requests)
Transfer rate: 10307.62 [Kbytes/sec] received
Connection Times (ms)
min mean[+/-sd] median max
Connect: 0 0 0.6 0 1
Processing: 8 8 1.1 9 10
Waiting: 0 0 0.7 0 1
Total: 9 9 0.3 9 10
Percentage of the requests served within a certain time (ms)
50% 9
66% 9
75% 9
80% 9
90% 10
95% 10
98% 10
99% 10
100% 10 (longest request)

  • 不太精準結論: nignx 有比較好,但是要趕上硬體的差異仍需要很努力....


Nginx-epel-centos.png

Nginx-ssl.png Nginx-ssl-certs.png

2013/04/07

MySQL 安裝及命令列使用說明(centos 6.4)

Contents


簡介

  • 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:                                          [  確定  ]
  • [option]預設開機 mysql 啟動
[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
  • [option] 改 root 密碼
# 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 客戶端工具應用於建立一個使用者帳號及資料庫,底下為即將建立的使用者資訊。
  • 建立的使用者及資料庫資訊如下:
  1. 資料庫連線主機名稱:localhost
  2. 資料庫使用者帳號:sample
  3. 資料庫使用者密碼:******
  4. 資料庫名稱: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 */
  • 刪除重建太麻煩了,可以使用 ALTER DATABASE 修改資料庫的預設編碼。
[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 驗證資料庫的行數是否正確?