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

####################

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

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_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

         |

参考文档：

<a href="http://forums.gentoo.org/viewtopic.php?t=241123">http://forums.gentoo.org/viewtopic.php?t=241123</a>

<a href="http://www.howtoforge.com/mysql_database_replication_p2">http://www.howtoforge.com/mysql_database_replication_p2</a>

<a href="http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html">http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html</a>

<a href="http://bbs.chinaunix.net/viewthread.php?tid=692359&amp;highlight=mysql">http://bbs.chinaunix.net/viewthread.php?tid=692359&amp;highlight=mysql</a>