数据库高可用

准备三台虚拟机

1.Paxos算法
2.一主多从:读远远大于写|主从均衡:分多个数据库|多主多从
3.实现mysql高可用的软件:1|2|3
4.监控系统zabbix不支持分库分表
5.大数据量大概超越5000万以上
6.mysql单表最好不要超过1000万,不像oreacal
7.多了解业业务场景
8.数据库的高可用切换,必须保证在数据一致性的基础上(MHA可以实现数据一致性检测)
9.数据库同步时有状态,而且还牵扯到数据一致性问题(开发时考虑:自主从切换|数据一致性)
10.数据库主从切换:建议不要使用自动切换
11.了解高可用的工作机制,简历上不需要写,因为实际中可以用saltstack实现一键部署
12.ospf协议:内部网关协议(使用场景:http://guojiping.blog.51cto.com/5635432/949899/)
13.灰度发布,蓝绿部署
14.IO线程error:用户授权或者selinux
15.了解gti并行复制d工作模式
16.mysql

数据库高可用:

先实现Mysql主从复制

mysql1:主
mysql2:备


[root@mysql1 ~]# yum install mysql-server -y
[root@mysql1 ~]# vim /etc/my.cnf 
.....
     8  server-id=1
     9  log-bin=binlog
    10  binlog-do-db=test
    11  binlog-ignore-db=mysql
.....
[root@mysql1 ~]# /etc/init.d/mysqld start
[root@mysql1 ~]# cd /var/lib/mysql/
[root@mysql1 mysql]# ls
binlog.000001  binlog.000003  ibdata1      ib_logfile1  mysql.sock
binlog.000002  binlog.index   ib_logfile0  mysql        test
[root@mysql1 mysql]# cat binlog.index 
./binlog.000001
./binlog.000002
./binlog.000003
[root@mysql1 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2013, 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> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 |      106 | test         | mysql            |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> grant replication slave on *.* to repl@'172.25.30.%' identified by 'westos';     #给u从授权
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+---------------+----------+--------------+------------------+
| binlog.000003 |      405 | test         | mysql            |
+---------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> quit
Bye
[root@mysql1 mysql]#



[root@mysql2 ~]# mysql -h 172.25.30.6 -u repl -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2013, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@mysql2 ~]# vim /etc/my.cnf   #修改从配置文件
..... 
     8  server-id=2
     9  read-only=1
.....
[root@mysql2 ~]# /etc/init.d/mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]
[root@mysql2 ~]# tail -f /var/log/mysqld.log 
[root@mysql2 ~]# cd /var/lib/mysql/
[root@mysql2 mysql]# ls
ibdata1  ib_logfile0  ib_logfile1  mysql  mysql.sock  test
[root@mysql2 mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.71 Source distribution

Copyright (c) 2000, 2013, 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> show slave status;
Empty set (0.00 sec)

mysql> change master to master_host='172.25.30.6',master_user='repl',master_password='westos',master_log_file='binlog.000003',master_log_pos=405;
Query OK, 0 rows affected (0.10 sec)    #同步主服务器

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;     
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.30.6
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 405
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 248
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.....
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> quit
Bye
[root@mysql2 mysql]# 
#至此主从复制成功







[root@mysql1 ~]# ls
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
[root@mysql1 ~]# cd /var/lib/mysql/
[root@mysql1 mysql]# ls
binlog.000001  binlog.000003  ibdata1      ib_logfile1  mysql.sock
binlog.000002  binlog.index   ib_logfile0  mysql        test
[root@mysql1 mysql]# rm -fr *
[root@mysql1 mysql]# rpm -qa | grep mysql
mysql-5.1.71-1.el6.x86_64
mysql-server-5.1.71-1.el6.x86_64
mysql-libs-5.1.71-1.el6.x86_64
[root@mysql1 mysql]# yum remove mysql mysql-server
[root@mysql1 ~]# ls
mysql-community-client-5.7.17-1.el6.x86_64.rpm
mysql-community-common-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-5.7.17-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.17-1.el6.x86_64.rpm
mysql-community-server-5.7.17-1.el6.x86_64.rpm
[root@mysql1 ~]# yum install * -y
[root@mysql1 ~]# /etc/init.d/mysqld start
[root@mysql1 ~]# mysql_secure_installation 
[root@mysql1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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 on *.* to repl@'172.25.30.%' identified by 'Xiamin0099+';    #主授权
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+----------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+---------------+----------+--------------+------------------+----------------------------------------+
| binlog.000006 |      447 | test         | mysql            | 3456e9d3-b157-11e7-a3eb-525400931c42:1 |
+---------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

mysql> 




##在从上同步,初始化密码
[root@mysql2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show slave status;
Empty set (0.00 sec)

mysql> change master to master_host='172.25.30.6',master_user='repl',master_password='Xiamin0099+',MASTER_AUTO_POSITION=1;  #从同步
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.30.6
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000006
          Read_Master_Log_Pos: 447
               Relay_Log_File: mysql2-relay-bin.000002
                Relay_Log_Pos: 654
        Relay_Master_Log_File: binlog.000006
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
.....
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 
[root@mysql2 ~]#







[root@mysql1 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test
Database changed
mysql> create table usertb (
    -> username varchar(20) not null,
    -> password varchar(20) not null);
Query OK, 0 rows affected (0.07 sec)

mysql> insert into usertb values ('user1','123'),('user2','456');
Query OK, 2 rows affected (0.03 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@mysql1 ~]# 





[root@mysql2 ~]# mysql -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.7.17 MySQL Community Server (GPL)

Copyright (c) 2000, 2016, 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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test
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> show tables;
+----------------+
| Tables_in_test |
+----------------+
| usertb         |
+----------------+
1 row in set (0.00 sec)

mysql> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.00 sec)

mysql> quit
Bye
[root@mysql2 ~]# 






&&&实现读写分离


[root@mysql3 ~]# ls
mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz
[root@mysql3 ~]# tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local/
[root@mysql3 ~]# cd /usr/local/
[root@mysql3 local]# ls
bin  games    lib    libexec                                sbin   src
etc  include  lib64  mysql-proxy-0.8.5-linux-el6-x86-64bit  share
[root@mysql3 local]# ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
[root@mysql3 local]# ls
bin  games    lib    libexec      mysql-proxy-0.8.5-linux-el6-x86-64bit  share
etc  include  lib64  mysql-proxy  sbin                                   src
[root@mysql3 local]# cd mysql-proxy
[root@mysql3 mysql-proxy]# ls
bin  include  lib  libexec  licenses  share
[root@mysql3 mysql-proxy]# pwd
/usr/local/mysql-proxy
[root@mysql3 mysql-proxy]# mkdir conf log 
[root@mysql3 mysql-proxy]# cd conf/
[root@mysql3 conf]# vim mysql-proxy.conf
[mysql-proxy]
daemon=true
user=root
keepalive=true
plugins=proxy,admin
log-level=info
log-file=/usr/local/mysql-proxy/log/mysql-proxy.log
proxy-address=172.25.30.8:3306
proxy-backend-address=172.25.30.6:3306
proxy-read-only-backend-addresses=172.25.30.7:3306
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
admin-address=172.25.30.8:4041
admin-username=admin
admin-password=westos
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
[root@mysql3 mysql-proxy]# bin/mysql-proxy --help


[root@mysql3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 
2017-10-21 10:56:31: (critical) mysql-proxy-cli.c:326: loading config from '/usr/local/mysql-proxy/conf/mysql-proxy.conf' failed: permissions of /usr/local/mysql-proxy/conf/mysql-proxy.conf aren't secure (0660 or stricter required)
2017-10-21 10:56:31: (message) Initiating shutdown, requested from mysql-proxy-cli.c:328
2017-10-21 10:56:31: (message) shutting down normally, exit code is: 1
[root@mysql3 conf]# chmod 600 /usr/local/mysql-proxy/conf/mysql-proxy.conf 
[root@mysql3 conf]# /usr/local/mysql-proxy/bin/mysql-proxy  --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf 
[root@mysql3 conf]# netstat -antlp
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address               Foreign Address             State       PID/Program name   
tcp        0      0 0.0.0.0:22                  0.0.0.0:*                   LISTEN      880/sshd            
tcp        0      0 127.0.0.1:25                0.0.0.0:*                   LISTEN      956/master          
tcp        0      0 172.25.30.8:4041            0.0.0.0:*                   LISTEN      1479/mysql-proxy    
tcp        0      0 172.25.30.8:3306            0.0.0.0:*                   LISTEN      1479/mysql-proxy    
tcp        0      0 172.25.30.8:22              172.25.30.250:35802         ESTABLISHED 1341/sshd           
tcp        0      0 172.25.30.8:22              172.25.30.250:38124         ESTABLISHED 1388/sshd           
tcp        0      0 :::22                       :::*                        LISTEN      880/sshd            
tcp        0      0 ::1:25                      :::*                        LISTEN      956/master          








#物理机

[root@foundation30 ~]# mysql -u wxh -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
[root@foundation30 ~]# mysql -h172.25.30.6 -u wxh -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test               |
+--------------------+
2 rows in set (0.01 sec)

MySQL [(none)]> use test
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 [test]> select * from usertb;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.00 sec)

MySQL [test]> 



[root@mysql1 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1721 mysql   33u  IPv6   9412      0t0  TCP *:mysql (LISTEN)
mysqld  1721 mysql   49u  IPv6   9423      0t0  TCP 172.25.30.6:mysql->172.25.30.7:43508 (ESTABLISHED)
mysqld  1721 mysql   51u  IPv6  10095      0t0  TCP 172.25.30.6:mysql->172.25.30.250:49588 (ESTABLISHED)
[root@mysql1 ~]# 






主从复制要求主从的配置文件一致




MHA实现高可用

先做mysql主从同步

[root@mysql1 ~]# ls
redis-4.0.1.tar.gz
[root@mysql1 ~]# tar zxf redis-4.0.1.tar.gz
[root@mysql1 ~]# ls
redis-4.0.1 redis-4.0.1.tar.gz
[root@mysql1 ~]# cd redis-4.0.1
[root@mysql1 redis-4.0.1]# ls
00-RELEASENOTES COPYING Makefile redis.conf runtest-sentinel tests
BUGS deps MANIFESTO runtest sentinel.conf utils
CONTRIBUTING INSTALL README.md runtest-cluster src
[root@mysql1 redis-4.0.1]# make
[root@mysql1 redis-4.0.1]# make install
[root@mysql1 redis-4.0.1]# cd utils/
[root@mysql1 utils]# ./install_server.sh
[root@mysql1 utils]# redis-cli


南继东的

mysql高可用:MHA
环境:Red Hat Enterprise Linux Server release 6.5 (Santiago)
MHA manager:    base_08:172.25.77.8
master      base_09:172.25.77.9
slave       base_10:172.25.77.10
slave       base_11:172.25.77.11
首先配置好主从复制(这里我们采用mysql-5.7.19的基于GTID的主从复制):
如果之前装过mysql,mysql-server,yum remove mysql-server mysql(先卸载掉)
安装包:
MHA:
master_ip_failover
master_ip_online_change
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm
MYSQL:
mysql-community-client-5.7.19-1.el6.x86_64.rpm
mysql-community-common-5.7.19-1.el6.x86_64.rpm
mysql-community-libs-5.7.19-1.el6.x86_64.rpm
mysql-community-libs-compat-5.7.19-1.el6.x86_64.rpm
mysql-community-server-5.7.19-1.el6.x86_64.rpm

配置master:
vim /etc/my.cnf
添加:
server-id=1
log_bin=binlog
gtid_mode=ON
enforce-gtid-consistency=true
[root@base_09 ~]# /etc/init.d/mysqld start

2017-10-21T06:38:29.052992Z 1 [Note] A temporary password is generated for root@localhost: 3K=fshf)*fO:
[root@base_09 ~]# mysql_secure_installation 初始化数据库,设置新密码
mysql> grant replication slave on *.* to red@'%' identified by 'Nanjidong@195413';
Query OK, 0 rows affected, 1 warning (0.21 sec)
mysql> show master status;
+---------------+----------+--------------+------------------+------------------------------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+---------------+----------+--------------+------------------+------------------------------------------+
| binlog.000002 |      680 |              |                  | 72c92b33-b62a-11e7-b896-525400e99f9c:1-2 |
+---------------+----------+--------------+------------------+------------------------------------------+
base_10:
mysql> change master to master_host='172.25.77.9',master_user='red',master_password='Nanjidong@195413',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (1.31 sec)
mysql> start slave;
Query OK, 0 rows affected (0.10 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.77.9
                  Master_User: red
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 680
               Relay_Log_File: base_10-relay-bin.000002
                Relay_Log_Pos: 887
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
base_11:
mysql> change master to master_host='172.25.77.9',master_user='red',master_password='Nanjidong@195413',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (1.19 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.77.9
                  Master_User: red
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 680
               Relay_Log_File: base_11-relay-bin.000002
                Relay_Log_Pos: 887
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
OK主从复制成功!!!
base_08:mha-manager节点配置:
yum install -y 

mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm

[root@base_08 ~]# rpm -ql mha4mysql-manager
/usr/bin/masterha_check_repl
/usr/bin/masterha_check_ssh
/usr/bin/masterha_check_status
/usr/bin/masterha_conf_host
/usr/bin/masterha_manager
/usr/bin/masterha_master_monitor
/usr/bin/masterha_master_switch
/usr/bin/masterha_secondary_check
/usr/bin/masterha_stop
......
[root@base_08 ~]# rpm -ql mha4mysql-node
/usr/bin/apply_diff_relay_logs
/usr/bin/filter_mysqlbinlog
/usr/bin/purge_relay_logs
/usr/bin/save_binary_logs
.......
[root@base_08 ~]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
b2:b8:e0:0d:0c:19:89:78:3a:27:0e:fb:04:a8:f4:d7 root@base_08
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
|o.               |
|= .              |
|.=               |
|X..   . S        |
|=O.  ..o         |
|o.=....E         |
| + +..           |
|  o o            |
+-----------------+
[root@base_08 .ssh]# ssh-copy-id base_08
Now try logging into the machine, with "ssh 'base_08'", and check in:

  .ssh/authorized_keys

to make sure we haven't added extra keys that you weren't expecting.

[root@base_08 ~]# scp -r .ssh/ base_11:

[root@base_08 ~]# ssh base_09
Last login: Sat Oct 21 09:05:35 2017 from 172.25.77.250
[root@base_09 ~]# logout
Connection to base_09 closed.
[root@base_08 ~]# ssh base_10
Last login: Sat Oct 21 09:16:23 2017 from 172.25.77.250
[root@base_10 ~]# logout
Connection to base_10 closed.
[root@base_08 ~]# ssh base_11
Last login: Sat Oct 21 12:56:53 2017 from 172.25.77.250
[root@base_11 ~]# logout
Connection to base_11 closed.

[root@base_08 mha]# mkdir conf data log
[root@base_08 mha]# ls
conf  data  log
[root@base_08 mha]# vim app.conf
[server default]
manager_workdir=/usr/local/mha/data
manager_log=/usr/local/mha/log/mha.log
master_binlog_dir=/var/lib/mysql
#master_ip_failover_script= /usr/local/bin/master_ip_failover 
#master_ip_online_change_script= /usr/local/bin/master_ip_online_change
user=root
password=Nanjidong@195413
ping_interval=1
remote_workdir=/tmp
repl_user=red
repl_password=Nanjidong@195413
ssh_user=root

[server1]
hostname=172.25.77.9
port=3306

[server2]
hostname=172.25.77.10
port=3306

candidate_master=1
check_repl_delay=0

[server3]
hostname=172.25.77.11
port=3306

[root@base_08 mha]# ls
app.conf  conf  data  log
[root@base_08 mha]# mv app.conf conf/
[root@base_08 mha]# ls
conf  data  log
[root@base_08 mha]# masterha_check_ssh --conf=/usr/local/mha/conf/app.conf 
Sat Oct 21 15:43:15 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Oct 21 15:43:15 2017 - [info] Reading application default configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 15:43:15 2017 - [info] Reading server configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 15:43:15 2017 - [info] Starting SSH connection tests..
Sat Oct 21 15:43:15 2017 - [debug] 
Sat Oct 21 15:43:15 2017 - [debug]  Connecting via SSH from root@172.25.77.9(172.25.77.9:22) to root@172.25.77.10(172.25.77.10:22)..
Sat Oct 21 15:43:15 2017 - [debug]   ok.
Sat Oct 21 15:43:15 2017 - [debug]  Connecting via SSH from root@172.25.77.9(172.25.77.9:22) to root@172.25.77.11(172.25.77.11:22)..
Sat Oct 21 15:43:15 2017 - [debug]   ok.
Sat Oct 21 15:43:16 2017 - [debug] 
Sat Oct 21 15:43:15 2017 - [debug]  Connecting via SSH from root@172.25.77.10(172.25.77.10:22) to root@172.25.77.9(172.25.77.9:22)..
Sat Oct 21 15:43:15 2017 - [debug]   ok.
Sat Oct 21 15:43:15 2017 - [debug]  Connecting via SSH from root@172.25.77.10(172.25.77.10:22) to root@172.25.77.11(172.25.77.11:22)..
Sat Oct 21 15:43:15 2017 - [debug]   ok.
Sat Oct 21 15:43:16 2017 - [debug] 
Sat Oct 21 15:43:16 2017 - [debug]  Connecting via SSH from root@172.25.77.11(172.25.77.11:22) to root@172.25.77.9(172.25.77.9:22)..
Sat Oct 21 15:43:16 2017 - [debug]   ok.
Sat Oct 21 15:43:16 2017 - [debug]  Connecting via SSH from root@172.25.77.11(172.25.77.11:22) to root@172.25.77.10(172.25.77.10:22)..
Sat Oct 21 15:43:16 2017 - [debug]   ok.
Sat Oct 21 15:43:16 2017 - [info] All SSH connection tests passed successfully.
[root@base_08 mha]# masterha_check_repl --conf=/usr/local/mha/conf/app.conf 
Sat Oct 21 15:46:25 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Oct 21 15:46:25 2017 - [info] Reading application default configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 15:46:25 2017 - [info] Reading server configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 15:46:25 2017 - [info] MHA::MasterMonitor version 0.56.
Sat Oct 21 15:46:25 2017 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.25.77.10(172.25.77.10:3306) :1045:Access denied for user 'root'@'base_08' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
 at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297
Sat Oct 21 15:46:25 2017 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.25.77.11(172.25.77.11:3306) :1045:Access denied for user 'root'@'base_08' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
 at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297
Sat Oct 21 15:46:25 2017 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln301] Got MySQL error when connecting 172.25.77.9(172.25.77.9:3306) :1045:Access denied for user 'root'@'base_08' (using password: YES), but this is not a MySQL crash. Check MySQL server settings.
 at /usr/share/perl5/vendor_perl/MHA/ServerManager.pm line 297
Sat Oct 21 15:46:25 2017 - [error][/usr/share/perl5/vendor_perl/MHA/ServerManager.pm, ln309] Got fatal error, stopping operations
Sat Oct 21 15:46:25 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln424] Error happened on checking configurations.  at /usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm line 326
Sat Oct 21 15:46:25 2017 - [error][/usr/share/perl5/vendor_perl/MHA/MasterMonitor.pm, ln523] Error happened on monitoring servers.
Sat Oct 21 15:46:25 2017 - [info] Got exit code 1 (Not master dead).

MySQL Replication Health is NOT OK!
显示没有权限:
登录到master:
mysql> grant all on *.* to root@'%' identified by 'Nanjidong@195413';
Query OK, 0 rows affected, 1 warning (0.22 sec)
[root@base_08 mha]# masterha_check_repl --conf=/usr/local/mha/conf/app.conf 
Sat Oct 21 15:49:54 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Oct 21 15:49:54 2017 - [info] Reading application default configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 15:49:54 2017 - [info] Reading server configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 15:49:54 2017 - [info] MHA::MasterMonitor version 0.56.
Sat Oct 21 15:49:54 2017 - [info] GTID failover mode = 1
Sat Oct 21 15:49:54 2017 - [info] Dead Servers:
Sat Oct 21 15:49:54 2017 - [info] Alive Servers:
Sat Oct 21 15:49:54 2017 - [info]   172.25.77.9(172.25.77.9:3306)
Sat Oct 21 15:49:54 2017 - [info]   172.25.77.10(172.25.77.10:3306)
Sat Oct 21 15:49:54 2017 - [info]   172.25.77.11(172.25.77.11:3306)
Sat Oct 21 15:49:54 2017 - [info] Alive Slaves:
Sat Oct 21 15:49:54 2017 - [info]   172.25.77.10(172.25.77.10:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 15:49:54 2017 - [info]     GTID ON
Sat Oct 21 15:49:54 2017 - [info]     Replicating from 172.25.77.9(172.25.77.9:3306)
Sat Oct 21 15:49:54 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Oct 21 15:49:54 2017 - [info]   172.25.77.11(172.25.77.11:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 15:49:54 2017 - [info]     GTID ON
Sat Oct 21 15:49:54 2017 - [info]     Replicating from 172.25.77.9(172.25.77.9:3306)
Sat Oct 21 15:49:54 2017 - [info] Current Alive Master: 172.25.77.9(172.25.77.9:3306)
Sat Oct 21 15:49:54 2017 - [info] Checking slave configurations..
Sat Oct 21 15:49:54 2017 - [info]  read_only=1 is not set on slave 172.25.77.10(172.25.77.10:3306).
Sat Oct 21 15:49:54 2017 - [info]  read_only=1 is not set on slave 172.25.77.11(172.25.77.11:3306).
Sat Oct 21 15:49:54 2017 - [info] Checking replication filtering settings..
Sat Oct 21 15:49:54 2017 - [info]  binlog_do_db= , binlog_ignore_db= 
Sat Oct 21 15:49:54 2017 - [info]  Replication filtering check ok.
Sat Oct 21 15:49:54 2017 - [info] GTID (with auto-pos) is supported. Skipping all SSH and Node package checking.
Sat Oct 21 15:49:54 2017 - [info] Checking SSH publickey authentication settings on the current master..
Sat Oct 21 15:49:54 2017 - [info] HealthCheck: SSH to 172.25.77.9 is reachable.
Sat Oct 21 15:49:54 2017 - [info] 
172.25.77.9(172.25.77.9:3306) (current master)
 +--172.25.77.10(172.25.77.10:3306)
 +--172.25.77.11(172.25.77.11:3306)

Sat Oct 21 15:49:54 2017 - [info] Checking replication health on 172.25.77.10..
Sat Oct 21 15:49:54 2017 - [info]  ok.
Sat Oct 21 15:49:54 2017 - [info] Checking replication health on 172.25.77.11..
Sat Oct 21 15:49:54 2017 - [info]  ok.
Sat Oct 21 15:49:54 2017 - [warning] master_ip_failover_script is not defined.
Sat Oct 21 15:49:54 2017 - [warning] shutdown_script is not defined.
Sat Oct 21 15:49:54 2017 - [info] Got exit code 0 (Not master dead).

MySQL Replication Health is OK.
此时主从复制检查成功!!!
[root@base_08 mha]# masterha_manager --conf=/usr/local/mha/conf/app.conf &
[1] 1227
开启监控:
 1227 pts/0    S      0:00 perl /usr/bin/masterha_manager --conf=/usr/local/mha/conf/app.conf
 1252 pts/0    R+     0:00 ps ax
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.77.9
                  Master_User: red
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 960
               Relay_Log_File: base_10-relay-bin.000002
                Relay_Log_Pos: 1167
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
可以看出此时主还是base_09:
 2529 pts/0    S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/li
 2783 pts/0    Sl     0:02 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=
 2915 ?        S      0:00 pickup -l -t fifo -u
 2928 pts/0    R+     0:00 ps ax
[root@base_09 ~]# kill -9 2529
[root@base_09 ~]# kill -9 2783172.25.77.11上看:发生切换了!!!
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.77.10
                  Master_User: red
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 398
               Relay_Log_File: base_11-relay-bin.000002
                Relay_Log_Pos: 605
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@base_09 ~]# /etc/init.d/mysqld start
将挂掉的master重新指向新的master:base_10:172.25.77.10
mysql> change master to master_host='172.25.77.10',master_user='red',master_password='Nanjidong@195413',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (1.15 sec)

mysql> start slave ;
Query OK, 0 rows affected (0.10 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.77.10
                  Master_User: red
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 398
               Relay_Log_File: base_09-relay-bin.000002
                Relay_Log_Pos: 605
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
ok!!!
接下来使用脚本切换哦!!!
[root@base_08 data]# ls
app.failover.complete
[root@base_08 data]# rm -fr app.failover.complete 
进行手动切换命令如下:
[root@base_08 data]#  masterha_master_switch --conf=/usr/local/mha/conf/app.conf --master_state=alive --new_master_host=172.25.77.9 --new_master_port=3306 --orig_master_is_new_slave
Sat Oct 21 16:10:23 2017 - [info] MHA::MasterRotate version 0.56.
Sat Oct 21 16:10:23 2017 - [info] Starting online master switch..
Sat Oct 21 16:10:23 2017 - [info] 
Sat Oct 21 16:10:23 2017 - [info] * Phase 1: Configuration Check Phase..
Sat Oct 21 16:10:23 2017 - [info] 
Sat Oct 21 16:10:23 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Oct 21 16:10:23 2017 - [info] Reading application default configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 16:10:23 2017 - [info] Reading server configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 16:10:23 2017 - [info] GTID failover mode = 1
Sat Oct 21 16:10:23 2017 - [info] Current Alive Master: 172.25.77.10(172.25.77.10:3306)
Sat Oct 21 16:10:23 2017 - [info] Alive Slaves:
Sat Oct 21 16:10:23 2017 - [info]   172.25.77.9(172.25.77.9:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 16:10:23 2017 - [info]     GTID ON
Sat Oct 21 16:10:23 2017 - [info]     Replicating from 172.25.77.10(172.25.77.10:3306)
Sat Oct 21 16:10:23 2017 - [info]   172.25.77.11(172.25.77.11:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 16:10:23 2017 - [info]     GTID ON
Sat Oct 21 16:10:23 2017 - [info]     Replicating from 172.25.77.10(172.25.77.10:3306)

It is better to execute FLUSH NO_WRITE_TO_BINLOG TABLES on the master before switching. Is it ok to execute on 172.25.77.10(172.25.77.10:3306)? (YES/no): YES
Sat Oct 21 16:10:37 2017 - [info] Executing FLUSH NO_WRITE_TO_BINLOG TABLES. This may take long time..
Sat Oct 21 16:10:37 2017 - [info]  ok.
Sat Oct 21 16:10:37 2017 - [info] Checking MHA is not monitoring or doing failover..
Sat Oct 21 16:10:37 2017 - [info] Checking replication health on 172.25.77.9..
Sat Oct 21 16:10:37 2017 - [info]  ok.
Sat Oct 21 16:10:37 2017 - [info] Checking replication health on 172.25.77.11..
Sat Oct 21 16:10:37 2017 - [info]  ok.
Sat Oct 21 16:10:37 2017 - [info] 172.25.77.9 can be new master.
Sat Oct 21 16:10:37 2017 - [info] 
From:
172.25.77.10(172.25.77.10:3306) (current master)
 +--172.25.77.9(172.25.77.9:3306)
 +--172.25.77.11(172.25.77.11:3306)

To:
172.25.77.9(172.25.77.9:3306) (new master)
 +--172.25.77.11(172.25.77.11:3306)
 +--172.25.77.10(172.25.77.10:3306)

Starting master switch from 172.25.77.10(172.25.77.10:3306) to 172.25.77.9(172.25.77.9:3306)? (yes/NO): YES
Sat Oct 21 16:10:39 2017 - [info] Checking whether 172.25.77.9(172.25.77.9:3306) is ok for the new master..
Sat Oct 21 16:10:39 2017 - [info]  ok.
Sat Oct 21 16:10:39 2017 - [info] 172.25.77.10(172.25.77.10:3306): SHOW SLAVE STATUS returned empty result. To check replication filtering rules, temporarily executing CHANGE MASTER to a dummy host.
Sat Oct 21 16:10:40 2017 - [info] 172.25.77.10(172.25.77.10:3306): Resetting slave pointing to the dummy host.
Sat Oct 21 16:10:41 2017 - [info] ** Phase 1: Configuration Check Phase completed.
Sat Oct 21 16:10:41 2017 - [info] 
Sat Oct 21 16:10:41 2017 - [info] * Phase 2: Rejecting updates Phase..
Sat Oct 21 16:10:41 2017 - [info] 
master_ip_online_change_script is not defined. If you do not disable writes on the current master manually, applications keep writing on the current master. Is it ok to proceed? (yes/NO): yes
Sat Oct 21 16:10:51 2017 - [info] Locking all tables on the orig master to reject updates from everybody (including root):
Sat Oct 21 16:10:51 2017 - [info] Executing FLUSH TABLES WITH READ LOCK..
Sat Oct 21 16:10:51 2017 - [info]  ok.
Sat Oct 21 16:10:51 2017 - [info] Orig master binlog:pos is binlog.000002:398.
Sat Oct 21 16:10:51 2017 - [info]  Waiting to execute all relay logs on 172.25.77.9(172.25.77.9:3306)..
Sat Oct 21 16:10:51 2017 - [info]  master_pos_wait(binlog.000002:398) completed on 172.25.77.9(172.25.77.9:3306). Executed 0 events.
Sat Oct 21 16:10:51 2017 - [info]   done.
Sat Oct 21 16:10:52 2017 - [info] Getting new master's binlog name and position..
Sat Oct 21 16:10:52 2017 - [info]  binlog.000003:194
Sat Oct 21 16:10:52 2017 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.77.9', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='red', MASTER_PASSWORD='xxx';
Sat Oct 21 16:10:52 2017 - [info] 
Sat Oct 21 16:10:52 2017 - [info] * Switching slaves in parallel..
Sat Oct 21 16:10:52 2017 - [info] 
Sat Oct 21 16:10:52 2017 - [info] -- Slave switch on host 172.25.77.11(172.25.77.11:3306) started, pid: 1491
Sat Oct 21 16:10:52 2017 - [info] 
Sat Oct 21 16:10:53 2017 - [info] Log messages from 172.25.77.11 ...
Sat Oct 21 16:10:53 2017 - [info] 
Sat Oct 21 16:10:52 2017 - [info]  Waiting to execute all relay logs on 172.25.77.11(172.25.77.11:3306)..
Sat Oct 21 16:10:52 2017 - [info]  master_pos_wait(binlog.000002:398) completed on 172.25.77.11(172.25.77.11:3306). Executed 0 events.
Sat Oct 21 16:10:52 2017 - [info]   done.
Sat Oct 21 16:10:52 2017 - [info]  Resetting slave 172.25.77.11(172.25.77.11:3306) and starting replication from the new master 172.25.77.9(172.25.77.9:3306)..
Sat Oct 21 16:10:53 2017 - [info]  Executed CHANGE MASTER.
Sat Oct 21 16:10:53 2017 - [info]  Slave started.
Sat Oct 21 16:10:53 2017 - [info] End of log messages from 172.25.77.11 ...
Sat Oct 21 16:10:53 2017 - [info] 
Sat Oct 21 16:10:53 2017 - [info] -- Slave switch on host 172.25.77.11(172.25.77.11:3306) succeeded.
Sat Oct 21 16:10:53 2017 - [info] Unlocking all tables on the orig master:
Sat Oct 21 16:10:53 2017 - [info] Executing UNLOCK TABLES..
Sat Oct 21 16:10:53 2017 - [info]  ok.
Sat Oct 21 16:10:53 2017 - [info] Starting orig master as a new slave..
Sat Oct 21 16:10:53 2017 - [info]  Resetting slave 172.25.77.10(172.25.77.10:3306) and starting replication from the new master 172.25.77.9(172.25.77.9:3306)..
Sat Oct 21 16:10:55 2017 - [info]  Executed CHANGE MASTER.
Sat Oct 21 16:10:55 2017 - [info]  Slave started.
Sat Oct 21 16:10:55 2017 - [info] All new slave servers switched successfully.
Sat Oct 21 16:10:55 2017 - [info] 
Sat Oct 21 16:10:55 2017 - [info] * Phase 5: New master cleanup phase..
Sat Oct 21 16:10:55 2017 - [info] 
Sat Oct 21 16:10:56 2017 - [info]  172.25.77.9: Resetting slave info succeeded.
Sat Oct 21 16:10:56 2017 - [info] Switching master to 172.25.77.9(172.25.77.9:3306) completed successfully.
登录base_11:查看
mysql> mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.77.9
                  Master_User: red
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000003
          Read_Master_Log_Pos: 194
               Relay_Log_File: base_11-relay-bin.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: binlog.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
使用脚本:
[root@base_08 ~]# cp master_ip_failover master_ip_online_change /usr/local/bin/
[root@base_08 conf]# vim app.conf
添加脚本:
master_ip_failover_script= /usr/local/bin/master_ip_failover 
master_ip_online_change_script= /usr/local/bin/master_ip_online_change
[root@base_08 bin]# chmod +x *
[root@base_08 bin]# ll
total 8
-rwxr-xr-x 1 root root 2167 Oct 21 16:15 master_ip_failover
-rwxr-xr-x 1 root root 3842 Oct 21 16:15 master_ip_online_change
改变master_ip_failover,master_ip_online_change中的VIP:
my $vip = '172.25.77.100/24';

在master上添加VIP:
[root@base_09 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:e9:9f:9c brd ff:ff:ff:ff:ff:ff
    inet 172.25.77.9/24 brd 172.25.77.255 scope global eth0
    inet6 fe80::5054:ff:fee9:9f9c/64 scope link 
       valid_lft forever preferred_lft forever
[root@base_09 ~]# ifconfig eth0:1 172.25.77.100/24
[root@base_09 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:e9:9f:9c brd ff:ff:ff:ff:ff:ff
    inet 172.25.77.9/24 brd 172.25.77.255 scope global eth0
    inet 172.25.77.100/24 brd 172.25.77.255 scope global secondary eth0:1
    inet6 fe80::5054:ff:fee9:9f9c/64 scope link 
       valid_lft forever preferred_lft forever
[root@base_09 ~]# /etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
[root@base_08 bin]# masterha_master_switch --master_state=dead --conf=/usr/local/mha/conf/app.conf --dead_master_host=172.25.77.9 --dead_master_port=3306 --new_master_host=172.25.77.10 --new_master_port=3306 --ignore_last_failover
--dead_master_ip=<dead_master_ip> is not set. Using 172.25.77.9.
Sat Oct 21 16:24:58 2017 - [warning] Global configuration file /etc/masterha_default.cnf not found. Skipping.
Sat Oct 21 16:24:58 2017 - [info] Reading application default configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 16:24:58 2017 - [info] Reading server configuration from /usr/local/mha/conf/app.conf..
Sat Oct 21 16:24:58 2017 - [info] MHA::MasterFailover version 0.56.
Sat Oct 21 16:24:58 2017 - [info] Starting master failover.
Sat Oct 21 16:24:58 2017 - [info] 
Sat Oct 21 16:24:58 2017 - [info] * Phase 1: Configuration Check Phase..
Sat Oct 21 16:24:58 2017 - [info] 
Sat Oct 21 16:24:58 2017 - [info] GTID failover mode = 1
Sat Oct 21 16:24:58 2017 - [info] Dead Servers:
Sat Oct 21 16:24:58 2017 - [info]   172.25.77.9(172.25.77.9:3306)
Sat Oct 21 16:24:58 2017 - [info] Checking master reachability via MySQL(double check)...
Sat Oct 21 16:24:58 2017 - [info]  ok.
Sat Oct 21 16:24:58 2017 - [info] Alive Servers:
Sat Oct 21 16:24:58 2017 - [info]   172.25.77.10(172.25.77.10:3306)
Sat Oct 21 16:24:58 2017 - [info]   172.25.77.11(172.25.77.11:3306)
Sat Oct 21 16:24:58 2017 - [info] Alive Slaves:
Sat Oct 21 16:24:58 2017 - [info]   172.25.77.10(172.25.77.10:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 16:24:58 2017 - [info]     GTID ON
Sat Oct 21 16:24:58 2017 - [info]     Replicating from 172.25.77.9(172.25.77.9:3306)
Sat Oct 21 16:24:58 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Oct 21 16:24:58 2017 - [info]   172.25.77.11(172.25.77.11:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 16:24:58 2017 - [info]     GTID ON
Sat Oct 21 16:24:58 2017 - [info]     Replicating from 172.25.77.9(172.25.77.9:3306)
Master 172.25.77.9(172.25.77.9:3306) is dead. Proceed? (yes/NO): yes
Sat Oct 21 16:25:02 2017 - [info] Starting GTID based failover.
Sat Oct 21 16:25:02 2017 - [info] 
Sat Oct 21 16:25:02 2017 - [info] ** Phase 1: Configuration Check Phase completed.
Sat Oct 21 16:25:02 2017 - [info] 
Sat Oct 21 16:25:02 2017 - [info] * Phase 2: Dead Master Shutdown Phase..
Sat Oct 21 16:25:02 2017 - [info] 
Sat Oct 21 16:25:02 2017 - [info] HealthCheck: SSH to 172.25.77.9 is reachable.
Sat Oct 21 16:25:03 2017 - [info] Forcing shutdown so that applications never connect to the current master..
Sat Oct 21 16:25:03 2017 - [info] Executing master IP deactivation script:
Sat Oct 21 16:25:03 2017 - [info]   /usr/local/bin/master_ip_failover --orig_master_host=172.25.77.9 --orig_master_ip=172.25.77.9 --orig_master_port=3306 --command=stopssh --ssh_user=root  


IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.25.77.100/24===

Disabling the VIP on old master: 172.25.77.9 
Sat Oct 21 16:25:03 2017 - [info]  done.
Sat Oct 21 16:25:03 2017 - [warning] shutdown_script is not set. Skipping explicit shutting down of the dead master.
Sat Oct 21 16:25:03 2017 - [info] * Phase 2: Dead Master Shutdown Phase completed.
Sat Oct 21 16:25:03 2017 - [info] 
Sat Oct 21 16:25:03 2017 - [info] * Phase 3: Master Recovery Phase..
Sat Oct 21 16:25:03 2017 - [info] 
Sat Oct 21 16:25:03 2017 - [info] * Phase 3.1: Getting Latest Slaves Phase..
Sat Oct 21 16:25:03 2017 - [info] 
Sat Oct 21 16:25:03 2017 - [info] The latest binary log file/position on all slaves is binlog.000003:194
Sat Oct 21 16:25:03 2017 - [info] Latest slaves (Slaves that received relay log files to the latest):
Sat Oct 21 16:25:03 2017 - [info]   172.25.77.10(172.25.77.10:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 16:25:03 2017 - [info]     GTID ON
Sat Oct 21 16:25:03 2017 - [info]     Replicating from 172.25.77.9(172.25.77.9:3306)
Sat Oct 21 16:25:03 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Oct 21 16:25:03 2017 - [info]   172.25.77.11(172.25.77.11:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 16:25:03 2017 - [info]     GTID ON
Sat Oct 21 16:25:03 2017 - [info]     Replicating from 172.25.77.9(172.25.77.9:3306)
Sat Oct 21 16:25:03 2017 - [info] The oldest binary log file/position on all slaves is binlog.000003:194
Sat Oct 21 16:25:03 2017 - [info] Oldest slaves:
Sat Oct 21 16:25:03 2017 - [info]   172.25.77.10(172.25.77.10:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 16:25:03 2017 - [info]     GTID ON
Sat Oct 21 16:25:03 2017 - [info]     Replicating from 172.25.77.9(172.25.77.9:3306)
Sat Oct 21 16:25:03 2017 - [info]     Primary candidate for the new Master (candidate_master is set)
Sat Oct 21 16:25:03 2017 - [info]   172.25.77.11(172.25.77.11:3306)  Version=5.7.19-log (oldest major version between slaves) log-bin:enabled
Sat Oct 21 16:25:03 2017 - [info]     GTID ON
Sat Oct 21 16:25:03 2017 - [info]     Replicating from 172.25.77.9(172.25.77.9:3306)
Sat Oct 21 16:25:03 2017 - [info] 
Sat Oct 21 16:25:03 2017 - [info] * Phase 3.3: Determining New Master Phase..
Sat Oct 21 16:25:03 2017 - [info] 
Sat Oct 21 16:25:03 2017 - [info] 172.25.77.10 can be new master.
Sat Oct 21 16:25:03 2017 - [info] New master is 172.25.77.10(172.25.77.10:3306)
Sat Oct 21 16:25:03 2017 - [info] Starting master failover..
Sat Oct 21 16:25:03 2017 - [info] 
From:
172.25.77.9(172.25.77.9:3306) (current master)
 +--172.25.77.10(172.25.77.10:3306)
 +--172.25.77.11(172.25.77.11:3306)

To:
172.25.77.10(172.25.77.10:3306) (new master)
 +--172.25.77.11(172.25.77.11:3306)

Starting master switch from 172.25.77.9(172.25.77.9:3306) to 172.25.77.10(172.25.77.10:3306)? (yes/NO): yes
Sat Oct 21 16:26:38 2017 - [info] New master decided manually is 172.25.77.10(172.25.77.10:3306)
Sat Oct 21 16:26:38 2017 - [info] 
Sat Oct 21 16:26:38 2017 - [info] * Phase 3.3: New Master Recovery Phase..
Sat Oct 21 16:26:38 2017 - [info] 
Sat Oct 21 16:26:38 2017 - [info]  Waiting all logs to be applied.. 
Sat Oct 21 16:26:38 2017 - [info]   done.
Sat Oct 21 16:26:38 2017 - [info] Getting new master's binlog name and position..
Sat Oct 21 16:26:38 2017 - [info]  binlog.000002:398
Sat Oct 21 16:26:38 2017 - [info]  All other slaves should start replication from here. Statement should be: CHANGE MASTER TO MASTER_HOST='172.25.77.10', MASTER_PORT=3306, MASTER_AUTO_POSITION=1, MASTER_USER='red', MASTER_PASSWORD='xxx';
Sat Oct 21 16:26:38 2017 - [info] Master Recovery succeeded. File:Pos:Exec_Gtid_Set: binlog.000002, 398, 083c1d99-b62b-11e7-9fc3-525400d0d01e:1,
72c92b33-b62a-11e7-b896-525400e99f9c:1-3
Sat Oct 21 16:26:38 2017 - [info] Executing master IP activate script:
Sat Oct 21 16:26:38 2017 - [info]   /usr/local/bin/master_ip_failover --command=start --ssh_user=root --orig_master_host=172.25.77.9 --orig_master_ip=172.25.77.9 --orig_master_port=3306 --new_master_host=172.25.77.10 --new_master_ip=172.25.77.10 --new_master_port=3306 --new_master_user='root' --new_master_password='Nanjidong@195413'  
Unknown option: new_master_user
Unknown option: new_master_password


IN SCRIPT TEST====/sbin/ifconfig eth0:1 down==/sbin/ifconfig eth0:1 172.25.77.100/24===

Enabling the VIP - 172.25.77.100/24 on the new master - 172.25.77.10 
Sat Oct 21 16:26:38 2017 - [info]  OK.
Sat Oct 21 16:26:38 2017 - [info] ** Finished master recovery successfully.
Sat Oct 21 16:26:38 2017 - [info] * Phase 3: Master Recovery Phase completed.
Sat Oct 21 16:26:38 2017 - [info] 
Sat Oct 21 16:26:38 2017 - [info] * Phase 4: Slaves Recovery Phase..
Sat Oct 21 16:26:38 2017 - [info] 
Sat Oct 21 16:26:38 2017 - [info] 
Sat Oct 21 16:26:38 2017 - [info] * Phase 4.1: Starting Slaves in parallel..
Sat Oct 21 16:26:38 2017 - [info] 
Sat Oct 21 16:26:38 2017 - [info] -- Slave recovery on host 172.25.77.11(172.25.77.11:3306) started, pid: 1523. Check tmp log /usr/local/mha/data/172.25.77.11_3306_20171021162458.log if it takes time..
Sat Oct 21 16:26:40 2017 - [info] 
Sat Oct 21 16:26:40 2017 - [info] Log messages from 172.25.77.11 ...
Sat Oct 21 16:26:40 2017 - [info] 
Sat Oct 21 16:26:38 2017 - [info]  Resetting slave 172.25.77.11(172.25.77.11:3306) and starting replication from the new master 172.25.77.10(172.25.77.10:3306)..
Sat Oct 21 16:26:40 2017 - [info]  Executed CHANGE MASTER.
Sat Oct 21 16:26:40 2017 - [info]  Slave started.
Sat Oct 21 16:26:40 2017 - [info]  gtid_wait(083c1d99-b62b-11e7-9fc3-525400d0d01e:1,
72c92b33-b62a-11e7-b896-525400e99f9c:1-3) completed on 172.25.77.11(172.25.77.11:3306). Executed 0 events.
Sat Oct 21 16:26:40 2017 - [info] End of log messages from 172.25.77.11.
Sat Oct 21 16:26:40 2017 - [info] -- Slave on host 172.25.77.11(172.25.77.11:3306) started.
Sat Oct 21 16:26:40 2017 - [info] All new slave servers recovered successfully.
Sat Oct 21 16:26:40 2017 - [info] 
Sat Oct 21 16:26:40 2017 - [info] * Phase 5: New master cleanup phase..
Sat Oct 21 16:26:40 2017 - [info] 
Sat Oct 21 16:26:40 2017 - [info] Resetting slave info on the new master..
Sat Oct 21 16:26:41 2017 - [info]  172.25.77.10: Resetting slave info succeeded.
Sat Oct 21 16:26:41 2017 - [info] Master failover to 172.25.77.10(172.25.77.10:3306) completed successfully.
Sat Oct 21 16:26:41 2017 - [info] 

----- Failover Report -----

app: MySQL Master failover 172.25.77.9(172.25.77.9:3306) to 172.25.77.10(172.25.77.10:3306) succeeded

Master 172.25.77.9(172.25.77.9:3306) is down!

Check MHA Manager logs at base_08 for details.

Started manual(interactive) failover.
Invalidated master IP address on 172.25.77.9(172.25.77.9:3306)
Selected 172.25.77.10(172.25.77.10:3306) as a new master.
172.25.77.10(172.25.77.10:3306): OK: Applying all logs succeeded.
172.25.77.10(172.25.77.10:3306): OK: Activated master IP address.
172.25.77.11(172.25.77.11:3306): OK: Slave started, replicating from 172.25.77.10(172.25.77.10:3306)
172.25.77.10(172.25.77.10:3306): Resetting slave info succeeded.
Master failover to 172.25.77.10(172.25.77.10:3306) completed successfully.
切换成功喽!!!
在base_11查看:
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.77.10
                  Master_User: red
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 398
               Relay_Log_File: base_11-relay-bin.000002
                Relay_Log_Pos: 405
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@base_10 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:d0:d0:1e brd ff:ff:ff:ff:ff:ff
    inet 172.25.77.10/24 brd 172.25.77.255 scope global eth0
    inet 172.25.77.100/24 brd 172.25.77.255 scope global secondary eth0:1
    inet6 fe80::5054:ff:fed0:d01e/64 scope link 
       valid_lft forever preferred_lft forever
VIP成功飘逸!!!

[root@base_09 ~]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
[root@base_09 ~]# mysql -p
手动指向master:
mysql> change master to master_host='172.25.77.10',master_user='red',master_password='Nanjidong@195413',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (1.18 sec)

mysql> start slave;
Query OK, 0 rows affected (0.11 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.77.10
                  Master_User: red
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000002
          Read_Master_Log_Pos: 398
               Relay_Log_File: base_09-relay-bin.000002
                Relay_Log_Pos: 405
        Relay_Master_Log_File: binlog.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

[root@base_08 data]# ls
app.failover.complete
[root@base_08 data]# rm -fr *
vim app.conf
#candidate_master=1
#check_repl_delay=0
[server3]
hostname=172.25.77.11
port=3306
no_master=1
[root@base_08 conf]# nohup masterha_manager --conf=/usr/local/mha/conf/app.conf &
[1] 1533
1533 pts/0    S      0:00 perl /usr/bin/masterha_manager --conf=/usr/local/mha/conf/app.con
1572 pts/0    R+     0:00 ps ax
[root@base_08 conf]# ps ax
 1906 ?        S      0:00 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --socket=/var/lib/my
 2160 ?        Sl     0:03 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr
 2274 ?        Ss     0:00 sshd: root@pts/1 
 2276 pts/1    Ss     0:00 -bash
 2306 ?        S      0:00 pickup -l -t fifo -u
 2355 pts/1    R+     0:00 ps ax
[root@base_10 ~]# kill -9 1906
[root@base_10 ~]# kill -9 2160
[root@base_08 log]# cat mha.log
app: MySQL Master failover 172.25.77.10(172.25.77.10:3306) to 172.25.77.9(172.25.77.9:3306) succeeded

Master 172.25.77.10(172.25.77.10:3306) is down!

Check MHA Manager logs at base_08:/usr/local/mha/log/mha.log for details.

Started automated(non-interactive) failover.
Invalidated master IP address on 172.25.77.10(172.25.77.10:3306)
Selected 172.25.77.9(172.25.77.9:3306) as a new master.
172.25.77.9(172.25.77.9:3306): OK: Applying all logs succeeded.
172.25.77.9(172.25.77.9:3306): OK: Activated master IP address.
172.25.77.11(172.25.77.11:3306): OK: Slave started, replicating from 172.25.77.9(172.25.77.9:3306)
172.25.77.9(172.25.77.9:3306): Resetting slave info succeeded.
Master failover to 172.25.77.9(172.25.77.9:3306) completed successfully.
[root@base_09 ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 16436 qdisc noqueue state UNKNOWN 
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
    inet6 ::1/128 scope host 
       valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
    link/ether 52:54:00:e9:9f:9c brd ff:ff:ff:ff:ff:ff
    inet 172.25.77.9/24 brd 172.25.77.255 scope global eth0
    inet 172.25.77.100/24 brd 172.25.77.255 scope global secondary eth0:1
    inet6 fe80::5054:ff:fee9:9f9c/64 scope link 
       valid_lft forever preferred_lft forever
的却VIP飘逸!!!

[root@base_10 ~]# /etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
[root@base_10 ~]# mysql -p
mysql> change master to master_host='172.25.77.9',master_user='red',master_password='Nanjidong@195413',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (1.14 sec)

mysql> start slave;
Query OK, 0 rows affected (0.09 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.25.77.9
                  Master_User: red
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000004
          Read_Master_Log_Pos: 194
               Relay_Log_File: base_10-relay-bin.000002
                Relay_Log_Pos: 361
        Relay_Master_Log_File: binlog.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
OK大功告成!!!                               

王若晨的

    mysql 的读写分离
——————————
除了做主从复制的虚拟机外,再准备一个虚拟机做调度器,在这里我的数据库服务器是server3(主)和server4,调度器服务器是server5.在上次主从复制的基础上,主从复制的操作可以参照上一篇。
为什么要读写分离?
大型网站为了软解大量的并发访问,除了在网站实现分布式负载均衡,到了数据业务层,数据访问层,如果还是传统的数据结构,或者只是单单靠一台服务器抗,如此多的数据库连接操作,数据库必然崩溃,数据丢失的话,后果更是不堪设想,这时候,我们会考虑如何减少数据库的连接,一方面采用优秀的代码框架,进行代码的优化,采用优秀的数据库缓存技术如:memecached,如果资金足够,必然会想到假设服务器群,来分担主数据库的压力。下面,我们利用mysql主从配置,实现读写分离,减轻数据库压力。
————————————————————————————————————————————————————————————————
确保server3 和 server4 主从复制正常的情况下开始配置。
server5上:
#tar zxf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /usr/local
#cd /usr/local
#ln -s mysql-proxy-0.8.5-linux-el6-x86-64bit/ mysql-proxy
#cd mysql-proxy
#mkdir etc log
#cd etc
#vim mysql-proxy.conf
[mysql-proxy]
user=root   ->>运行mysql-proxy用户
admin-username=admin -》》主从mysql公有的用户
admin-password=westos -》》用户的密码
proxy-address=172.25.41.5:3306 -》》proxy的运行ip和端口
proxy-read-only-backend-addresses=172.25.41.4:3306-》》slave端ip
proxy-backend-addresses=172.25.41.3:3306-》》master端ip
proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua -》》脚本位置
admin-lua-script=/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua
log-file=/usr/local/mysql-proxy/logs/mysql-proxy.log-》》日志位置
log-level=debug
daemon=true-》以守护进程方式运行
keepalive=true-》》崩溃时,尝试重启
:wq
#chmod 660 mysql-proxy.conf

#vim /usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua 
--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 1,
                max_idle_connections = 2,

                is_debug = false
        }
end
:wq
————————————————————————————————————————
将最大最小连接数改成 12,意思就是至少连两次以上,proxy才能建立和slave端的连接
————————————————————————————————————————
启动:
#/usr/local/mysql-proxy/bin/mysql-proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf
#netstat -antpl ->>查看端口
——————————————————————————————————————————

server3上授权:
grant all on test.* to repl@'%' identified by '111@Wangruochen'
其他的ip可以用这个用户,这个密码,对test.*库 做所有操作。
——————————————————————————————————————————
真机上通过 4041 端口连接proxy,用之前配置文件中指定的用户密码。
[root@localhost iso]# mysql -h 172.25.41.5 -P 4041 -u admin -pwestos
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> select * from backends;
+-------------+------------------+-------+------+------+-------------------+
| backend_ndx | address          | state | type | uuid | connected_clients |
+-------------+------------------+-------+------+------+-------------------+
|           1 | 172.25.41.3:3306 | up    | rw   | NULL |                 0 |
|           2 | 172.25.41.4:3306 | up    | ro   | NULL |                 0 |
+-------------+------------------+-------+------+------+-------------------+
2 rows in set (0.00 sec)
可以看到两个数据库服务器。
——————————————————————————————————————————
真机上连接数据库服务器:
用户密码是之前主从复制的用户名密码。
——————————————————————————————————————————
[root@localhost iso]# mysql -h 172.25.41.5 -u repl -p111@Wangruochen

-》》真机通过proxy代理访问到了数据库服务器 34 ,但是这个时候我们不知道proxy到底连接的是哪个服务器,我们可以在3 4 上安装
[root@server3 ~]# yum install -y lsof
[root@server4 ~]# yum install -y lsof
来查看数据流到底去了哪里。第一次连接上proxy时,在 3 上查看。
[root@server3 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1337 mysql   34u  IPv6   8509      0t0  TCP *:mysql (LISTEN)
mysqld  1337 mysql   57u  IPv6   8560      0t0  TCP server3:mysql->server4:33504 (ESTABLISHED)
在 4 上
[root@server4 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1333 mysql   25u  IPv6   8590      0t0  TCP *:mysql (LISTEN)
mysqld  1333 mysql   58u  IPv4   8612      0t0  TCP server4:33504->server3:mysql (ESTABLISHED)
可以看到连接上了 3 ,但是 4 没有连接上

真机上退出数据库,再连接两次
[root@server4 ~]# lsof -i :3306
COMMAND  PID  USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mysqld  1333 mysql   25u  IPv6   8590      0t0  TCP *:mysql (LISTEN)
mysqld  1333 mysql   58u  IPv4   8612      0t0  TCP server4:33504->server3:mysql (ESTABLISHED)
mysqld  1333 mysql   61u  IPv6   9954      0t0  TCP server4:mysql->server5:44781 (ESTABLISHED)
这个时候真正的读写分离才可以开始。

测试:
主上 3 :
mysql> use test;
Database changed
mysql> create table usertb (
    -> username varchar(20) not null,
    -> password varchar(20) not null);
Query OK, 0 rows affected (0.73 sec)

mysql> insert into usertb values ('user1','123'),('user2','456');
Query OK, 2 rows affected (0.35 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from usertb
    -> ;
+----------+----------+
| username | password |
+----------+----------+
| user1    | 123      |
| user2    | 456      |
+----------+----------+
2 rows in set (0.00 sec)
——————————————————————————————————————————————————————
当数据库检测到写操作的时候,交给 3 解决,当数据库检测到读操作时,交给 4 解决,这样我们就实现了读写分离。也就是说,如果 3 挂掉,就不能写了, 4 挂掉 ,就不能读了。
——————————————————————————————————————————————————————








==========================================
MHA高可用
==========================================
在之前的基础上新开一个虚拟机,做调度服务器。在这里我们开启server2
在 2 上:
安装软件
mha4mysql-manager-0.56-0.el6.noarch.rpm
mha4mysql-node-0.56-0.el6.noarch.rpm
perl-Config-Tiny-2.12-7.1.el6.noarch.rpm
perl-Email-Date-Format-1.002-5.el6.noarch.rpm
perl-Log-Dispatch-2.27-1.el6.noarch.rpm
perl-Mail-Sender-0.8.16-3.el6.noarch.rpm
perl-Mail-Sendmail-0.79-12.el6.noarch.rpm
perl-MIME-Lite-3.027-2.el6.noarch.rpm
perl-MIME-Types-1.28-2.el6.noarch.rpm
perl-Parallel-ForkManager-0.7.9-1.el6.noarch.rpm

#mkdir /etc/masterha
#cd /etc/masterha
#vim app.cnf
[server default]
manager_workdir=/etc/masterha
manager_log=/etc/masterha/mha.log
master_binlog_dir=/var/lib/mysql
#master_ip_failover_script= /usr/local/bin/master_ip_failover    
#master_ip_online_change_script= /usr/local/bin/master_ip_online_change  
password=111@Wangruochen
user=root  -》》连接数据库时的用户
ping_interval=1
remote_workdir=/tmp
repl_password=111@Wangruochen
repl_user=repl   -》》连接数据库后使用的用户
#report_script=/usr/local/send_report    
#secondary_check_script= /usr/local/bin/masterha_secondary_check -s server03 -s server02 --user=root --master_host=server02 --master_ip=192.168.0.50 --master_port=3306               
#shutdown_script=""      
ssh_user=root    连接的时候使用的用户

[server1]
hostname=172.25.41.3
port=3306

[server2]
hostname=172.25.41.4
port=3306
candidate_master=1
check_repl_delay=0


[server3]
hostname=172.25.41.5
port=3306

:wq

生成加密key
#ssh-keygen  一路回车
#cd /root/.ssh
#ssh-copy-id 172.25.41.3
#ssh-copy-id 172.25.41.4
#ssh-copy-id 172.25.41.5
#scp -p id_rsa server3:~/.ssh/
#scp -p id_rsa server4:~/.ssh/

________________________________
在三个数据库服务器上修改文件:

修改/etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

symbolic-links=0

server_id=3
gtid_mode=ON
enforce_gtid_consistency=true
#master_info_repository=TABLE
#relay_log_info_repository=TABLE
#binlog_checksum=NONE
#log_slave_updates=ON
log_bin=binlog
#binlog_format=ROW

#transaction_write_set_extraction=XXHASH64
#loose-group_replication_group_name="aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa"
#loose-group_replication_start_on_boot=off
#loose-group_replication_local_address= "172.25.41.5:24901"
#loose-group_replication_group_seeds= "172.25.41.3:24901,172.25.41.4:24901,172.25.41.5:24901"
#loose-group_replication_bootstrap_group= off
#loose-group_replication_single_primary_mode=off
#loose-group_replication_enforce_update_everywhere_checks=on

[mysql_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
:wq
将组复制的配置注释掉。
______________________________________________
在其他的三个数据库服务器上安装
mha4mysql-node-0.56-0.el6.noarch.rpm
____________________________________________
在 3 上授权:(如果不授权,那 3 上的数据库是不允许其他服务器以root身份连接它)
mysql>grant all on *.* to root@'172.25.41.%' identified by '111@Wangruochen';
____________________________________________
在 2 上检测:
#masterha_check_ssh --conf=/etc/masterha/app.cnf
#masterha_check_repl --conf=/etc/masterha/app.cnf
____________________________________________
在 2 上:开启MHA Manager监控
# nohup masterha_manager --conf=/etc/masterha/app.cnf &
————————————————————————————————————
 3 上:
#ps ax
#kill -9 XXXX
#kill -9 XXXX ->>kill 掉 mysql-safe 和 mysql的进程
再看看其他服务器,发现主已经切换到了 4 上。
当原来的主服务器再启动时,可以用如下命令让他变成从
mysql>change master to master_host='172.25.41.3',master_user='repl',master_password='111@Wangruochen',master_auto_position=1;
mysql> start slave
==============================================
=============================================
当切换不成功的时候,将app.failover.complete and nohup.out 删除,重新尝试。

==============================================
加上vip
2 上:
#cd
#cp master_ip_failover master_ip_online_change /usr/local/bin
#vim /etc/masterha/app.cnf
master_ip_failover_script= /usr/local/bin/master_ip_failover    
master_ip_online_change_script= /usr/local/bin/master_ip_online_change 
:wq
#cd /usr/local/bin
#ls
#chmod +x *
#vim master_ip_failover 
修改vip为 172.25.41.100
#vim master_ip_online_change
修改vip
——————————————————————————————————————————
3 上:
#ifconfig eth0:1 172.25.41.100/24
#ip addr
#/etc/init.d/mysqld stop
__________________________
2 上:

#masterha_master_switch --master_state=dead --conf=/etc/masterha/app.cnf --dead_master_host=172.25.41.100 --dead_master_port=3306 --new_master_host=172.25.41.5 --new_master_port=3306 --ignore_last_failover

成功之后vip就切换到 5 上了。
——————————————————————————————————————————
==================================================
==================================================
===================================================
在 3 4 上都做
[root@server3 ~]# tar zxf redis-4.0.1.tar.gz 
[root@server3 ~]# cd redis-4.0.1
[root@server3 redis-4.0.1]# make
[root@server3 redis-4.0.1]#make install
[root@server3 redis-4.0.1]# cd utils/
[root@server3 utils]# ./install_server.sh
Welcome to the redis service installer
This script will help you easily set up a running redis server

Please select the redis port for this instance: [6379] 
Selecting default: 6379
Please select the redis config file name [/etc/redis/6379.conf] 
Selected default - /etc/redis/6379.conf
Please select the redis log file name [/var/log/redis_6379.log] 
Selected default - /var/log/redis_6379.log
Please select the data directory for this instance [/var/lib/redis/6379] 
Selected default - /var/lib/redis/6379
Please select the redis executable path [/usr/local/bin/redis-server] 
Selected config:
Port           : 6379
Config file    : /etc/redis/6379.conf
Log file       : /var/log/redis_6379.log
Data dir       : /var/lib/redis/6379
Executable     : /usr/local/bin/redis-server
Cli Executable : /usr/local/bin/redis-cli
Is this ok? Then press ENTER to go on or Ctrl-C to abort.
Copied /tmp/6379.conf => /etc/init.d/redis_6379
Installing service...
Successfully added to chkconfig!
Successfully added to runlevels 345!
Starting Redis server...
Installation successful!
[root@server3 utils]# cd /etc/redis/
[root@server3 redis]# vim 6379.conf 
: 70   bind 0.0.0.0
3 上只修改 70 行
:283  slaveof 172.25.41.3 6379
4 上修改之外,还要填加 283行内容。

[root@server3 redis]# /etc/init.d/redis_6379 restart
Stopping ...
Redis stopped
Starting Redis server...
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值