CDH6安装（六）——MySQL+Keepalived双主高可用

作者: PandaEyes聊大数据 | 来源:发表于2018-08-30 11:09 被阅读0次

CDH6安装（六）——MySQL+Keepalived双主高可用
综合架构之keepalived(高可用)
Nginx与Keepalived 实现站点高可用方案
Mysql双主高可用
MySql双主+高可用
分布式高可用负载均衡集群架构设计的实战演练
2019-10-12
RocketMQ双主双从模式集群部署
hadoop+spark 整合
MySQL高可用架构：mysql+keepalived实现

检查是否安装mariadb（全部主机）

sudo rpm -qa | grep -i mariadb #如图，存在必须卸载
sudo rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
sudo rpm -qa | grep -i mariadb #再检查一遍

MySQL5.7安装与配置(bigdata02\03)

1.MySQL安装

sudo yum list | grep -i mysql  #查看版本
sudo yum install mysql-community-server.x86_64
#sudo yum install mysql-community-libs-compat mysql-community-libs #在全部
agent节点上装上通用共享包
#sudo yum install  mysql-community-client

2.启动MySQL

sudo systemctl start  mysqld.service #启动
sudo systemctl status mysqld.service 
sudo systemctl enable mysqld.service #开机启动

3.查看原始密码

sudo grep "password" /var/log/mysqld.log

4.修改密码

sudo mysql -uroot -p
输入原始密码
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Root123456!';

5.创建同步用户
需要在bigdata02/03上都创建对应的同步用户

sudo mysql -uroot -pRoot123456!
#bigdata02
grant replication slave on *.* to 'backup'@'192.168.88.202' identified by 'Root123456!'; 
flush privileges;
#bigdata03
grant replication slave on *.* to 'backup'@'192.168.88.201' identified by 'Root123456!'; 
flush privileges;

6.配置my.conf

sudo chown -R mysql:mysql /etc/my.cnf
sudo vim /etc/my.cnf

添加

log-bin=mysql-bin
binlog-ignore-db=mysql  #不需要记录进制日志的数据库 
relay-log= bigdata02-relay-bin  # bigdata03主机可设置为bigdata03-relay-bin
replicate-ignore-db=mysql #不需要同步的数据库
skip-name-resolve       #禁止掉DNS的查询。mysql会在用户登录过程中对客户端IP进行DNS反查，不管你是使用IP登录还
log-slave-updates=true #保证slave挂在任何一台master上都会接收到另一个master的写入信息
sync_binlog=1 #当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘,系统默认的设置是sync_binlog=0，也就是不做任何强制性的磁盘刷新指令，这时候的性能是最好的，但是风险也是最大的
auto_increment_offset=1 #自增字段的起始值，Bigdata-MySQL2主机设置值为2即可
auto_increment_increment=2  #字段一次递增值
skip_external_locking   #跳过外部锁定
lower_case_table_names=1        #不区分大小写
slave-skip-errors=1022,1032,1060,1062   #忽略类型错误
max_connections=200             #设置mysql最大连接数
wait_timeout = 600              #设置10分钟内该连接没有请求就断开
interactive_timeout = 600
expire_logs_days=7              #保存7天的日志文件
character-set-server=utf8
server-id = 1   #唯一标识，bigdata03主机设置为2，不可以为数字
#slave-skip-errors=all #过滤掉一些没啥大问题的错误

sudo systemctl restart  mysqld.service
sudo mysql -uroot -pRoot123456!
# mysql> flush tables with read lock; #防止进入新的数据
mysql> show master status;

7.同步配置

#master_host为slave ip，master_log_file就是上面查到的file值,master_log_pos为Position
#bigdata02上执行
mysql> change master to master_host='192.168.88.202',master_user='backup',master_password='Root123456!',master_log_file='mysql-bin.000001',master_log_pos=107;
#bigdata03上执行
mysql> change master to master_host='192.168.88.201',master_user='backup',master_password='Root123456!',master_log_file='mysql-bin.000001',master_log_pos=107;

mysql> start slave;
# mysql> unlock tables; #释放锁
mysql> show slave status\G; #查看从服务器状态
mysql> show processlist\G; #同时可查看复制进度

8.测试双主是否成功

#bigdata02
mysql> create database scm charset utf8;
mysql> use scm;
mysql> create table user(id int(5),name char(10));
mysql> insert into scm.user values(00001,'zhangsan');
mysql> commit;
#bigdata03
mysql> show databases;
mysql> select * from scm.user;
mysql> use scm;
mysql> insert into user values(00002,'lisi');
mysql> commit;
mysql> select * from scm.user;
#bigdata02
mysql> select * from scm.user;
mysql> use scm;
mysql> drop table scm.user;
mysql> drop database scm;
mysql> show databases;

9.本次生产环境需要同步的元数据库

备注1：数据库名称、用户名和密码可以自行创建和命名，后续在相应的配置上保持一致即可。非程序自动创建的数据库必须实现创建好数据和用户，并事先对用户进行数据库授权，且不一定需要创建这么多用户，只需要保重使用相应的用户可以连接上Mysql对应的数据库即可。
以hive为例：

mysql> create database hive charset utf8;
mysql> grant all on hive.* to hive@'%' identified by 'hive'; //用户为hive密码为hive
mysql> flush privileges;
# ------------------------ 在其中一台建就行了----------------
create database hive charset utf8;
create database amon charset utf8;
create database oozie charset utf8;
create database hue charset utf8;
create database zabbix charset utf8;
create database sqoop  charset utf8;
# ---------------------两台都执行，因为是双主------------------
grant all on amon.* to amon@'%' identified by 'Root123456!';
flush privileges;
grant all on hive.* to hive@'%' identified by 'Root123456!';
flush privileges;
grant all on oozie.* to oozie@'%' identified by 'Root123456!';
flush privileges;
grant all on hue.* to hue@'%' identified by 'hue';
flush privileges;
grant all on zabbix.* to zabbix@'%' identified by 'Root123456!';
flush privileges;
grant all on sqoop.* to sqoop@'%' identified by 'Root123456!';
flush privileges;

Keepalived高可用配置(bigdata02\03)

https://blog.csdn.net/hanzheng260561728/article/details/76252503
1.安装Keepalived

sudo yum install keepalived
sudo yum install ipvsadm
rpm -qa # 查看所有安装的软件
rpm -ql keepalived # 查找软件安装的相关路径

2.查网卡

ip addr

3.高可用配置

sudo vim /etc/keepalived/keepalived.conf

#bigdata02的keepalived.conf配置
! Configuration File for keepalived
global_defs {
#   notification_email {
#     acassen@firewall.loc
#     failover@firewall.loc
#     sysadmin@firewall.loc
#   }
#   notification_email_from Alexandre.Cassen@firewall.loc
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}

vrrp_instance VI_1 {
    state MASTER
    interface ens33 #具体网卡
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.88.210 #vip
    }
}

virtual_server 192.168.88.210 3306 {
    delay_loop 6
    lb_algo rr
    lb_kind DR
    nat_mask 255.255.255.0
    persistence_timeout 50
    protocol TCP

    real_server 192.168.88.201 3306 {
        weight 1

        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
    real_server 192.168.88.202 3306 {
        weight 1
        TCP_CHECK {
            connect_timeout 3
            nb_get_retry 3
            delay_before_retry 3
            connect_port 3306
        }
    }
}


#---------------------------------------------------------------
#bigdata03的keepalived.conf配置

! Configuration File for keepalived

    global_defs {
#       notification_email {
#         acassen@firewall.loc
#         failover@firewall.loc
#         sysadmin@firewall.loc
#       }
#       notification_email_from Alexandre.Cassen@firewall.loc
       smtp_server 127.0.0.1
       smtp_connect_timeout 30
       router_id LVS_DEVEL
    }

    vrrp_instance VI_1 {
        state BACKUP
        interface ens33 #具体网卡
        virtual_router_id 51
        priority 99
        advert_int 1
        authentication {
            auth_type PASS
            auth_pass 1111
        }
        virtual_ipaddress {
            192.168.88.210 #vip
        }
    }

    virtual_server 192.168.88.210 3306{
        delay_loop 6
        lb_algo rr
        lb_kind DR
        nat_mask 255.255.255.0
        persistence_timeout 50
        protocol TCP

        real_server 192.168.88.201 3306{
            weight 1
            TCP_CHECK {
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
                connect_port 3306
            }
        }
        real_server 192.168.88.202 3306{
            weight 1
            TCP_CHECK {
                connect_timeout 3
                nb_get_retry 3
                delay_before_retry 3
                connect_port 3306
            }
        }
    }

sudo cat /var/log/messages # 查看日志
sudo systemctl start  keepalived.service
sudo systemctl status  keepalived.service
sudo systemctl enable  keepalived.service
#sudo systemctl stop  keepalived.service #此处不执行
#sudo systemctl restart  keepalived.service #此处不执行
sudo ipvsadm -Ln #查看结果，结果如下图
ip addr
mysql -h 192.168.88.210 -uroot -pRoot123456!

mysql连接jar包（全部主机）

cd
sudo yum install wget 
sudo wget http://192.168.88.200/mysql/5.7.27/mysql-connector-java-5.1.48.tar.gz
sudo tar -zxvf ~/mysql-connector-java-5.1.48.tar.gz
sudo mkdir -p /usr/share/java/
sudo mv ~/mysql-connector-java-5.1.48/mysql-connector-java-5.1.48-bin.jar /usr/share/java/mysql-connector-java.jar

（原创文章：如果有需要，请留言告知）

CDH6安装（六）——MySQL+Keepalived双主高可用
检查是否安装mariadb（全部主机） MySQL5.7安装与配置(bigdata02\03) 1.MySQL安装...
综合架构之keepalived(高可用)
内容涉及高可用原理高可用配置文件高可用服务常见问题实现高可用服务双主功能 keepalivd----------...
Nginx与Keepalived 实现站点高可用方案
目录概述规划与准备工作安装、部署Nginx和Keepalived 高可用方案--主主配置高可用方案--主从...
Mysql双主高可用
1.两台MySQL互为主从2.采用keepalived来实现MySQL的故障转移3.同时只有一台MySQL能进行读...
MySql双主+高可用
准备工作系统：Centos7.6 / 64 位MySql：v5.7.34Keepalived：v1.2.24Rp...
分布式高可用负载均衡集群架构设计的实战演练
一、总体架构双主负载均衡服务器架构方式：nginx + keepalived实现nginx双主高可用的负载均衡主...
2019-10-12
利用LVS+Keepalived搭建Mysql双主复制高可用负载均衡环境应用背景： MySQL复制（主主，主从....
RocketMQ双主双从模式集群部署
双主双从模式集群在生产环境中为了保障集群无单点故障问题，保证高可用性，需要采用双主双从模式来构建RocketMQ...
hadoop+spark 整合
zookeeper安装 zookeeper3.4.6安装 spark高可用安装完成 spark高可用安装 hado...
MySQL高可用架构：mysql+keepalived实现
系统环境及架构在master和slave上分别进行数据库的安装在mysqlMaster上配置mysql配置文件...