[MySQL] 資料庫latin1塞big5資料，轉換成utf8

環境 CentOS 6.4_x64 + MySQL 5.1_x64

將資料庫字集編碼是latin1，但是存入的資料卻是big5的資料，轉換成utf8。

將底下shell script存成一個檔案，例如:/tmp/_mysql2utf8.sh
假設資料庫名稱為: mydb，帳號:root，密碼:xxxx，請執行：
sh /tmp/_mysql2utf8.sh mydb
會產生一個 mydb_utf8 的資料庫

#!/bin/bash
pwd='xxxx'
dir=`pwd`

[ $# != 1 ] && echo "請輸入資料庫名稱 database_name" && exit 0

tmppath='/tmp'
cd $tmppath

dbs=$1
databases="$(mysql -uroot -p$pwd -Bse 'show databases like "'$dbs'%"')"
for db in $databases
do
  db1=$db'_utf8'
  workpath=$tmppath'/'$db
  test -d $workpath && rm -rf $workpath
  test ! -d $workpath && mkdir -p $workpath
  chmod 777 $workpath
  create=$workpath'/create.sql'
  create1=$workpath'/create_utf8.sql'
  echo 'DROP DATABASE IF EXISTS '$db1';' > $create
  echo 'CREATE DATABASE '$db1';' >> $create
  echo 'USE '$db1';' >> $create
  mysqldump $db -uroot -p$pwd --opt --default-character-set=latin1 --no-data >> $create
  cat $create | sed -e "s/AUTO_INCREMENT=[1-9]* //g" | sed -e "s/CHARSET=latin1/CHARSET=utf8/g" | sed "s/InnoDB/MyISAM/g" > $create1
  mysql -uroot -p$pwd --default-character-set=utf8 < $create1

  tables="$(mysql $db -uroot -p$pwd -Bse 'show tables')"
  for tname in $tables
  do
    txt=$workpath'/'$tname'.txt'
    txt1=$workpath'/'$tname'_utf8.txt'
    mysql -uroot -p$pwd -Bse 'SELECT * FROM `'$db'`.`'$tname'` INTO OUTFILE "'$txt'"'
    iconv -c -f big5 -t utf-8 $txt -o $txt1
    mysql -uroot -p$pwd --default-character-set=utf8 -Bse 'LOAD DATA INFILE "'$txt1'" INTO TABLE `'$db1'`.`'$tname'` CHARACTER SET utf8'
  done

  rm -rf $workpath
done

[MySQL] Master/Slave Replication 同步設定

參考: http://blog.longwin.com.tw/2008/03/mysql_replication_master_slave_set_2008/

MySQL 設定 Master 有異動後, 自動 Replication（複寫） 到 Slave 去，由於公司的主機已上線工作，不可能暫停下來設定。但是主機每天就會跑備份，研究了一下，寫了一個 shell script，讓Master主機備份後，自動倒資料到Slave備機，並經由Master主機自動設定並啟用 Master/Slave Replication 同步機制。

複寫機制注意事項:

• MySQL 複寫機制預設就是用非同步的方式進行複寫。


• MySQL 複寫機制是由 Slave 主動向 Master 要求資料庫的內容更新紀錄。


• Slave 不需要一直連接 Master 才能運作，即便長時間中斷連線都還是能回復資料庫同步的狀態。

主機與備機環境 CentOS 6.4-x86_64 + MySQL.5.5.18-x86_64

準備工作：
1. 設定 Master主機:
vi /etc/my.cnf

[mysqld]
server-id=1
log-bin=mysql-bin # 其實預設已經啟用日誌檔
# 若是 innodb, 且有用 transaction 的話, 需再加入下面兩行
innodb_flush_log_at_trx_commit=1
sync_binlog=1

自己找適合的時間重啟 mysql:
service mysql restart

2. 設定 Slave備機:
vi /etc/my.cnf

[mysqld]
server-id=2
log-bin=mysql-bin # 其實預設已經啟用日誌檔

重啟 mysql:
service mysql restart

3.並且假設Master主機與Slave備機(IP:192.168.1.2)，都有root帳號且密碼相同，並可互相登入。

4.上述工作完成後，將底下的 shell script 加入Master主機的排程即可。

#!/bin/bash
pwd='密碼'
master_ip=$(LC_ALL=C ifconfig  | grep 'inet addr:'| grep -v '127.0.0.1' | cut -d: -f2 | awk '{ print $1}')
slave_ip='192.168.1.2'

sql="GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' IDENTIFIED BY '$pwd';"
mysql -uroot -p$pwd -e "$sql"

sql="FLUSH TABLES WITH READ LOCK;"
mysql -uroot -p$pwd -e "$sql"

logbin=$(mysql -uroot -p$pwd -e "show master status \G;" | grep -i file | awk '{print $2}')
logpos=$(mysql -uroot -p$pwd -e "show master status \G;" | grep -i pos | awk '{print $2}')

# 同步資料庫 dump master #####################
mysqldump -uroot -p$pwd DB > dbdump.sql
###############################################

sql="UNLOCK TABLES;"
mysql -uroot -p$pwd -e "$sql"

sql="STOP SLAVE;"
mysql -h$slave_ip -uroot -p$pwd -e "$sql"

# 同步資料庫 restore to slave #################
mysql -h$slave_ip -uroot -p$pwd < dbdump.sql
###############################################

sql="RESET MASTER;"
mysql -h$slave_ip -uroot -p$pwd -e "$sql"

sql="RESET SLAVE;CHANGE MASTER TO MASTER_HOST=' ';"
mysql -h$slave_ip -uroot -p$pwd -e "$sql"

sql="CHANGE MASTER TO MASTER_HOST='$master_ip',MASTER_USER='root',MASTER_PASSWORD='$pwd',MASTER_LOG_FILE='$logbin',MASTER_LOG_POS=$logpos;"
mysql -h$slave_ip -uroot -p$pwd -e "$sql"

sql="START SLAVE;"
mysql -h$slave_ip -uroot -p$pwd -e "$sql"

# OK, show master/slave status ################
sql="show master status \G;"
mysql -uroot -p$pwd -e "$sql" > /tmp/_mysql_slave.log
sql="show slave status \G;"
mysql -h$slave_ip -uroot -p$pwd -e "$sql" > /tmp/_mysql_slave.log

[Linux] 找出非法試探性登錄失敗的IP

來源: http://netsecurity.51cto.com/art/201306/400871.htm

一.找出嘗試登入Linux失敗的IP
輸入指令:
cat /var/log/secure | awk '/Failed/{print $(NF-3)}' | sort | uniq -c | awk '{print $1"\t"$2}'
結果如下:
80 116.228.164.2
412 119.10.114.52
20 121.0.137.45
162 121.78.117.12
573 1.25.202.50
15 125.45.229.163
5546 175.113.148.3
230 175.236.217.37
78 210.13.73.29
144 221.176.53.74
366 222.122.129.115
439 222.186.59.21
47 60.174.198.14
118 91.232.208.38
第一個數字為：登錄失敗的次數

 

二.找出嘗試登入MySQL失敗的IP
1.先需要啟用mysql log紀錄，修改設定檔 /etc/my.cnf，在[mysqld]底下中加入:
log = /var/lib/mysql/mysql.log

2.重啟mysql服務:
service mysql restart

3.輸入指令:
cat /var/lib/mysql/mysql.log |grep 'denied' |awk '{print $(NF-3)}' |sed "s/^.*@//g" |sed "s/'//g" |sort |uniq -c |awk '{print $1"\t"$2}'
結果如下:

[MySQL] 資料表使用變數

SET @TABLE = 'MA020';
SET @INTYPE=2;
SET @s = CONCAT('SELECT * FROM ', @TABLE, ' WHERE INTYPE=?');
PREPARE stmt FROM @s;
EXECUTE stmt USING @INTYPE;

[MySQL] 非標準的資料庫備份與還原

最近使用mysqldump備份資料庫，有時候不會成功。改用 MySQL 的 INTO OUTFILE 指令轉出文字檔，再壓縮。此方式的備份速度出奇的快速，至於有沒有缺點，還不知道。

將底下程式碼儲存到 _dumptxt.sh

#!/bin/bash
week=`date +%d`
user='user'
pwd='1234'
dir=`pwd`

#echo $# #參數數量
[ $# != 1 ] && echo "請輸入資料庫名稱 database_name" && exit 0

path='/light/mysql/dump/'
tmppath='/tmp'
cd $tmppath

dbs=$1
databases="$(mysql -u$user -p$pwd -Bse 'show databases like "'$dbs'"')"
for db in $databases
do
  fname=$path$db'.sql.'$week'.tgz'
  workpath=$tmppath'/'$db'.sql.'$week
  test -d $workpath && rm -rf $workpath
  test ! -d $workpath && mkdir -p $workpath
  chmod 777 $workpath
  create=$workpath'/create.sql'
  echo 'DROP DATABASE IF EXISTS '$db';' > $create
  echo 'CREATE DATABASE '$db';' >> $create
  echo 'USE '$db';' >> $create
  mysqldump $db -u$user -p$pwd --opt --default-character-set=latin1 --no-data >> $create

  tables="$(mysql $db -u$user -p$pwd -Bse 'show tables')"
  for tname in $tables
  do
    txt=$workpath'/'$tname'.txt'
    mysql -u$user -p$pwd -Bse 'SELECT * FROM `'$db'`.`'$tname'` INTO OUTFILE "'$txt'"'
  done
  cd $tmppath
  tar -zcf $fname $db'.sql.'$week
  rm -rf $workpath
  echo $fname
done

cd $dir

 

將底下程式碼儲存到 _restore.sh

#!/bin/bash
user='user'
pwd='1234'
dir=`pwd`

#echo $# ＃參數數量
[ $# != 1 ] && echo "請輸入備份檔名 {dbname}.sql.{01-31}.tgz" && exit 0
test ! -f $1 && echo '檔案 '$1' 不存在!' && exit 0

dumpfile=$1
dumpdir=`echo $1 | sed 's/^.*\///g' | sed 's/.tgz//g'`
dbs=`echo $dumpdir | sed 's/\.sql.*$//g'`
#dbs=`echo $dumpdir | sed 's/\..*$//g'`
tmppath='/tmp/'
workpath=$tmppath$dumpdir

test -d $workpath && rm -rf $workpath
tar -zxf $dumpfile -C $tmppath
test ! -d $workpath && exit 0

cd $workpath
mysql -u$user -p$pwd < create.sql

databases="$(mysql -u$user -p$pwd -Bse 'show databases like "'$dbs'"')"
for db in $databases
do
  tables="$(mysql $db -u$user -p$pwd -Bse 'show tables')"
  for tname in $tables
  do
    txt=$workpath'/'$tname'.txt'
    mysql -u$user -p$pwd -Bse 'LOAD DATA INFILE "'$txt'" INTO TABLE `'$db'`.`'$tname'`'
  done
done
cd $tmppath
rm -rf $workpath
cd $dir

 

假設資料庫名稱 mydb，備份日期為2012/11/20
備份資料庫請輸入：
sh _dumptxt.sh mydb
會產生 mydb.sql.20.tgz 的備份檔案

回復資料庫請輸入：
sh _restore.sh mydb.sql.20.tgz

Oracle開始搞小動作，要毀掉MySQL

來源:【Linux Pilot http://www.linuxpilot.com/industry/news/2012082401Oracle-MySQL】

http://bit.ly/efjKha (Drizzle = MySQL ??)

Drizzle = MySQL ?? ( http://bit.ly/efjKha )  【 阿舍的隨手記記、隨手寫寫... 】

[MySQL] 資料庫引擎 InnoDB 設定參數

來源:  http://swachian.javaeye.com/blog/193788 innodb_buffer_pool_size 如 果用Innodb，那麼這是一個重要變量。相對於MyISAM來說，Innodb對於buffer size更敏感。MySIAM可能對於大數據量使用默認的key_buffer_size也還好，但Innodb在大數據量時用默認值就感覺在爬了。 Innodb的緩衝池會緩存數據和索引，所以不需要給系統的緩存留空間，如果只用Innodb，可以把這個值設為內存的70%-80%。和 key_buffer相同，如果數據量比較小也不怎麼增加，那麼不要把這個值設太高也可以提高內存的使用率。 innodb_additional_pool_size   這個的效果不是很明顯，至少是當操作系統能合理分配內存時。但你可能仍需要設成20M或更多一點以看Innodb會分配多少內存做其他用途。 innodb_log_file_size 對於寫很多尤其是大數據量時非常重要。要注意，大的文件提供更高的性能，但數據庫恢復時會用更多的時間。我一般用64M-512M，具體取決於服務器的空間。 innodb_log_buffer_size   默認值對於多數中等寫操作和事務短的運用都是可以的。如 果經常做更新或者使用了很多blob數據，應該增大這個值。但太大了也是浪費內存，因為1秒鐘總會 flush（這個詞的中文怎麼說呢？）一次，所以不需要設到超過1秒的需求。8M-16M一般應該夠了。小的運用可以設更小一點。 innodb_flush_log_at_trx_commit  （這個很管用）  抱怨Innodb比MyISAM慢 100倍？那麼你大概是忘了調整這個值。默認值1的意思是每一次事務提交或事務外的指令都需要把日誌寫入（flush）硬盤，這是很費時的。特別是使用電 池供電緩存（Battery backed up cache）時。設成2對於很多運用，特別是從MyISAM表轉過來的是可以的，它的意思是不寫入硬盤而是寫入系統緩存。日誌仍然會每秒flush到硬 盤，所以你一般不會丟失超過1-2秒的更新。設成0會更快一點，但安全方面比較差，即使MySQL掛了也可能會丟失事務的數據。而值2只會在整個操作系統 掛了時才可能丟數據。

[MySQL] 無法連結遠端MySQL 5.5.8

在 /etc/my.cnf  的 下加入  skip-name-resolve 重啟MySQL即可。

[MySQL] 資料庫安全配置

來源: http://vfp.sunyear.com.tw/viewtopic.php?t=598 MySQL一些小竅門 1)如果不慎忘記了MySQL的root密碼，我們可以在啟動MySQL伺服器時加上參數--skip-grant-tables來跳過授權表的驗證 (./safe_mysqld --skip-grant-tables &)，這樣我們就可以直接登陸MySQL伺服器，然後再修改root用戶的口令，重啟MySQL就可以用新口令登陸了。 2)啟動MySQL伺服器時加上--skip-show-database使一般資料庫用戶不能流覽其他資料庫。 3)啟動MySQL伺服器時加上--chroot=path參數，讓mysqld守護進程運行在chroot環境中。這樣SQL語句LOAD DATA INFILE和SELECT ... INTO OUTFILE就限定在chroot_path下讀寫文件了。這裡有一點要注意，MySQL啟動後會建立一個mysql. sock 檔，默認是在/tmp目錄下。使用了chroot後，MySQL會在chroot_path/tmp去建立mysql. sock 檔，如果沒有chroot_path/tmp目錄或啟動MySQL的用戶沒有這個目錄寫許可權就不能建立mysql. sock 檔，MySQL會啟動失敗。比如我們加了--chroot=/usr/local/mysql/啟動參數，那麼最好建立一個啟動MySQL的用戶能寫的 /usr/local/mysql/tmp目錄，當然我們也可以用--socket=path來指定mysql. sock 檔的路徑，但這個path一定要在chroot_path裡面。 4)啟動MySQL伺服器時加上--log-slow-queries 參數，這樣mysqld會把SQL命令執行時間超過 long_query_time的寫入file檔。如果沒有指定=file，mysqld默認會寫到資料目錄下的hostname-slow.log。如 果只指定了filename，沒有指定路徑，那麼mysqld也會把filename寫到資料目錄下。我們通過這個日誌檔可以找出執行時間超長的查詢語 句，然後儘可能的優化它減輕MySQL伺服器的負擔。 5)如果我們只需本機使用MySQL服務，那麼我們還可以加上--skip-networking啟動參數使MySQL不監聽任何TCP/IP連接，增加安全性。（非常推薦）

[MySQL] 使用指令將資料表最佳化

來源: http://blog.dgps.kh.edu.tw/blog/u882061/cce-linux/2009/10/31/1237

 

把資料庫內所有資料表最佳化，這招久久用一次，效果很明顯。

mysqlcheck -a -c -o -r --all-databases -uroot -p

重建資料表索引，這招也得久久用一次，效果不錯。

service mysqld stop

myisamchk -s /var/lib/mysql/*/*.MYI

service mysqld start

 

檯面材料好 檯面材料好

[MySQL] utf8 設定

設定檔:
linux: /etc/my.cnf
windows: %systemroot%/my.ini

加入參數設定
default-character-set = utf8
character-set-server = utf8