mysql数据库复制—双主

一、实现环境

    centos

    master1：172.16.20.245

    master2：172.16.20.219

    两数据库均是新安装

二、master1安装配置

    1配置文件

    #vim /etc/my.cnf

    server-id = 10 

    log-bin = mysql-bin

    log-bin-index = mysql-bin.index

    binlog-format = mixed

    relay-log = relay-mysql

    relay-log-index = relay-mysql.index

    auto-increment-increment = 2

    auto-increment-offset = 1

    innodb_file_per_table=1

    sync-binlog = 1

    innodb_flush_log_at_trx_commit=1

     #service mysqld restart

     2创建复制用户

     mysql>grant replication slave on *.* to ‘user1‘@‘172.16.20.%‘ identified by ‘password1‘;

     mysql>flush privileges;

     3记录二进制日志位置

    mysql> show master status;

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

    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

    | mysql-bin.000003 |      353 |              |                  |

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

三、master2安装配置

    1master2配置文件

    #vim /etc/my.cnf

    server-id = 20

    log-bin = mysql-bin

    log-bin-index = mysql-bin.index

    binlog-format = mixed

    relay-log = relay-mysql

    relay-log-index = relay-mysql.index

    auto-increment-increment = 2

    auto-increment-offset = 2

    innodb_file_per_table=1

    sync-binlog = 1

    innodb_flush_log_at_trx_commit=1

    #service mysqld restart

     2创建复制用户

     mysql>grant replication slave on *.* to ‘user2‘@‘172.16.20.%‘ identified by

‘password2‘;

     mysql>flush privileges;   

     3记录二进制日志位置

    mysql> show master status;

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

    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

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

    | mysql-bin.000002 |      253 |              |                  |

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

四、启动复制

    1master1启动复制

    mysql>CHANGE MASTER TO      

    MASTER_HOST=‘172.16.20.219‘,

    MASTER_USER=‘user2‘,

    MASTER_PASSWORD=‘passwd2‘,

    MASTER_LOG_FILE=‘master-bin.000002‘,

    MASTER_LOG_POS=253;

    mysql>start slave;    

    #或者单独START SLAVE IO_Thread;START SLAVE SQL_Thread;来启动这两个线程

    master2启动复制

    mysql>CHANGE MASTER TO      

    MASTER_HOST=‘172.16.20.245‘,

    MASTER_USER=‘user‘,

    MASTER_PASSWORD=‘passwd‘,

    MASTER_LOG_FILE=‘master-bin.000003‘,

    MASTER_LOG_POS=353;

    mysql>start slave;    

    #或者单独START SLAVE IO_Thread;START SLAVE SQL_Thread;来启动这两个线程

五、检查

    mysql>show slave status\G   

    Slave_IO_Running: Yes

    Slave_SQL_Running: Yes

    以上连个进程启动则表示双主复制启动，双主复制安装完成。

本文出自 “不莱梅乐队” 博客，谢绝转载！

mysql数据库复制—双主,古老的榕树,5-wow.com