mysql双主多从配置_MySQL 双主双从安装与配置

安装mysql

配置mysql

vim /etc/my.cnf

Master-A (192.168.7.18)

#Replication master

server-id=18

log-bin=mycat-mysql-bin

binlog-ignore-db=information_schema

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

replicate-ignore-db=information_schema

replicate-ignore-db=mysql

replicate-ignore-db=performance_schema

replicate-ignore-db=sys

log-slave-updates

slave-skip-errors=all

auto_increment_increment=2

auto_increment_offset=1

Master-B (192.168.7.17)

#Replication master

server-id=17

log-bin=mycat-mysql-bin

binlog-ignore-db=information_schema

binlog-ignore-db=mysql

binlog-ignore-db=performance_schema

binlog-ignore-db=sys

replicate-ignore-db=information_schema

replicate-ignore-db=mysql

replicate-ignore-db=performance_schema

replicate-ignore-db=sys

log-slave-updates

slave-skip-errors=all

auto_increment_increment=2

auto_increment_offset=2

Slave-C (192.168.7.23)

#Replication slave

server-id=23

Slave-D (192.168.7.22)

#Replication slave

server-id=22

在Master MySQL上创建一个用户'slave'，并允许其他Slave服务器可以通过远程访问Master，通过该用户读取二进制日志，实现数据同步。

mysql> create user slave;

mysql> GRANT REPLICATION SLAVE ON *.* TO 'slave'@'192.168.7.%' IDENTIFIED BY 'Camry88!';

查看Master状态，配置Slave从服务器

Master-A (192.168.7.18)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mycat-mysql-bin.000012 | 154 | | information_schema,mysql,performance_schema,sys | |

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

1 row in set (0.00 sec)

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.7.17',

MASTER_USER='slave',

MASTER_PASSWORD='Camry88!',

MASTER_LOG_FILE='mycat-mysql-bin.000010',

MASTER_LOG_POS=154; #连接master

mysql> start slave; #启动Slave

mysql> show slave status \G; #查看是否连接成功

Master-B (192.168.7.17)

mysql> show master status;

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

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

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

| mycat-mysql-bin.000010 | 154 | | information_schema,mysql,performance_schema,sys | |

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

1 row in set (0.00 sec)

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.7.18',

MASTER_USER='slave',

MASTER_PASSWORD='Camry88!',

MASTER_LOG_FILE='mycat-mysql-bin.000012',

MASTER_LOG_POS=154; #连接master

mysql> start slave; #启动Slave

mysql> show slave status \G; #查看是否连接成功

Slave-C (192.168.7.23)

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.7.18',

MASTER_USER='slave',

MASTER_PASSWORD='Camry88!',

MASTER_LOG_FILE='mycat-mysql-bin.000012',

MASTER_LOG_POS=154; #连接master

mysql> start slave; #启动Slave

mysql> show slave status \G; #查看是否连接成功

Slave-D (192.168.7.22)

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.7.17',

MASTER_USER='slave',

MASTER_PASSWORD='Camry88!',

MASTER_LOG_FILE='mycat-mysql-bin.000010',

MASTER_LOG_POS=154; #连接master

mysql> start slave; #启动Slave

mysql> show slave status \G; #查看是否连接成功