MySQL 的整理與研究
1.Linux上安裝的問題(RedHat 7.0)
Q:裝完所有的rpm，執行mysql_install_db後，為何仍無法啟動mysql?
/var/lib/mysql/localhost.localdomain.err的內容說找到不./mysql
/tables.frm
A:因為安裝時，不是用mysql的身份，故mysql資料庫目錄的檔
案owner不是mysql造成的檔案權限問題。
請用chown去修正即可。
2.資料庫指令
create database 建立資料庫
create table [if not exists] 建立表格
create [unique] index on (col1,col2...) 建立索引
drop databaes 刪除資料庫
drop table [if exists] 刪除表單
drop index on 刪除索引
use 使用當成作業中的資料庫
alter table [,<...>] 修改的資料結構
optimize table 重整表單
load data [local] infile 'file_path&name' into table
fields terminated by ',' 直接將文字檔轉入
local指非server端的文字檔
欄位不論數字或文字，均不需加任何字元，只需
以terminated by的字元隔開即可
3.存取類指令
delete from [where ] [limit ] 刪除記錄(row(s))
where/limit都沒有就一定會變成空檔
select from 查詢記錄(row(s))
[where]
[group by]
[having ]
[order by [asc|desc]]
[limit ,]
insert [(cols...)] values (exps...) 新增記錄(row)
replace [(cols...)] set 新增記錄，若已unique已重複
則以本筆取代之
update set [where] 修改記錄(row(s))，若無
則全部記錄都會改成與此筆相同
lock tables read|write,... 表格鎖定
unlock tables 解除鎖定
grant [(cols)] on to ... 建立user權限
revoke[(cols)] on to ... 刪除user權限
4.訊息類指令
show databases...[like...] 列出資料庫清單
show tables [from ][like...] 列出資料庫的表格清單
show index from [like...] 列出表格索引
show columns from...[like...]
show status
show variables [like...]
show processlist
show tables status
show grants for
5.權限管理
mysql database下有五個table:
host
user
db
tables_priv
column_priv
也就是說，權限的設定可以大到以host來管理，也可以小到以cloumn來管理
檢查的順序:
user or (db and host) or tables_priv or column_priv
6.中文問題及重做 (總覺得mysql把文字編碼放在complier time很怪，應該當成模組才對吧...)
重作MySQL吧....
1.取得mysql-(version).tar.gz，並放到/usr/local之下之
2.tar zxf mysql-(version).tar.gz會解到mysql-(version)目錄下
3.cd mysql-(version)並執行：
make clean (可選擇，如果你已經configure過一次，得用這個清楚，不然上次的設定值會有殘留)
./configure --with-charset=big5
以下參數自己斟酌
--with-unix-socket-path=/tmp/mysql.sock (加這行讓你自己更清楚sock跑在那兒)
--prefix=/home/mysql (可選擇)
--with-mysqld-user=mysql (可選擇)
--without-debug --without-docs --without-bench --without-readline (可選擇)
make
make install
4.設定
(如果你跟我一樣，用mysql來run, 得將檔案全歸給myaql，才有讀寫的權限)
cd /usr/local -->依據你設定的--prefix目錄(預設為/usr/local)
[chown -R mysql:mysql * (如果你指定--with-mysqld-use，便要更改owner)]
bin/mysql_install_db (安裝mysql自己用的資料庫)
bin/safe_mysqld& (手動啟動，加&為背景執行，不然mysql沒結束前你就沒辦法用了...)
然後用用看吧...
5.設成redhat linux的service
cp share/mysql/mysql.service /etc/init.d/mysql.d
chmod +x /etc/init.d/mysql.d
setup 將mysqld設成要啟動的service
(後記:這樣設，每次shutdown時killall都說找不到mysql，不知如何處理，不過使用上倒是沒問題)
搞定
附記:configure的參數列表
Usage: configure [options] [host]
Options: [defaults in brackets after descriptions]
Configuration:
--cache-file=FILE cache test results in FILE
--help print this message
--no-create do not create output files
--quiet, --silent do not print `checking...' messages
--version print the version of autoconf that created configure
Directory and file names:
--prefix=PREFIX install architecture-independent files in PREFIX
[/usr/local]
--prefix=/home/mysql mysql安裝的根目錄
--exec-prefix=EPREFIX install architecture-dependent files in EPREFIX
[same as prefix]
--bindir=DIR user executables in DIR [EPREFIX/bin]
--sbindir=DIR system admin executables in DIR [EPREFIX/sbin]
--libexecdir=DIR program executables in DIR [EPREFIX/libexec]
--datadir=DIR read-only architecture-independent data in DIR
[PREFIX/share]
--datadir=/home/mysql/data 資料庫的目錄
--sysconfdir=DIR read-only single-machine data in DIR [PREFIX/etc]
--sharedstatedir=DIR modifiable architecture-independent data in DIR
[PREFIX/com]
--localstatedir=DIR modifiable single-machine data in DIR [PREFIX/var]
--libdir=DIR object code libraries in DIR [EPREFIX/lib]
--includedir=DIR C header files in DIR [PREFIX/include]
--oldincludedir=DIR C header files for non-gcc in DIR [/usr/include]
--infodir=DIR info documentation in DIR [PREFIX/info]
--mandir=DIR man documentation in DIR [PREFIX/man]
--srcdir=DIR find the sources in DIR [configure dir or ..]
--program-prefix=PREFIX prepend PREFIX to installed program names
--program-suffix=SUFFIX append SUFFIX to installed program names
--program-transform-name=PROGRAM
run sed PROGRAM on installed program names
Host type:
--build=BUILD configure for building on BUILD [BUILD=HOST]
--host=HOST configure for HOST [guessed]
--target=TARGET configure for TARGET [TARGET=HOST]
Features and packages:
--disable-FEATURE do not include FEATURE (same as --enable-FEATURE=no)
--enable-FEATURE[=ARG] include FEATURE [ARG=yes]
--with-PACKAGE[=ARG] use PACKAGE [ARG=yes]
--without-PACKAGE do not use PACKAGE (same as --with-PACKAGE=no)
--x-includes=DIR X include files are in DIR
--x-libraries=DIR X library files are in DIR
--enable and --with options recognized:
--enable-maintainer-mode enable make rules and dependencies not useful
(and sometimes confusing) to the casual installer
--enable-shared[=PKGS] build shared libraries [default=yes]
--enable-static[=PKGS] build static libraries [default=yes]
--enable-fast-install[=PKGS] optimize for fast installation [default=yes]
--with-gnu-ld assume the C compiler uses GNU ld [default=no]
--disable-libtool-lock avoid locking (might break parallel builds)
--with-mit-threads Always use included thread lib.
--with-pthread Force use of pthread library.
--with-named-thread-libs=ARG
Use specified thread libraries instead of
those automatically found by configure.
--with-named-curses-libs=ARG
Use specified curses libraries instead of
those automatically found by configure.
--with-named-z-libs=ARG
Use specified zlib libraries instead of
those automatically found by configure.
--enable-thread-safe-client
Compile the client with threads.
--enable-assembler Use assembler versions of some string
functions if available.
--with-raid Enable RAID Support
--with-unix-socket-path=SOCKET
Where to put the unix-domain socket. SOCKET must be
an absolute file name.
--with-unix-socket-path=/tmp/mysql.sock mysql執行時的unix socket要放那讓別人找
--with-tcp-port=port-number
Which port to use for MySQL services (default 3306)
--with-mysqld-user=username
--with-mysqld-user=mysql mysql以unix上的何人帳號來跑服務
What user the mysqld daemon shall be run as.
--disable-large-files Omit support for large files
--with-libwrap[=DIR] Compile in libwrap (tcp_wrappers) support
--without-debug Build a production version without debugging code
--without-debug
--with-mysqld-ldflags Extra linking arguments for mysqld
--with-client-ldflags Extra linking arguments for clients
--with-low-memory Try to use less memory to compile to avoid
memory limitations.
--with-comment Comment about compilation environment.
--without-server Only build the client.
--without-docs Skip building of the documentation.
--without-docs 不要產生文件的相關檔案
--without-bench Skip building of the benchmark suite.
--without-bench 不要產生效能效試的相關檔案
--without-readline Use system readline instead of bundled copy.
--without-readline "mysql"這支程式不要用自己的readline模組
--with-charset=CHARSET Use CHARSET by default (one of: big5 cp1251 cp1257
croat czech danish dec8 dos estonia euc_kr gb2312 gbk
german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr
latin1 latin2 latin5 swe7 usa7 win1250 win1251ukr
ujis sjis tis620; Default is latin1)
--with-charset=big5 以中文big5字元集處理資料庫(無中文存檔問題，可以中文資料庫資料表及欄位)
--with-extra-charsets=CHARSET[,CHARSET,...]
Use charsets in addition to default (none, complex,
all, or a list selected from the above sets)
--with-berkeley-db[=DIR]
Use BerkeleyDB located in DIR
--with-berkeley-db-includes=DIR
Find Berkeley DB headers in DIR
--with-berkeley-db-libs=DIR
Find Berkeley DB libraries in DIR
簡單的用法
./configure --with-charset=big5 --prefix=/home/mysql --with-unix-socket-path=/home/mysql.sock --with-mysqld-user=mysql