當前位置:首頁 » 技術分享 » 數據中心

MySQL主主互備結合keepalived實現高可用

發布時間:2016-01-13 15:00:40  來源:  作者:

試驗環境:

master192.168.1.210CentOS6.5

slave192.168.1.211CentOS6.5

VIP192.168.1.208

 

MySQL主主互備模式配置

step1Master服務的/etc/my.cnf配置

1

2

3

4

5

6

7

8

9

10

11

12

[mysqld]

basedir = /usr/local/mysql

datadir = /var/lib/mysql

port = 3306

socket = /var/lib/mysql/mysql.sock

 

server_id = 1

log-bin = mysql-bin

relay-log = mysql-relay-bin

replicate-wild-ignore-table=mysql.%   #指定不需要複製的庫,mysql.%表示mysql庫下的所有對象

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

step2Slave服務的/etc/my.cnf配置

1

2

3

4

5

6

7

8

9

10

11

12

[mysqld]

basedir = /usr/local/mysql

datadir = /var/lib/mysql

port = 3306

socket = /var/lib/mysql/mysql.sock

 

server_id = 2

log-bin = mysql-bin

relay-log = mysql-relay-bin

replicate-wild-ignore-table=mysql.%

replicate-wild-ignore-table=test.%

replicate-wild-ignore-table=information_schema.%

step3:重啟兩台主從mysql服務

1

2

3

4

5

6

[root@master ~]# service mysqld restart

Shutting down MySQL..                                      [  OK  ]

Starting MySQL.                                            [  OK  ]

[root@slave ~]# service mysqld restart

Shutting down MySQL..                                      [  OK  ]

Starting MySQL.                                            [  OK  ]

 

step4:查看主從的log-bin日誌狀態

記錄FilePosition的值

1

2

3

4

5

6

7

[root@master ~]# mysql -uroot -ppasswd -e 'show master status'

Warning: Using a password on the command line interface can be insecure.

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      414 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

1

2

3

4

5

6

7

[root@slave ~]# mysql -uroot -ppasswd -e 'show master status'

Warning: Using a password on the command line interface can be insecure.

+------------------+----------+--------------+------------------+-------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000001 |      414 |              |                  |                   |

+------------------+----------+--------------+------------------+-------------------+

 

step5:創建主從同步replication用戶

1master

1

2

3

4

5

6

7

8

9

10

mysql> grant replication slave on *.* to 'replication'@'192.168.1.%' identified by 'replication';

mysql> flush privileges;

mysql> change master to

    -> master_host='192.168.1.211',

    -> master_user='replication',

    -> master_password='replication',

    -> master_port=3306,

    -> master_log_file='mysql-bin.000001',

    -> master_log_pos=414;

mysql> start slave;

2slave

1

2

3

4

5

6

7

8

9

10

mysql> grant replication slave on *.* to 'replication'@'192.168.1.%' identified by 'replication';<

公司簡介
company profile

解決方案
solution

客戶案例
Customer case

電話:029-88213996/ 029-88272226/ 029-88242862
在線QQ:1623634940
傳真:029-88272226-8032
e-mail:bodaxitong@163.com
地址:西安市雁塔區太白南路181號西部電子社區軟件公寓B座707室
網站建設藝源視網

官方微信