mysql 主主环境搭建,MySQL双主环境搭建

一、安装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.pidconsole2

[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 setconsole2 --> 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八、功能验证

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