(一)建立anndb資料庫,及使用者ann
[root@life ann]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.0.27-log Source distribution
Copyright (c) 2000, 2011, 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.
mysql> create database anndb;
Query OK, 1 row affected (0.00 sec)
mysql> grant all privileges on anndb.* to ann@localhost identified by '??????';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@life ann]#
(二)初始化anndb的資料表
[root@life conf]# cd /usr/local/apache2/htdocs
[root@life htdocs]# cd ann
[root@life ann]# mysql -u ann -p anndb < sql/mysql.sql
Enter password:
[root@life ann]#
[root@life ann]# cat sql/mysql.sql
create table parttb (
partid int(10) UNSIGNED NOT NULL auto_increment,
pid varchar(8) NOT NULL,
partname varchar(24) NOT NULL,
partident varchar(64) NOT NULL,
rootuid int(10) DEFAULT '0',
PRIMARY KEY(partid)
);
create table usertb (
userid int(10) UNSIGNED NOT NULL auto_increment,
uid char(32) DEFAULT 'test',
partid int(10) UNSIGNED NOT NULL,
username varchar(12) BINARY NOT NULL,
realname varchar(12) NOT NULL,
userpass varchar(12) BINARY NOT NULL,
email varchar(64) NOT NULL,
userident varchar(64) NOT NULL,
PRIMARY KEY(userid)
);
create table titletb (
tid int(10) UNSIGNED NOT NULL auto_increment,
partid int(10) UNSIGNED NOT NULL,
partname varchar(24) NOT NULL,
subject varchar(255) NOT NULL,
posttime datetime NOT NULL,
overtime datetime NOT NULL,
firsttime datetime NOT NULL,
hits int(6) UNSIGNED DEFAULT '0' NOT NULL,
type varchar(8) NOT NULL,
local varchar(8) DEFAULT 'no',
up int(1) UNSIGNED DEFAULT '0' NOT NULL,
PRIMARY KEY(tid)
);
create table anntb (
tid int(10) UNSIGNED NOT NULL,
userid int(10) UNSIGNED NOT NULL,
ip varchar(20) NOT NULL,
filename varchar(255) NOT NULL,
url varchar(255) NOT NULL,
comment blob NOT NULL
);
create table sessions (
session_key char(32) NOT NULL,
session_expire int(11) UNSIGNED NOT NULL,
session_value text NOT NULL,
PRIMARY KEY (session_key)
);
[root@life ann]#
(三)匯入資料 usertb 及parttb
[root@life ann]# ls ~webadm/sql -l
總計 8420
-rw-rw-r--. 1 webadm webadm 4307267 8月 15 15:08 anndb.sql
-rw-r--r--. 1 webadm webadm 3922795 8月 16 22:07 anntb.sql
-rw-r--r--. 1 webadm webadm 1429 8月 16 21:56 parttb.sql
-rw-r--r--. 1 webadm webadm 67 8月 16 21:55 roottb.sql
-rw-r--r--. 1 webadm webadm 376605 8月 16 21:54 titletb.sql
-rw-r--r--. 1 webadm webadm 3491 8月 16 21:39 usertb.sql
[root@life ann]#
[root@life ann]# cat ~webadm/sql/usertb.sql
INSERT INTO `usertb` VALUES (1, 'e60c6e4e69a7c26bef8024f60f44243c', 1, 0x6b6d7673636d, '教務處', 0x636478766533, '', '');
:
:
INSERT INTO `usertb` VALUES (29, 'dfaeb0394bddf61b82af1f914902f5df', 21, 0x6b6d7673656d77, '文書組', 0x6b6d7673656d773132, '', '');
INSERT INTO `usertb` VALUES (30, '', 24, 0x6b6d76736378, '均值化承辦人', 0x6162636431323334, '', '');
[root@life ann]#
[root@life ann]# mysql -u ann -p anndb < ~webadm/sql/usertb.sql
Enter password:
[root@life ann]# cat ~webadm/sql/parttb.sql
INSERT INTO `parttb` VALUES (1, 'kmvsc', '教務處', '', 1);
:
INSERT INTO `parttb` VALUES (23, 'kmvsea', '出納組', '', 11);
INSERT INTO `parttb` VALUES (24, 'kmvscx', '均質化', '', 30);
[root@life ann]# mysql -u ann -p anndb < ~webadm/sql/parttb.sql
Enter password:
[root@life ann]#
(四)察看匯入結果，中文顯示有誤
[root@life ann]# mysql -u ann -p
Enter password:
mysql> use anndb;
Database changed
mysql> show tables;
+-----------------+
| Tables_in_anndb |
+-----------------+
| anntb |
| parttb |
| sessions |
| titletb |
| usertb |
+-----------------+
5 rows in set (0.00 sec)
mysql> select * from usertb;
+--------+----------------------------------+--------+----------+----------+-------------+----------------------------+---------------------+
| userid | uid | partid | username | realname | userpass | email | userident |
+--------+----------------------------------+--------+----------+----------+-------------+----------------------------+---------------------+
| 30 | | 24 | kmvscx | ?????? | pwdpwd | | |
+--------+----------------------------------+--------+----------+----------+-------------+----------------------------+---------------------+
25 rows in set (0.00 sec)
mysql>
mysql> select * from parttb;
+--------+---------+----------+-----------+---------+
| partid | pid | partname | partident | rootuid |
+--------+---------+----------+-----------+---------+
| 1 | kmvsc | ??? | | 1 |
| 2 | kmvsd | ??? | | 2 |
| 3 | kmvse | ??? | | 4 |
| 4 | kmvsf | ??? | | 5 |
| 5 | kmvsg | ??? | | 6 |
| 6 | kmvsh | ??? | | 7 |
| 7 | kmvsi | ??? | | 0 |
| 8 | kmvsj | ??? | | 9 |
| 9 | kmvsk | ??? | | 10 |
| 10 | kmvscc | ???? | | 3 |
| 17 | kmvsi1 | ????? | | 25 |
| 11 | kmvsca | ??? | | 0 |
| 12 | kmvscb | ??? | | 17 |
| 13 | kmvscd | ??? | | 0 |
| 14 | kmvsce | ?????? | | 0 |
| 19 | kmvscf | ??? | | 27 |
| 16 | kmvsc1 | ???? | | 0 |
| 18 | kmvsia | ????? | | 26 |
| 20 | kmvscfe | ??? | | 28 |
| 21 | kmvsemw | ??? | | 29 |
| 23 | kmvsea | ??? | | 11 |
| 24 | kmvscx | ??? | | 30 |
+--------+---------+----------+-----------+---------+
22 rows in set (0.00 sec)
mysql> quit
Bye
[root@life ann]#
(五)修改php timezone支援
[root@life ann]# vi /etc/php.ini
[root@life ann]# cat /etc/php.ini|grep timezone
; Defines the default timezone used by the date functions
; http://php.net/date.timezone
date.timezone ="Asia/Taipei"
[root@life ann]#
(六)修改mysql中文支援為utf8
[root@life ann]# vi /etc/my.cnf
# The following options will be passed to all MySQL clients
[client]
:
default-character-set=utf8
# The MySQL server
[mysqld]
:
default-character-set=utf8
default-collation=utf8_general_ci
(七)查看/usr/local/mysql/var
[root@life ann]# ls /usr/local/mysql/var -l
總計 21028
drwx------ 2 mysql mysql 4096 8月 23 15:35 anndb
-rw-rw---- 1 mysql mysql 10485760 8月 23 14:45 ibdata1
-rw-rw---- 1 mysql mysql 5242880 8月 23 14:45 ib_logfile0
-rw-rw---- 1 mysql mysql 5242880 8月 23 14:45 ib_logfile1
-rw-rw---- 1 mysql root 1069 8月 23 14:45 life.kmvs.km.edu.tw.err
-rw-rw---- 1 mysql mysql 5 8月 23 14:45 life.kmvs.km.edu.tw.pid
drwx------ 2 mysql root 4096 8月 23 14:43 mysql
-rw-rw---- 1 mysql mysql 15145 8月 23 14:43 mysql-bin.000001
-rw-rw---- 1 mysql mysql 504809 8月 23 14:43 mysql-bin.000002
-rw-rw---- 1 mysql mysql 10057 8月 23 15:59 mysql-bin.000003
-rw-rw---- 1 mysql mysql 57 8月 23 14:45 mysql-bin.index
drwx------ 2 mysql root 4096 8月 23 14:43 test
[root@life ann]#
(八)重建支援中文的anndb;
[root@life ann]# mysql -p -uroot
Enter password:
mysql> CREATE DATABASE `anndb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| anndb |
| mysql |
| test |
+--------------------+
4 rows in set (0.00 sec)
mysql> use anndb;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> grant all privileges on anndb.* to ann@localhost identified by '??????';
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
[root@life ann]#
(九)再次初始化anndb的資料表
[root@life mysql]# cd /usr/local/apache2/htdocs/ann
[root@life ann]# mysql -u ann -p anndb < sql/mysql.sql
Enter password:
[root@life ann]#
(十)匯入資料 usertb 及parttb，中文顯示終於正常。
[root@life ann]# ls ~webadm/sql -l
總計 8420
-rw-rw-r--. 1 webadm webadm 4307267 8月 15 15:08 anndb.sql
-rw-r--r--. 1 webadm webadm 3922795 8月 16 22:07 anntb.sql
-rw-r--r--. 1 webadm webadm 1429 8月 16 21:56 parttb.sql
-rw-r--r--. 1 webadm webadm 67 8月 16 21:55 roottb.sql
-rw-r--r--. 1 webadm webadm 376605 8月 16 21:54 titletb.sql
-rw-r--r--. 1 webadm webadm 3491 8月 16 21:39 usertb.sql
[root@life ann]#
[root@life ann]# cat ~webadm/sql/usertb.sql
INSERT INTO `usertb` VALUES (1, 'e60c6e4e69a7c26bef8024f60f44243c', 1, 0x6b6d7673636d, '教務處', 0x636478766533, '', '');
:
:
INSERT INTO `usertb` VALUES (29, 'dfaeb0394bddf61b82af1f914902f5df', 21, 0x6b6d7673656d77, '文書組', 0x6b6d7673656d773132, '', '');
INSERT INTO `usertb` VALUES (30, '', 24, 0x6b6d76736378, '均值化承辦人', 0x6162636431323334, '', '');
[root@life ann]#
[root@life ann]# mysql -u ann -p anndb < ~webadm/sql/usertb.sql
Enter password:
[root@life ann]# cat ~webadm/sql/parttb.sql
INSERT INTO `parttb` VALUES (1, 'kmvsc', '教務處', '', 1);
:
INSERT INTO `parttb` VALUES (23, 'kmvsea', '出納組', '', 11);
INSERT INTO `parttb` VALUES (24, 'kmvscx', '均質化', '', 30);
[root@life ann]# mysql -u ann -p anndb < ~webadm/sql/parttb.sql
Enter password:
[root@life ann]#
(十一)匯入資料 titletb
[root@life ann]# cat ~webadm/sql/titletb.sql|more
INSERT INTO `titletb` VALUES (4123, 7, '圖書館', '991班級讀書會紀錄完成班級第二次公告', '2011-01-13 08:30:00', '2011-07-31 00:00:00', '0000-0
0-00 00:00:00', 7565, '1.1', 'no', 0);
INSERT INTO `titletb` VALUES (4124, 7, '圖書館', '公告991寒假閱讀心得寫作比賽辦法', '2011-01-13 08:40:00', '2011-02-28 00:00:00', '0000-00-00
00:00:00', 6460, '1.1', 'no', 0);
:
[root@life ann]# mysql -u ann -p anndb < ~webadm/sql/titletb.sql
Enter password:
[root@life ann]#
(十二)匯入資料 anntb.sql
create table anntb (
tid int(10) UNSIGNED NOT NULL,
userid int(10) UNSIGNED NOT NULL,
ip varchar(20) NOT NULL,
filename varchar(255) NOT NULL,
url varchar(255) NOT NULL,
comment blob NOT NULL
);
[root@life ann]# cat ~webadm/sql/anntb.sql|more
INSERT INTO `anntb` VALUES (18, 1, '163.25.20.123', '三年級期末考試科目暨時間配當表7節(3天).doc', '', 0xe4b889e5b9b4e7b49ae69c9fe69cabe88083e
8a9a6e59084e78fade88083e8a9a6e7a791e79baee69aa8e69982e99693e9858de795b6e8a1a820);
INSERT INTO `anntb` VALUES (53, 12, '163.25.20.125', '', 'www.tcte.edu.tw', 0x203934e68a80e8a193e999a2e6a0a1e59b9be68a80e4ba8ce5b088e7b5b1e4b
880e585a5e5adb8e6b8ace9a997e7b0a1e7aba028e585a7e590abe5a0b1e5908de8a1a829e6af8fe4bbbd3235e58583efbc8c0d0ae38080e8ab8be4b889e5b9b4e7b49ae58f8a
e5bbb6e4bfaee78fade59084e78fade7b5b1e8a888e695b8e9878fe4b8a6e7b9b3e4baa4e8b2bbe794a80d0aefbc8aefbc8ae688aae6ada2e697a5e69c9fefbc9a3130e69c883
239e697a50d0aefbc8aefbc8ae980bee69c9fe4b88de58099efbc8aefbc8a0d0aefbc8aefbc8ae7b0a1e7aba028e4b88de590abe5a0b1e5908de8a1a829e58fafe4b88ae7b6b2
e4b88be8bc89efbc8c0d0aefbc8aefbc8ae7b6b2e59d80e782ba3a20266c743b20207777772e746374652e6564752e74772020202667743b20e8ab8be5908ce5adb8e5a49ae5a
49ae588a9e794a8200d0a20);
:
[root@life ann]# mysql -u ann -p anndb < ~webadm/sql/anntb.sql
Enter password:
[root@life ann]#
|