测试环境：
vmware
workstation 7.1.4
两台虚拟机
master1：
OS：Windows
XP SP3
IP：192.168.61.201
DB：MySQL
5.1.6-win32
master2：
OS：Windows
XP SP3
IP：192.168.61.202
DB：MySQL
5.1.6-win32
一、创建同步用户，该用户只是访问bin日志。
1、在master1上
mysql>
grant replication slave,file on *.* to 'repl'@'192.168.61.202'
identified by
'123456'; #master2的IP地址
Query
OK, 0 rows affected (0.00 sec)
mysql>
flush privileges;
Query
OK, 0 rows affected (0.00 sec)
2、在master2上
mysql>
grant replication slave,file on *.* to 'repl'@'192.168.61.201'
identified by
'123456'; #master1的IP地址
Query
OK, 0 rows affected (0.00 sec)
mysql>
flush privileges;
Query
OK, 0 rows affected (0.00 sec)
二、修改配置文件my.ini
1、在master1上
#master1
log-bin=mysql-bin
server-id=1
#数据库服务器的id，两台服务器的该参数不能相同
binlog-do-db=abc
binlog-ignore-db=mysql
replicate-do-db=abc #同步的数据库
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
#整个结构中服务器的总数
auto_increment_offset=1
#自动增长的起点
2、在master2上
#master2
log-bin=mysql-bin
server-id=2
binlog-do-db=abc
binlog-ignore-db=mysql
replicate-do-db=abc
replicate-ignore-db=mysql
log-slave-updates
slave-skip-errors=all
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=2
分别重启master1和master2.
三、确定master数据库的binlog信息
1、在master1上
mysql>
flush tables with read lock;
Query
OK, 0 rows affected (0.00 sec)
mysql>
show master status;
+------------------+----------+--------------+------------------+
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|
mysql-bin.000001 | 106 | ipms
| mysql |
+------------------+----------+--------------+------------------+
1 row
in set (0.00 sec)
2、在master2上
mysql>
flush tables with read lock;
Query
OK, 0 rows affected (0.00 sec)
mysql>
show master status;
+------------------+----------+--------------+------------------+
| File
| Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
|
mysql-bin.000003 | 106 | ipms
| mysql |
+------------------+----------+--------------+------------------+
1 row
in set (0.00 sec)
四、在slave上设置master信息
1、在master1上
mysql>
change master to
->
master_host='192.168.61.202',
->
master_user='repl',
->
master_password='123456',
->
master_log_file='mysql-bin.000003',
->
master_log_pos=106;
Query
OK, 0 rows affected (0.01 sec)
mysql>
start slave;
Query
OK, 0 rows affected (0.00 sec)
mysql>
show processlist;
+----+-------------+---------------------+------+-------------+------+----------
-------------------------------------------------------------+------------------
+
| Id |
User | Host | db | Command | Time | State
| Info
|
+----+-------------+---------------------+------+-------------+------+----------
-------------------------------------------------------------+------------------
+
| 1 |
root | localhost:1111 | NULL | Query | 0 | NULL
| show
processlist
|
| 4 |
system user | | NULL | Connect | 187 |
Waiting f
or
master to send event | NULL
|
| 5 |
system user | | NULL | Connect | 182 | Has
read
all
relay log; waiting for the slave I/O thread to update it | NULL
|
| 6 |
repl | 192.168.61.202:1195 | NULL | Binlog Dump | 35 | Has
sent
all
binlog to slave; waiting for binlog to be updated | NULL
|
+----+-------------+---------------------+------+-------------+------+----------
-------------------------------------------------------------+------------------
+
4 rows
in set (0.00 sec)
6为master的IO线程;4为slave的IO线程，5为slave的sql更新线程。
2、在master2上
mysql>
change master to
->
master_host='192.168.61.201',
->
master_user='repl',
->
master_password='123456',
->
master_log_file='mysql-bin.000001',
->
master_log_pos=106;
Query
OK, 0 rows affected (0.03 sec)
mysql>
start slave;
Query
OK, 0 rows affected (0.00 sec)
mysql>
show processlist;
+----+-------------+---------------------+------+-------------+------+----------
-------------------------------------------------------------+------------------
+
| Id |
User | Host | db | Command | Time | State
| Info
|
+----+-------------+---------------------+------+-------------+------+----------
-------------------------------------------------------------+------------------
+
| 1 |
root | localhost:1194 | NULL | Query | 0 | NULL
| show
processlist
|
| 4 |
repl | 192.168.61.201:1112 | NULL | Binlog Dump | 242 | Has
sent
all
binlog to slave; waiting for binlog to be updated | NULL
|
| 5 |
system user | | NULL | Connect | 100 |
Waiting f
or
master to send event | NULL
|
| 6 |
system user | | NULL | Connect | 95 | Has
read
all
relay log; waiting for the slave I/O thread to update it | NULL
|
+----+-------------+---------------------+------+-------------+------+----------
-------------------------------------------------------------+------------------
+
4 rows
in set (0.02 sec)
4为master的IO线程;5为slave的IO线程，6为slave的sql更新线程。
五、配置完成解锁数据库表
1、在master1上
mysql>
unlock tables;
Query
OK, 0 rows affected (0.00 sec)
2、在master2上
mysql>
unlock tables;
Query
OK, 0 rows affected (0.00 sec)
好了，剩下的事情就是测试了。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/690447/viewspace-712663/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
没有了~
下一篇：
没有了~
请登录后发表评论
登录
全部评论
