mySQL資料同步設定

使用的mysql版本：mysql4.0.X

master: 192.168.1.102
slave:    192.168.1.109

如果是win機子修改c:/winnt/my.ini 如果是linux的機子請修改 /etc/my.cnf

一、master操作
1、修改my.ini
##############
server-id=1
log-bin=c:/mysql/logs/mysql_binary_log
binlog-do-db=test
binlog-ignore-db=mysql
##############

說明
binlog-do-db=test                  允許同步的資料庫
binlog-ignore-db=mysql      不允許同步的資料庫

2、然後重啟mysql
c:/mysql/bin/mysql restart  或 /etc/init.d/mysql restart

3、在master上增加一個同步的使用者名稱
mysql> GRANT REPLICATION SLAVE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';
注:
如果mysql版本在4.0.2以前的版本請用
mysql> GRANT FILE ON *.* TO 'backup'@'%' IDENTIFIED BY '123456';

4、接下來操作要master上要同步的資料庫
mysql> USE test;
mysql> FLUSH TABLES WITH READ LOCK;       #鎖定要同步的test表，然後匯出資料結構

執行如下命令查看master的狀態
mysql> SHOW MASTER STATUS;

得到如下結果
Code:
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_do_db | Binlog_ignore_db |
+----------------------+----------+--------------+------------------+
| mysql_binary_log.025 | 796947     | test            |  mysql              |
+----------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

接下來備份要同步資料庫（為匯入slave作準備）
$ mysqldump --opt test > test.sql

mysql> UNLOCK TABLES;                   #已做好同步資料庫結構匯出後，解鎖這個表

二、slave操作
1、把master裡匯出的同步資料庫結構再匯入slave的mysql裡
mysql tes < test.sql

2、修改slave的my.ini
####################
log-bin=c:/mysql/logs/mysql_binary_log
server-id=2
master-host=192.168.1.102
master-user=backup
master-password=123456
master-connect-retry=60
replicate-do-db=test
####################

說明：
master-host=192.168.1.102                #master的IP
master-user=backup                            #master上作為同步用的使用者名稱
master-password=123456                 #同步處理的使用者名的密碼
master-connect-retry=60                     #設定同步的時間
replicate-do-db=test                             #需要同步的資料庫

3、重新啟用mysql
c:/mysql/bin/mysql restart  或 /etc/init.d/mysql restart

4、進入slave的mysql，對mysql進行操作
mysql> stop slave;                  #停止slave伺服器

mysql> CHANGE MASTER TO
    ->     MASTER_HOST='192.168.1.102',
    ->     MASTER_USER='backup',
    ->     MASTER_PASSWORD='123456',
    ->     MASTER_LOG_FILE='mysql_binary_log.025',
    ->     MASTER_LOG_POS=796947;

mysql> START SLAVE;         #開啟slave伺服器就可以同步了

註：
  MASTER_LOG_FILE='mysql_binary_log.025',
  MASTER_LOG_POS=796947;
上面這兩條是一開始從master上進入mysql，運行 SHOW MASTER STATUS; 查看到的,在實際操作中也可以不加的。

 

 

##############################################
在master的mysql裡運行
mysql > show processlist;
看到兩個 system user 就是正常的
+----+-------------+----------------+------+-------------+------+---------------
--------------------------------------------------------+------------------+
| Id | User        | Host           | db   | Command     | Time | State
                                                        | Info             |
+----+-------------+----------------+------+-------------+------+---------------
--------------------------------------------------------+------------------+
|  1 | system user |                | NULL | Connect     | 4499 | Waiting for ma
ster to send event                                      | NULL             |
|  2 | system user |                | NULL | Connect     | 4499 | Has read all r
elay log; waiting for the I/O slave thread to update it | NULL             |
|  7 | root        | localhost:1309 | test | Query       | 0    | NULL
                                                        | show processlist |
| 40 | backup      | ETE-KF2:1354   | NULL | Binlog Dump | 513  | Has sent all b
inlog to slave; waiting for binlog to be updated        | NULL             |
+----+-------------+----------------+------+-------------+------+---------------
--------------------------------------------------------+------------------+
4 rows in set (0.00 sec)

mysql> show master status;
出現如下:
+----------------------+----------+--------------+------------------+
| File                 | Position | Binlog_do_db | Binlog_ignore_db |
+----------------------+----------+--------------+------------------+
| mysql_binary_log.001 | 1011     | test         | mysql            |
+----------------------+----------+--------------+------------------+

在slave的mysql裡運行
mysql> show processlist;
出現如下：
+----+-------------+----------------+------+---------+------+-------------------
----------------------------------------------------+------------------+
| Id | User        | Host           | db   | Command | Time | State
                                                    | Info             |
+----+-------------+----------------+------+---------+------+-------------------
----------------------------------------------------+------------------+
|  3 | root        | localhost:1353 | test | Query   | 0    | NULL
                                                    | show processlist |
|  4 | system user |                | NULL | Connect | 740  | Waiting for master
 to send event                                      | NULL             |
|  5 | system user |                | NULL | Connect | 730  | Has read all relay
 log; waiting for the I/O slave thread to update it | NULL             |
+----+-------------+----------------+------+---------+------+-------------------
----------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> show slave status;
+---------------+-------------+-------------+---------------+-------------------
---+---------------------+-----------------------+---------------+--------------
---------+------------------+-------------------+-----------------+-------------
--------+------------+------------+--------------+---------------------+--------
---------+
| Master_Host   | Master_User | Master_Port | Connect_retry | Master_Log_File
   | Read_Master_Log_Pos | Relay_Log_File        | Relay_Log_Pos | Relay_Master_
Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ig
nore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_l
og_space |
+---------------+-------------+-------------+---------------+-------------------
---+---------------------+-----------------------+---------------+--------------
---------+------------------+-------------------+-----------------+-------------
--------+------------+------------+--------------+---------------------+--------
---------+
| 192.168.1.102 | backup      | 3306        | 10            | mysql_binary_log.0
01 | 1011                | ete-kf2-relay-bin.002 | 227           | mysql_binary_
log.001  | Yes              | Yes               | test            |
        | 0          |            | 0            | 1011                | 223
         |
+---------------+-------------+-------------+---------------+-------------------
---+---------------------+-----------------------+---------------+--------------
---------+------------------+-------------------+-----------------+-------------
--------+------------+------------+--------------+---------------------+--------
---------+
1 row in set (0.00 sec)

 

參考文檔：
http://forums.gentoo.org/viewtopic.php?t=241123
http://www.howtoforge.com/mysql_database_replication_p2
http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html
http://bbs.chinaunix.net/viewthread.php?tid=692359&highlight=mysql