CentOS7 install MySQL MMM below

2. Install the operating system

Minimal installation of CentOS7 (64-bit)

3. Configure the operating system

1. Close selinux

cat / etc / selinux / config SELINUX = disabled
2. Turn off the firewall

systemctl stop firewalld
systemctl disable firewalld
3. Configure NTP to synchronize time

yum -y install ntp
vi /etc/ntp.conf
Add 3 time servers
server s2d.time.edu.cn iburst #Southwest Area Network Center
server s2e.time.edu.cn iburst #Northwest Region Network Center
server s2f.time.edu.cn iburst #Northeast Area Network Center

Start ntp service
systemctl start ntpd
systemctl enable ntpd

View status
ntpq -p
Four. Install MySQL 5.6 (not MariaDB)

1. Install MySQL Repository

rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
2. Install MySQL Server, MySQL client

yum install mysql-community-server
3. Start MySQL automatically at boot

systemctl enable mysqld
4. Start MySQL

systemctl start mysqld
5. MySQL defaults to a blank password, execute the following command to modify

mysql_secure_installation
mysql -u root -p

#Enter the mysql console, increase the root user's remote login permission

GRANT ALL PRIVILEGES ON *. * TO [email protected] "%" IDENTIFIED BY ‘GDkyUDJM6gr2Dx’ WITH GRANT OPTION;
flush privileges;
6. Modify the storage directory of mysql

a. Stop mysql

systemctl stop mysql
b. Migrate the database storage directory

mkdir / opt / mysql
chown mysql: mysql / opt / mysql
mv / var / lib / mysql / opt / mysql
cd / opt / mysql
mv mysql data
c. Modify the configuration file

vim /etc/my.cnf

#Modify datadir and socket pointing

datadir = / opt / mysql / data
socket = / opt / mysql / mysql.sock

#At the same time add the following code, this is to enable mysql client to connect to mysql, to avoid Ca n’t connect to local MySQL server through socket ‘/var/lib/mysql/mysql.sock error!


[mysql]
socket = / opt / mysql / mysql.sock
d. Start mysql

systemctl start mysql
7. Configure MySQL Relication

a. Put the added content in the [mysqld] section of the configuration file /etc/my.cnf, as follows:

[mysqld]
datadir = / opt / mysql / data
socket = / opt / mysql / mysql.sock
user = mysql

#The following is the newly added content

read_only = 1

default-storage-engine = innodb

replicate-ignore-db = mysql, information_schema #Unsynchronized database, multiple write multiple lines
replicate-do-db = meerkat #Synchronized database, multiple write multiple lines

binlog-ignore-db = mysql, information_schema #No need to record binary log database, multiple separated by commas
binlog-do-db = meerkat # need to record binary log database, multiple separated by commas

log_slave_updates #When one main failure, the other immediately takes over
sync-binlog = 1 #Each item is automatically updated with high security, the default is 0

server-id = 1 # The value of server-id on each server is different, here in order 1, 2, 3, 4.

#The log file names here are also different for each machine, such as (mysql-master-001-bin.log, mysql-master-002-bin.log, mysql-slave-001-bin.log, mysql-slave-002 -bin.log)

log-bin = /opt/mysql/log/mysql-master-001-bin.log
log_bin_index = /opt/mysql/log/mysql-master-001-bin.log.index
relay_log = /opt/mysql/log/mysql-master-001-bin.relay
relay_log_index = /opt/mysql/log/mysql-master-001-bin.relay.index

# End of log file specification

expire_logs_days = 10
max_binlog_size = 100M
log_slave_updates = 1
b. Add log directory under / opt / mysql

cd / opt / mysql
mkdir log
chown mysql: mysql log
d. Restart mysql

systemctl restart mysql
e. Check if the configuration is successful
1) Log in to mysql and execute show master status to see if there is the following output
+ —————— + ————- + ————– + —————— + ——————- +
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ —————— + ————- + ————– + —————— + ——————- +
| mysql-master-001-bin.000001 | 120 | | mysql | |
+ —————— + ————- + ————– + —————— + ——————- +
2) Go to the / var / log / mysql directory to see if files like mysql-master-001-bin.000001 and mysql-master-001-bin.log.index are generated.

f. Modify it on all 4 mysql servers. Note that the log naming is different for each machine

5. Users needed to create a new synchronization database
When using mysql-mmm, a total of three users are required: replication, mmm_agent, and mmm_monitor (users on the management server used to monitor the status of the cluster, so you can restrict login to the management server) Use the following three commands to create these three users and assign the corresponding permissions
All mysql servers are run once

GRANT REPLICATION CLIENT ON *. * TO ‘mmm_monitor‘@‘192.168.1.%’ IDENTIFIED BY ‘monitor’;
GRANT SUPER, REPLICATION CLIENT, PROCESS ON *. * TO ‘mmm_agent’@‘192.168.1.%’ IDENTIFIED BY ‘agent’;
GRANT REPLICATION SLAVE ON *. * TO ‘replication‘@‘192.168.1.%‘ IDENTIFIED BY ‘replication’;

flush privileges;
6. Set the replication mechanism (from master-001 to master-002, from master-002 to slave-001, slave-002)

1. Configure master-001 as the master and copy to master-002

a. Configure master-001
Since master-001 is configured as the source data, there is no need to set change master and start the slave process
b. Configure master-002 (specify change master as master-001)

#ssh to master-002 server

mysql -u root -p

#Enter in the mysql console

CHANGE MASTER TO MASTER_HOST = ‘192.168.1.211’, MASTER_USER = ‘replication’, MASTER_PASSWORD = ‘replication‘, MASTER_PORT = 3306, MASTER_LOG_FILE = ‘mysql-master-002-bin.000001’, MASTER_LOG_POS = 260, MASTER_CONNECT_RETRY = 10;

#RESET

reset slave;

#Start slave

start slave;

#View slave status

show slave status \ G

#Results are as follows

mysql> show slave status \ G
*************************** 1. row ******************** *******
       Slave_IO_State: Waiting for master to send event
          Master_Host: 192.168.1.212
          Master_User: replication
          Master_Port: 3306
        Connect_Retry: 10
      Master_Log_File: mysql-master-002-bin.000001
  Read_Master_Log_Pos: 120
       Relay_Log_File: mysql-master-001-bin.000004
        Relay_Log_Pos: 294
Relay_Master_Log_File: mysql-master-002-bin.000001
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes
      Replicate_Do_DB:
  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: 472
      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: 2
          Master_UUID: 3d3b9f4f-f74f-11e5-9a30-005056b324c4
     Master_Info_File: /opt/mysql/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)
Note:
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

b. Configure slave-001


#ssh to slave-001 server

mysql -u root -p

#Enter in the mysql console

CHANGE MASTER TO MASTER_HOST = ‘192.168.1.212’, MASTER_USER = ‘replication’, MASTER_PASSWORD = ‘replication‘, MASTER_PORT = 3306, MASTER_LOG_FILE = ‘mysql-slave-001-bin.000001’, MASTER_LOG_POS = 260, MASTER_CONNECT_RETRY = 10;

#RESET

reset slave;

#Start slave

start slave;

#View slave status

show slave status \ G

#Results are as follows

mysql> show slave status \ G
c. Configure slave-002


#ssh to the slave-002 server

mysql -u root -p

#Enter in the mysql console

CHANGE MASTER TO MASTER_HOST = ‘192.168.1.212’, MASTER_USER = ‘replication’, MASTER_PASSWORD = ‘replication‘, MASTER_PORT = 3306, MASTER_LOG_FILE = ‘mysql-slave-002-bin.000001’, MASTER_LOG_POS = 260, MASTER_CONNECT_RETRY = 10;

#RESET

reset slave;

#Start slave

start slave;

#View slave status

show slave status \ G

#Results are as follows

mysql> show slave status \ G
d. Test the replication mechanism
On master-001, add, delete, and modify table spaces, tables, fields, and contents can be synchronized to master-002, slave-001, slave-002
2. Configure master-002 as the master and copy to master-001

a. Configure master-001 (specify change master as master-002)

#ssh to master-001 server

mysql -u root -p

#Enter in the mysql console

CHANGE MASTER TO MASTER_HOST = ‘192.168.1.212’, MASTER_USER = ‘replication’, MASTER_PASSWORD = ‘replication‘, MASTER_PORT = 3306, MASTER_LOG_FILE = ‘mysql-master-001-bin.000001’, MASTER_LOG_POS = 260, MASTER_CONNECT_RETRY = 10;

#RESET

reset slave;

#Start slave

start slave;

#View slave status

show slave status \ G

#Results are as follows

mysql> show slave status \ G
This completes the mutual replication of master-001 and master-002, and from master-002 to slave-001 and slave-002.

2. Test whether the replication mechanism is successful.
a. Create a database in master-001 and confirm that master-002, slave-001, and slave-002 are all replicated synchronously.
b. Create a database in master-002 and confirm that master-001, slave-001, and slave-002 are all replicated synchronously.
7. Install MMM (http://mysql-mmm.org/)

1. Install epel expansion pack
The CentOS software repository does not include these software by default, and must be supported by epel. So we must install epel first

wget http://dl.fedoraproject.org/pub/epel/7/x86_64/e/epel-release-7-5.noarch.rpm
rpm -ivh epel-release-7-5.noarch.rpm
yum install mysql-mmm-monitor
yum install mysql-mmm-agent

#When the mysql-mmm component installed with yum starts, an error occurs.

So use the following tar.gz file to install
yum erase mysql-mmm-monitor
yum erase mysql-mmm-agent
2. Install the monitor program on monitor (192.168.1.210)

cd / tmp
wget http://pkgs.fedoraproject.org/repo/pkgs/mysql-mmm/mysql-mmm-2.2.1.tar.gz/f5f8b48bdf89251d3183328f0249461e/mysql-mmm-2.2.1.tar.gz
tar -xzvf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1.tar.gz
make install
3. Install the agent on the database server (192.168.1.211-214)

cd / tmp
wget http://pkgs.fedoraproject.org/repo/pkgs/mysql-mmm/mysql-mmm-2.2.1.tar.gz/f5f8b48bdf89251d3183328f0249461e/mysql-mmm-2.2.1.tar.gz
tar -xzvf mysql-mmm-2.2.1.tar.gz
cd mysql-mmm-2.2.1.tar.gz
make install
4. Configure MMM

a. Edit /etc/mysql-mmm/mmm_common.conf
After the installation is complete, all configuration files are placed under / etc / mysql-mmm /. Both the management server and the database server must contain a common file mmm_common.conf, as follows:

active_master_role writer
<host default>
    cluster_interface eno16777984 #Network card equipment
    pid_path /var/run/mmm_agentd.pid
    bin_path / usr / lib / mysql-mmm / #Here to confirm whether there is a directory of agent, monitor, tools. Otherwise, the VIP cannot be generated
    replication_user replication
    replication_password replication
    agent_user mmm_agent
    agent_password agent
</ host>
<host db1>
    ip 192.168.1.211
    mode master
    peer db2
</ host>
<host db2>
    ip 192.168.1.212
    mode master
    peer db1
</ host>
<host db3>
    ip 192.168.1.213
    mode slave
</ host>
<host db4>
    ip 192.168.1.214
    mode slave
</ host>
<role writer>
    hosts db1, db2
    ips 192.168.1.220
    mode exclusive
</ role>
<role reader>
    hosts db2, db3, db4
    ips 192.168.1.221, 192.168.1.222, 192.168.1.223
    mode balanced
</ role>
Copy to the five machines of monitor-001, master-001, master-002, slave-001 and slave-002 through scp command.

b. Edit /etc/mysql-mmm/mmm_agent.conf on 4 mysql node machines
On the database server, there is a mmm_agent.conf that needs to be modified, and its contents are:

include mmm_common.conf


# The ‘this‘ variable refers to this server. Proper operation requires


# that ‘this‘ server (db1 by default), as well as all other servers, have the


# proper IP addresses set in mmm_common.conf.

this db1
Db1 in the last line should be changed to db2, db3 and db4 on different database servers, otherwise the agent will not start.

c. Edit /etc/mysql-mmm/mmm_mon.conf on the monitor host
Configure mmm_mon.conf on monitor-001

include mmm_common.conf

<monitor>
    ip 127.0.0.1 #For security, set to monitor only on the local machine, mmm_mond monitors 9988 by default
    pid_path /var/run/mmm_mond.pid
    bin_path / usr / lib / mysql-mmm /
    status_path /var/lib/misc/mmm_mond.status
    ping_ips192.168.1.211, 192.168.1.212, 192.168.1.213, 192.168.1.214 #Used to test the network availability IP address list, as long as one of the addresses is pinged, it means the network is normal, do not write the local address here
    #flap_duration 3600 #Jitter time range, in seconds, these two parameters are added considering the situation
    # flap_count 3 #Maximum number of times of shaking in the time range of shaking
    auto_set_online 60 # Whether to set automatic online, if the value is greater than 0, the jittering host will set automatic online after the time range of jitter

    # The kill_host_bin does not exist by default, though the monitor will
    # throw a warning about it missing. See the section 5.10 "Kill Host
</ monitor>

<host default>
    monitor_user mmm_monitor
    monitor_password monitor
</ host>
5. Automatically start agent and monitor

a. Start monitor on monitor

systemctl enable mysql-mmm-monitor
systemctl start mysql-mmm-monitor
b. Start the agent on 4 database servers

 systemctl enable mysql-mmm-agent
 systemctl starat mysql-mmm-agent
If there is a configuration file /etc/mysql-mmm/mmm_common.conf is world readable! This error, you need to check the permissions of the /etc/mysql-mmm/mmm_common.conf file, it should be chmod 640 / etc / mysql-mmm / mmm_common .conf
The permissions of all configuration files in the cluster are preferably set to 640, otherwise, errors may occur when starting the MMM service

8. MMM startup sequence: start monitor first, then start agent

IX. Problems and solutions

1. Using systemctl start mysql-mmm-monitor on the monitor host cannot allocate vip. And through /etc/init.d/mysql-mmm-monitor start you can assign vip
Solution 
http://blog.csdn.net/remote_roamer/article/details/49869395
Vip cannot be activated due to lack of environment variables.
Then at the top of the script file in /etc/rc.d/init.d/mysql-mmm-monitor, add
source /root/.bash_profile
2. After vip drift, 192.168.1.200 cannot be pinged

Problem Description: 
When master-001 fails and the DB is unavailable, the VIP will automatically drift to master-002 to achieve high availability. But there is a problem, because the ARP aging time is too long, so that the drifting VIP is unavailable and cannot be pinged. In other words, MySQL-MMM does not consider the situation that ARP aging time is too long and adopts the method of strong ARP brush.
a. Solution 1

Modify the code on master-001 and master-002 at the same time (this is not tested successfully)
Modify file
/usr/share/perl5/vendor_perl/MMM/Agent/Helpers/Actions.pm
vim /usr/share/perl5/vendor_perl/MMM/Agent/Helpers/Actions.pm

_Exit_ok () in the sub configure_ip ($$) code segment; add the following code before


#Here is the ping failed after solving the VIP offset. The reason is that the arp aging time is too long, here is forced to refresh arp

my $ getway = `/ sbin / route | awk‘ default / (print $ 2) ‘` `/ sbin / arping -I $ if -c 3 -s $ ip $ getway`; #Configuration end
650) this.width = 650; "alt =" "src =" http://img.blog.csdn.net/20160405123255733 "/>

b. Solution 2
Manually reset arping on the host where the VIP drifted

arping -I eno16777984 -c 3 -s 192.168.1.220 192.168.1.1

c. Solution 3
Use a shell script to refresh on master-001 and master-002 (this method works normally)

vim /root/refresh_vip.sh

#Add the following content:


#! / bin / sh

PATH = / sbin: / bin: / usr / sbin: / usr / bin: / usr / local / bin: / usr / local / sbin
MMM_COMM_CONF = "/ etc / mysql-mmm / mmm_common.conf"
ETH_NAME = `awk‘ / cluster_interface / {print $ 2} ’$ {MMM_COMM_CONF}`
VIP_ADDR = `grep -A 2‘ <role writer> ’$ {MMM_COMM_CONF} | awk‘ / ips / {print $ 2} ’`
GETWAY_ADDR = `/ sbin / route | awk‘ / default / {print $ 2} ’`

if [[-n `/ sbin / ip addr show $ {ETH_NAME} | grep $ {VIP_ADDR}`]]; then
    / sbin / arping -I $ {ETH_NAME} -c 3 -s $ {VIP_ADDR} $ {GETWAY_ADDR}> / dev / null 2> & 1
fi
Put it in crontab and run

crontab -e
* * * * * sleep 10; /root/refresh_vip.sh> / dev / null 2> & 1
* * * * * sleep 20; /root/refresh_vip.sh> / dev / null 2> & 1
* * * * * sleep 30; /root/refresh_vip.sh> / dev / null 2> & 1
* * * * * sleep 40; /root/refresh_vip.sh> / dev / null 2> & 1
* * * * * sleep 50; /root/refresh_vip.sh> / dev / null 2> & 1
Install Mysql MMM under CentOS7