MySQL读写分离

于由宇宙最帅的男人发布

本文最后更新于 2020 年 3 月 9 日，文章已超过 6 个月！内容可能已失效，请自行测试 ~

主机列表：

MySQL Master(dbm1) 192.168.230.101
MySQL Master(dbm2) 192.168.230.102
VIP1 192.168.230.140
MySQL Slave(dbs1) 192.168.230.103
MySQL Slave(dbs2) 192.168.230.104
MySQL Slave(dbs3) 192.168.230.105
Lvs1 192.168.230.106
Lvs2 192.168.230.107
VIP2 192.168.230.125


安装DRBD及heartdeta

[root@dbm1 ~]# fdisk /dev/sdb
[root@dbm1 ~]# vim /etc/hosts
192.168.230.101 dbm1
192.168.230.102 dbm2
[root@dbm1 ~]# yum -y install perl-TimeDate kernel-devel kernel-headers flex cluster-glue-libs glib2-devel libxml2 libxml2-devel bzip2-devel e2fsprogs-devel libxslt-devel libtool-ltdl-devel make wget docbook-dtds docbook-style-xsl bzip2-devel libuuid-devel
[root@dbm1 ~]# wget --no-check-certificate http://ym68.cc/download/asciidoc-8.4.5-4.1.el6.noarch.rpm
[root@dbm1 ~]# wget --no-check-certificate http://ym68.cc/download/Heartbeat-3.0.tar.bz2
[root@dbm1 ~]# wget --no-check-certificate http://ym68.cc/download/Cluster_Glue_1.0.12.tar.bz2
[root@dbm1 ~]# wget --no-check-certificate http://ym68.cc/download/resource-agents-3.9.6.tar.gz
[root@dbm1 ~]# wget --no-check-certificate http://ym68.cc/download/drbd-8.4.3.tar.gz
[root@dbm1 ~]# tar xf drbd-8.4.3.tar.gz
[root@dbm1 ~]# tar xf Cluster_Glue_1.0.12.tar.bz2
[root@dbm1 ~]# tar xf Heartbeat-3.0.tar.bz2 
[root@dbm1 ~]# tar xf resource-agents-3.9.6.tar.gz
[root@dbm1 ~]# rpm -ivh asciidoc-8.4.5-4.1.el6.noarch.rpm
[root@dbm1 ~]# groupadd haclient
[root@dbm1 ~]# useradd -M -g haclient -s /sbin/nologin hacluster
[root@dbm1 ~]# cd Reusable-Cluster-Components-glue--0a7add1d9996/
[root@dbm1 Reusable-Cluster-Components-glue--0a7add1d9996]# ./autogen.sh
[root@dbm1 Reusable-Cluster-Components-glue--0a7add1d9996]# ./configure --prefix=/usr/local/heartbeat &&make &&make install
[root@dbm1 Reusable-Cluster-Components-glue--0a7add1d9996]# cd ../resource-agents-3.9.6
[root@dbm1 resource-agents-3.9.6]# ./autogen.sh 
[root@dbm1 resource-agents-3.9.6]# ./configure --prefix=/usr/local/heartbeat/
[root@dbm1 resource-agents-3.9.6]# ln -s /usr/local/heartbeat/lib/* /lib64/
[root@dbm1 resource-agents-3.9.6]# ln -s /usr/local/heartbeat/lib/* /lib/
[root@dbm1 resource-agents-3.9.6]# make &&make install
[root@dbm1 resource-agents-3.9.6]# cd ../Heartbeat-3-0-958e11be8686
[root@dbm1 Heartbeat-3-0-958e11be8686]# ./bootstrap
[root@dbm1 Heartbeat-3-0-958e11be8686]# export CFLAGS="$CFLAGS -I/usr/local/heartbeat/include -L/usr/local/heartbeat/lib"
[root@dbm1 Heartbeat-3-0-958e11be8686]# ./configure --prefix=/usr/local/heartbeat/
[root@dbm1 Heartbeat-3-0-958e11be8686]# sed -i 's#\#define HA_HBCONF_DIR "/usr/local/heartbeat/etc/ha.d/"#/* & */#gp' /usr/local/heartbeat/include/heartbeat/glue_config.h
[root@dbm1 Heartbeat-3-0-958e11be8686]# make &&make install
[root@dbm1 Heartbeat-3-0-958e11be8686]# cd ../drbd-8.4.3
[root@dbm1 drbd-8.4.3]# ./configure --prefix=/usr/local/drbd --with-km --with-heartbeat &&make KDIR=/usr/src/kernels/2.6.32-696.el6.x86_64/
[root@dbm1 drbd-8.4.3]# make &&make install
[root@dbm1 drbd-8.4.3]# mkdir -p /usr/local/drbd/var/run/drbd
[root@dbm1 drbd-8.4.3]# cp /usr/local/drbd/etc/rc.d/init.d/drbd /etc/init.d/
[root@dbm1 drbd-8.4.3]# chkconfig --add drbd
[root@dbm1 drbd-8.4.3]# cd drbd
[root@dbm1 drbd]# make clean
[root@dbm1 drbd]# make KDIR=/usr/src/kernels/2.6.32-696.el6.x86_64/
[root@dbm1 drbd]# cp drbd.ko /lib/modules/2.6.32-696.el6.x86_64/kernel/lib/
[root@dbm1 drbd]# depmod
[root@dbm1 drbd]# cd /usr/local/drbd/etc/drbd.d/
[root@dbm1 drbd.d]# cp global_common.conf{,-$(date +%s)}

配置DRBD

[root@dbm1 drbd.d]# vim global_common.conf
global {
        usage-count yes;
}

common {
        handlers {
        }
        startup {
        wfc-timeout 120;
        degr-wfc-timeout 120;
        }

        options { 
        }

        disk {  
        on-io-error detach;
        }

        net {   
        protocol C;
        }
}
[root@dbm1 drbd.d]# vim r0.res
resource r0 {
        on dbm1 {
                device /dev/drbd0;
                disk    /dev/sdb1;
                address 192.168.230.101:7788;
                meta-disk internal;
        }
        on dbm2 {
                device /dev/drbd0;
                disk    /dev/sdb1;
                address 192.168.230.102:7788;
                meta-disk internal;
        }
}
[root@dbm1 drbd.d]# modprobe drbd
WARNING: Deprecated config file /etc/modprobe.conf, all config files belong into /etc/modprobe.d/.
[root@dbm1 drbd.d]# dd if=/dev/zero bs=1M count=1 of=/dev/sdb1
[root@dbm1 drbd.d]# drbdadm create-md r0
[root@dbm1 drbd.d]# /etc/init.d/drbd start

dbm1执行

[root@dbm1 drbd.d]# drbdadm -- --overwrite-data-of-peer primary r0
[root@dbm1 drbd.d]# cat /proc/drbd 
version: 8.4.3 (api:1/proto:86-101)
GIT-hash: 89a294209144b68adb3ee85a73221f964d3ee515 build by root@dbm1, 2018-07-07 21:16:39
 0: cs:SyncSource ro:Primary/Secondary ds:UpToDate/Inconsistent C r---n-
    ns:125584 nr:0 dw:0 dr:127648 al:0 bm:7 lo:0 pe:1 ua:2 ap:0 ep:1 wo:f oos:20839188
	[>....................] sync'ed:  0.7% (20348/20472)M
	finish: 0:13:52 speed: 24,984 (24,984) K/sec
[root@dbm1 drbd.d]# mkfs.ext4 /dev/drbd0 
[root@dbm1 drbd.d]# mkdir /mysqldata
[root@dbm1 drbd.d]# mount /dev/drbd0 /mysqldata/

配置MySQL

[root@dbs1 ~]# yum -y install mysql mysql-server
[root@dbm1 ~]# vim /etc/my.cnf
[mysqld]
datadir=/mysqldata/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/lib/mysql/mysql.err
log = /var/lib/mysql/query_log.log
log-slow-queries = /var/lib/mysql/slow_query_log.log
user=mysql
default-character-set=utf8
log-bin=mysql-bin
server-id=1		#ID不能相同
binlog-ignore-db=mysql,information_schema
log-slave-updates
sync_binlog=1
auto_increment_increment=2
auto_increment_offset=1
[client]
default-character-set=utf8
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@dbm1 ~]# service mysqld start

MySQL双主

[root@dbm1 ~]# mysql
mysql> grant replication slave on *.* to 'replication'@'192.168.230.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='192.168.230.102',master_user='replication',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=106;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.230.102
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 598
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 497
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

[root@dbm1 ~]# chkconfig mysqld on
[root@dbm2 ~]# mysql
mysql> change master to master_host='192.168.230.101',master_user='replication',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=106;
Query OK, 0 rows affected (0.06 sec)

MySQL3从

[root@dbs1 ~]# yum -y install mysql mysql-server
[root@dbs1 ~]# vim /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=mysql-bin
server-id=3
log-slave-updates
sync_binlog=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
[root@dbs1 ~]# service mysqld start
[root@dbs1 ~]# mysql
mysql> change master to master_host='192.168.230.101',master_user='replication',master_password='123456',master_log_file='mysql-bin.000003',master_log_pos=598;
Query OK, 0 rows affected (0.02 sec)

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

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.230.101
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 598
               Relay_Log_File: mysqld-relay-bin.000002
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

配置heartbeat

[root@dbm1 ~]# cd /usr/local/heartbeat/share/doc/heartbeat/
[root@dbm1 heartbeat]# cp ha.cf authkeys haresources /usr/local/heartbeat/etc/ha.d/
[root@dbm1 heartbeat]# cd /usr/local/heartbeat/etc/ha.d/
[root@dbm1 ha.d]# vim ha.cf 
logfile /var/log/ha-log
logfacility     local0
keepalive 2
deadtime 10
warntime 5
initdead 100
udpport 694
ucast eth0 192.168.230.102
auto_failback on
node    dbm1
node    dbm2
respawn hacluster /usr/local/heartbeat/libexec/heartbeat/ipfail

[root@dbm1 ha.d]# vim haresources
dbm1 IPaddr::192.168.230.140/24/eth0:0 drbddisk::r0 Filesystem::/dev/drbd0::/mysqldata::ext4 mysqld
[root@dbm1 ha.d]# ln -s /etc/init.d/mysqld /usr/local/heartbeat/etc/ha.d/resource.d/
[root@dbm1 ha.d]# vim authkeys
auth 1
1 crc
[root@dbm1 ha.d]# chmod 600 authkeys 
[root@dbm1 ha.d]# service heartbeat start
Starting High-Availability services: INFO:  Resource is stopped
                                                           [FAILED]
heartbeat: udpport setting must precede media statementsMay 30 21:16:47 dbm1 heartbeat: [41213]: ERROR: Illegal directive [ucast] in /usr/local/heartbeat/etc/ha.d/ha.cf
May 30 21:16:47 dbm1 heartbeat: [41213]: ERROR: Heartbeat not started: configuration error.
May 30 21:16:47 dbm1 heartbeat: [41213]: ERROR: Configuration error, heartbeat not started.

[root@dbm1 ha.d]# ln -s /usr/local/heartbeat/lib64/heartbeat/plugins/* /usr/local/heartbeat/lib/heartbeat/plugins/
[root@dbm1 ha.d]# service heartbeat start
Starting High-Availability services: INFO:  Resource is stopped
                                                           [  OK  ]

Lvs1

[root@lvs1 ~]# modprobe ip_vs
[root@lvs1 ~]# yum -y install ipvsadm kernel-devel openssl-devel popt-devel
[root@lvs1 ~]# wget --no-check-certificate https://ym68.cc/download/keepalived-2.0.5.tar.gz
[root@lvs1 ~]# tar xf  keepalived-2.0.5.tar.gz
[root@lvs1 ~]# cd keepalived-2.0.5
[root@lvs1 keepalived-2.0.5]# ./configure --prefix=/ --with-kernel-dir=/usr/src/kernels/2.6.32-696.el6.x86_64/ &&make &&make install
[root@lvs1 keepalived-2.0.5]# chkconfig --add keepalived
[root@lvs1 keepalived-2.0.5]# chkconfig keepalived on
[root@lvs1 keepalived-2.0.5]# cd /etc/keepalived/
[root@lvs1 keepalived]# cp keepalived.conf{,.bak}

配置keepalived主

[root@lvs1 keepalived]# vim keepalived.conf
! Configuration File for keepalived
global_defs {
   router_id LVS_MASTER
}
vrrp_instance VI_1 {
    state MASTER
    interface eth0
    virtual_router_id 51
    priority 100
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.230.125
    }
}
virtual_server 192.168.230.125 3306 {
	delay_loop 6
	lb_algo wrr
	lb_kind DR
	protocol TCP
	real_server 192.168.230.103 3306 {
		weight 1
		TCP_CHECK {
			connect_timeout 10
			nb_get_retry 3
			delay_before_retry 3
			connect_port 3306
			}
		}
        real_server 192.168.230.104 3306 {
                weight 1
                TCP_CHECK {
                        connect_timeout 10
                        nb_get_retry 3
                        delay_before_retry 3
                        connect_port 3306
                        }
                }
        real_server 192.168.230.105 3306 {
                weight 1
                TCP_CHECK {
                        connect_timeout 10
                        nb_get_retry 3
                        delay_before_retry 3
                        connect_port 3306
                        }
                }
}

配置keepalived从

[root@lvs2 keepalived]# vim keepalived.conf
! Configuration File for keepalived
global_defs {
   router_id LVS_SLAVE
}
vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 51
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    virtual_ipaddress {
        192.168.230.125
    }
}
virtual_server 192.168.230.125 3306 {
	delay_loop 6
	lb_algo wrr
	lb_kind DR
	protocol TCP
	real_server 192.168.230.103 3306 {
		weight 1
		TCP_CHECK {
			connect_timeout 10
			nb_get_retry 3
			delay_before_retry 3
			connect_port 3306
			}
		}
        real_server 192.168.230.104 3306 {
                weight 1
                TCP_CHECK {
                        connect_timeout 10
                        nb_get_retry 3
                        delay_before_retry 3
                        connect_port 3306
                        }
                }
        real_server 192.168.230.105 3306 {
                weight 1
                TCP_CHECK {
                        connect_timeout 10
                        nb_get_retry 3
                        delay_before_retry 3
                        connect_port 3306
                        }
                }
}

验证

[root@lvs1 keepalived]# service keepalived restart
停止 keepalived：                                          [确定]
正在启动 keepalived：                                      [确定]
[root@dbs1 ~]# vim ipvs.sh 
#!/bin/bash  
SNS_VIP=192.168.230.125
. /etc/rc.d/init.d/functions  
case "$1" in  
start)         
	ifconfig lo:0 $SNS_VIP netmask 255.255.255.255 broadcast $SNS_VIP         
	/sbin/route add -host $SNS_VIP dev lo:0         
	echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore         
	echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce         
	echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore         
	echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce         
	sysctl -p >/dev/null 2>&1         
	echo "RealServer Start OK"          
	;;  
stop)         
	ifconfig lo:0 down         
	route del $SNS_VIP >/dev/null 2>&1         
	echo "0" >/proc/sys/net/ipv4/conf/lo/arp_ignore         
	echo "0" >/proc/sys/net/ipv4/conf/lo/arp_announce         
	echo "0" >/proc/sys/net/ipv4/conf/all/arp_ignore         
	echo "0" >/proc/sys/net/ipv4/conf/all/arp_announce         
	echo "RealServer Stoped"         
	;;  
	*)         
	echo "Usage: $0 {start|stop}"         
exit 1  
esac  
exit 0

[root@dbs1 ~]# mysql -h192.168.230.125 -utemps -p123456
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 452
Server version: 5.1.73-log Source distribution

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

[root@lvs1 keepalived]# ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
  -> RemoteAddress:Port           Forward Weight ActiveConn InActConn
TCP  192.168.230.125:3306 wrr
  -> 192.168.230.103:3306         Route   1      0          0         
  -> 192.168.230.104:3306         Route   1      0          0         
  -> 192.168.230.105:3306         Route   1      0          0
版权属于：宇宙最帅的男人
本文链接：https://www.ym68.cc/sql_server/mysql/2020/09572.html
作品采用：《 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 》许可协议授权
分类： Mysql
标签：

相关文章

Mysql

Mysql8.0源码安装和基础配置

概述 mysql官网：https://downloads.mysql. 阅读更多…

Mysql

Mysql5.7安装部署及基础配置

概述 用于快捷安装MySQL5.7配置。 安装部署 mysql官网：h 阅读更多…

Mysql

MySQL重建某个用户账号

概述 在MySQL5.7中删除用户信息后无法重建ymcc用户，提示报错 阅读更多…