[轉]windows下mysql主從同步備份步驟

From : http://jbyw.blog.163.com/blog/static/1104520071017819198/

環境：
A、B的MySQL資料庫版本同為5.0.24

A：主伺服器
作業系統：Windows xp
IP地址：192.168.0.81
B：從伺服器
作業系統：Windows xp
的IP地址：192.168.0.82

配置過程：
1、在A的資料庫中建立一個備份帳戶，命令如下：
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*
TO test@'192.168.0.82'
IDENTIFIED BY '1234';

建立一個帳戶test，並且只能允許從192.168.0.82這個地址上來登陸，密碼是1234。

在B Slave上做串連測試:

mysql -h 192.168.0.81 -u test -p

2、對A伺服器的配置進行修改，開啟mysql/my.ini檔案，在[mysqld]下面添加如下內容：
server-id=1
log-bin=D:\server2\mysql\log-bin.log

server-id：為主伺服器A的ID值
log-bin：二進位變更日值

3、重啟A伺服器，從現在起，它將把客戶堆有關資料庫的修改記載到二進位變更日誌裡去。

4、對B伺服器的配置進行修改，開啟mysql/my.ini檔案，在[mysqld]下面添加如下內容：
[mysqld]
server-id=2
master-host=192.168.0.81
master-user=test
master-password=1234

master-port=3306 #主伺服器連接埠
master-connect-retry=60 #同步時間間隔為60秒
//以下內容為可選

#需要同步的資料庫
replicate-do-db=test

#不需要同步的資料庫,忽略掉
binlog-ignore-db=mysql

server-id：從伺服器B的ID值。注意不能和主伺服器的ID值相同。
master-host：主伺服器的IP地址。
master-user：從伺服器串連主伺服器的帳號。
master-password：從伺服器串連主伺服器的帳號密碼。
replicate-do-db：告訴主伺服器只對指定的資料庫進行同步鏡像。

binlog-ignore-db：告訴主伺服器不對指定的資料庫進行同步鏡像。

5、重啟從伺服器B。至此所有設定全部完成。更新A中的資料，B中也會立刻進行同步更新。如果從伺服器沒有進行同步更新，你可以通過查看從伺服器中的mysql_error.log記錄檔進行排錯

 

在master上，其實不需要做什麼設定，只需要開啟log-bin,寫上server-id=1,寫上要備份的資料庫，則自動是master模式，於是問題主要集中在slave上.那麼slave上是如何工作的呢?
Slave 上Mysql的Replication工作有兩個線程,I/O thread和SQL thread,I/O 的作用是從master 3306連接埠上把它的binlog取過來(master在被修改了任何內容之後,就會把修改了什麼寫到自己的binlog等待slave更新),然後寫到本地的relay-log,而SQL thread則是去讀本地的relay-log,再把它轉換成本Mysql所能理解的東西，於是同步就這樣一步一步的完成.決定I/O thread的是mysql/master.info,而決定SQL thread的是mysql/relay-log.info.
請注意，因為上邊提到了binlog裡的內容是改了什麼東東,而不是改了以後是什麼東東,所以在進行同步之前必須保證兩個資料庫是完全相同的,不然可能出錯.打個比方來說.A機上有一個表裡的元組為2,而操作是減一,則binlog只會記錄減一這個操作，如果B機上沒有，那麼則無法得到同步,因為B機沒有這個欄位，就不知道減一是什麼操作.

 

 

 

錯誤FAQ:

1,主從不能同步:

show slave status;報錯:Error xxx dosn't exist
且show slave status\G:
Slave_SQL_Running: NO
Seconds_Behind_Master: NULL

解決方法:
stop slave;
set global sql_slave_skip_counter =1 ;
start slave;

之後Slave會和Master去同步 主要看:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否為0，0就是已經同步了

2,還需要做的一些最佳化與監視:
show full processlist; //查看mysql當前同步線程號
skip-name-resolve       //跳過dns名稱查詢，有助於加快串連及同步的速度
max_connections=1000    //增大Mysql的串連數目，(預設100)
max_connect_errors=100 //增大Mysql的錯誤串連數目,(預設10)

 

查看日誌一些命令
1,  show master status\G;
           在這裡主要是看log-bin的檔案是否相同。
    show slave status\G;
    在這裡主要是看:
                   Slave_IO_Running=Yes
                   Slave_SQL_Running=Yes
   如果都是Yes,則說明配置成功.

2,在master上輸入show processlist\G;
     mysql> SHOW PROCESSLIST\G
     *************************** 1. row ***************************
       Id: 2
       User: root
       Host: localhost:32931
       db: NULL
       Command: Binlog Dump
       Time: 94
       State: Has sent all binlog to slave; waiting for binlog to
         be updated
       Info: NULL

   如果出現Command: Binlog Dump,則說明配置成功.

 

 

 

 

stop slave    #停止同步

start slave    #開始同步，從日誌終止的位置開始更新。

SET SQL_LOG_BIN=0|1  #主機端運行，需要super許可權，用來開停日誌，隨意開停，會造成主機從機資料不一致，造成錯誤

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=n  # 用戶端運行，用來跳過幾個事件，只有當同步進程出現錯誤而停止的時候才可以執行。

RESET MASTER  #主機端運行,清除所有的日誌，這條命令就是原來的FLUSH MASTER

RESET SLAVE   #從機運行，清除日誌同步位置標誌，並重建master.info
雖然重建了master.info,但是並不起用，最好，將從機的mysql進程重啟一下，

LOAD TABLE tblname FROM MASTER #從機運行，從主機端重讀指定的表的資料，每次只能讀取一個，受timeout時間限制，需要調整timeout時間。執行這個命令需要同步帳號有 reload和super許可權。以及對相應的庫有select許可權。如果表比較大，要增加net_read_timeout 和 net_write_timeout的值

LOAD DATA FROM MASTER  #從機執行，從主機端重新讀入所有的資料。執行這個命令需要同步帳號有reload和super許可權。以及對相應的庫有select許可權。如果表比較大，要增加net_read_timeout 和 net_write_timeout的值

CHANGE MASTER TO master_def_list  #線上改變一些主機設定，多個用逗號間隔,比如
CHANGE MASTER TO
  MASTER_HOST='master2.mycompany.com',
  MASTER_USER='replication',s
  MASTER_PASSWORD='bigs3cret'

MASTER_POS_WAIT() #從機運行

SHOW MASTER STATUS #主機運行，看日誌匯出資訊

SHOW SLAVE HOSTS #主機運行，看連入的從機的情況。

SHOW SLAVE STATUS (slave)

SHOW MASTER LOGS (master)

SHOW BINLOG EVENTS [ IN 'logname' ] [ FROM pos ] [ LIMIT [offset,] rows ]

PURGE [MASTER] LOGS TO 'logname' ; PURGE [MASTER] LOGS BEFORE 'date'