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

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 status/G

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

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

print ?

01	MySQL> change master to master_host='192.168.1.202',master_user='replication',master_password='replication',master_log_file='MySQL-bin.000003',master_log_pos=374;

02	Query OK, 0 rows affected (0.05 sec)

03

04	MySQL> start slave;

05	Query OK, 0 rows affected (0.00 sec)

06

07	MySQL> show slave status/G

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

09	Slave_IO_State: Waiting for master to send event

10	Master_Host: 192.168.1.202

11	Master_User: replication

12	Master_Port: 3306

13	Connect_Retry: 60

14	Master_Log_File: MySQL-bin.000003

15	Read_Master_Log_Pos: 374

16	Relay_Log_File: MySQL-master1-relay-bin.000002

17	Relay_Log_Pos: 235

18	Relay_Master_Log_File: MySQL-bin.000003

19	Slave_IO_Running: Yes

20	Slave_SQL_Running: Yes

21	Replicate_Do_DB:

22	Replicate_Ignore_DB:

23	Replicate_Do_Table:

24	Replicate_Ignore_Table:

25	Replicate_Wild_Do_Table:

26	Replicate_Wild_Ignore_Table:

27	Last_Errno: 0

28	Last_Error:

29	Skip_Counter: 0

30	Exec_Master_Log_Pos: 374

31	Relay_Log_Space: 235

32	Until_Condition: None

33	Until_Log_File:

34	Until_Log_Pos: 0

35	Master_SSL_Allowed: No

36	Master_SSL_CA_File:

37	Master_SSL_CA_Path:

38	Master_SSL_Cert:

39	Master_SSL_Cipher:

40	Master_SSL_Key:

41	Seconds_Behind_Master: 0

42	1 row in set (0.00 sec)