?
                    ?
                    当前位置:首页 ? 技术分享 ? 数据中心

                    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
                    在线QQ:1623634940
                    传真:029-88272226-8032
                    e-mail:bodaxitong@163.com
                    地址:陕西省西安市未央区太华北路369号万达广场3号甲写1401室
                    网站建设艺源视网

                    官方微信

                    Copyright ? 2019 陕西博大系统信息技术有限公司. 陕ICP备19023696号-1
                    在线购彩快三