Linux CentOS 5.5 下 MySQL 5.5.3-m3 同步與主從備份

**************************前言**************************

一.主從的作用：

1.可以當做一種備份方式

2.用來實現讀寫分離，緩解一個資料庫的壓力

二.環境：

OS    CentOS5.5

DB    MySQL5.5.3-m3

安裝 CentOS5.5 請看

安裝 MySQL5.5.3-m3 請看

三.MySQL主從備份原理

master  上提供binlog ,

slave    通過 I/O線程從 master拿取 binlog,並複製到slave的中繼日誌中

slave    通過 SQL線程從 slave的中繼日誌中讀取binlog ,然後解析到slave中

四.主從複製大前提

需要master與slave同步，因為筆者的資料庫資料量不大，所以無需考慮太多，直接把

master上的data複製到了slave上，但是如果是大的資料量，比如像taobao這個的系統

那麼資料同步也是很難得，需要有一個完善的方案，有興趣的可以看看這篇文章

http://www.taobaodba.com/html/564_%E5%A2%9E%E9%87%8F%E6%97%A5%E5%BF%97%E8%BF%AD%E4%BB%A3%E5%90%8C%E6%AD%A5%E5%92%8C%E9%98%BF%E5%9F%BA%E9%87%8C%E6%96%AF%E6%82%96%E8%AE%BA.html

**************************開始***************************************************

一.將master設定為唯讀。

mysql> flush tables with read lock;

二.用master中的data檔案夾替換slave中的data檔案夾

比如 用 tar zcvf  mysql_data.gz   /media/raid10/mysql/3306/data

然後 mv  mysql_data.gz /media/raid10/htdocs/blog/wordpress/

因為我的 /media/raid10/htdocs/blog/wordpress/  是 Nginx 的主目錄

所以可以在 slave上，用wget下載這個檔案，然後 解壓，並覆蓋slave上的data檔案

注意：覆蓋之前最好備份源檔案

三.配置master的my.cnf,添加以下內容

在[mysqld]配置段添加如下欄位

server-id=1

log-bin=/media/raid10/mysql/3306/binlog/binlog //這裡寫你的binlog絕對路徑名

binlog-do-db=blog //需要同步的資料庫，如果沒有本行，即表示同步所有的資料庫

binlog-ignore-db=mysql //被忽略的資料庫

這裡給出我的my.cnf設定檔

[client]

character-set-server = utf8

port    = 3306

socket  = /tmp/mysql.sock

[mysqld]

character-set-server = utf8

replicate-ignore-db = mysql

replicate-ignore-db = test

replicate-ignore-db = information_schema

user    = mysql

port    = 3306

socket  = /tmp/mysql.sock

basedir = /usr/local/webserver/mysql

datadir = /media/raid10/mysql/3306/data

log-error = /media/raid10/mysql/3306/mysql_error.log

pid-file = /media/raid10/mysql/3306/mysql.pid

open_files_limit    = 10240

back_log = 600

max_connections = 5000

max_connect_errors = 6000

table_cache = 614

external-locking = FALSE

max_allowed_packet = 16M

sort_buffer_size = 1M

join_buffer_size = 1M

thread_cache_size = 300

#thread_concurrency = 8

query_cache_size = 20M

query_cache_limit = 2M

query_cache_min_res_unit = 2k

default-storage-engine = MyISAM

thread_stack = 192K

transaction_isolation = READ-COMMITTED

tmp_table_size = 20M

max_heap_table_size = 20M

long_query_time = 3

log-slave-updates

log-bin = /media/raid10/mysql/3306/binlog/binlog

binlog-do-db=blog

binlog-ignore-db=mysql

binlog_cache_size = 4M

binlog_format = MIXED

max_binlog_cache_size = 8M

max_binlog_size = 20M

relay-log-index = /media/raid10/mysql/3306/relaylog/relaylog

relay-log-info-file = /media/raid10/mysql/3306/relaylog/relaylog

relay-log = /media/raid10/mysql/3306/relaylog/relaylog

expire_logs_days = 30

key_buffer_size = 10M

read_buffer_size = 1M

read_rnd_buffer_size = 6M

bulk_insert_buffer_size = 4M

myisam_sort_buffer_size = 8M

myisam_max_sort_file_size = 20M

myisam_repair_threads = 1

myisam_recover

interactive_timeout = 120

wait_timeout = 120

skip-name-resolve

#master-connect-retry = 10

slave-skip-errors = 1032,1062,126,1114,1146,1048,1396

#master-host     =   192.168.1.2

#master-user     =   username

#master-password =   password

#master-port     =  3306

server-id = 1

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 20M

innodb_data_file_path = ibdata1:56M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit = 2

innodb_log_buffer_size = 16M

innodb_log_file_size = 20M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

innodb_file_per_table = 0

#log-slow-queries = /media/raid10/mysql/3306/slow.log

#long_query_time = 10

[mysqldump]

quick

max_allowed_packet = 32M

四.在master機上為slave機添加一同步帳號

mysql> grant replication slave on *.* to 'admin'@'172.29.141.115' identified by '12345678';

mysql> flush privileges ;

1 2 3 4 下一頁