Heartbeat+Drbd+Mysql高可用服务应用指南

作者: print("") 分类: linux 发布时间: 2017-07-06 16:00

10、1 Mysql高可用需求介绍 

10.1.1 mysql高可用生产业务需求
在企业实际生产场景中,一般多为主从的mysql数据库架构是最常用的架构方案,该架构方案部署简单、维护方便、并且通过简单的代理或通过程序的方式就可以实现对主从库的读写分离、并多个从库还可以通过lvs或haproxy等代理实现对多个从库的负载均衡,
但是、在以上的mysql数据库架构中、我们不难发现、虽然从库是多个、但是主库仅有一个、也就是说主库一旦宕机、所有写业务就会终止、而从库宕机1个就没有多大的影响、那么如何解决这个问题呢?
这就是我们本章实现的主题 heartbeat+DRBD+mysql高可用服务应用、这个解决方案可以有些的解决主库单点的问题、当主库宕机后、可以实现主库从一个节点切换到另一个节点、而所有的从库会自动和更新主库进行同步、且新主库的数据和宕机瞬间的主几乎完全一致、从而实现了mysql主库热备方案、这套高可用方案也是mysql官方推荐的。
部署逻辑图: 正常情况说明:

  • Heartbeat通过串口线或直连对DB服务做健康检查、并执行DRBD、Mysql、VIP等资源自动动态切换
  • Db-1-2作为db-1-1高可用备份、正常情况下db-1-1提供一个主分区mysql使用。
  • 无聊磁盘做RAID10或RAID0,根据性能和冗余需求来选
  • 服务器之间、服务器和交换机之间都是千兆网卡绑定(bonding)
  • 应用服务器通过VIP访问Mysql主库
  • Mysql在DRBD分区1中
  • Mysql slave1.mysql slave2 通过VIP同步主库mysql 3306
  • 以上高可用Mysql多从的模式、属于一主二从。
  • 提示:经过高可用方案切换后的数据库架构、就是一个常规的主从结构、此时、主库就从热备变成了单点、因此要尽快修复原来的主库、以确保切换后的主库宕机对业务带来的影响。如果热备主库的硬件配置和原主库一致或更好的情况下、也可以在mysql原宕机主库恢复后、降低角色作为热备节点。
    10.1.3服务主机资源规划

    名称 接口 IP 用途
    Master(Rserver-1) Eth0 192.168.236.143 外网管理IP、用WAN转发数据转发
    Eth1 172.16.1.1 内网管理IP,用于LAN数据转发
    Eth2 192.168.1.1 用于提供心跳线路连接(直连)
    VIP 192.168.236.10 用于提供应用程序A挂载服务
    BACKUP(Lserver-1) Eth0 192.168.236.192 外网管理IP、用WAN转发数据转发
    Eth1 172.16.1.2 内网管理IP,用于LAN数据转发
    Eth2 192.168.1.2 用于服务器间心跳连接
    VIP 192.168.236.10 用于提供应用程序A挂载
    Mysql-1 Eth0 192.168.236.150 Mysql 从库
    Mysql-2 Eth0 192.168.236.151 Mysql从库

    Rserver-1 的数据库文件目录为/data 对外提供的访问VIP为192.168.236.10
    配置目标:一旦主服务器Rserver-1 宕机、服务器上面的mysql数据库服务和虚拟IP会自动切换到服务器Lserver-1继续提供服务、从而达到MySQL数据库高可用无业务影响的目的、
    在这里会有一个特别的问题、就是以前的多个从库如何能自动和行的主库同步、经过实践、通过Drbd的方式同步然后做从库时和主库对外提供服务器VIP为同步IP、当主宕机后、VIP飘移到热备主机、默认情况60秒内、从库就可以得到行的VIP、从而自动和新的主库同步、这里需要强调下、通过mysql同步做双主的方式、是难以做到主库宕机从库和新的主库自动同步的。这也是绝大数的网友的困惑所在。

  • 双主热备
  • 在这里我们搭建单主的模式 我们首先搭建好两台主的配置。后面的从库的配置后面再去动10.2搭建DRBD
    10.2.1drbd的环境配置
    设置hosts文件 两台都配置 注意 这里是主机名也需要改成picadata-1-1 是主机名需要改
    例如:hostname picadata-1-1 如果这步没有操作启动服务的时候会出现报错。
  • echo ‘172.16.1.1 Rserver-1’>>/etc/hostsecho ‘172.16.1.2 Lserver-1’>>/etc/hosts
    [root@Lserver-1 ~]# tail -2 /etc/hosts
    172.16.1.2 Rserver-1
    172.16.1.1 Lserver-1
    10.2.2配置服务器间心跳连接:
    192.168.1.1 和192.168.1.2 两块网卡之间是通过普通网线直连连接的、即不通过交换机、直接把两块网卡连接在一起用于做心跳检测
    Master:
    ifconfig eth2 192.168.1.1 netmask 255.255.255.0
    Backup:
    ifconfig eth2 192.168.1.2 netmask 255.255.255.0
    Rserver-1 server 上添加如下主机路由
    route add –host 192.168.1.2 dev eth2
    ####这条命令是:从picdata-1-1 server 访问192.168.1.2 走网卡eth2出去、作为心跳线路
    echo ‘route add -host 192.168.1.2 dev eth2’ >>/etc/rc.local
    ##-à加入开机自启动配置里、这样下次启动后就会自动加载这个路由配置。
    route -n
    Lserver-1 server 上添加如下主机路由
    route add –host 192.168.1.2 dev eth2
    ####这条命令是:从picdata-1-2 server 访问192.168.1.2 走网卡eth2出去、作为心跳线路
    echo ‘route add -host 192.168.1.1 dev eth2’ >>/etc/rc.local
    ##-à加入开机自启动配置里、这样下次启动后就会自动加载这个路由配置。
    10.2.3开始实施部署
    安装相关软件
    1、安装drbd
    2、安装heartbeat
    3、安装MySQL
    10.2.4硬盘进行分区
    首先,通过fdisk,mkfs,ext3,tune2fs 等命令、对硬盘进行分区、分区信息如下表
    提示:如果生产环境中单个硬盘和raid的硬盘大于2T fdisk 命令是查看不到的。
    在虚拟机中添加两块硬盘。后面查看一下
    Rserver-1 查看
    [root@Rserver-1 ~]#  fdisk -l
    Disk /dev/sda: 21.5 GB, 21474836480 bytes
    255 heads, 63 sectors/track, 2610 cylinders
    Units = cylinders of 16065 * 512 = 8225280 bytes
    Sector size (logical/physical): 512 bytes / 512 bytes
    I/O size (minimum/optimal): 512 bytes / 512 bytes
    Disk identifier: 0x000486f5
    Device Boot      Start         End      Blocks   Id  System
    /dev/sda1   *           1          64      512000   83  Linux
    Partition 1 does not end on cylinder boundary.
    /dev/sda2              64        2611    20458496   8e  Linux LVM
    Disk /dev/sdb: 21.5 GB, 21474836480 bytes
    Lserver-1 查看
    [root@Lserver-1 ~]# fdisk -l
    Disk /dev/sda: 21.5 GB, 21474836480 bytes
    255 heads, 63 sectors/track, 2610 cylinders
    Units = cylinders of 16065 * 512 = 8225280 bytes
    Sector size (logical/physical): 512 bytes / 512 bytes
    I/O size (minimum/optimal): 512 bytes / 512 bytes
    Disk identifier: 0x00087dae
    Device Boot      Start         End      Blocks   Id  System
    /dev/sda1   *           1          64      512000   83  Linux
    Partition 1 does not end on cylinder boundary.
    /dev/sda2              64        2611    20458496   8e  Linux LVM
    Disk /dev/sdb: 21.5 GB, 21474836480 bytes
    255 heads, 63 sectors/track, 2610 cylinders
    Units = cylinders of 16065 * 512 = 8225280 bytes
    10.2.5在master和backup中做分区操作(注:两台一样)
    因此、我们需要做的就是对/dev/sdb 进行分区、需要分区具体内容见下表

    Device Mount point 存储大小 作用
    /dev/sdb1 /data 500M 存储图片
    /dev/sdb2 Meta data 分区 300M 存储DRBD同步状态信息
  • [root@Lserver-1 ~]# fdisk /dev/sdbDevice contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
    Building a new DOS disklabel with disk identifier 0x95767900.
    Changes will remain in memory only, until you decide to write them.
    After that, of course, the previous content won’t be recoverable.
    Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)
    WARNING: DOS-compatible mode is deprecated. It’s strongly recommended to
    switch off the mode (command ‘c’) and change display units to
    sectors (command ‘u’).
    Command (m for help): n
    Command action
    e   extended
    p   primary partition (1-4)
    p   ####新建一个主分区
    Partition number (1-4): 1
    First cylinder (1-2610, default 1):
    Using default value 1
    Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610): +500M  ####大小为500M
    Command (m for help): p
    Disk /dev/sdb: 21.5 GB, 21474836480 bytes
    255 heads, 63 sectors/track, 2610 cylinders
    Units = cylinders of 16065 * 512 = 8225280 bytes
    Sector size (logical/physical): 512 bytes / 512 bytes
    I/O size (minimum/optimal): 512 bytes / 512 bytes
    Disk identifier: 0x95767900
    Device Boot      Start         End      Blocks   Id  System
    /dev/sdb1               1          65      522081   83  Linux
    Command (m for help): n
    Command action
    e   extended
    p   primary partition (1-4)
    p
    Partition number (1-4): 2
    First cylinder (66-2610, default 66):
    Using default value 66
    Last cylinder, +cylinders or +size{K,M,G} (66-2610, default 2610): +200M ####新建一个200M
    Command (m for help): p
    Disk /dev/sdb: 21.5 GB, 21474836480 bytes
    255 heads, 63 sectors/track, 2610 cylinders
    Units = cylinders of 16065 * 512 = 8225280 bytes
    Sector size (logical/physical): 512 bytes / 512 bytes
    I/O size (minimum/optimal): 512 bytes / 512 bytes
    Disk identifier: 0x95767900
     
    Device Boot      Start         End      Blocks   Id  System
    /dev/sdb1               1          65      522081   83  Linux
    /dev/sdb2              66          91      208845   83  Linux
    Command (m for help):w  ######表示保存
    如果提示
    the kernel still uses the old table
    The new table will be used at next reboot
    上面这句话的意思是内核还不知道你做了分区,需要重启才能让内核知道,可以用如下命令让内核知晓
    partprobe
    现在查看一下分区的结果
    [root@Lserver-1 ~]# fdisk -l
    Disk /dev/sda: 21.5 GB, 21474836480 bytes
    255 heads, 63 sectors/track, 2610 cylinders
    Units = cylinders of 16065 * 512 = 8225280 bytes
    Sector size (logical/physical): 512 bytes / 512 bytes
    I/O size (minimum/optimal): 512 bytes / 512 bytes
    Disk identifier: 0x00087dae
    Device Boot      Start         End      Blocks   Id  System
    /dev/sda1   *           1          64      512000   83  Linux
    Partition 1 does not end on cylinder boundary.
    /dev/sda2              64        2611    20458496   8e  Linux LVM
    Disk /dev/sdb: 21.5 GB, 21474836480 bytes
    255 heads, 63 sectors/track, 2610 cylinders
    Units = cylinders of 16065 * 512 = 8225280 bytes
    Sector size (logical/physical): 512 bytes / 512 bytes
    I/O size (minimum/optimal): 512 bytes / 512 bytes
    Disk identifier: 0x95767900
    Device Boot      Start         End      Blocks   Id  System
    /dev/sdb1               1          65      522081   83  Linux
    /dev/sdb2              66          91      208845   83  Linux
    现在对数据分区格式化
    [root@Rserver-1 ~]# mkfs.ext4 /dev/sdb1
    [root@Lserver-1 ~]# mkfs.ext4 /dev/sdb1
    [root@Rserver-1 ~]# tune2fs -c -1 /dev/sdb1
    tune2fs 1.41.12 (17-May-2010)
    Setting maximal mount count to -1   ####设置最大挂载次数为-1
    [root@Lserver-1 ~]# tune2fs -c -1 /dev/sdb1
    tune2fs 1.41.12 (17-May-2010)
    Setting maximal mount count to -1  ####设置最大挂载次数为-1
    10.2.6、安装前准备:(Rserver-1,Lserver-1) 1、关闭iptables和SELINUX,避免安装过程中报错。
    # service iptables stop
    # chkconfig iptables off
    # setenforce 0
    # vi /etc/selinux/config
    —————
    SELINUX=disabled
    —————
    10.2.7时间同步: ntpdate -u asia.pool.ntp.org
    10.2.8 DRBD的安装配置: # yum install gcc gcc-c++ make glibc flex kernel-devel kernel-headers  提示这里的版本一定跟uname –r 里面的版本一样不然是不行的。
    yum 安装drbd
    rpm -ivh http://www.elrepo.org/elrepo-release-6-6.el6.elrepo.noarch.rpm
    yum -y install drbd83-utils kmod-drbd83
    10.2.9安装DRBD:(Rserver-1主,Lserver-1备) # wget http://oss.linbit.com/drbd/8.4/drbd-8.4.2.tar.gz # tar zxvf drbd-8.4.3.tar.gz
    # cd drbd-8.4.3
    # ./configure –prefix=/usr/local/drbd –with-km –with-heartbeat –sysconfdir=/etc/
    # make KDIR=/usr/src/kernels/2.6.32-504.16.2.el6.x86_64/
    # make install
    # mkdir -p /usr/local/drbd/var/run/drbd
    # chkconfig –add drbd
    # chkconfig drbd on 一、加载DRBD模块:(Rserver-1主,Lserver-1备) # modprobe drbd 二、查看DRBD模块是否加载到内核: # lsmod |grep drbd
    drbd 310172 4
    libcrc32c 1246 1 drbd 三、参数配置:(Rserver-1主,Lserver-1备) vi /etc/drbd.conf 清空文件内容,并添加如下配置: resource r0{
    protocol C;
    startup { wfc-timeout 0; degr-wfc-timeout 120;}
    disk { on-io-error detach;}
    net{
    timeout 60;
    connect-int 10;
    ping-int 10;
    max-buffers 2048;
    max-epoch-size 2048;
    }
    syncer { rate 200M;}
    on Rserver-1{     #######on 后面是主机名
    device /dev/drbd0;        #####指定的是一个drbd一个盘
    disk /dev/sdb1;         #####本地磁盘。就是上面分区好的硬盘
    address 172.16.1.1:7788;       ######内网IP
    meta-disk internal;
    }
    on Lserver-1{
    device /dev/drbd0;
    disk /dev/sdb1;
    address 172.16.1.2:7788;
    meta-disk internal;
    }
    } 注:请修改上面配置中的主机名、IP、和disk为自己的具体配置 4,创建DRBD设备并激活r0资源:(Rserver-1主,Lserver-1备) #mknod /dev/drbd0 b 147 0
    #dd if=/dev/zero of=/dev/sdb1 bs=1M count=100
    # drbdadm create-md r0
    等待片刻,显示success表示drbd块创建成功
    Writing meta data…
    initializing activity log
    NOT initializing bitmap
    New drbd meta data block successfully created.
    –== Creating metadata ==–
    As with nodes, we count the total number of devices mirrored by DRBD
    at http://usage.drbd.org.
    The counter works anonymously. It creates a random number to identify
    the device and sends that random number, along with the kernel and
    DRBD version, to usage.drbd.org.
    http://usage.drbd.org/cgi-bin/insert_usage.pl?
    nu=716310175600466686&ru=15741444353112217792&rs=1085704704
    * If you wish to opt out entirely, simply enter ‘no’.
    * To continue, just press [RETURN]
    success
     
      再次输入该命令: # drbdadm create-md r0
    成功激活r0
    [need to type ‘yes’ to confirm] yes
    Writing meta data…
    initializing activity log
    NOT initializing bitmap
    New drbd meta data block successfully created. 5、启动DRBD服务:(Rserver-1主,Lserver-1备) service drbd start 注:需要主从共同启动方能生效 6、查看状态:(Rserver-1主,Lserver-1备) # service drbd status
    drbd driver loaded OK; device status:
    version: 8.4.3 (api:1/proto:86-101)
    GIT-hash: 89a294209144b68adb3ee85a73221f964d3ee515 build by root@drbd1.corp.com, 2015-05-12 21:05:41
    m:res  cs         ro                   ds                         p  mounted     fstype
    0:r0   Connected  Secondary/Secondary  Inconsistent/Inconsistent  C 这里ro:Secondary/Secondary表示两台主机的状态都是备机状态,ds是磁盘状态,显示的状态内容为“Inconsistent不一致”,这是因为DRBD无法判断哪一方为主机,应以哪一方的磁盘数据作为标准。
    7、将drbd1.example.com主机配置为主节点(Rserver-1)
    # drbdsetup /dev/drbd0 primary -force  或者
    drbdadm — –overwrite-data-of-peer primary r0 8、分别查看主从DRBD状态:(Rserver-1) # service drbd status
    drbd driver loaded OK; device status:
    version: 8.4.3 (api:1/proto:86-101)
    GIT-hash: 89a294209144b68adb3ee85a73221f964d3ee515 build by root@Rserver-1, 2017-05-18 13:40:26
    m:res  cs         ro                 ds                 p  mounted     fstype
    0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C (Lserver-1)备 # service drbd status
    drbd driver loaded OK; device status:
    version: 8.4.3 (api:1/proto:86-101)
    GIT-hash: 89a294209144b68adb3ee85a73221f964d3ee515 build by root@Lserver-1, 2017-05-18 13:38:57
    m:res  cs         ro                 ds                 p  mounted     fstype
    0:r0   Connected  Secondary/Primary  UpToDate/UpToDate  C ro在主从服务器上分别显示 Primary/Secondary和Secondary/Primary
    ds显示UpToDate/UpToDate
    表示主从配置成功。
    9、挂载DRBD:(Rserver-1)主
    从刚才的状态上看到mounted和fstype参数为空,所以我们这步开始挂载DRBD到系统目录/store
    # mkfs.ext4 /dev/drbd0
    # mkdir /data
    # mount /dev/drbd0 /data 注:Secondary节点上不允许对DRBD设备进行任何操作,包括挂载;所有的读写操作只能在Primary节点上进行,只有当Primary节点挂掉时,Secondary节点才能提升为Primary节点,并自动挂载DRBD继续工作。
    成功挂载后的DRBD状态:(Rserver-1主)
    [root@Rserver-1 ~]# service drbd status
    drbd driver loaded OK; device status:
    version: 8.4.2 (api:1/proto:86-101)
    GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@Rserver-1, 2017-05-18 13:40:26
    m:res  cs         ro                 ds                 p  mounted  fstype
    0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /data    ext4 10.3配置heartbeat服务 yum install heartbeat –y 10.3.1配置ha.cf cd /usr/share/doc/heartbeat-3.0.4
    ll|grep ha.cf authkeys haresources
    8.4.2.1配置ha.cf 文件
    debugfile /var/log/ha-debug
    logfile /var/log/ha-log
    logfacility     local0
    ####-à以上三行为日志的配置、在你配置时一般不需要改动、
    keepalive 2
    deadtime 30
    warntime 10
    initdead 120
    ###-à以上四行为一些基础的参数、在你配置时一般不需要改动
    #       serial  serialportname …
    mcast eth2 225.0.0.219 694 1 0
    ##-à 此行表示使用多播的方式、需要改动的仅有eth2 改成你的心跳线的网卡
    auto_failback on
    node    Rserver-1  ##-à两台存储server的主机名
    node    Lserver-1  ##-à两台存储server的主机名
    crm     no 10.3.2配置authkeys auth 3
    #1 crc
    #2 sha1 HI!
    3 md5 Hello!
    authkey 文件必须为600 权限。Authkey文件中已经说明了需要配置600 权限
    #       Authentication file.  Must be mode 600 10.3.3配置haresources 添加一行文件 Rserver-1 IPaddr::172.16.1.10/24/eth1 drbddisk::r0 Filesystem::/dev/drbd0::/data::ext4 rsdata 注:

    • Rserver-1 ß为主机名。表示初始化状态会在Rserver-1绑定16.1.10/24
    • Ipaddr ß 为heartbeat配置IP的默认脚本,气候的IP等都是其参数
    • 16.1.10/24/ 为集群VIP 初始化在Rserver-1上 #24为子网掩码这一段配置相当于执行/etc/ha.d/resource.d/IPaddr 172.16.1.10/24/eth1 stop/start 相当于drbdadm down r0 drbdadm up r0
    • Filesystem::/dev/drbd0::/data::ext4 ßdrbd分区挂载到/data 目录、这里相当于执行/etc/ha.d/resource.d/Filesystem /dev/drbd0 /data ext3 stop/start 相当于mount /dev/drbd0 /data
    • Rsdata ß 启动mysql服务脚本。这里相当与/etc/init.d/rsdata stop/start 或者用mysqld 也行的。因为后面编译mysql启动脚本会在/etc/init.d 中的。

    先测试一下heartbeat+drbd的一个正确性。首先把配置文件最后的那个rsdata删除、因为暂时还没有安装mysql [root@Rserver-1 /]# /etc/init.d/heartbeat start
    [root@Rserver-1 /]# ip addr list|grep 172
    inet 172.16.1.1/24 brd 172.16.1.255 scope global eth1
    inet 172.16.1.100/24 brd 172.16.1.255 scope global secondary eth1
    [root@Rserver-1 /]#
     
    /dev/drbd0                         505552   10510    468941   3% /data
    [root@Rserver-1 /]# 关闭主尝试一下备节点的无缝接管。 [root@Rserver-1 /]# /etc/init.d/heartbeat stop
    Stopping High-Availability services: Done.
      查看一下被节点的drbd的状态就可以了 [root@Lserver-1 ha.d]# /etc/init.d/drbd status
    drbd driver loaded OK; device status:
    version: 8.4.2 (api:1/proto:86-101)
    GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@Lserver-1, 2017-05-19 19:45:41
    m:res  cs         ro                 ds                 p  mounted  fstype
    0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /data    ext4
    [root@Lserver-1 ha.d]# 已经显示为主了。并且已经挂载到了/data 目录里面去了。 [root@Lserver-1 ha.d]# ip addr list|grep 172
    inet 172.16.1.2/24 brd 172.16.1.255 scope global eth1
    inet 172.16.1.100/24 brd 172.16.1.255 scope global secondary eth1 ip地址也接管过来了。尝试一下主节点重启后的效果吧  
    [root@Rserver-1 /]# /etc/init.d/heartbeat start
    Starting High-Availability services: INFO:  Resource is stopped
    Done.
     
    [root@Rserver-1 /]# /etc/init.d/drbd status
    drbd driver loaded OK; device status:
    version: 8.4.2 (api:1/proto:86-101)
    GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@Rserver-1, 2017-05-19 19:46:05
    m:res  cs         ro                 ds                 p  mounted  fstype
    0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /data    ext4
    [root@Rserver-1 /]# ip addr list|grep 172
    inet 172.16.1.1/24 brd 172.16.1.255 scope global eth1
    inet 172.16.1.100/24 brd 172.16.1.255 scope global secondary eth1
    [root@Rserver-1 /]#
    看到主节点上已经已经没有问题了。

    10.4 配置mysql

    解决perl编译问题

    echo ‘export LC_ALL=C’>> /etc/profile
    source /etc/profile yum 安装所需的环境
    yum -y install make gcc gcc-c++ gcc-g77 flex bison file libtool libtool-libs autoconf kernel-devel libjpeg libjpeg-devel libpng libpng-devel libpng10 libpng10-devel gd gd-devel freetype freetype-devel libxml2 libxml2-devel zlib zlib-devel glib2 glib2-devel bzip2 bzip2-devel libevent libevent-devel ncurses ncurses-devel curl curl-devel e2fsprogs e2fsprogs-devel krb5 krb5-devel libidn libidn-devel openssl openssl-devel gettext gettext-devel ncurses-devel gmp-devel pspell-devel unzip libcap lsof

    10.4.1、安装MySQL5.6.17

    #创建群组
    groupadd mysql
    #创建一个用户,不允许登陆和不创主目录
    useradd -s /sbin/nologin -g mysql -M mysql
    #检查刚创建的用户
    tail -1 /etc/passwd
    centos最小化安装后,会有mysql的库因此先卸载!
    10.4.2检查安装与否
    rpm -qa|grep mysql
    #强制卸载
    rpm -erpm -e mysql-libs-5.1.73-3.el6_5.x86_64 –nodeps

    10.4.3安装cmake

    wget http://www.cmake.org/files/v2.8/cmake-2.8.12.2.tar.gz
    tar zxvf cmake-2.8.12.2.tar.gz
    cd cmake-2.8.12.2
    ./configure
    make && make install

    10.4.5编译mysql

    wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.17.tar.gz
    cmake \
    -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
    -DSYSCONFDIR=/etc \
    -DWITH_MYISAM_STORAGE_ENGINE=1 \
    -DWITH_INNOBASE_STORAGE_ENGINE=1 \
    -DWITH_MEMORY_STORAGE_ENGINE=1 \
    -DWITH_READLINE=1 \
    -DMYSQL_UNIX_ADDR=/var/lib/mysql/mysql.sock \
    -DMYSQL_TCP_PORT=3306 \
    -DENABLED_LOCAL_INFILE=1 \
    -DWITH_PARTITION_STORAGE_ENGINE=1 \
    -DEXTRA_CHARSETS=all \
    -DDEFAULT_CHARSET=utf8 \
    -DDEFAULT_COLLATION=utf8_general_ci \
    -DMYSQL_USER=mysql \
    -DWITH_DEBUG=0 \
    -DWITH_SSL=system
    make && make install
     
     
    chown -R mysql:mysql /usr/local/mysql
    cd support-files/
    #如果还有my.cnf请备份
    mv /etc/my.cnf /etc/my.cnf.bak
    cp my-default.cnf /etc/my.cnf
    #拷贝mysql安装目录下support-files服务脚本到init.d目录
    cp support-files/mysql.server /etc/init.d/mysqld
    把脚本里面的datadir=/usr/local/mysql/data 改成datadir=/data/3306/data
    chmod +x /etc/init.d/mysqld
    #修改/etc/profile文件
    vim /etc/profile
    #在文件末尾添加
    PATH=/usr/local/mysql/bin:$PATH
    export PATH
    #让配置立即生效
    source /etc/profile

    以下的操作不需要备节点只要主节点操作就行了。

    10.4.6查看一下主节点的挂载位置看看有没有挂载到/data 下面去

    [root@Rserver-1 data]# df |grep data
    /dev/drbd0                         505552  123148    356303  26% /data
    10.6.7主节点已经挂载到了/data 目录中
    mkdir /data/3306/data -p 如果是多个mysql可以根据mysql多实例来建立
    vim /data/3306/my.cnf
    [mysqld]
     
    # Remove leading # and set to the amount of RAM for the most important data
    # cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
    # innodb_buffer_pool_size = 128M
     
    # Remove leading # to turn on a very important data integrity option: logging
    # changes to the binary log between backups.
    # log_bin
     
    # These are commonly set, remove the # and set as required.
    basedir =/usr/local/mysql
    datadir=/data/3306/data
    port =3306
    server_id = 1
    socket =/data/3306/mysql.sock
    log-bin=/data/3306/mysql-bin
    lower_case_table_names=1
    innodb_file_per_table=1
    port = 3306
    socket = /data/3306/mysql.sock
    back_log = 50
    max_connections = 300
    max_connect_errors = 1000
    table_open_cache = 2048
    max_allowed_packet = 16M
    binlog_cache_size = 2M
    max_heap_table_size = 64M
    sort_buffer_size = 2M
    join_buffer_size = 2M
    thread_cache_size = 64
    thread_concurrency = 8
    query_cache_size = 64M
    query_cache_limit = 2M
    ft_min_word_len = 4
    default-storage-engine = innodb
    thread_stack = 192K
    transaction_isolation = REPEATABLE-READ
    tmp_table_size = 64M
    binlog_format=mixed
    slow_query_log
    long_query_time = 1
    key_buffer_size = 8M
    read_buffer_size = 2M
    # Remove leading # to set options mainly useful for reporting servers.
    # The server defaults are faster for transactions and fast SELECTs.
    # Adjust sizes as needed, experiment to find the optimal values.
    # join_buffer_size = 128M
    # sort_buffer_size = 2M
    # read_rnd_buffer_size = 2M
     
    sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

    这是编译mysql的默认的配置文件。我这边只是稍微修改了一下。这边只是一个测试的情况如果生产环境中最好还是按照生产环境中的实际需求定一下。

    初始化数据库

    /usr/local/mysql/scripts/mysql_install_db –basedir=/usr/local/mysql  –datadir=/data/3306/data/   –user=mysql

    10.6.8启动一下

    /usr/local/mysql/bin/mysqld_safe –defaults-file=/data/3306/my.cnf &
    或者也可用用/etc/init.d/mysqld start

    这里我自己写了一个脚本

    #!/bin/sh
    basedir=/usr/local/mysql
    datadir=/data/3306/data
    . /etc/init.d/functions
     
    [ ! -f $basedir/bin/mysqld_safe ] && exit 1
     
    case “$1” in
    start)
    echo $echo_n “Starting MySQL”
    $basedir/bin/mysqld_safe –defaults-file=/data/3306/my.cnf  >/dev/null 2>&1 &
    if [ ! -f /data/3306/mysql.sock  ];then
    action “启动:”  /bin/true
    else
    action “失败:” /bin/false
    fi
    ;;
    stop)
    killall mysqld >/dev/null 2>&1
    sleep 1
    if [  -f /data/3306/mysql.sock  ];then
    killall mysqld >/dev/null 2>&1
    sleep 1
    killall mysqld >/dev/null 2>&1
    else
    action “停止 mysql:” /bin/true
    fi
    ;;
    *)
    echo “请选择参数!!!”
    echo “$Usage:$0 {start|stop|restart}”
    exit 1
    ;;
    Esac
     

    10.6.9查看端口

    [root@Rserver-1 data]# netstat -nltp|grep 3306
    tcp        0      0 :::3306                     :::*                        LISTEN      78974/mysqld
    [root@Rserver-1 data]#
    [root@Rserver-1 data]# ps -ef |grep mysqld
    root      78859      1  0 03:42 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe –datadir=/data/3306/data –pid-file=/data/3306/data/Rserver-1.pid
    mysql     78974  78859  0 03:42 ?        00:00:00 /usr/local/mysql/bin/mysqld –basedir=/usr/local/mysql –datadir=/data/3306/data –plugin-dir=/usr/local/mysql/lib/plugin –user=mysql –log-error=/data/3306/data/Rserver-1.err –pid-file=/data/3306/data/Rserver-1.pid
    root      79020   2459  0 03:47 pts/1    00:00:00 grep mysqld

    10.6.10修改一下密码

    mysqladmin –uroot password  ‘123456’
    [root@Rserver-1 data]# mysql -uroot -p’123456′
    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 1
    Server version: 5.6.17 Source distribution
     
    Copyright (c) 2000, 2014, 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>
    mysql>
    mysql> create database liang;
    Query OK, 1 row affected (0.00 sec)
     
    mysql>

    10.7 测试

    10.7.1 这里新建一个数据库到后面看看备节点上有没有这个数据库

    设置heartbeat haresource文件
    前面我们后面没有添加脚本文件,我们这边添加的脚本文件是mysqld 这个文件,现在修改一下吧,
    Rserver-1 IPaddr::172.16.1.10/24/eth1 drbddisk::r0 Filesystem::/dev/drbd0::/data::ext4 mysqld

    10.7.2把mysql关闭掉。

    [root@Rserver-1 data]# /etc/init.d/mysqld stop
    Shutting down MySQL.[  OK  ]
    [root@Rserver-1 data]#
    [root@Rserver-1 data]# /etc/init.d/heartbeat stop
    Stopping High-Availability services: Terminated
    [root@Rserver-1 data]#

    10.7.3现在启动一下heartbeat 看看mysql是否启动

    查看drbd [root@Rserver-1 ~]# /etc/init.d/drbd status
    drbd driver loaded OK; device status:
    version: 8.4.2 (api:1/proto:86-101)
    GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@Rserver-1, 2017-05-24 01:26:36
    m:res  cs         ro                 ds                 p  mounted  fstype
    0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /data    ext4
    [root@Rserver-1 ~]#

    10.7.4查看VIP

    [root@Rserver-1 ~]# /etc/init.d/drbd status
    drbd driver loaded OK; device status:
    version: 8.4.2 (api:1/proto:86-101)
    GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@Rserver-1, 2017-05-24 01:26:36
    m:res  cs         ro                 ds                 p  mounted  fstype
    0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /data    ext4
    [root@Rserver-1 ~]#

    10.7.5查看mysql

    [root@Rserver-1 ~]# netstat -nltp|grep mysqld
    tcp        0      0 :::3306                     :::*                        LISTEN      80715/mysqld
    [root@Rserver-1 ~]#
    结论:通过上面的查看我们已经知道了。Heartbeat已经完成了自动启动的所有步骤,
    现在模拟一下宕机的情况。
    主关闭了heartbeat
    [root@Rserver-1 ~]# /etc/init.d/heartbeat stop
    Stopping High-Availability services: Done.
     
    [root@Rserver-1 ~]#

    10.7.6现在查看一下备节点的状况。

    查看备节点VIP
    [root@Lserver-1 ha.d]# ip addr list|grep 172
    inet 172.16.1.2/24 brd 172.16.1.255 scope global eth1
    inet 172.16.1.10/24 brd 172.16.1.255 scope global secondary eth1
    查看备节点的DRBD
    [root@Lserver-1 ha.d]# /etc/init.d/drbd status
    drbd driver loaded OK; device status:
    version: 8.4.2 (api:1/proto:86-101)
    GIT-hash: 7ad5f850d711223713d6dcadc3dd48860321070c build by root@Lserver-1, 2017-05-24 01:25:31
    m:res  cs         ro                 ds                 p  mounted  fstype
    0:r0   Connected  Primary/Secondary  UpToDate/UpToDate  C  /data    ext4 10.7.7查看备节点的mysql [root@Lserver-1 ha.d]# netstat -nltp|grep 3306
    tcp        0      0 :::3306                     :::*                        LISTEN      36123/mysqld
    [root@Lserver-1 ha.d]# 10.7.8进去查看一下有没有liang的数据库 [root@Lserver-1 ha.d]# mysql -uroot -p’123456′ –S /data/33306/mysql.sock
    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 1
    Server version: 5.6.17 Source distribution
    Copyright (c) 2000, 2014, 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 |
    | liang              |
    | mysql              |
    | performance_schema |
    | test               |
    +——————–+
    5 rows in set (0.01 sec)
    mysql>
    结论:通过上述的试验我们可以得到一个结论heartbeat+DRBD+mysql实现高可用的情况是比较稳定的,可以保证了数据的不丢失和文件的稳定性,上面已经做了两台主节点的配置现在做一下从库的配置,从库的配置是比较简单操作的。
      10.8 从库的配置 从库的IP设置为172.16.1.3 最好设置为内网的IP。 从库mysql安装也和上面一样的。 mysql> use liang
    Database changed
    mysql> create table liang(namg int(20));
    Query OK, 0 rows affected (0.11 sec)
    mysql> grant all on *.* to liang@’172.16.1.%’ identified by ‘123456’;
    Query OK, 0 rows affected (0.01 sec)
    mysql> select user,host from mysql.user;
    +——-+————+
    | user  | host       |
    +——-+————+
    | root  | 127.0.0.1  |
    | liang | 172.16.1.% |
    | root  | ::1        |
    |       | localhost  |
    | root  | localhost  |
    |       | rserver-1  |
    | root  | rserver-1  |
    +——-+————+
    7 rows in set (0.00 sec)
    查看一下bin-log

  • [root@Rserver-1 ~]# mysqldump -uroot -p’123456′ –S /data/3306/mysql.sock -A -B >/root/mysql.sqlWarning: Using a password on the command line interface can be insecure.
    [root@Rserver-1 ~]#
    [root@Rserver-1 ~]# 打包数据。发送到备节点。 scp /root/mysql.sql root@192.168.236.215:~ 不管是用scp还是其他的方式,只要把mysql导出的文件传到从库就可以了。 从从库导入到进去 [root@Lserver-1 3306]# mysql -uroot -p’123456′ -S /data/3306/mysql.sock </root/mysql.sql
    Warning: Using a password on the command line interface can be insecure.
    mysql> CHANGE MASTER TO
    -> MASTER_HOST=’172.16.1.10′,
    -> MASTER_PORT=3306,
    -> MASTER_USER=’liang’,
    ->  MASTER_PASSWORD=’123456′,
    -> MASTER_LOG_FILE=’mysql-bin.000002′,
    -> MASTER_LOG_POS=120;
    Query OK, 0 rows affected, 2 warnings (0.02 sec)
     
    mysql> start slave
    -> ;
    Query OK, 0 rows affected (0.01 sec)
     
    mysql> show slave status\G;
    *************************** 1. row ***************************
    Slave_IO_State: Connecting to master
    Master_Host: 172.16.1.10
    Master_User: liang
    Master_Port: 3306
    Connect_Retry: 60
    Master_Log_File: mysql-bin.000002
    Read_Master_Log_Pos: 120
    Relay_Log_File: Lserver-1-relay-bin.000001
    Relay_Log_Pos: 4
    Relay_Master_Log_File: mysql-bin.000002
    Slave_IO_Running: yes
    Slave_SQL_Running: Yes
    Replicate_Do_DB:
    Replicate_Ignore_DB:
    Replicate_Do_Table:
    Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
    Replicate_Wild_Ignore_Table:
    Last_Errno: 0
    Last_Error:
    Skip_Counter: 0
    Exec_Master_Log_Pos: 120
    Relay_Log_Space: 120
    Until_Condition: None
    Until_Log_File:
    Until_Log_Pos: 0
    Master_SSL_Allowed: No
    Master_SSL_CA_File:
    Master_SSL_CA_Path:
    Master_SSL_Cert:
    Master_SSL_Cipher:
    Master_SSL_Key:
    Seconds_Behind_Master: NULL
    Master_SSL_Verify_Server_Cert: No
    Last_IO_Errno: 0
    Last_IO_Error:
    Last_SQL_Errno: 0
    Last_SQL_Error:
    Replicate_Ignore_Server_Ids:
    Master_Server_Id: 0
    Master_UUID:
    Master_Info_File: /data/3306/data/master.info
    SQL_Delay: 0
    SQL_Remaining_Delay: NULL
    Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
    Master_Retry_Count: 86400
    Master_Bind:
    Last_IO_Error_Timestamp:
    Last_SQL_Error_Timestamp:
    Master_SSL_Crl:
    Master_SSL_Crlpath:
    Retrieved_Gtid_Set:
    Executed_Gtid_Set:
    Auto_Position: 0
    1 row in set (0.00 sec)
  • 测试一下主从的状况,在主端新建一个数据库尝试一下mysql>
    mysql> create database cc;
    Query OK, 1 row affected (0.02 sec)
     
    mysql> 在从库查看一下。 mysql> show databases;
    +——————–+
    | Database           |
    +——————–+
    | information_schema |
    | cc                 |
    | liang              |
    | mysql              |
    | performance_schema |
    | test               |
    +——————–+
    6 rows in set (0.01 sec)
     
    mysql> 从库已经显示有了。现在宕掉主的尝试一下。 看看从库如何同步的。 [root@Rserver-1 3306]# /etc/init.d/heartbeat stop
    Stopping High-Availability services: 170524 06:01:22 mysqld_safe mysqld from pid file /data/3306/data/Rserver-1.pid ended
    [1]+  Done                    /usr/local/mysql/bin/mysqld_safe –defaults-file=/data/3306/my.cnf
    Terminated
    [root@Rserver-1 3306]#
    主库已经关闭了。查看一下备库的VIP和挂载
    root@Lerver-1 log]# ip addr list|grep  172
    inet 172.16.1.2/24 brd 172.16.1.255 scope global eth1
    inet 172.16.1.10/24 brd 172.16.1.255 scope global secondary eth1
    [root@Lserver-1 log]# df|grep data
    /dev/drbd0                         505552  123313    356138  26% /data
    [root@Lserver-1 log]# 查看一下从库的状态
  • 这里需要特别说明下,因为在heartbeat设置接管时间为30秒,mysql从库从新连接的时间为60秒。一般等待个一分钟就会自动同步的。
  • 好了已经同步完成了。再备库中建立一个数据库看看有没有数据同步。 mysql> create database ll;
    Query OK, 1 row affected (0.00 sec)
     
    mysql>
    查看一下从库的的同步情况
     
    mysql> show databases;
    +——————–+
    | Database           |
    +——————–+
    | information_schema |
    | cc                 |
    | liang              |
    | ll                 |
    | mysql              |
    | performance_schema |
    | test               |
    +——————–+
    7 rows in set (0.00 sec)
      已经成功了,



如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!

发表评论

电子邮件地址不会被公开。 必填项已用*标注