试验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。
Tags:Mysql replication Windows