MySQL主主复制以及常见的一些问题

原理说明

配置步骤

需要2台搭建完成的mysql

两台机器上都建立用户，并赋予复制权限

   

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*  TO weijie@'%' IDENTIFIED BY '123456';

GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.*  TO weijie@'%' IDENTIFIED BY '123456';

修改配置文件

MASTER1:

[mysqld]
server-id=1
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=1
port=3306
basedir="D:/phpStudys/MySQL/"
datadir="D:/phpStudys/MySQL/data/"

MASTER2

[mysqld]
server-id=2
log-bin=mysql-bin
auto-increment-increment=2
auto-increment-offset=2
port=3307
basedir="D:/phpStudy/MySQL/"
datadir="D:/phpStudy/MySQL/data/"6

配置主库

master1上指定master2为主

CHANGE MASTER TO master_host = '127.0.0.1',
 master_port=3307,
 master_user = 'roots',
 master_password = 'root',
 master_log_file = 'mysql-bin.000001',
 master_log_pos = 107;

start slave

show slave status

就配置好了