MySQL master-slave Configuration

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 version: 5.0.89

7 Keepalived version: 1.1.20

1. MySQL master-master Configuration

1. Modify the MySQL configuration file

To enable the binlog function for both MySQL instances, enable the function by adding log-bin = MySQL-bin in the [MySQLd] section of the MySQL configuration file.

The server-ID of the two MySQL instances cannot be the same. By default, the serverID of the two MySQL instances is 1 and you need to change one of them to 2.

2. Set 192.168.1.201 to the master server of 192.168.1.202.

Create an authorized user on 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 on 192.168.1.202, set 192.168.1.201 as the primary server.

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 MAID: 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. Set 192.168.1.202 to the master server of 192.168.1.201.

Create an authorized user on 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)

On 192.168.1.201, set 192.168.1.202 as your master server.

View source
<Id = "highlighter_574354_clipboard" title = "copy to clipboard" classid = "clsid: d27cdb6