MMM(Master-Master replication manager for MySQL,Mysql 主主復(fù)制管理器)是一套支持雙主故障切換和雙主日常管理的腳本程序。MMM使用Perl語言開發(fā),主要用來監(jiān)控和管理Mysql Master-Master(雙主)復(fù)制,雖然叫做雙主復(fù)制,但是業(yè)務(wù)上同一時刻只允許對一個主進(jìn)行寫入,另一臺備選主上提供部分讀服務(wù),以加速在主主切換時備選主的預(yù)熱,可以說MMM這套腳本程序一方面實現(xiàn)了故障切換的功能,另一方面其內(nèi)部附加的工具腳本也可以實現(xiàn)多個Slave的read負(fù)載均衡
1)mmm_mon:監(jiān)控進(jìn)程,負(fù)責(zé)所有的監(jiān)控工作,決定和處理所有節(jié)點角色活動。此腳本需要在監(jiān)管機(jī)上運(yùn)行
2)mmm_agent:運(yùn)行在每個Mysql服務(wù)器上的代理進(jìn)程,完成監(jiān)控的探針工作和執(zhí)行簡單的遠(yuǎn)端服務(wù)設(shè)置。此腳本需要在各節(jié)點上運(yùn)行
3)mmm_control:一個簡單的腳本,提供管理mmm_mond進(jìn)程的命令
4)mysql-mmm的監(jiān)控端會提供多個虛擬IP(VIP),包括一個可寫VIP,多個可讀VIP,通過監(jiān)管的管理,這些IP會綁定在可用的Mysql之上,當(dāng)某一臺Mysql宕機(jī)時,監(jiān)控會將VIP遷移至其他Mysql
創(chuàng)新互聯(lián)建站專注于濟(jì)陽網(wǎng)站建設(shè)服務(wù)及定制,我們擁有豐富的企業(yè)做網(wǎng)站經(jīng)驗。 熱誠為您提供濟(jì)陽營銷型網(wǎng)站建設(shè),濟(jì)陽網(wǎng)站制作、濟(jì)陽網(wǎng)頁設(shè)計、濟(jì)陽網(wǎng)站官網(wǎng)定制、重慶小程序開發(fā)服務(wù),打造濟(jì)陽網(wǎng)絡(luò)公司原創(chuàng)品牌,更為您提供濟(jì)陽網(wǎng)站排名全網(wǎng)營銷落地服務(wù)。
本案例環(huán)境使用五臺服務(wù)器模擬搭建
主機(jī) 操作系統(tǒng) IP地址
master1 CentOS7 192.168.195.128
master2 CentOS7 192.168.195.137
slave1 CentOS7 192.168.195.140
slave2 CentOS7 192.168.195.141
monitor CentOS7 192.168.195.142
CentOS默認(rèn)沒有mysql-mmm軟件包,官方推薦使用epel源,五臺主機(jī)都要安裝eperl源和MMM
service firewalld stop
setenforce 0
wget -0 /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo //使用阿里云提供的yum源
yum -y install epel-release
yum clean all && yum makecache
yum -y install mariadb-server mariadb
systemctl start mariadb
首先修改master1主配置文件
vi /etc/my.cnf (9dd刪掉[mysqld]標(biāo)簽之前的內(nèi)容,重新插入下面的配置)
[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 //不需要同步的數(shù)據(jù)庫名稱
character_set_server=utf8 //設(shè)置默認(rèn)的字符集為utf-8
log_bin=mysql_bin //開啟二進(jìn)制日志,用于主從數(shù)據(jù)復(fù)制
server_id=1 //每臺server_id的值不能相同
log_slave_updates=true //此數(shù)據(jù)庫宕機(jī),備用數(shù)據(jù)庫接管
sync_binlog=1
auto_increment_increment=2 //字段一次遞增2
auto_increment_offset=1 //自增字段的起始值:1,3,5,7.........等奇數(shù)ID
systemctl restart mariadb
netstat -anpt | grep 3306tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN 5029/mysqld
沒有問題后,把配置文件my.cnf復(fù)制到其他3臺數(shù)據(jù)庫服務(wù)器上并啟動服務(wù)器
每臺mysql主機(jī)的server_id參數(shù)不能相同,其他配置文件參數(shù)相同
scp /etc/my.cnf root@192.168.195.140:/etc/
scp /etc/my.cnf root@192.168.195.137:/etc/
scp /etc/my.cnf root@192.168.195.141:/etc/
注:配置文件中的server_id參數(shù)不能相同,需要修改。第二臺server_id=2 第三臺3 第四臺4
首先進(jìn)入mysql查看log-bin日志和pos值的位置(在授權(quán)的時候要隨時更新position位置變化)
在master1上
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 411 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)在master2上
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000004 | 491 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)master1,master2互相提升訪問權(quán)限
注意:在master1上要指定master2上的日志文件名和位置參數(shù)。在m2上要反過來指定m1的。在授權(quán)的時候要隨時查看
在master1上授權(quán)
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.195.%' identified by 'abc123';
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> change master to master_host='192.168.195.137',master_user='replication',master_password='abc123',master_log_file='mysql_bin.000004',master_log_pos=491;
Query OK, 0 rows affected (0.06 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)在master2上授權(quán)
MariaDB [(none)]> grant replication slave on *.* to 'replication'@'192.168.195.%' identified by 'abc123';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> change master to master_host='192.168.195.128',master_user='replication',master_password='abc123',master_log_file='mysql_bin.000003',master_log_pos=411;
Query OK, 0 rows affected (0.02 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)分別查看master1,master2服務(wù)器的主從狀態(tài)
start slave; //開啟同步功能
show slave status\G;
Slave_I0_Running: Yes //這兩個選項參數(shù)必須為yes
Slave_SQL_Running: Yes
MariaDB [(none)]> create database school;
Query OK, 1 row affected (0.01 sec)
在master2上可以查看到剛才創(chuàng)建的庫school,同步已經(jīng)完成
MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| test |
+--------------------+
5 rows in set (0.01 sec)
首先查看master1的狀態(tài)值
MariaDB [(none)]> show master status;
+------------------+----------+--------------+--------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+--------------------------+
| mysql_bin.000003 | 583 | | mysql,information_schema |
+------------------+----------+--------------+--------------------------+
1 row in set (0.00 sec)在slave1,slave2上分別設(shè)置
MariaDB [(none)]> change master to master_host='192.168.195.128',master_user='replication',master_password='abc123',master_log_file='mysql_bin.000003',master_log_pos=583;
分別查看slave1,slave2的主從狀態(tài)
start slave; //開啟同步功能
show slave status\G;
Slave_I0_Running: Yes //這兩個選項參數(shù)必須為yes
Slave_SQL_Running: Yes
數(shù)據(jù)庫服務(wù)器之間的主主復(fù)制同步,主從復(fù)制同步配置成功
yum -y install mysql-mmm*
系統(tǒng)中所有主機(jī)的該配置文件的內(nèi)容都是一樣的,包括監(jiān)控主機(jī)monitor
修改后的內(nèi)容為帶加粗部分
vim /etc/mysql-mmm/mmm_common.conf
active_master_role writer
<host default>
cluster_interface ens33
pid_path /run/mysql-mmm-agent.pid
bin_path /usr/libexec/mysql-mmm/
replication_user replicant
replication_password abc123
agent_user mmm_agent
agent_password agent
</host><host db1>
ip 192.168.195.128
mode master
peer db2
</host><host db2>
ip 192.168.195.137
mode master
peer db1
</host><host db3>
ip 192.168.195.140
mode slave
</host><host db4>
ip 192.168.195.141
mode slave
</host><role writer>
hosts db1, db2
ips 192.168.195.188 //設(shè)置虛擬IP
mode exclusive
</role><role reader>
hosts db3, db4
ips 192.168.195.200, 192.168.195.210 //設(shè)置虛擬IP
mode balanced
</role>遠(yuǎn)程復(fù)制 覆蓋配置文件:
scp mmm_common.conf root@192.168.195.137:/etc/mysql-mmm/scp mmm_common.conf root@192.168.195.140:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.195.141:/etc/mysql-mmm/
scp mmm_common.conf root@192.168.195.142:/etc/mysql-mmm/
vim /etc/mysql-mmm/mmm_common.conf 挨個查看一下
cd /etc/mysql-mmm/ //改密碼
vim mmm_mon.conf
ping_ips 192.168.195.128,192.168.195.137,192.168.195.140,192.168.195.141 //監(jiān)視器監(jiān)聽的服務(wù)器地址
auto_set_online 10 //超時連接時間10秒
<host default>
monitor_user mmm_monitor //用戶名
monitor_password 123456 //密碼
</host>
vim /etc/mysql-mmm/mmm_agent.conf
this db1 //分別修改為db1,db2,db3,db4
MariaDB [(none)]> grant replication client on *.* to 'mmm_monitor'@'192.168.195.%' identified by '123456';
Query OK, 0 rows affected (0.01 sec)MariaDB [(none)]> grant super, replication client, process on *.* to 'mmm_agent'@'192.168.195.%' identified by 'agent';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)
在所在數(shù)據(jù)庫服務(wù)器上啟動mysql-mmm-agent
systemctl start mysql-mmm-agent.service
在monitor服務(wù)器上啟動監(jiān)控服務(wù)mysql-mmm-montior
systemctl start mysql-mmm-monitor.service
在monitor服務(wù)器上進(jìn)行測試
[root@localhost ~]# mmm_control show
db1(192.168.195.128) master/ONLINE. Roles: writer(192.168.195.188)
db2(192.168.195.137) master/ONLINE. Roles:
db3(192.168.195.140) slave/ONLINE. Roles: reader(192.168.195.210)
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200)檢測狀態(tài)需要全部OK
[root@localhost ~]# mmm_control checks all
db4 ping [last change: 2018/09/08 14:53:06] OK
db4 mysql [last change: 2018/09/08 14:53:06] OK
db4 rep_threads [last change: 2018/09/08 14:53:06] OK
db4 rep_backlog [last change: 2018/09/08 14:53:06] OK: Backlog is null
db2 ping [last change: 2018/09/08 14:53:06] OK
db2 mysql [last change: 2018/09/08 14:53:06] OK
db2 rep_threads [last change: 2018/09/08 14:53:06] OK
db2 rep_backlog [last change: 2018/09/08 14:53:06] OK: Backlog is null
db3 ping [last change: 2018/09/08 14:53:06] OK
db3 mysql [last change: 2018/09/08 14:53:06] OK
db3 rep_threads [last change: 2018/09/08 14:53:06] OK
db3 rep_backlog [last change: 2018/09/08 14:53:06] OK: Backlog is null
db1 ping [last change: 2018/09/08 14:53:06] OK
db1 mysql [last change: 2018/09/08 14:53:06] OK
db1 rep_threads [last change: 2018/09/08 14:53:06] OK
db1 rep_backlog [last change: 2018/09/08 14:53:06] OK: Backlog is null將虛擬IP:192.168.195.188切換到db2服務(wù)器上
[root@localhost ~]# mmm_control move_role writer db2
OK: Role 'writer' has been moved from 'db1' to 'db2'. Now you can wait some time and check new roles info!
[root@localhost ~]# mmm_control show
db1(192.168.195.128) master/ONLINE. Roles:
db2(192.168.195.137) master/ONLINE. Roles: writer(192.168.195.188)
db3(192.168.195.140) slave/ONLINE. Roles: reader(192.168.195.210)
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200)
1)停掉主db1數(shù)據(jù)庫,等待幾秒后,可以看到數(shù)據(jù)庫db1處于HARD_OFFLINE(離線狀態(tài)),檢測不到數(shù)據(jù)庫的存在
[root@localhost ~]# mmm_control show
db1(192.168.195.128) master/HARD_OFFLINE. Roles:
db2(192.168.195.137) master/ONLINE. Roles: writer(192.168.195.188)
db3(192.168.195.140) slave/ONLINE. Roles: reader(192.168.195.210)
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200)2)停掉從db3數(shù)據(jù)庫,從的虛擬IP會全部在另一臺正常數(shù)據(jù)庫上,
[root@localhost ~]# mmm_control show
db1(192.168.195.128) master/HARD_OFFLINE. Roles:
db2(192.168.195.137) master/ONLINE. Roles: writer(192.168.195.188)
db3(192.168.195.140) slave/HARD_OFFLINE. Roles:
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200), reader(192.168.195.210)重新開啟db3數(shù)據(jù)庫,從虛擬IP又會回到原來的位置
[root@localhost ~]# mmm_control show
db1(192.168.195.128) master/ONLINE. Roles:
db2(192.168.195.137) master/ONLINE. Roles: writer(192.168.195.188)
db3(192.168.195.140) slave/ONLINE. Roles: reader(192.168.195.210)
db4(192.168.195.141) slave/ONLINE. Roles: reader(192.168.195.200)
以監(jiān)控機(jī)monitor充當(dāng)客戶進(jìn)行遠(yuǎn)程登錄mysql服務(wù)進(jìn)行測試
按理來講監(jiān)控服務(wù)器只單獨充當(dāng)監(jiān)控這一角色就行了,這里臨時將它也作為客戶端:
yum install -y mariadb-server mariadbsystemctl start mariadb.service
在數(shù)據(jù)庫服務(wù)器上進(jìn)行遠(yuǎn)程登錄授權(quán)
MariaDB [(none)]> grant all on *.* to 'test'@'192.168.195.142' identified by '123123';
Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)在monitor進(jìn)行遠(yuǎn)程登錄mysql服務(wù) ,并創(chuàng)建一個庫shujuku,
mysql -utest -p123123 -h 192.168.195.188 //虛擬IP
MariaDB [school]> create database shujuku;
Query OK, 1 row affected (0.01 sec)登錄其他四臺節(jié)點,都可以看到剛創(chuàng)建的庫shujuku,證明群集同步成功
MariaDB [school]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| school |
| shujuku |
| test |
+--------------------+
6 rows in set (0.00 sec)
網(wǎng)站名稱:Mysql-MMM高可用群集部署
分享網(wǎng)址:http://muchs.cn/article44/iheshe.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián),為您提供標(biāo)簽優(yōu)化、域名注冊、網(wǎng)站維護(hù)、品牌網(wǎng)站建設(shè)、企業(yè)網(wǎng)站制作、網(wǎng)站導(dǎo)航
聲明:本網(wǎng)站發(fā)布的內(nèi)容(圖片、視頻和文字)以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主,如果涉及侵權(quán)請盡快告知,我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場,如需處理請聯(lián)系客服。電話:028-86922220;郵箱:631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載,或轉(zhuǎn)載時需注明來源: 創(chuàng)新互聯(lián)