Mysql---1.数据库中间件（代理） 2.集群HA （同步复制）

===============================================
今天学习的内容：
1.数据库中间件（代理）
2.集群HA （同步复制）


（AB replication 默认选择异步，mysql5.7版本有了半同步）
-----------------------------------------------------------
1.数据库中间件的作用 （软件）
1）读写分离 支持sql协议，能够区分读写，将相应的sql语句分发给后端真实数据库
2）负载均衡 slavea slaveb 将前端请求，以相对均衡的方式分发给后端数据库服务器
提高读的性能
3）分库、分表、分片
4）sql路由并发查询
--------------------------------
db1 mastera db1
db2 masterb db2
db3 slavea db3
db4 slaveb db4
---------------------------------
php.t1 t2 t3 t4
------------------------------------
php.t1
1 5 9 mastera
2 6 10 masterb
3 7 11 slavea
4 8 12 slaveb
------------------------------------------------
select * from db1.t1 where id=1 or id=11;


select * from db1.t1 where id=1;---->mastera--->id=1
select * from db1.t1 where id=11;--->slavea--->id=11


1 11--->


2.市面上的一些数据库代理软件
# 淘宝 tddl 用不了
# 阿里巴巴 corbar 坑
# 奇虎360 atlas rhel5 rhel6  
# mysql-proxy mysql lua
# mycat 2013 神话 java
------------------------------------------------
软件名 mysql-proxy 
http://classroom.example.com/materials/mysql-proxy-0.8.5.tar.gz
服务启动脚本
mysql-proxy
-P, --proxy-address=<host:port> 4040 指定mysql-proxy运行的位置和端口号
-b, --proxy-backend-addresses=<host:port> 可读可写
-r, --proxy-read-only-backend-addresses=<host:port> 只读
-s, --proxy-lua-script=<file> lua脚本


# 安装并启动mysql-proxy
wget http://classroom.example.com/materials/mysql-proxy-0.8.5.tar.gz
tar -xf mysql-proxy-0.8.5.tar.gz
cd mysql-proxy-0.8.5
./configure --prefix=/usr/local/mysql-proxy
make
make install
--------------------------------------------------------------
->>mastera->mysql
>grant all on *.* to dbproxy@172.25.8.15 identified by '(Uploo00king)';
>flush privileges;
--------------------------------------------------------------
cd mysql-proxy-0.8.5/lib/
cp rw-splitting.lua /usr/local/mysql-proxy
mysql-proxy -P 172.25.0.15:3306 -b 172.25.0.11:3306 -b 172.25.0.12:3306 -r 172.25.0.13:3306 -r 172.25.0.14:3306 -s /usr/local/mysql-proxy/rw-splitting.lua
>begin;
select @@hostname;
insert


1）依赖关系
yum install -y  gcc* libev lua  lua-devel libevent libevent-devel glib2 glib2-devel pkgconfig mariadb-devel flex
2）源码编译---------------------------------------------------------
1.configure
2.make
3.make install
---------------
3）启动服务
mysql-proxy -P 172.25.0.15:3306 -b 172.25.0.11:3306 -b 172.25.0.12:3306 -r 172.25.0.13:3306 -r 172.25.0.14:3306 -s /usr/local/mysql-proxy/rw-splitting.lua


注意，启动服务之前需要：
1）关闭所有服务器的selinux和firewalld
2）后端真实数据库需要给dbproxy授权


---------------
mysql-proxy详细步骤


1.网络拓扑规划 
mastera0 172.25.0.11 mariadb5.7 mysql-5.7.repo
masterb0 172.25.0.12 mariadb5.7 mysql-5.7.repo
slavea0 172.25.0.13 mariadb5.7 mysql-5.7.repo
slaveb0 172.25.0.14 mariadb5.7 mysql-5.7.repo
dbproxy0 172.25.0.15 mysql-proxy0.8.5 thirdpart.repo
2.安装软件 
1）无密码ssh登陆虚拟机
# ssh-keygen -t rsa
# for i in 10 11 12 13 14 15 ;do ssh-copy-id root@172.25.0.$i ;done


2）关闭selinux,firewalld
for i in 10 11 12 13 14 15 ;\
do ssh root@172.25.0.$i \
"sed -i '/^SELINUX/s/SELINUX=enforcing/SELINUX=disabled/' /etc/selinux/config;\
grep -v "^#" /etc/selinux/config;\
systemctl stop firewalld;setenforce 0 ";done


3）需要安装的软件包wget，net-tools,rpc-bind,nfs-utils,vim
for i in 10 11 12 13 14 ;do ssh root@172.25.0.$i "yum install -y wget net-tools rpc-bind nfs-utils vim ";done


4）查看ip地址ip addr 
5）修改yum源，根据实际需求来
从服务器上下载到/etc/yum.repos.d/目录


6) mastera--slaveb四台数据库服务器，安装mysql5.7，并启动服务,修改初始密码
为了测试中间件的功能，分别创建db1.t1
mastera db1.t1 (1,'mastera')
masterb db1.t1 (1,'masterb')
slavea db1.t1 (1,'slavea')
slaveb db1.t1 (1,'slaveb')


7) dbproxy安装依赖包 (需要第三方yum源 thirdpart.repo)
yum install -y  gcc* libev lua  lua-devel libevent libevent-devel glib2 glib2-devel pkgconfig mariadb-devel flex


8) 安装mysql-proxy 
# wget http://172.25.254.254/content/courses/db100/rhel7.2/materials/mysql-proxy-0.8.5.tar.gz 
# tar -zxf mysql-proxy-0.8.5.tar.gz 
# cd mysql-proxy-0.8.5 
# ./configure --prefix=/usr/local/mysql-proxy 
# make 
# make install 




3) 进入mysql-proxy的家目录/usr/local/mysql-proxy 
# cd /usr/local/mysql-proxy/;ls 
bin  include  lib  libexec  share 
# cd bin;ls 
mysql-binlog-dump  mysql-myisam-dump  mysql-proxy 
# ./mysql-proxy --help-proxy  
# 将lua脚本复制到mysql-proxy的家目录 
# cp ~/mysql-proxy-0.8.5/lib/rw-splitting.lua /usr/local/mysql-proxy/ 
# 启动服务
# mysql-proxy -P 172.25.0.15:3306 -b 172.25.0.11:3306 -b 172.25.0.12:3306 -r 172.25.0.13:3306 -r 172.25.0.14:3306 -s /usr/local/mysql-proxy/rw-splitting.lua
# 查看mysql-proxy监听端口号
# netstat -ntalp|grep 3306 
tcp        0      0 172.25.0.15:3306        0.0.0.0:*               LISTEN      24483/./mysql-proxy 


4.workstation测试(需要安装mariadb客户端，或者mysql客户端)
客户端172.25.0.10 
# mysql -u'dbproxy' -p'Uploo00king' -h172.25.0.15 
MySQL [(none)]> select * from db1.t1;
+----+---------+
| id | name    |
+----+---------+
|  1 | mastera |
+----+---------+
1 row in set (0.00 sec)




*************************************** 
#将mastera0的服务关闭后，再查看表1中的内容 
# systemctl stop mysqld 
**************************************** 


MySQL [(none)]> select * from db1.t1;
ERROR 2013 (HY000): Lost connection to MySQL server during query
MySQL [(none)]> select * from db1.t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    9
Current database: *** NONE ***


+----+---------+
| id | name    |
+----+---------+
|  1 | masterb |
+----+---------+
1 row in set (0.00 sec)


*************************************** 
#插入数据，查看插入到哪个服务器中了
****************************************  
MySQL [(none)]> insert into db1.t1 values (2,'test');
Query OK, 1 row affected (0.04 sec)
MySQL [(none)]> select @@hostname;
+----------------------+
| @@hostname           |
+----------------------+
| masterb0.example.com |
+----------------------+
1 row in set (0.00 sec)




*************************************** 
#将masterb0的服务关闭后，再插入数据
# systemctl stop mysqld 
**************************************** 


MySQL [(none)]> insert into db1.t1 values (100,'oo');
ERROR 2013 (HY000): Lost connection to MySQL server during query


MySQL [(none)]> select @@hostname;
ERROR 2013 (HY000): Lost connection to MySQL server during query
MySQL [(none)]> select @@hostname;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    7
Current database: *** NONE ***


+---------------------+
| @@hostname          |
+---------------------+
| slavea0.example.com |
+---------------------+
1 row in set (0.01 sec)




*************************************** 
#将mastera0的服务启动后，再插入数据
# systemctl start mysqld 
****************************************


MySQL [(none)]> begin;
Query OK, 0 rows affected (0.00 sec)


MySQL [(none)]> select @@hostname;
+----------------------+
| @@hostname           |
+----------------------+
| mastera0.example.com |
+----------------------+
1 row in set (0.00 sec)


MySQL [(none)]> insert into db1.t1 values (101,'oo');
Query OK, 1 row affected (0.00 sec)


MySQL [(none)]> commit;
Query OK, 0 rows affected (0.07 sec)




MySQL [(none)]> select @@hostname;
+---------------------+
| @@hostname          |
+---------------------+
| slavea0.example.com |
+---------------------+
1 row in set (0.00 sec)


MySQL [(none)]> select * from db1.t1;
+----+--------+
| id | name   |
+----+--------+
|  1 | slavea |
+----+--------+
1 row in set (0.00 sec)


*************************************** 
#此时发现，写操作连接mastera，读操作连接slavea
****************************************


=====================================================
数据库高可用——HA
1.什么是高可用？
表示法：百分比、9规则
例如：99.999% 代表一年中只能有5分钟的宕机时间(365*24*60*0.00001=5.256m)
宕机造成的损失和降低宕机时间所花费的成本的衡量
一般能做到两个9（3天）、3个9（8h）、4个9（52.56m）


2.宕机的原因归纳
硬件上最普遍的问题是磁盘空间耗尽；
性能上最普遍的问题是糟糕的sql语句；
复制问题通常是主备数据不一致导致的；
人为误操作


3.如何实现高可用
降低故障率
优化架构
4.数据库高可用软件
mariadb galera cluster
mysql NDB cluster 


5.mariadb galera cluster
多台服务器，坏掉任意一台都不影响服务使用。


功能 同步复制
新节点加入自动同步数据
失效节点自动清除

优势 没有延迟
拓展能力强

适用架构
     ,-------.     ,-------.    ,--------.
     | node1 |-----| node2 |<---| client |
     `-------'  G  `-------'    `--------'
             \     /
            ,-------.    ,--------.
            | node3 |<---| client |
            `-------'    `--------'


            ,---------------------.
             |     application     |
             `---------------------'
       writes /         | reads    \ reads
      ,-------.     ,-------.     ,-------.
      | node1 |     | node2 |     | node3 |
      `-------'     `-------'     `-------'
       <========= cluster nodes =========>
-------------------------------------------------------
1.同步复制，冗余
2.存储引擎，innodb
3.wsrep接口用于同步数据
-----------------------------
软件名：Galera mariadb jemalloc---管理内存的软件


http://classroom.example.com/materials/mariadb-10.2.repo 
http://classroom.example.com/materials/thirdpart.repo
















=======================================================
1.安装了，4个节点
2.关闭第一个节点后重起服务，需要修改配置文件，
3.关闭第三个节点，去查看错误日志，在集群中作插入动作，重起服务，看是否能够同步到数据。


--------------------------
# 详细步骤
1.安装软件包
galera   25.3.15
jemalloc*                               
MariaDB-client  10.2         
MariaDB-server  10.2     
MariaDB-compat  10.2


@需要先卸载mariadb-libs
# rpm -e mariadb-libs --nodeps
# rpm -e mariadb-common --nodeps 
# yum install -y galera jemalloc* MariaDB-client  MariaDB-server MariaDB-compat


2.初始化MariaDB数据库并启动MariaDB服务，并作安全加固
# systemctl start mysql
# mysql_secure_installation
# systemctl stop mysql


3.配置
=======================================================
vim /etc/my.cnf.d/galera.cnf


[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#启动节点时需要指定galera cluster的地址,作为cluster中第一个启动的节点,wsrep_cluster_address=gcomm://,对于后续启动的节点,wsrep_cluster_address=gcomm://node1,node2,node3
wsrep_cluster_address='gcomm://'
#所有node必须一样
wsrep_cluster_name='galera'
#节点地址
wsrep_node_address='172.25.0.11'
#节点名称
wsrep_node_name='galera1'
#Snapshot State Transter快照状态转移方法:mysqldump/rsync,默认mysqldump
wsrep_sst_method=rsync
#binlog的格式也有三种：STATEMENT，ROW，MIXED
binlog_format=row
default_storage_engine=InnoDB
#调整锁策略的
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
===========================================================




db1.t1(id,name)
1,booboo
2,batman
3,superman


update db1.t1 set id=10 where id=1;


update db1.t1 set id=10 where id=1;
update db1.t1 set id=10,name='booboo' where id=1 and name='booboo';










vim /etc/my.cnf.d/galera.cnf
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://'
wsrep_cluster_name='galera'
wsrep_node_address='172.25.0.11'
wsrep_node_name='galera1'
wsrep_sst_method=rsync


binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0


4.启动 
systemctl start mysql


#查看目前mysql的端口
netstat -ntpl|grep sql
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      12877/mysqld        
tcp        0      0 0.0.0.0:4567            0.0.0.0:*               LISTEN      12877/mysqld 




5.其他节点上做相同配置
依次启动其他节点,其他节点会根据配置自动加入集群并同步数据，一定要关闭selinux，或者设为0
[galera]
# Mandatory settings
wsrep_on=ON
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address='gcomm://172.25.0.11'
wsrep_cluster_name='galera'
wsrep_node_address='172.25.0.12'
wsrep_node_name='<注意这里与解析的主机名相同>'
wsrep_sst_method=rsync


binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0


6.观察日志


7.在galera上查看集群状态
MariaDB [(none)]> show global status like 'wsrep_cluster%';
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id    | 14                                   |
| wsrep_cluster_size       | 2                                    |
| wsrep_cluster_state_uuid | ff8cb12b-168a-11e6-ba3c-b36f59743764 |
| wsrep_cluster_status     | Primary                              |
+--------------------------+--------------------------------------+


MariaDB [(none)]> show global status like 'wsrep_cluster%';
+--------------------------+--------------------------------------+
| Variable_name            | Value                                |
+--------------------------+--------------------------------------+
| wsrep_cluster_conf_id    | 15                                   |
| wsrep_cluster_size       | 3                                    |
| wsrep_cluster_state_uuid | ff8cb12b-168a-11e6-ba3c-b36f59743764 |
| wsrep_cluster_status     | Primary                              |
+--------------------------+--------------------------------------+


8.测试
1) 在一节点上新建表并插入数据以在其他实例上观测数据是否同步
2）测试节点故障机恢复A.masterb\slavea B.mastera停掉之后，需要修改配置文件，才能重新加入。




我们可以关注几个关键的参数:
wsrep_connected = on 链接已开启wsrep_local_index = 1 在集群中的索引值
wsrep_cluster_size =3 集群中节点的数量
wsrep_incoming_addresses = 172.25.0.11:3306,172.25.0.12:3306,172.25.0.13:3306
集群中节点的访问地址