地址规划:
monitor:192.168.35.139
master1:192.168.35.147 vip:192.168.35.66
master2:192.168.35.145
slave1:192.168.35.146 vip:192.168.35.88
slave2:192.168.35.143 vip:192.168.35.77
除了监控主机,其余主机都需要安装MySQL数据库
配置ALI云源,然后安装epel-release源
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
systemctl stop firewalld.service
setenforce 0
yum -y install epel-release
yum clean all && yum makecache //清空缓存
搭建本地yum源
yum -y install mariadb-server mariadb
修改ml主配置文件
vim /etc/my.cnf
删除前9行,添加以下内容
[mysqld]
log_error=/var/lib/mysql/mysql.err
log=/var/lib/mysql/mysql_log.log
log_slow_queries=/var/lib/mysql_slow_queris.log
binlog-ignore-db=mysql,information_schema
character_set_server=utf8
log_bin=mysql_bin
server_id=1
log_slave_updates=true
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
注释:
[mysqld]
log_error=/var/lib/mysql/mysql.err //错误日志的文件位置
log=/var/lib/mysql/mysql_log.log //访问日志的文件位置
log_slow_queries=/var/lib/mysql_slow_queris.log //man日志的文件位置
binlog-ignore-db=mysql,information_schema //mysql,information_schema不生成二进制日志文件
character_set_server=utf8 //字符集
log_bin=mysql_bin //二进制日志文件功能开启
server_id=1 //不同主机id不同
log_slave_updates=true //同步
sync_binlog=1 //二进制日志文件功能开启
auto_increment_increment=2 //增量
auto_increment_offset=1 //起始值
开启服务并查看端口状态
systemctl start mariadb.service
netstat -natp | grep 3306
没问题后,把配置文件复制到其他3台数据库服务器上并启动的服务
scp /etc/my.cnf root@192.168.35.146:/etc/
注意:server_id要修改
配置主主复制
show master status;
//查看记录日志文件名称和位置值
主主执行授权
grant replication slave on *.* to 'replication'@'192.168.35.%' identified by '123456';
//两台主服务器都执行
(1)master1
change master to master_host='192.168.35.145',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245;
//master1上要指定master2上的日志文件名和位置参数(master2反之)
(2)master2
change master to master_host='192.168.35.147',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=245;
flush privileges; //刷新
start slave;
show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试:
1、在master1上创建一个数据库,保证在master2上能同步看到
(1)master1
MariaDB [(none)]> create database school;
MariaDB [(none)]> show databases;
(2)master2
MariaDB [(none)]> show databases;
2、在master2上删除创建的数据库,保证在master1上能同步删除
(1)master2
MariaDB [(none)]> drop database school;
MariaDB [(none)]> show databases;
(2)master1MariaDB [(none)]> show databases;
在两台从服务器上做主从同步
slave1
change master to master_host='192.168.35.147',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=2269;
slave2
change master to master_host='192.168.35.147',master_user='replication',master_password='123456',master_log_file='mysql_bin.000003',master_log_pos=2269;
flush privileges; //刷新
start slave;
show slave status\G;
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试:在master1上创建一个数据库,master2、slave1、slave2应该都可以看到
(1)master1
MariaDB [(none)]> create database myschool;
MariaDB [(none)]> show databases;
(2)master2
MariaDB [(none)]> show databases;
(3)slave1
MariaDB [(none)]> show databases;
(4)slave2
MariaDB [(none)]> show databases;
monitor主机上配置
systemctl stop firewalld.service
setenforce 0
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo
yum -y install epel-release
yum install mysql-mmm* -y
vim /etc/mysql-mmm/mmm_mon.conf
第8行
ping_ips 192.168.35.147,192.168.35.145,192.168.35.146,192.168.35.143
第9行,设置自动上线时间
auto_set_online 10
第21行
monitor_password 123456
在所有服务器上安装MMM
yum install mysql-mmm* -y
安装好后,对mmm进行配置
vim /etc/mysql-mmm/mmm_common.conf //所有主机都要配置
1 active_master_role writer
2
3 <host default>
4 cluster_interface ens33 #修改网卡为ens33
5 pid_path /run/mysql-mmm-agent.pid
6 bin_path /usr/libexec/mysql-mmm/
7 replication_user replication
8 replication_password 123456 #修改授权密码
9 agent_user mmm_agent
10 agent_password 123456 #修改代理授权密码
11 </host>
12
13 <host db1>
14 ip 192.168.35.147
15 mode master
16 peer db2
17 </host>
18
19 <host db2>
20 ip 192.168.35.145
21 mode master
22 peer db1
23 </host>
24
25 <host db3>
26 ip 192.168.35.146
27 mode slave
28 </host>
29
30 <host db4>
31 ip 192.168.35.143
32 mode slave
33 </host>
34
35 <role writer>
36 hosts db1, db2
37 ips 192.168.35.66 #主服务器虚拟IP
38 mode exclusive
39 </role>
40
41 <role reader>
42 hosts db3, db4
43 ips 192.168.35.77, 192.168.35.88 #从服务器虚拟IP
44 mode balanced
45 </role>
从m1上面复制配置文件到其他三台服务器
scp /etc/mysql-mmm/mmm_common.conf root@192.168.35.145:/etc/mysql-mmm/mmm_common.conf
scp /etc/mysql-mmm/mmm_common.conf root@192.168.35.146:/etc/mysql-mmm/mmm_common.conf
scp /etc/mysql-mmm/mmm_common.conf root@192.168.35.143:/etc/mysql-mmm/mmm_common.conf
scp /etc/mysql-mmm/mmm_common.conf root@192.168.35.139:/etc/mysql-mmm/mmm_common.conf
在其他主机上进行查看
vim /etc/mysql-mmm/mmm_common.conf
在所有数据库上为mmm_agent授权
grant super, replication client, process on *.* to 'mmm_agent'@'192.168.35.%' identified by '123456';
在所有数据库上为mmm_moniter授权
grant replication client on *.* to 'mmm_monitor'@'192.168.35.%' identified by '123456';
flush privileges;
修改所有数据库的mmm_agent.conf
vim /etc/mysql-mmm/mmm_agent.conf
this db1 //根据规划逐一调整
在所有数据库服务器上启动mysql-mmm-agent
systemctl start mysql-mmm-agent.service
systemctl enable mysql-mmm-agent.service //开机自启
监控主机上配置
systemctl start mysql-mmm-monitor.service
//启动mysql-mmm-monitor服务
mmm_control show
//查看各节点情况
db1(192.168.35.145) master/ONLINE. Roles: writer(192.168.35.66)
db2(192.168.35.146) master/ONLINE. Roles:
db3(192.168.35.143) slave/ONLINE. Roles: reader(192.168.35.77)
db4(192.168.35.142) slave/ONLINE. Roles: reader(192.168.35.88)
mmm_control move_role writer db2 //指定主机绑定虚拟IP
mmm_control checks all //需要各种OK
模拟测试
模拟m1服务器宕机,停止服务
[root@localhost ~]# systemctl stop mariadb.service
当m1服务器宕机后,m2接收虚拟IP继续提供服务
[root@localhost ~]# mmm_control show
db1(192.168.35.145) master/ONLINE. Roles:
db2(192.168.35.146) master/ONLINE. Roles: writer(192.168.35.66)
db3(192.168.35.143) slave/ONLINE. Roles: reader(192.168.35.77)
db4(192.168.35.142) slave/ONLINE. Roles: reader(192.168.35.88)
模拟s1服务器宕机,停止服务
[root@localhost ~]# systemctl stop mariadb.service
当s1服务器宕机后,s2接收虚拟IP继续提供服务
[root@localhost ~]# mmm_control show
db1(192.168.35.145) master/ONLINE. Roles:
db2(192.168.35.146) master/ONLINE. Roles: writer(192.168.35.66)
db3(192.168.35.143) slave/HARD_OFFLINE. Roles:
db4(192.168.35.142) slave/ONLINE. Roles: reader(192.168.35.77), reader(192.168.35.88)
在m1服务器上为监控服务器地址授权登录
MariaDB [(none)]> grant all on *.* to 'root'@'192.168.35.139' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
在监控服务器上安装数据库客户端
[root@localhost ~]# yum -y install mariadb
在监控服务器上使用指定用户登录虚拟IP
[root@localhost ~]# mysql -u root -p -h 192.168.35.139
Enter password:
//输入密码即可
创建一个数据库
MariaDB [(none)]> create database BDQN;
Query OK, 1 row affected (0.01 sec)
在m1上查看同步到的数据信息
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| BDQN |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
6 rows in set (0.00 sec)