MySQL MHA 高可用集群

tar xf mysql-5.5.32.tar.gz 
cd mysql-5.5.32
cmake . -DCMAKE_INSTALL_PREFIX=/application/mysql-5.5.32 -DMYSQL_DATADIR=/application/mysql-5.5.32/data -DMYSQL_UNIX_ADDR=/application/mysql-5.5.32/tmp/mysql.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii -DENABLED_LOCAL_INFILE=ON -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_FEDERATED_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1 -DWITHOUT_EXAMPLE_STORAGE_ENGINE=1 -DWITHOUT_PARTITION_STORAGE_ENGINE=1 -DWITH_FAST_MUTEXES=1 -DWITH_ZLIB=bundled -DENABLED_LOCAL_INFILE=1 -DWITH_READLINE=1 -DWITH_EMBEDDED_SERVER=1 -DWITH_DEBUG=0
make
make install

mkdir /data/{3306,3307}/data -p
unzip data.zip 
mkdir /data/{3306,3307}/data -p
ln -s /application/mysql-5.5.32/ /application/mysq
find /data -type f -name "mysql"|xargs chmod +x
chown -R mysql.mysql /data

cd /application/mysql
./scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3306/data --user=mysql
./scripts/mysql_install_db --basedir=/application/mysql/ --datadir=/data/3307/data --user=mysql 
/data/3306/mysql start
lsof -i:3306
mysql -S /data/3306/mysql.sock
/data/3307/mysql start
lsof -i:3307
mysql -S /data/3307/mysql.sock

[root@mysql 3306]# grep log-bin my.cnf 
log-bin = /data/3306/mysql-bin
[root@mysql 3306]# grep server-id my.cnf        
server-id = 1

mysql> grant replication slave on *.* to ‘rep‘@‘%‘ identified by ‘oldboy123‘;
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> flush table with read lock;  锁表
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+------------------+----------+--------
| mysql-bin.000001 |      476 |            
+------------------+----------+--------
[root@mysql ~]# mysqldump -uroot -poldboy123 -S /data/3306/mysql.sock -A -B --events >/opt/mysql_bak.sql
mysql> unlock tables； 解锁

[root@mysql opt]# mysql -uroot -poldboy456 -S /data/3307/mysql.sock < mysql_bak.sql  
[root@mysql opt]# mysql -uroot -poldboy456 -S /data/3307/mysql.sock -e "show databases;"

CHANGE MASTER TO
MASTER_HOST=‘192.168.160.138‘,
MASTER_PORT=3307,
MASTER_USER=‘slave‘,
MASTER_PASSWORD=‘123‘,
MASTER_LOG_FILE=‘mysql-bin.000001‘,
MASTER_LOG_POS=107;

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G
   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   Seconds_Behind_Master: 0

# ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

# yum install perl-DBD-MySQL
# yum install perl-Config-Tiny
# yum install perl-Log-Dispatch
# yum install perl-Parallel-ForkManager
# yum install -y rrdtool perl-rrdtool rrdtool-devel perl-Params-Validate
# yum install -y perl-Time-HiRes

rpm -ivh mha4mysql-manager-0.56-0.el6.noarch.rpm --nodeps --force
rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm --nodeps --force

yum install perl-DBD-MySQL

rpm -ivh mha4mysql-node-0.56-0.el6.noarch.rpm --nodeps --force

    
mysql> grant all privileges on *.* to mha@‘%‘ identified by ‘123456‘;

mkdir /etc/masterha/ -p
/var/log/masterha/app1/ -p

[server default]
manager_workdir=/var/log/masterha/app1
manager_log=/var/log/masterha/app1/manager.log
user=mha    #MHA的授权管理用户
password=123456
ssh_user=root
repl_user=slave   #MySQL主从实例授权的用户
repl_password=123
ping_interval=1
shutdown_script=""
master_ip_online_change_script=""
report_script=""

[server1]
hostname=192.168.178.130
port=3306
candidate_master=1  #可以成为master
master_binlog_dir="/data/3306"    #binlog目录


[server2]
hostname=192.168.178.130
port=3307
candidate_master=1  #可以成为master
master_binlog_dir="/data/3307"

[server3]
hostname=192.168.178.130
port=3308    
master_binlog_dir=/data/3308
no_master=1    #不能成为master

[server4]
hostname=192.168.178.130
port=3309
master_binlog_dir=/data/3309
no_master=1    #不能成为master

masterha_check_ssh --conf=/etc/masterha/app1.cnf    #检查SSH互认是否成功
masterha_check_repl --conf=/etc/masterha/app1.cnf　 #检查MySQL主从复制是否成功
masterha_manager --conf=/etc/masterha/app1.cnf &

Sat Aug 27 10:33:04 2016 - [info] 
db2 (current master)
 +--db1
 +--db3
 +--db4
 
Sat Aug 27 10:33:04 2016 - [warning] master_ip_failover_script is not defined.
Sat Aug 27 10:33:04 2016 - [warning] shutdown_script is not defined.
Sat Aug 27 10:33:04 2016 - [info] Set master ping interval 1 seconds.
Sat Aug 27 10:33:04 2016 - [warning] secondary_check_script is not defined. It is highly recommended setting it to check master reachability from two or more routes.
Sat Aug 27 10:33:04 2016 - [info] Starting ping health check on (192.168.178.130:3306)..
Sat Aug 27 10:33:04 2016 - [info] Ping succeeded, sleeping until it doesn‘t respond..

Started automated(non-interactive) failover.
The latest slave 192.168.178.130(192.168.178.130:3307) has all relay logs for recovery.
Selected 192.168.178.130(192.168.178.130:3307) as a new master.
192.168.178.130(192.168.178.130:3307): OK: Applying all logs succeeded.
192.168.178.130(192.168.178.130:3309): This host has the latest relay log events.
192.168.178.130(192.168.178.130:3308): This host has the latest relay log events.
Generating relay diff files from the latest slave succeeded.
192.168.178.130(192.168.178.130:3309): OK: Applying all logs succeeded. Slave started, replicating from 192.168.178.130(192.168.178.1
30:3307)
192.168.178.130(192.168.178.130:3308): OK: Applying all logs succeeded. Slave started, replicating from 192.168.178.130(192.168.178.1
30:3307)
192.168.178.130(192.168.178.130:3307): Resetting slave info succeeded.
Master failover to 192.168.178.130(192.168.178.130:3307) completed successfully.