CentOS 6.3 學習筆記 (20) - Mysql replication 設定

/etc/my.cnf

注意事項：主從資料庫起始狀況要一樣




[client]
port            = 3306
socket          = /var/lib/mysql/mysql.sock
default-character-set=utf8

[mysqld]
socket          = /var/lib/mysql/mysql.sock
skip-locking

skip-name-resolve
停止名稱反查(若資料庫主機無法連外網可加速速度)

skip-innodb


log-slow-queries=/var/lib/mysql/slow-query.log
long_query_time=1
記錄查詢時間花費超過的 1s 的 SQL 語法

default-character-set=utf8
default-collation=utf8_general_ci

set-variable    = max_connections=1024
set-variable    = wait_timeout=28800
set-variable    = key_buffer=384M
set-variable    = max_allowed_packet=16M
set-variable    = table_cache=2048
set-variable    = sort_buffer=2M
set-variable    = record_buffer=2M
set-variable    = thread_cache=128
set-variable    = thread_concurrency=128
set-variable    = myisam_sort_buffer_size=128M
query_cache_size= 512M
query_cache_limit=1M
記憶體設定

log-bin
log-slave-updates
產生二進位LOG檔

server-id       = (數字隨意、不可重複)
master-host     =   192.168.*.*
master-user     =   slave (在主資料庫建一個帳號給slave機器登入)
master-password =   (隨意)
master-port     =  3306

master-connect-retry=60
預設重試間隔60秒
replicate-do-db=phpBB
若slave只做phpBB資料庫的更新