MariaDB10多主，读写分离

 

规划：

monitor(192.168.77.221)　　db1(master)(192.168.77.222)(root/root1)　　db2(master)(192.168.77.223)(root/root2)　　db3(slave to db1)(192.168.77.224)(root/root3)

写地址：192.168.77.122　　　读地址：192.168.77.123　　192.168.77.124　　

 

用作复制账号密码为：copy/paste

用作mmm-monitor账号：mmm/mmm

安装：

1.每台db节点上添加mariadb源

vi /etc/yum.repos.d/mariadb.repo

# MariaDB 10.1 CentOS repository list - created 2017-05-20 03:01 UTC
# http://downloads.mariadb.org/mariadb/repositories/
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.1/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1

2.每台db节点上：      

yum -y install MariaDB-server

yum -y install mysql-mmm-agent

3.monitor上：　　　　

yum -y install mysql-mmm*　　（epel）

配置

1.db1

cp  /usr/share/mysql/my-huge.cnf  /etc/my.cnf

service mysql restart 　　

mysql_secure_installation

vi /etc/my.cnf　　添加

 

server-id = 1
log-bin = mysql-bin
binlog_format = row
log-slave-updates
sync_binlog = 1
auto_increment_increment = 2  
auto_increment_offset = 1

 

mysql -u root -p

grant replication slave,replication client on *.* to 'copy'@'192.168.77.223' identified by 'paste';
grant replication slave,replication client on *.* to 'copy'@'192.168.77.224' identified by 'paste';

2.db2

cp  /usr/share/mysql/my-huge.cnf  /etc/my.cnf

service mysql restart 　　

mysql_secure_installation

vi /etc/my.cnf　　添加

server-id = 2
log-bin = mysql-bin
binlog_format = row
log-slave-updates
sync_binlog = 1
auto_increment_increment = 2  
auto_increment_offset = 2

mysql -uroot -p

grant replication slave,replication client on *.* to 'copy'@'192.168.77.222' identified by 'paste';

3.db1和db2作主从

先重启db1和db2服务；

reset master;
reset slave;
flush tables with read lock;
show master status;
change master to master_host='192.168.77.223',master_user='copy',master_password='paste',master_log_file='mysql-bin.000001',master_log_pos=313;
start slave;
show master status\G
unlock tables;

reset master;
reset slave;
flush tables with read lock;
show master status;
change master to master_host='192.168.77.222',master_user='copy',master_password='paste',master_log_file='mysql-bin.000001',master_log_pos=313;
start slave;
show master status\G
unlock tables;

 

4.测试db1和db2

5.db3

cp  /usr/share/mysql/my-huge.cnf  /etc/my.cnf

service mysql restart 　　

mysql_secure_installation

vi /etc/my.cnf　　添加

server-id       = 3
log-bin=mysql-bin
log-slave-updates
relay-log=relay-log-bin

change master to master_host='192.168.77.222',master_user='copy',master_password='paste',master_log_file='mysql-bin.000001',master_log_pos=563;

start slave;
show slave status\G

 

6.测试db1,db2,db3互连

db2:

create db reptest

db1和db3：

show databases;

 

7.monitor

db1，2，3上分别安装

yum -y install mysql-mmm-agent

并授权：

grant super, replication client , process on *.* to 'mmm'@'192.168.77.221' identified by 'mmm';

monitor上配置 /etc/mysql-mmm/mmm_common.conf 和mon.conf

 

 

参照：http://mysql-mmm.org/doku.php