mysql 主從同步

確認主從兩伺服器的MySQL，特別是從伺服器的預設連結編碼，否則同步可能會出現亂碼。

[mysqld]

server-id = 2
slave-skip-errors=all
default-character-set=utf8
expire_logs_days=8;

[client]
default-character-set=utf8

 

binlog:

set global expire_logs_days=8;

PURGE MASTER LOGS TO ‘mysql-bin.010′;
PURGE MASTER LOGS BEFORE ‘2008-06-22 13:00:00′;
清除3天前的 binlog
PURGE MASTER LOGS BEFORE DATE_SUB( NOW( ), INTERVAL 3 DAY);
BEFORE變數的date自變數可以為’YYYY-MM-DD hh:mm:ss’格式。MASTER和BINARY是同義字。

 

 

 

修改主伺服器：

log-bin=/data/mysql/bin

binlog_do_db = uc
binlog_do_db = web

#下面是不需要同步的

#binlog-ignore-db = mysql
#binlog-ignore-db = test
#binlog-ignore-db = information_schema
#binlog-ignore-db = cacti

 

查看主要資料庫狀態：

show master status;

GRANT REPLICATION SLAVE ON *.* to 'sync'@'%' identified by 'sync123456';

 change master to aster_host='192.168.145.222',master_user='tb',master_password='q123456',master_log_file='mysql-bin.000004',master_log_pos=308;

show slave status\G

              Slave_IO_Running: Yes       //此狀態必須YES

             Slave_SQL_Running: Yes       //此狀態必須YES

 

如果不是 yes，執行：

stop slave;SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1000; START SLAVE;

set global  slave-skip-errors = 1062

 

mysql的binlog詳解

 

什麼是binlog
binlog日誌用於記錄所有更新了資料或者已經潛在更新了資料（例如，沒有匹配任何行的一個DELETE）的所有語句。語句以“事件”的形式儲存，它描述資料更改。

binlog作用
因為有了資料更新的binlog，所以可以用於即時備份，與master/slave複製

和binlog有關參數

log_bin
設定此參數表示啟用binlog功能，並指定路徑名稱
log_bin_index
設定此參數是指定二進位索引檔案的路徑與名稱
binlog_do_db
此參數表示只記錄指定資料庫的二進位日誌
binlog_ignore_db
此參數表示不記錄指定的資料庫的二進位日誌
max_binlog_cache_size
此參數表示binlog使用的記憶體最大的尺寸
binlog_cache_size
此參數表示binlog使用的記憶體大小，可以通過狀態變數binlog_cache_use和binlog_cache_disk_use來協助測試。
       binlog_cache_use：使用二進位日誌緩衝的事務數量
       binlog_cache_disk_use:使用二進位日誌緩衝但超過binlog_cache_size值並使用臨時檔案來儲存事務中的語句的事務數量

max_binlog_size
Binlog最大值，最大和預設值是1GB，該設定並不能嚴格控制Binlog的大小，尤其是Binlog比較靠近最大值而又遇到一個比較大事務時，為了保證事務的完整性，不可能做切換日誌的動作，只能將該事務的所有SQL都記錄進當前日誌，直到事務結束

sync_binlog
這個參數直接影響mysql的效能和完整性

sync_binlog=0：
當事務提交後，Mysql僅僅是將binlog_cache中的資料寫入Binlog檔案，但不執行fsync之類的磁碟        同步指令通知檔案系統將緩衝重新整理到磁碟，而讓Filesystem自行決定什麼時候來做同步，這個是效能最好的。

sync_binlog=n，在進行n次事務提交以後，Mysql將執行一次fsync之類的磁碟同步指令，同志檔案系統將Binlog檔案快取重新整理到磁碟。

Mysql中預設的設定是sync_binlog=0，即不作任何強制性的磁碟排清指令，這時效能是最好的，但風險也是最大的。一旦系統繃Crash，在檔案系統快取中的所有Binlog資訊都會丟失

binlog的刪除
binlog的刪除可以手工刪除或自動刪除

自動刪除binlog
通過binlog參數（expire_logs_days ）來實現mysql自動刪除binlog
mysql> show binary logs;
mysql> show variables like 'expire_logs_days';
mysql> set global expire_logs_days=3;

手工刪除binlog
mysql> reset master;   //刪除master的binlog
mysql> reset slave;    //刪除slave的中繼日誌
mysql> purge master logs before '2012-03-30 17:20:00';  //刪除指定日期以前的日誌索引中binlog記錄檔
mysql> purge master logs to 'binlog.000002';   //刪除指定記錄檔的日誌索引中binlog記錄檔
或者直接用作業系統命令直接刪除

mysql> set sql_log_bin=1/0; //如果使用者有super許可權，可以啟用或禁用當前會話的binlog記錄

mysql> show master logs; //查看master的binlog日誌 
mysql> show binary logs; //查看master的binlog日誌
mysql> show master status; //用於提供master二進位記錄檔的狀態資訊
mysql> show slave hosts; //顯示當前註冊的slave的列表。不以--report-host=slave_name選項為開頭的slave不會顯示在本列表中

binglog的查看
通過mysqlbinlog命令可以查看binlog的內容
[root@localhost ~]# mysqlbinlog  /home/mysql/binlog/binlog.000003  | more

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#120330 16:51:46 server id 1  end_log_pos 98    Start: binlog v 4, server v 5.0.45-log created 120330 1
6:51:46
# Warning: this binlog was not closed properly. Most probably mysqld crashed writing it.
# at 196
#120330 17:54:15 server id 1  end_log_pos 294   Query   thread_id=3     exec_time=2     error_code=0
SET TIMESTAMP=1333101255/*!*/;
insert into tt7 select * from tt7/*!*/;
# at 294
#120330 17:54:46 server id 1  end_log_pos 388   Query   thread_id=3     exec_time=28    error_code=0
SET TIMESTAMP=1333101286/*!*/;
alter table tt7 engine=innodb/*!*/;

 

解析binlog格式

位置
位於檔案中的位置，“at 196”說明“事件”的起點，是以第196位元組開始；“end_log_pos 294”說明以第294位元組結束

 

時間戳記
事件發生的時間戳記：“120330 17:54:46”

 

事件執行時間
事件執行花費的時間:"exec_time=28"

 

錯誤碼
錯誤碼為：“error_code=0”

 

伺服器的標識
伺服器的標識id：“server id 1”