Windows环境下Mysql主从服务器(replication)搭建

Eddy 发布于2013-3-29 12:44:3 分类: 技术心得 已浏览loading 网友评论0条 我要评论

试验Mysql版本:Master-5.5.29,Slave-5.5.29。

1、配置主从服务器

Master服务器配置:

log-bin=mysql-bin

log-bin-index=mysql-bin.index

server-id=1

binlog-format=MIXED

expire-logs-days=7

sync-binlog=1

skip-slave-start = TRUE

 

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

在master服务器上创建一个专门用于复制的mysql用户:

mysql> create user 'repl'@'%' identified by '123456';

mysql> grant replication slave on *.* to 'repl'@'%';

mysql> flush privileges;

Slave服务器配置:

 log-bin=mysql-bin

log-bin-index=mysql-bin.index

server-id=2

binlog-format=MIXED

expire-logs-days=7

sync-binlog=1

skip-slave-start = TRUE

 

relay-log = slave-relay-bin

relay-log-index = slave-relay-bin.index

#yeepay为需要同步的数据库名

replicate-do-db=yeepay

2、获取master服务器快照及log_bin的pos。

开一个命令行窗口连接到mysql:

mysql> flush tables with read lock;

再开一个命令行窗口连接到mysql:

 

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000003 |    34319 |              |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

红色信息是我们需要的,记下。

 

在cmd下用mysqldump命令导出数据:

mysqldump  --default-character-set=gbk -u root -p yeepay > c:/bk.sql

完成后在最先的mysql连接窗口中:

mysql> unlock tables;

3、启动slaver

首先导入步骤2中获取的bk.sql中的数据:

mysql> source c:bk.sql;

然后启动slaver,需要用到步骤2中的红色字体信息及步骤1中蓝色字体信息:

 

mysql> change master to

    -> master_host = '58.64.xx.xx',

    -> master_user = 'repl',

    -> master_password='123456',

    -> master_port=3306,

    -> master_log_file='mysql-bin.000003',

    -> master_log_pos=34319,

    -> master_connect_retry=10;

Query OK, 0 rows affected (0.09 sec)

至此,整个配置完成,不出意外的话现在应该能够正常同步数据了:)

测试下,在master服务器插入一条数据:

insert into XXXX values (null,'111111');

这时在slave端这条数据已经同步过来了:

 

mysql> select * from XXXX;

+----+-------------+

| id | orderNum    |

+----+-------------+

| 35 | 111111      |

+----+-------------+

mysql> show processlist\G;

*************************** 1. row ***************************

     Id: 8

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 11375

  State: Waiting for master to send event

   Info: NULL

*************************** 2. row ***************************

     Id: 9

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 355

  State: Slave has read all relay log; waiting for the slave I/O thread to updat

e it

   Info: NULL 

官方文档参考:http://dev.mysql.com/doc/refman/5.5/en/replication-howto.html

已经有(0)位网友发表了评论,你也评一评吧!
原创文章如转载,请注明:转载自Eddy Blog
原文地址:http://www.rrgod.com/technique/875.html     欢迎订阅Eddy Blog

记住我的信息,下次不用再输入 欢迎给Eddy Blog留言