MySQL互为主从配置_MySQL

MySQL-VIP：192.168.1.200

2 MySQL-master1：192.168.1.201

3 MySQL-master2：192.168.1.202

4

5 OS版本：CentOS 5.4

6 MySQL版本：5.0.89

7 Keepalived版本：1.1.20

　一、MySQL master-master配置

　　1、修改MySQL配置文件

　　两台MySQL均如要开启binlog日志功能，开启方法：在MySQL配置文件[MySQLd]段中加上log-bin=MySQL-bin选项

　　两台MySQL的server-ID不能一样，默认情况下两台MySQL的serverID都是1，需将其中一台修改为2即可

　　2、将192.168.1.201设为192.168.1.202的主服务器

　　在192.168.1.201上新建授权用户

view source
< id="highlighter_12830_clipboard" title="copy to clipboard" classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" width="16" height="16" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,0,0" type="application/x-shockwave-flash">
print?
01 MySQL> grant replication slave on *.* to replication@% identified by replication;

02 Query OK, 0 rows affected (0.00 sec)

03

04 MySQL> show master status;

05 +------------------+----------+--------------+------------------+

06 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

07 +------------------+----------+--------------+------------------+

08 | MySQL-bin.000003 | 374 | | |

09 +------------------+----------+--------------+------------------+

10 1 row in set (0.00 sec)

11 　　在192.168.1.202上将192.168.1.201设为自己的主服务器

12

13 MySQL> change master to master_host=192.168.1.201,master_user=replication,master_password=replication,master_log_file=MySQL-bin.000003,master_log_pos=374;

14 Query OK, 0 rows affected (0.05 sec)

15

16 MySQL> start slave;

17 Query OK, 0 rows affected (0.00 sec)

18

19 MySQL> show slave statusG

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

21 Slave_IO_State: Waiting for master to send event

22 Master_Host: 192.168.1.201

23 Master_User: replication

24 Master_Port: 3306

25 Connect_Retry: 60

26 Master_Log_File: MySQL-bin.000003

27 Read_Master_Log_Pos: 374

28 Relay_Log_File: MySQL-master2-relay-bin.000002

29 Relay_Log_Pos: 235

30 Relay_Master_Log_File: MySQL-bin.000003

31 Slave_IO_Running: Yes

32 Slave_SQL_Running: Yes

33 Replicate_Do_DB:

34 Replicate_Ignore_DB:

35 Replicate_Do_Table:

36 Replicate_Ignore_Table:

37 Replicate_Wild_Do_Table:

38 Replicate_Wild_Ignore_Table:

39 Last_Errno: 0

40 Last_Error:

41 Skip_Counter: 0

42 Exec_Master_Log_Pos: 374

43 Relay_Log_Space: 235

44 Until_Condition: None

45 Until_Log_File:

46 Until_Log_Pos: 0

47 Master_SSL_Allowed: No

48 Master_SSL_CA_File:

49 Master_SSL_CA_Path:

50 Master_SSL_Cert:

51 Master_SSL_Cipher:

52 Master_SSL_Key:

53 Seconds_Behind_Master: 0

54 1 row in set (0.00 sec)

　　3、将192.168.1.202设为192.168.1.201的主服务器

　　在192.168.1.202上新建授权用户

view source
< id="highlighter_331829_clipboard" title="copy to clipboard" classid="clsid:d27cdb6e-ae6d-11cf-96b8-444553540000" width="16" height="16" codebase="http://download.macromedia.com/pub/shockwave/cabs/flash/swflash.cab#version=9,0,0,0" type="application/x-shockwave-flash">
print?
01 MySQL> grant replication slave on *.* to replication@% identified by replication;

02 Query OK, 0 rows affected (0.00 sec)

03

04 MySQL> show master status;

05 +------------------+----------+--------------+------------------+

06 | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

07 +------------------+----------+--------------+------------------+

08 | MySQL-bin.000003 | 374 | | |

09 +------------------+----------+--------------+------------------+

10 1 row in set (0.00 sec)

　　在192.168.1.201上，将192.168.1.202设为自己的主服务器

view source
< id="highlighter_574354_clipboard" title="copy to clipboard" classid="clsid:d27cdb6 bitsCN.com