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

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用戶

1、master

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;

2、slave

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
在線QQ:1623634940
傳真:029-88272226-8032
e-mail:bodaxitong@163.com
地址:陜西省西安市未央區太華北路369號萬達廣場3號甲寫1401室
網站建設藝源視網

官方微信

Copyright ? 2019 陜西博大系統信息技術有限公司. 陜ICP備19023696號-1
国产精品免费精品自在线观看,国产3p一区二区三区视频,国产尤物一区二区三区在线观看,波兰性交大赛视频,国产不卡一区二区