mysql5.7主从搭建

centos7搭建mysql5.7
ip 192.168.20.181 192.168.20.182
1,先关闭防火墙和selinux

[root@192 ~]# systemctl stop firewalld
[root@192 ~]# setenforce 0

2,查看是否安装mysql如果有则卸载

[root@192 ~]# rpm -qa |grep mysql

3,安装下载工具

[root@192 ~]# yum -y install wget
已加载插件:fastestmirror
Determining fastest mirrors
 * base: mirrors.huaweicloud.com
 * extras: mirrors.huaweicloud.com
 * updates: mirrors.huaweicloud.com
base                                                                                                              | 3.6 kB  00:00:00     
extras                                                                                                            | 2.9 kB  00:00:00     
updates                                                                                                           | 2.9 kB  00:00:00     
(1/4): base/7/x86_64/group_gz                                                                                     | 153 kB  00:00:00     
(2/4): extras/7/x86_64/primary_db                                                                                 | 194 kB  00:00:00     
(3/4): updates/7/x86_64/primary_db                                                                                | 1.3 MB  00:00:00     
(4/4): base/7/x86_64/primary_db                                                                                   | 6.1 MB  00:00:02     
正在解决依赖关系
--> 正在检查事务
---> 软件包 wget.x86_64.0.1.14-18.el7_6.1 将被 安装
--> 解决依赖关系完成

依赖关系解决

=========================================================================================================================================
 Package                      架构                           版本                                     源                            大小
=========================================================================================================================================
正在安装:
 wget                         x86_64                         1.14-18.el7_6.1                          base                         547 k

事务概要
=========================================================================================================================================
安装  1 软件包

总下载量:547 k
安装大小:2.0 M
Downloading packages:
wget-1.14-18.el7_6.1.x86_64.rpm                                                                                   | 547 kB  00:00:00     
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  正在安装    : wget-1.14-18.el7_6.1.x86_64                                                                                          1/1 
  验证中      : wget-1.14-18.el7_6.1.x86_64                                                                                          1/1 

已安装:
  wget.x86_64 0:1.14-18.el7_6.1                                                                                                          

完毕!

4,使用wget下载安装包
wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm

[root@192 ~]# wget https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
--2020-05-22 10:41:17--  https://dev.mysql.com/get/mysql57-community-release-el7-9.noarch.rpm
正在解析主机 dev.mysql.com (dev.mysql.com)... 137.254.60.11
正在连接 dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 302 Found
位置:https://repo.mysql.com//mysql57-community-release-el7-9.noarch.rpm [跟随至新的 URL]
--2020-05-22 10:41:18--  https://repo.mysql.com//mysql57-community-release-el7-9.noarch.rpm
正在解析主机 repo.mysql.com (repo.mysql.com)... 104.75.165.42
正在连接 repo.mysql.com (repo.mysql.com)|104.75.165.42|:443... 已连接。
已发出 HTTP 请求,正在等待回应... 200 OK
长度:9224 (9.0K) [application/x-redhat-package-manager]
正在保存至: “mysql57-community-release-el7-9.noarch.rpm”

100%[===============================================================================================>] 9,224       --.-K/s 用时 0s      

2020-05-22 10:41:21 (270 MB/s) - 已保存 “mysql57-community-release-el7-9.noarch.rpm” [9224/9224])

5,查看下载的包

[root@192 ~]# ll
总用量 16
-rw-r--r--. 1 root root 9224 9月  12 2016 mysql57-community-release-el7-9.noarch.rpm

6,解压安装包

[root@192 ~]# rpm -ivh mysql57-community-release-el7-9.noarch.rpm 
警告:mysql57-community-release-el7-9.noarch.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY
准备中...                          ################################# [100%]
正在升级/安装...
   1:mysql57-community-release-el7-9  ################################# [100%]

执行完成后会在/etc/yum.repos.d/目录下生成两个repo文件mysql-community.repo mysql-community-source.repo

使用yum命令即可完成安装
注意:必须进入到 /etc/yum.repos.d/目录后再执行以下命令

[root@192 yum.repos.d]# yum -y install mysql mysql-server
已安装:
  mysql-community-client.x86_64 0:5.7.30-1.el7                          mysql-community-libs.x86_64 0:5.7.30-1.el7                      
  mysql-community-libs-compat.x86_64 0:5.7.30-1.el7                     mysql-community-server.x86_64 0:5.7.30-1.el7                    

作为依赖被安装:
  mysql-community-common.x86_64 0:5.7.30-1.el7                                                                                           

作为依赖被升级:
  postfix.x86_64 2:2.10.1-9.el7                                                                                                          

替代:
  mariadb-libs.x86_64 1:5.5.56-2.el7                                                                                                     

完毕!

7,修改配置文件

[root@192 yum.repos.d]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin=mysql-bin
skip-grant-tables

8,启动数据库

[root@192 yum.repos.d]# systemctl restart mysqld

9,进入数据库

[root@192 yum.repos.d]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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.

10,解决方法

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

mysql> alter user root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
[root@192 yum.repos.d]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.30-log MySQL Community Server (GPL)

Copyright (c) 2000, 2020, 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> exit
Bye

11,进入数据库授权

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to tom@'%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      756 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> exit
Bye

12,从上面安装数据库同样的操作 把配置文件修改就好了

[root@192 yum.repos.d]# vim /etc/my.cnf
[mysqld]
server-id=2
relay-log=mysql-relay

13,启动数据库

[root@192 yum.repos.d]# systemctl start mysqld

14,在从服务器做同步

mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> change master to
    -> master_host='192.168.20.181',
    -> master_user='tom',
    -> master_password='123',
    -> master_log_file='mysql-bin.000008',
    -> master_log_pos=756;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

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

15,查看主从是否同步

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.20.181
                  Master_User: tom
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 756
               Relay_Log_File: mysql-relay.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 

16,Haproxy安装:

[root@localhost ~]# yum -y install haproxy

17,配置文件路径:/etc/haproxy/haproxy.cfg

global
    log         127.0.0.1 local2
    chroot      /var/lib/haproxy
    pidfile     /var/run/haproxy.pid
    maxconn     4000
    user        haproxy
    group       haproxy
    daemon

    stats socket /var/lib/haproxy/stats

defaults
    mode                    tcp
    log                     global
    option                  dontlognull
    option                  redispatch
    retries                 3
    timeout http-request    10s
    timeout queue           1m
    timeout connect         10s
    timeout client          1m
    timeout server          1m
    timeout http-keep-alive 10s
    timeout check           10s
    maxconn                 600

frontend  main *:5000
    acl url_static       path_beg       -i /static /images /javascript /stylesheets
    acl url_static       path_end       -i .jpg .gif .png .css .js

    use_backend static          if url_static
    default_backend             app

backend static
    balance     roundrobin
    server      static 127.0.0.1:4331 check

backend app
    balance     roundrobin
    server  app1 127.0.0.1:5001 check
    server  app2 127.0.0.1:5002 check
    server  app3 127.0.0.1:5003 check
    server  app4 127.0.0.1:5004 check

listen stats                             //配置haproxy状态页(用来查看的页面)
    mode http
    bind :8888
    stats enable
    stats hide-version                    //隐藏haproxy版本号
    stats uri     /haproxyadmin?stats     //一会用于打开状态页的uri
    stats realm   Haproxy\ Statistics     //输入账户密码时的提示文字
    stats auth    admin:admin             //用户名:密码
 
# MySQL 负载配置
listen proxy-mysql 
    bind 0.0.0.0:13306 # 监听端口
    mode tcp # 模式
    balance roundrobin # 负载均衡的方式,轮询(平均)方式
    option tcplog # 允许记录tcp 连接的状态和时间
    #option mysql-check user haproxy
    server MySQL1 192.168.20.181:3306 check weight 1 maxconn 2000
    server MySQL2 192.168.20.182:3306 check weight 1 maxconn 2000
    option tcpka # 是否允许向server和client发送keepalive

18,启动Haproxy

[root@localhost ~]# systemctl restart haproxy

19,查看是否启动成功

[root@localhost ~]# ps -ef |grep haproxy
root      14961      1  0 10:58 ?        00:00:00 /usr/sbin/haproxy-systemd-wrapper -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid
haproxy   14963  14961  0 10:58 ?        00:00:00 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
haproxy   14964  14963  0 10:58 ?        00:00:00 /usr/sbin/haproxy -f /etc/haproxy/haproxy.cfg -p /run/haproxy.pid -Ds
root      14966  14796  0 10:58 pts/2    00:00:00 grep --color=auto haproxy

20,查看负载情况
http://192.168.20.181:8888/haproxyadmin?stats
在这里插入图片描述
21,安装 HAProxy 的步骤在 192.168.20.182 上做相同步骤即可。

[root@localhost ~]# yum -y install keepalived

22,配置文件路径:/etc/keepalived/keepalived.conf

! Configuration File for keepalived
#简单的头部,这里主要可以做邮件通知报警等的设置,此处就暂不配置了;
global_defs {
        notificationd LVS_DEVEL
}
#预先定义一个脚本,方便后面调用,也可以定义多个,方便选择;
vrrp_script chk_haproxy {
    script "/etc/keepalived/chk.sh"  #具体脚本路径
    interval 2  #脚本循环运行间隔
}
#VRRP虚拟路由冗余协议配置
vrrp_instance VI_1 {   #VI_1 是自定义的名称;
    state MASTER #MASTER表示是一台主设备,BACKUP表示为备用设备【我们这里因为设置为开启不抢占,所以都设置为备用】
    nopreempt      #开启不抢占
    interface ens33   #指定VIP需要绑定的物理网卡
    virtual_router_id 11   #VRID虚拟路由标识,也叫做分组名称,该组内的设备需要相同
    priority 120   #定义这台设备的优先级 1-254;开启了不抢占,所以此处优先级必须高于另一台
 
    advert_int 1   #生存检测时的组播信息发送间隔,组内一致
    authentication {    #设置验证信息,组内一致
        auth_type PASS   #有PASS 和 AH 两种,常用 PASS
        auth_pass asd    #密码
    }
    virtual_ipaddress {
        192.168.20.180    #指定VIP地址,组内一致,可以设置多个IP
    }
    track_script {    #使用在这个域中使用预先定义的脚本,上面定义的
        chk_haproxy   
    }
 
    notify_backup "systemctl restart haproxy"   #表示当切换到backup状态时,要执行的脚本
    notify_fault "systemctl stop haproxy"     #故障时执行的脚本
}

23,编写脚本

[root@localhost ~]# vim /etc/keepalived/chk.sh
#!/bin/bash
if [ $(ps -C haproxy --no-header | wc -l) -eq 0 ]; then
       systemctl stop keepalived
fi
 
[root@localhost ~]# chmod 777 /etc/keepalived/chk.sh

24,启动keepalived

[root@localhost ~]# systemctl restart keepalived
[root@localhost ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:15:37:e3 brd ff:ff:ff:ff:ff:ff
    inet 192.168.20.181/24 brd 192.168.117.255 scope global noprefixroute dynamic ens33
       valid_lft 1632sec preferred_lft 1632sec
    inet 192.168.20.180/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::fdf1:237c:2a96:81c6/64 scope link noprefixroute
       valid_lft forever preferred_lft forever

25,安装 Keepalived 的步骤在 192.168.20。182 上做相同步骤即可。
26,手动 kill 掉主机上的 HAProxy,出现了错误信息,在之后又继续新增操作。

[root@localhost ~]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
    link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
    inet 127.0.0.1/8 scope host lo
       valid_lft forever preferred_lft forever
    inet6 ::1/128 scope host
       valid_lft forever preferred_lft forever
2: ens33: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
    link/ether 00:0c:29:83:3b:07 brd ff:ff:ff:ff:ff:ff
    inet 192.168.20.182/24 brd 192.168.117.255 scope global noprefixroute dynamic ens33
       valid_lft 1505sec preferred_lft 1505sec
    inet 192.168.20.180/32 scope global ens33
       valid_lft forever preferred_lft forever
    inet6 fe80::f6e7:5569:d37:4950/64 scope link noprefixroute
       valid_lft forever preferred_lft forever
    inet6 fe80::fdf1:237c:2a96:81c6/64 scope link tentative noprefixroute dadfailed
       valid_lft forever preferred_lft forever
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 1024 设计师: 上身试试
应支付0元
点击重新获取
扫码支付

支付成功即可阅读