作者 主題: mysql管理筆記  (閱讀 4128 次)

0 會員 與 1 訪客 正在閱讀本文。

treble

  • 活潑的大學生
  • ***
  • 文章數: 215
    • 檢視個人資料
    • 牛的大腦
mysql管理筆記
« 於: 2012-11-04 12:21 »
由於最近發現自己的mysql管理筆記十分雜亂,所以小弟重新整理了一下
筆記內容如下,主要包括目前曾經用到過的功能,希望對有需要的人有幫助
若有miss掉什麼,歡迎補充

######################################################


MySQL

歷史
1994瑞典,從商業(AB公司)到開源,並於2008年被SUN花10億美金收購

.........................................................................

安裝部份

方法1
yum安裝
yum install mysql
yum install mysql-server

方法2
rpm安裝
相關資料
glibc 2.0.7-19 此寒式庫之前的版本對安裝mysql有不良影響
perl-dbi*.rpm 安裝mysql-server*.rpm時可能會須要
mysql-server*.rpm 主伺服器,安裝順序1 (大部份安裝在/usr/bin/*,/usr/share/*)
mysql-client*.rpm 客戶端連線程式,安裝順序2 (安裝在/usr/bin/*,/usr/share/man/*)
mysql-devel*.rpm 寒式庫和標頭檔,安裝順序3 (大部份安裝在/usr/include/mysql/*,/usr/lib/mysql/*)
mysql-shared*.rpm 動態客戶端寒式庫,安裝順序4 (安裝在/usr/lib/*)
mysql-test*.rpm 效能測試套件,可省略的套件

...

啟動mysql
mysql.server [start|stop] //啟動關閉mysqld

設定root密碼
/usr/bin/mysqladmin -u root password 'newpwd' //設定mysql裡帳號root的新密碼為newpwd

登入mysql
mysql -u root -p //用root身份進入mysql
ps:
mysql db -u root -p //用root身份進入mysql,並自動執行use db
mysql db -u root -p < file.sql //以root身份,將file.sql匯入到資料庫db

...

安裝後狀態

檔案配置(依版本可能各有不同)
/etc/my.cnf MySQL設定檔
/usr/bin  客戶端程式
/usr/sbin  mysqld伺服器
/var/lib/mysql  紀錄檔與資料庫檔案,備份mysql時需連同此目錄
/usr/share/doc/packages  文件

ps:
在資料庫檔案內資料檔
 資料表名.frm :資料表定義檔
 資料表名.myd :資料檔
 資料表名.myi :索引檔

ps:
mysql資料表格式
 myisam 速度快,預設使用,無法容錯因此需要常備份
 heap 留在記憶體的表格,速度最快
 innodb 具有交易特性,速度慢

...

web管理軟體
phpmyadmin安裝步驟如下
1
download
ex:http://www.phpmyadmin.net/home_page/downloads.php
2 [optional]
#tar -xzvf phpMyAdmin_x.x.x-all-languages.tar.gz
#mv phpMyAdmin_x.x.x-all-languages /var/www/html/phpmyadmin
#cd /var/www/html/phpmyadmin
3
#cp config.sample.inc.php config.inc.php
4
#vi config.inc.php
$cfg['blowfish_secret'] = 'ba17c1ec07d65003'; // use here a value of your choice
$cfg['Servers'][$i]['auth_type'] = 'http';
ps:
$cfg['Servers'][$i]['auth_type'] string ['HTTP'|'http'|'cookie'|'config'|'signon']
'HTTP' authentication (was called 'advanced' in previous versions and can be written also as 'http') ($auth_type = 'HTTP') as introduced in 1.3.0 allows you to log in as any valid MySQL user via HTTP-Auth.
5
open browser
http:// server ip /phpmyadmin/
login vaild mysql user


參考文章
http://www.phpmyadmin.net/documentation/


................................................................................................
................................................................................................
 
管理mysql常用功能


設定密碼
mysql> set password for snortuser@localhost=password('snortpassword');

移除遠端存取
1 mysql> use mysql
2 mysql> delete from user where host='%'

移除無使用者
1 mysql> use mysql
2 mysql> delete from user where user=''

給所有權限
mysql> GRANT ALL ON db.* TO user@localhost IDENTIFIED BY 'userpasswd';

更新
mysql> flush privileges

...

清除mysql log
有時因log檔太大,可將mysql-bin.0000001...等log清除
mysql> reset master
Query OK, 0 rows affected (3.56 sec)
ps:
若要求mysq不產log,可在my.cnf內設為以下
#log-bin=mysql-bin

另一方法為
PURGE BINARY LOGS
ps:http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html
ex:
刪除mysql-bin.010這個log檔
PURGE BINARY LOGS TO 'mysql-bin.010';
ex:
刪除2012-01-01 12:00:00之前的log
PURGE BINARY LOGS BEFORE '2012-01-01 12:00:00';

...


直接登入mysql且不使用密碼
當忘記密碼,或出現Access denied for user 'root'@'localhost' (using password: NO)的狀況時
做法:
先停止mysql,並執行以下
# mysqld_safe --user=mysql --skip-grant-tables --skip-networking &
# mysql -u root mysql
mysql> UPDATE user SET Password=PASSWORD('newpassword') where USER='root';
mysql> FLUSH PRIVILEGES;
mysql> quit
重新啟動mysql


...............................................................................................
...............................................................................................
 
資料備份部份

匯出資料常用有以下兩種

使用dump
ex:
mysqldump -u test -h 192.168.0.100 -p testdb > alltable.sql
mysqldump -u test -h 192.168.0.100 -p testdb testtable > alltesttable.sql
mysqldump -u test -h 192.168.0.100 -d -p testdb testtable > schematesttable.sql
mysqldump -u test -h 192.168.0.100 -d -p testdb testtable1 testtable2 > schemasometable.sql
參數說明
>: 表示匯出資料
<: 表示匯入資料
-u: mysql使用者
-h: 連線host IP或Domain Name
-d: 只需要匯出(dump)Table的結構, 若沒有此參數, 會將Table的結構和資料一併匯出
-p: 需要密碼, 指令執行後待會會要求輸入
testdb: 指定要處理的DB名稱
testtable: 指定要處理的Table名稱
testtable.sql: 匯入或是匯出Table資料的SQL語法

使用outfile
ex:
select * from data into outfile '/tmp/test.csv' FIELDS TERMINATED BY ','

...................................

匯入資料常用有以下三種

使用mysqlimport
tablename必須和來源文字檔一樣,也就是在匯入之前須先建好
--fields-terminated-by=分隔欄位的字元
--local 設定為本地
mysqlimport -u root [other parameter] -p .txt
ex:
mysqlimport -u root --local --fields-terminated-by=, -p kdd99 kddcupdata10.txt

使用 重導向
ex:
mysql -u test -p testdb < testtable.sql

使用 load data 
ex:
LOAD DATA INFILE '/root/test.csv' INTO TABLE ibtable FIELDS TERMINATED BY ','

ps:
load data參數結構如下
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
 [REPLACE | IGNORE]
 INTO TABLE tbl_name
 [CHARACTER SET charset_name]
 [{FIELDS | COLUMNS}
  [TERMINATED BY 'string']
  [[OPTIONALLY] ENCLOSED BY 'char']
  [ESCAPED BY 'char']
 ]
 [LINES
  [STARTING BY 'string']
  [TERMINATED BY 'string']
 ]
 [IGNORE number LINES]
 [(col_name_or_user_var,...)]
 [SET col_name = expr,...]


........................................................................................
........................................................................................

其他部份

更改MySQL資料庫目錄
假設MySQL資料庫目錄在linux下預設為/var/lib/mysql,若要改到/data,如下所示:
1
stop mysql service
mysqladmin -u root -p shutdown
2
move /var/lib/mysql to /data
mv /var/lib/mysql /data/
3
edit /etc/my.cnf 
vi  my.cnf   
# The MySQL server
[mysqld]
port   = 3306
#socket  = /var/lib/mysql/mysql.sock
socket  = /data/mysql/mysql.sock //指定mysql.sock的產生位置 
4 
edit MySQL start script
vi /etc/rc.d/init.d/mysqld
#datadir=/var/lib/mysql   
datadir=/data/mysql   //指定實際存放的路徑 
5
restart mysql service
/etc/rc.d/init.d/mysqld start
若運作正常則移動成功 
ps
若有使用phpmyadmin,需修改conf.inc.php中的socket路徑,並重新啟動網頁伺服器
vi /etc/conf.inc.php
$cfg['Servers'][$i]['socket'] = '/data/mysql/mysql.sock';
ps
若有發生找不到mysql的socket之類相關訊息,可直接將舊的mysql.sock導引到新的路徑,如下
ln -s /network/mysql/mysql.sock /var/lib/mysql/mysql.sock
« 上次編輯: 2012-11-06 19:56 由 treble »
[牛的大腦  http://systw.net ] 用來放一些筆記資料
[單字我朋友  http://systw.net/word ] 練英文用的
2分鐘檢測你的單字能力 http://systw.net/word/q.php

Niko

  • 活潑的大學生
  • ***
  • 文章數: 281
  • 性別: 男
    • 檢視個人資料
Re: mysql管理筆記
« 回覆 #1 於: 2012-11-04 15:29 »
感謝大大分享的筆記!!!讚!!!!

西歪街

  • 鑽研的研究生
  • *****
  • 文章數: 696
  • 性別: 男
    • 檢視個人資料
Re: mysql管理筆記
« 回覆 #2 於: 2012-11-05 22:49 »
<(_ _)>

kknrs29423

  • 懷疑的國中生
  • **
  • 文章數: 58
    • 檢視個人資料
Re: mysql管理筆記
« 回覆 #3 於: 2012-11-06 17:34 »
清bin log,用purge binary logs  應該比較好?,reset master有點太暴力@@

http://dev.mysql.com/doc/refman/5.0/en/purge-binary-logs.html

treble

  • 活潑的大學生
  • ***
  • 文章數: 215
    • 檢視個人資料
    • 牛的大腦
Re: mysql管理筆記
« 回覆 #4 於: 2012-11-06 19:49 »
感謝大大提供好方法
我一定會把他記下來
[牛的大腦  http://systw.net ] 用來放一些筆記資料
[單字我朋友  http://systw.net/word ] 練英文用的
2分鐘檢測你的單字能力 http://systw.net/word/q.php