环境规划： 主db1 IP:192.168.1.247 host:tong1 主db2 IP:192.168.1.248 host:tong2 从db3 IP:192.168.1.249 host:tong3 monitor IP:192.168.1.249 host:tong3 数据库:mysql-5.6.21 mysqldba技术群 378190849 武汉-linux运维群 236415619 1.网络环境布置 ton
环境规划：
主db1 IP:192.168.1.247 host:tong1
主db2 IP:192.168.1.248 host:tong2
从db3 IP:192.168.1.249 host:tong3
monitor IP:192.168.1.249 host:tong3
数据库:mysql-5.6.21
mysql dba技术群 378190849
武汉-linux运维群 236415619
1.网络环境布置
tong1数据节点:
[root@tong1 ~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 10:78:D2:C7:83:03
inet addr:192.168.1.247 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::1278:d2ff:fec7:8303/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:4953337 errors:0 dropped:0 overruns:0 frame:0
TX packets:78512 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:346785301 (330.7 MiB) TX bytes:5389268 (5.1 MiB)
[root@tong1 ~]# cat /etc/hosts
192.168.1.247 tong1
192.168.1.248 tong2
192.168.1.249 tong3
[root@tong1 ~]#
tong2数据节点:
[root@tong2 ~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 10:78:D2:C7:17:E8
inet addr:192.168.1.248 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::1278:d2ff:fec7:17e8/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:4930658 errors:0 dropped:0 overruns:0 frame:0
TX packets:19441 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:315661835 (301.0 MiB) TX bytes:2133138 (2.0 MiB)
[root@tong2 ~]# cat /etc/hosts
192.168.1.247 tong1
192.168.1.248 tong2
192.168.1.249 tong3
[root@tong2 ~]#
tong3监控节点:
[root@tong3 ~]# ifconfig eth0
eth0 Link encap:Ethernet HWaddr 10:78:D2:C8:F7:50
inet addr:192.168.1.249 Bcast:192.168.1.255 Mask:255.255.255.0
inet6 addr: fe80::1278:d2ff:fec8:f750/64 Scope:Link
UP BROADCAST RUNNING MULTICAST MTU:1500 Metric:1
RX packets:6864426 errors:0 dropped:0 overruns:0 frame:0
TX packets:99046 errors:0 dropped:0 overruns:0 carrier:0
collisions:0 txqueuelen:1000
RX bytes:694563286 (662.3 MiB) TX bytes:7322797 (6.9 MiB)
[root@tong3 ~]# cat /etc/hosts
192.168.1.247 tong1
192.168.1.248 tong2
192.168.1.249 tong3
[root@tong3 ~]#
2.在监控节点上tong3安装mysql-mmm软件
下载地址:http://mysql-mmm.org/downloads
[root@tong3 ~]# tar xvf mysql-mmm-2.2.1.tar.gz
[root@tong3 ~]# cd mysql-mmm-2.2.1
[root@tong3 mysql-mmm-2.2.1]# make && make install
[root@tong3 mysql-mmm-2.2.1]# mmm_
mmm_agentd mmm_backup mmm_clone mmm_control mmm_mond mmm_restore
[root@tong3 mysql-mmm-2.2.1]# ll /etc/mysql-mmm/
total 16
-rw-r—–. 1 root root 33 Apr 29 14:06 mmm_agent.conf
-rw-r—–. 1 root root 684 Apr 29 14:06 mmm_common.conf
-rw-r—–. 1 root root 321 Apr 29 14:06 mmm_mon.conf
-rw-r—–. 1 root root 1293 Apr 29 14:06 mmm_tools.conf
[root@tong3 mysql-mmm-2.2.1]#
3.在tong1,tong2,tong3节点安装mysql数据库(步骤一样)
[root@tong1 ~]# wget http://mirrors.sohu.com/mysql/MySQL-5.6/mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz
[root@tong1 ~]# tar xvf mysql-5.6.23-linux-glibc2.5-x86_64.tar.gz -C /usr/local/
[root@tong1 ~]# cd /usr/local/
[root@tong1 ~]# mv mysql-5.6.23-linux-glibc2.5-x86_64/ mysql-5.6.23
[root@tong1 ~]# cd mysql-5.6.23/
[root@tong1 ~]# ./scripts/mysql_install_db –user=mysql –group=mysql –basedir=/usr/local/mysql-5.6.23 –datadir=/usr/local/mysql-5.6.23/data
[root@tong1 ~]# cp -a my.cnf /etc/
[root@tong1 ~]# cp -a support-files/mysql.server /etc/init.d/mysqld
[root@tong1 ~]# chkconfig –add mysqld
[root@tong1 ~]# chkconfig mysqld on
[root@tong1 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 20 –server_id在三台主机不同,分别用10,20,30表示
socket = /tmp/mysql.sock
[root@tong1 ~]# pkill mysqld
[root@tong1 ~]# /etc/init.d/mysqld restart
[root@tong1 ~]# /usr/local/mysql-5.6.23/bin/mysqladmin -u root password ‘system’
[root@tong1 ~]# /usr/local/mysql-5.6.23/bin/mysql -u root -p –输入密码system
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> exit
Bye
[root@tong1 ~]#
4.将tong1和tong2配置成主主架构
tong1节点配置文件:
[root@tong1 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 20
socket = /tmp/mysql.sock
replicate-do-db=tong –复制tong数据库
replicate-ignore-db=mysql –忽略mysql数据库
log-bin=mysql-bin –开启二进制日志
log-bin-index=mysql-bin-index
auto_increment_offset=1
auto_increment_increment=2
relay-log=relay-log –开启中继日志
relay-log-index=relay-log-index
log_slave_updates –当任意一台主宕机,从可以接管主应用
sync-binlog=1
[root@tong1 ~]#
tong2节点配置文件:
[root@tong2 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 10
socket = /tmp/mysql.sock
replicate-do-db=tong
replicate-ignore-db=mysql
log-bin=mysql-bin
log-bin-index=mysql-bin-index
auto_increment_offset=2
auto_increment_increment=2
relay-log=relay-log
relay-log-index=relay-log-index
log_slave_updates
sync-binlog=1
[root@tong2 ~]#
tong3节点配置文件:
[root@tong3 ~]# vim /etc/my.cnf
basedir = /usr/local/mysql-5.6.23
datadir = /usr/local/mysql-5.6.23/data
port = 3306
server_id = 30
socket = /tmp/mysql.sock
replicate-do-db=tong
replicate-ignore-db=mysql
[root@tong3 ~]#
配置主主模式:
tong1节点:
[root@tong1 ~]# /etc/init.d/mysqld restart
Shutting down MySQL…. SUCCESS!
Starting MySQL. SUCCESS!
[root@tong1 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> grant replication slave,replication client on *.* to repl_user@’192.168.1.%’ identified by ‘system!#%246’; –创建复制用户
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 120 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)
mysql>
tong2节点:
[root@tong2 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> grant replication slave,replication client on *.* to repl_user@’192.168.1.%’ identified by ‘system!#%246’;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 120 | | | |
+——————+———-+————–+——————+——————-+
1 row in set (0.00 sec)
mysql> change master to master_host=’192.168.1.247‘,master_port=3306,master_user=’repl_user’,master_password=’system!#%246′,master_log_file=’mysql-bin.000001′,master_log_pos=120; –复制tong1中的数据
Query OK, 0 rows affected, 2 warnings (0.50 sec)
mysql> start slave; –开启从服务
Query OK, 0 rows affected (0.05 sec)
mysql>
tong1节点:
mysql> change master to master_host=’192.168.1.248‘,master_port=3306,master_user=’repl_user’,master_password=’system!#%246′,master_log_file=’mysql-bin.000001′,master_log_pos=120; –复制tong2中的数据
Query OK, 0 rows affected, 2 warnings (0.22 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.248
Master_User: repl_user
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 120
Relay_Log_File: relay-log.000002
Relay_Log_Pos: 283
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes –主主同步成功
Slave_SQL_Running: Yes
Replicate_Do_DB: tong
Replicate_Ignore_DB: mysql
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 120
Relay_Log_Space: 450
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 10
Master_UUID: de5d22d1-ed4b-11e4-9390-1078d2c717e8
Master_Info_File: /usr/local/mysql-5.6.23/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)
mysql>
5.测试主主架构是否生效
tong1节点:
mysql> create table a(a int);
Query OK, 0 rows affected (0.39 sec)
mysql> insert into a values(1);
Query OK, 1 row affected (0.03 sec)
mysql> select * from a;
+——+
| a |
+——+
| 1 |
+——+
1 row in set (0.00 sec)
mysql>
tong2节点:
mysql> \u tong
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from a;
+——+
| a |
+——+
| 1 |
+——+
1 row in set (0.00 sec)
mysql> insert into a values(2);
Query OK, 1 row affected (0.03 sec)
mysql> select * from a;
+——+
| a |
+——+
| 来@源gao*daima.com搞@代#码网 1 |
| 2 |
+——+
2 rows in set (0.00 sec)
mysql>
6.将tong3设置成tong1节点的从机
[root@tong3 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
[root@tong3 ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 5.6.23 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.
mysql> change master to master_host=’192.168.1.247′,master_port=3306,master_user=’repl_user’,master_password=’system!#%246′,master_log_file=’mysql-bin.000001′,master_log_pos=120; –复制tong1中的数据
Query OK, 0 rows affected, 2 warnings (0.27 sec)
mysql> start slave;
Query OK, 0 rows affected (0.07 sec)
mysql> \u tong
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from a; –数据已同步
+——+
| a |
+——+
| 1 |
| 2 |
+——+
2 rows in set (0.00 sec)
mysql>
7.在三个数据库节点分别创建监控用户和代理用户
mysql> grant replication client on *.* to ‘mmm_moniton’@’192.168.1.%’ identified by ‘moniton’;
Query OK, 0 rows affected (0.00 sec)
mysql> grant super,replication client,process on *.* to ‘mmm_agent’@’192.168.1.%’ identified by ‘agent’;
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql>
8.在监控节点修改配置文件
[root@tong3 ~]# cd /etc/mysql-mmm/
[root@tong3 mysql-mmm]# ll
total 16
-rw-r—–. 1 root root 33 Apr 29 14:06 mmm_agent.conf
-rw-r—–. 1 root root 684 Apr 29 14:06 mmm_common.conf
-rw-r—–. 1 root root 321 Apr 29 14:06 mmm_mon.conf
-rw-r—–. 1 root root 1293 Apr 29 14:06 mmm_tools.conf
[root@tong3 mysql-mmm]# vim mmm_common.conf
active_master_role writer
cluster_interface eth0 –心跳网卡接口
pid_path /var/run/mmm_agentd.pid
bin_path /usr/lib/mysql-mmm
replication_user repl_user –复制用户名和密码
replication_password system!#%246
agent_user mmm_agent –代理用户名和密码
agent_password agent
–主机名
ip 192.168.1.247 –tong1的IP地址
mode master –主模式
peer tong1 –主机名
ip 192.168.1.248
mode master
peer tong2
ip 192.168.1.249
mode slave –从服务器
hosts tong1, tong2 –tong1和tong2可写
ips 192.168.1.120
mode exclusive
hosts tong1, tong2, tong3 –三台可读
ips 192.168.1.121
mode balanced
[root@tong3 mysql-mmm]# vim mmm_mon.conf
include mmm_common.conf
ip 127.0.0.1
pid_path /var/run/mmm_mond.pid
bin_path /usr/lib/mysql-mmm
status_path /var/lib/misc/mmm_mond.status
ping_ips 192.168.1.247, 192.168.1.248, 192.168.1.249
auto_set_online 10
monitor_user mmm_moniton –监控用户名和密码
monitor_password moniton
debug 1 –为1是开启服务打印日志,为0是只开启服务
[root@tong3 mysql-mmm]# cat mmm_agent.conf
include mmm_common.conf
this tong3 –主机名
[root@tong3 mysql-mmm]#
9.将tong3节点的mmm_common.conf复制到tong1和tong2节点中
[root@tong3 mysql-mmm]# scp mmm_common.conf tong1:/etc/mysql-mmm/
mmm_common.conf 100% 674 0.7KB/s 00:00
You have mail in /var/spool/mail/root
[root@tong3 mysql-mmm]# scp mmm_common.conf tong2:/etc/mysql-mmm/
mmm_common.conf 100% 674 0.7KB/s 00:00
[root@tong3 mysql-mmm]#
tong1,tong2修改mmm_agent.conf文件并启动服务
[root@tong1 mysql-mmm]# cat mmm_agent.conf
include mmm_common.conf
this tong1 –修改主机名
[root@tong1 mysql-mmm]# /etc/init.d/mysql-mmm-agent restart –重启服务
Daemon bin: ‘/usr/sbin/mmm_agentd’
Daemon pid: ‘/var/run/mmm_agentd.pid’
Daemon bin: ‘/usr/sbin/mmm_agentd’
Daemon pid: ‘/var/run/mmm_agentd.pid’
Shutting down MMM Agent daemon. Ok
Daemon bin: ‘/usr/sbin/mmm_agentd’
Daemon pid: ‘/var/run/mmm_agentd.pid’
Starting MMM Agent daemon… Ok
[root@tong1 mysql-mmm]#
tong3启动mmm-mysql-agent和mysql-mmm-monitor服务
[root@tong3 mysql-mmm]# /etc/init.d/mysql-mmm-agent restart
Daemon bin: ‘/usr/sbin/mmm_agentd’
Daemon pid: ‘/var/run/mmm_agentd.pid’
Daemon bin: ‘/usr/sbin/mmm_agentd’
Daemon pid: ‘/var/run/mmm_agentd.pid’
Shutting down MMM Agent daemon. Ok
Daemon bin: ‘/usr/sbin/mmm_agentd’
Daemon pid: ‘/var/run/mmm_agentd.pid’
Starting MMM Agent daemon… Ok
[root@tong3 mysql-mmm]# /etc/init.d/mysql-mmm-monitor restart
Daemon bin: ‘/usr/sbin/mmm_mond’
Daemon pid: ‘/var/run/mmm_mond.pid’
Daemon bin: ‘/usr/sbin/mmm_mond’
Daemon pid: ‘/var/run/mmm_mond.pid’
Shutting down MMM Monitor daemon: not running.
Daemon bin: ‘/usr/sbin/mmm_mond’
Daemon pid: ‘/var/run/mmm_mond.pid’
Starting MMM Monitor daemon: Ok
[root@tong3 mysql-mmm]# mmm_control show
tong1(192.168.1.247) master/ONLINE. Roles: writer(192.168.1.120)
tong2(192.168.1.248) master/ONLINE. Roles:
tong3(192.168.1.249) slave/ONLINE. Roles: reader(192.168.1.121)
[root@tong3 mysql-mmm]# mmm_control set_offline tong1
OK: State of ‘tong1’ changed to ADMIN_OFFLINE. Now you can wait some time and check all roles!
[root@tong3 mysql-mmm]# mmm_control show
tong1(192.168.1.247) master/ADMIN_OFFLINE. Roles:
tong2(192.168.1.248) master/ONLINE. Roles: writer(192.168.1.120)
tong3(192.168.1.249) slave/ONLINE. Roles: reader(192.168.1.121)
[root@tong3 mysql-mmm]# mmm_control set_offline tong3
OK: State of ‘tong3’ changed to ADMIN_OFFLINE. Now you can wait some time and check all roles!
[root@tong3 mysql-mmm]# mmm_control show
tong1(192.168.1.247) master/ADMIN_OFFLINE. Roles:
tong2(192.168.1.248) master/ONLINE. Roles: reader(192.168.1.121), writer(192.168.1.120)
tong3(192.168.1.249) slave/ADMIN_OFFLINE. Roles:
[root@tong3 mysql-mmm]# mmm_control set_online tong1
OK: State of ‘tong1’ changed to ONLINE. Now you can wait some time and check its new roles!
[root@tong3 mysql-mmm]# mmm_control set_online tong3
OK: State of ‘tong3’ changed to ONLINE. Now you can wait some time and check its new roles!
[root@tong3 mysql-mmm]# mmm_control show
tong1(192.168.1.247) master/ONLINE. Roles: reader(192.168.1.121)
tong2(192.168.1.248) master/ONLINE. Roles: writer(192.168.1.120)
tong3(192.168.1.249) slave/ONLINE. Roles:
You have mail in /var/spool/mail/root
[root@tong3 mysql-mmm]
转载请注明原文链接：mysql高可用方案之MMM
