mysql主从架构搭建+并行复制+MHA原理，搭建MHA，mha的手工，故障切换、修复

mysql主从架构搭建

主机环境
关闭防火墙跟selinux
两台版本、安装方式一致的数据库
配置搭建环境脚本《mysql项目需要的主机基础环境设置》

安装
rpm -ivh libev4-4.15-7.1.x86_64.rpm libev-devel-4.15-21.1.x86_64.rpm percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
安装依赖包
yum install perl-DBD-mysql
主从架构+mha软件包

都关闭dns

vim /etc/ssh/sshd_config
#useDNS yes 改为  useDNS no

修改主从的配置文件
主库配置：

binlog_format=row
server-id=3306100
binlog_rows_query_log_events=on
log-bin=server

从库配置：

read_only=1
server-id=3306101
binlog_format=row
binlog_rows_query_log_events=on
log-bin=slave

对主库进行复制

innobackupex --user=root --password=123 /backup/

得到

2018-07-17_14-42-41这个文件

然后应用这个文件

innobackupex --apply-log 2018-07-17_14-42-41/

然后关闭从库数据库，清空数据库

rm –rf * //因为这是实验，所以用rm -rf比较方便，但不推荐用这种方式，可以考虑把文件拷贝到别的地方！！！！很危险的操作！！！

在主库中通过scp传输 将刚才得到的文件传送到从库的/backup目录下/

scp -r 2018-07-17_14-05-15/ root@172.16.10.4:/backup/

回到从库进入到/backup/目录确认
然后执行恢复操作

innobackupex --move-back /backup/2018-07-13_14-05-15/

授权

chown -R mysql:mysql  /var/lib/mysql

查看

var/lib/mysql/xtrabackup_info

确保

binlog_pos = filename 'server.000002', position '154'

参数有值
这样就知道主从关系了

在主库
创建从库复制的用户

grant replication slave on *.* to 'repl'@'%' identified by 'repl';

进入从库数据库设置主从关系（在从库上设置）

mysql>
CHANGE MASTER TO  MASTER_HOST='172.16.10.6',   //主库的ip
MASTER_USER='repl',MASTER_PASSWORD='repl',
MASTER_PORT=3306,
MASTER_LOG_FILE='server.000002',MASTER_LOG_POS=154,  //来自上面查看 /var/lib/mysql/xtrabackup_info
MASTER_CONNECT_RETRY=10;         //重连次数

启动从库

mysql> start slave;

启动从库前后 线程对比

mysql> show processlist;
+----+------+-----------+------+---------+------+----------+------------------+
| Id | User | Host      | db   | Command | Time | State    | Info             |
+----+------+-----------+------+---------+------+----------+------------------+
|  3 | root | localhost | NULL | Query   |    0 | starting | show processlist |
+----+------+-----------+------+---------+------+----------+------------------+
1 row in set (0.00 sec)

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

mysql> show processlist;
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time | State                                                  | Info             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+
|  3 | root        | localhost | NULL | Query   |    0 | starting                                               | show processlist |
|  4 | system user |           | NULL | Connect |   39 | Connecting to master                                   | NULL             |          //连接io线程
|  5 | system user |           | NULL | Connect |   39 | Slave has read all relay log; waiting for more updates | NULL             |
+----+-------------+-----------+------+---------+------+--------------------------------------------------------+------------------+          //SQL线程，日志应用线程
3 rows in set (0.00 sec)

mysql>

mysql> show slave status \G 
            Slave_IO_State: Waiting for master to send event
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
主库show processlist;

mysql> show processlist;
+----+------+-------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host              | db    | Command     | Time | State                                                         | Info             |
+----+------+-------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
|  4 | root | localhost         | mysql | Query       |    0 | starting                                                      | show processlist |
|  5 | repl | 172.16.10.4:47768 | NULL  | Binlog Dump |  465 | Master has sent all binlog to slave; waiting for more updates | NULL             |
+----+------+-------------------+-------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)

多了一个Binlog Dump线程

主库 新建一个测试库跟测试表

mysql> create database zhucongfuzhiceshi1 character set =utf8;
Query OK, 1 row affected (0.02 sec)

mysql> use zhucongfuzhiceshi1;
Database changed
mysql> create table ceshi as