MySQL双主环境搭建

最新推荐文章于 2024-10-02 01:44:41 发布

wenph2008

最新推荐文章于 2024-10-02 01:44:41 发布

阅读量595

点赞数

CC 4.0 BY-SA版权

分类专栏：数据库

本文链接：https://blog.csdn.net/wenph2008/article/details/51093861

数据库专栏收录该内容

14 篇文章

订阅专栏

一、安装MySQL-server

二、环境描述

console1： 1.1.1.1/24

console2： 1.1.1.2/24

三、创建授权账户

console1 作为 console2 的master，console1要允许console2的接入

console1 --> mysql
    grant replication slave on *.* to 'shdbreplicate'@'1.1.1.2' identified by 'db_console2';

console2 作为 console1 的master，console2要允许console1的接入

console2 --> mysql
    grant replication slave on *.* to 'shdbreplicate'@'1.1.1.1' identified by 'db_console1';

四、修改/etc/my.cnf文件并重启数据库：service mysqld restart
console1

[mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0

    # modified by zhongmou.llp
    bind-address = 0.0.0.0
    server-id = 1
    auto-increment-increment = 2
    auto-increment-offset = 1
    log-bin = master-bin
    log-bin-index = master-bin.index
    binlog-do-db = project
    binlog-do-db = project_console
    binlog-ignore-db = mysql
    binlog-ignore-db = test
    slave-skip-errors=1062

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

console2

[mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql
    # Disabling symbolic-links is recommended to prevent assorted security risks
    symbolic-links=0

    # modified by zhongmou.llp
    bind-address = 0.0.0.0
    server-id = 2
    auto-increment-increment = 2
    auto-increment-offset = 2
    log-bin = master-bin
    log-bin-index = master-bin.index
    binlog-do-db = project
    binlog-do-db = project_console
    binlog-ignore-db = mysql
    binlog-ignore-db = test
    slave-skip-errors=1062

    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

五、查看数据库master状态
File和position可能随时会变，在使用时以实际的为准
console1 --> mysql

show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000006 |      107 |              |                  | 
+------------------+----------+--------------+------------------+ 
1 row in set

console2 --> mysql

show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | 
+------------------+----------+--------------+------------------+ 
| mysql-bin.000008 |      201 |              |                  | 
+------------------+----------+--------------+------------------+ 
1 row in set

六、互为备用数据库的配置

console1 --> mysql

stop slave;

change master to master_host='1.1.1.2',master_user='shdbreplicate',master_password='db_console1',
master_log_file='mysql-bin.000008',master_log_pos=201;

start slave;

console2 --> mysql

stop slave;

change master to master_host='1.1.1.1',master_user='shdbreplicate',master_password='db_console2',
master_log_file='mysql-bin.000006',master_log_pos=107;

start slave;

七、验证双主建立成功

检查Slave_IO_Running，Slave_SQL_Running都为Yes,与Errno相关的参数都为空或者为0

console1和console2--> mysql

show slave status\G;

Slave_IO_Running: Yes
Slave_SQL_Running: Yes

八、功能验证

在双主环境下增删改数据，看对方数据库是否能同步。