投稿
  1. 首页
  2. 随笔

MySQL主从原理

绝对值不等式 • 2023-4-6 • 随笔 • 阅读 15

MySQL主从原理,第1张

系统优化：假如现在用户反映系统太卡

1：如果是请求太多，判断web服务器压力过大，增加前端的web服务器，做负载均衡

2：如果请求静态页面不卡了，但是请求动态数据还是卡，说明MySQL处理的请求太多了，在应用层增加缓存

3：如果请求激增，还是有大量的查询到MySQL,想办法处理MySQL ,在一个应用中，大量的消耗MySQL的还是查询，就可以把MySQL拆开，就像公路分道 公交车道，小轿车道，把MySQL做成主从

1,就算MYSQL拆成了多个,也必须分出主和从,所有的写 *** 作都必须要在主MYSQL 上完成

2,所有的从MYSQL的数据都来自于(同步于)主MYSQL

3,既然涉及到同步,那一定有延迟有延迟,就一定可能在读的时候产生脏数据所以,能够在从MYSQL上进行的读 *** 作,一定对实时性和脏数据有一定容忍度的数据比如,登陆日志,后台报表,首页统计信息来源文章资讯SNS消息

4,[注意]:在MYSQL主从时,如果一个业务(service中的一个方法)中,如果既有R *** 作,又有W *** 作,因为W *** 作一定要在主MYSQL上,所以在一个事务中所有的数据来源都只能来自于一个MYSQL

1,要完成主从同步,就必须让在Master上执行的所有的DML和DDL能够正确的在Salve上再执行一遍MYSQL选择使用文件来记录SQL

2,要完成主从同步,第一个事情就是把在主服务器上的bin-log(二进制文件)打开,bin-log文件就可以记录在MYSQL上执行的所有的DML+DDL+TCL

3,MYSQL使用被动注册的方式来让从MYSQL请求同步主MYSQL的binlog原因:被动请求的方式,主的MYSQL不需要知道有哪些从的MYSQL,我额外添加/去掉从MYSQL服务器,对主MYSQL服务器的正常运行没有任何影响

4,第二步,从MYSQL后台一个线程发送一个请求,到主服务器请求更新数据最重要的数据(我这次请求,请求你bin-log的哪一行数据之后的数据)

5,第三步,主MYSQL后台一个线程接收到从MYSQL发送的请求,然后读取bin-log文件中指定的内容,并放在从MYSQL的请求响应中

6,第四步,从MYSQL的请求带回同步的数据,然后写在从MYSQL中的relay-log(重做日志)中relay-log中记录的就是从主MYSQL中请求回来的哪些SQL数据

7,第五步,从MYSQL后台一个线程专门用于从relay-log中读取同步回来的SQL,并写入到从MYSQL中,完成同步

8,MYSQL的主从同步是经过高度优化的,性能非常高

部署完成之后，通过VIP：192.168.21.254，根据LVS调度算法来访问后端真实的MySQL从服务器，实现负载均衡。

具体 *** 作：

第一部分：分别在两台MySQL从服务器上 *** 作

一、关闭SELINUX

vi /etc/selinux/config

#SELINUX=enforcing #注释掉

#SELINUXTYPE=targeted #注释掉

SELINUX=disabled #增加

:wq! #保存退出

setenforce 0 #使配置立即生效

二、配置防火墙，开启3306端口

vi /etc/sysconfig/iptables #编辑

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT #允许3306端口通过防火墙

:wq! #保存退出

/etc/init.d/iptables restart #重启防火墙使配置生效

系统运维 www.osyunwei.com 温馨提醒：qihang01原创内容 版权所有,转载请注明出处及原文链接

三、绑定LVS虚拟服务器（VIP）：192.168.21.254到lo:0

vi /etc/rc.d/init.d/realserver #编辑，添加以下代码

#################################################

#!/bin/sh

# chkconfig: - 80 90

# description:realserver

# mysql_vip start realserver

mysql_vip=192.168.21.254 #LVS虚拟服务器（VIP）

. /etc/rc.d/init.d/functions

case "$1" in

start)

ifconfig lo:0 $mysql_vip netmask 255.255.255.255 broadcast $mysql_vip

/sbin/route add -host $mysql_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 $mysql_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

#################################################

chmod +x /etc/rc.d/init.d/realserver #添加脚本执行权限

chkconfig realserver on #添加开机启动

/etc/rc.d/init.d/realserver start #开启，参数stop为关闭

四、调整服务器参数，使LVS虚拟服务器（VIP）忽略ARP广播包

vi /etc/sysctl.conf #编辑

net.ipv4.ip_forward= 1 #修改0为1，开启转发

net.ipv4.conf.lo.arp_ignore= 1

net.ipv4.conf.lo.arp_announce= 2

net.ipv4.conf.all.arp_ignore= 1

net.ipv4.conf.all.arp_announce= 2

:wq! #保存退出

/sbin/sysctl -p #使配置立即生效

五、设置能够远程监控MySQL主从同步状态的MySQL用户和密码

mysql -u root -p #进入MySQL控制台

insert into mysql.user(Host,User,Password) values('localhost','checkslave',password('123456')) #新建账户checkslave，密码123456

flush privileges #刷新系统授权表

grant all on *.* to 'checkslave'@'192.168.21.129' identified by '123456' with grant option #允许账户checkslave从LVS主服务器192.168.21.129连接到数据库服务器

grant all on *.* to 'checkslave'@'192.168.21.130' identified by '123456' with grant option #允许账户checkslave从LVS从服务器192.168.21.130连接到数据库服务器

第二部分：分别在两台LVS主备服务器上 *** 作

一、关闭SElinux、配置防火墙

1、vi /etc/selinux/config

#SELINUX=enforcing #注释掉

#SELINUXTYPE=targeted #注释掉

SELINUX=disabled #增加

:wq! #保存退出

setenforce 0 #使配置立即生效

2、vi /etc/sysconfig/iptables #编辑

-A RH-Firewall-1-INPUT -d 224.0.0.18 -j ACCEPT #允许VRRP（虚拟路由器冗余协议）组播地址通信

-A RH-Firewall-1-INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT #允许3306端口通过防火墙

:wq! #保存退出

/etc/init.d/iptables restart #重启防火墙使配置生效

二、安装lvs软件

yum install ipvsadm #Linux 2.6内核已经集成了lvs软件，此处ipvsadm为lvs管理工具

modprobe ip_vs #加载ip_vs

三、安装keepalived

yum install gcc gcc-c++ make openssl-devel kernel-devel ncurses-devel #安装编译工具包

下载keeplived：http://www.keepalived.org/software/keepalived-1.2.12.tar.gz

上传keepalived-1.2.12.tar.gz到/usr/local/src目录

cd /usr/local/src

tar zxvf keepalived-1.2.12.tar.gz

cd keepalived-1.2.12

./configure #配置，必须看到以下提示，说明配置正确，才能继续安装

Use IPVS Framework : Yes

IPVS sync daemon support : Yes

Use VRRP Framework : Yes

make #编辑

make install #安装

cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/

mkdir /etc/keepalived

cp /usr/local/etc/keepalived/keepalived.conf /etc/keepalived/

cp /usr/local/sbin/keepalived /usr/sbin/

cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/

chmod +x /etc/rc.d/init.d/keepalived #添加执行权限

chkconfig keepalived on #设置开机启动

service keepalived start #启动

service keepalived stop #关闭

service keepalived restart #重启

四、安装perl、perl-DBI、DBD-mysql模块以及MySQL客户端 #perl脚本连接MySQL数据库需要

yum install perl perl-DBI perl-DBD-MySQL mysql #执行此命令安装

vi /tmp/test_perl.pl

#!/usr/bin/perl

print "Hello, world!\n"

:wq! #保存退出

perl /tmp/test_perl.pl #运行测试脚本，如果出现：Hello,world! 说明perl安装成功

perldoc DBI #查看DBI模块是否安装

perldoc DBD::mysql #查看DBD::mysql模块是否安装

五、配置keepalived

cp /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf-bak

vi /etc/keepalived/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 192.168.200.1

smtp_connect_timeout 30

router_id LVS_DEVEL

}

vrrp_instance VI_1 {

state MASTER #LVS备机修改为BACKUP

interface eth0

virtual_router_id 51

priority 100 #LVS备机修改为80

advert_int 1

authentication {

auth_type PASS

auth_pass 1111

}

virtual_ipaddress {

192.168.21.254

}

notify_master "/etc/keepalived/clean_arp.sh" #LVS主备机都为notify_master

}

virtual_server 192.168.21.254 3306 {

delay_loop 30

lb_algo wlc

lb_kind DR

#nat_mask 255.255.255.0

persistence_timeout 120

protocol TCP

real_server 192.168.21.127 3306 {

weight 1

MISC_CHECK {

misc_path "/etc/keepalived/check_slave.pl 192.168.21.127"

misc_dynamic

}

}

real_server 192.168.21.128 3306 {

weight 1

MISC_CHECK {

misc_path "/etc/keepalived/check_slave.pl 192.168.21.128"

misc_dynamic

}

}

##################################################################################################

:wq! #保存退出

系统运维 www.osyunwei.com 温馨提醒：qihang01原创内容 版权所有,转载请注明出处及原文链接

六、设置MySQL主从监控脚本check_slave.pl

vi /etc/keepalived/check_slave.pl #编辑，添加以下代码

##################################################################################################

#!/usr/bin/perl -w

use DBI

use DBD::mysql

# CONFIG VARIABLES

$SBM = 120

$db = "osyunweidb"

$host = $ARGV[0]

$port = 3306

$user = "checkslave"

$pw = "123456"

# SQL query

$query = "show slave status"

$dbh = DBI->connect("DBI:mysql:$db:$host:$port", $user, $pw, { RaiseError =>0,PrintError =>0 })

if (!defined($dbh)) {

exit 1

}

$sqlQuery = $dbh->prepare($query)

$sqlQuery->execute

$Slave_IO_Running = ""

$Slave_SQL_Running = ""

$Seconds_Behind_Master = ""

while (my $ref = $sqlQuery->fetchrow_hashref()) {

$Slave_IO_Running = $ref->{'Slave_IO_Running'}

$Slave_SQL_Running = $ref->{'Slave_SQL_Running'}

$Seconds_Behind_Master = $ref->{'Seconds_Behind_Master'}

}

$sqlQuery->finish

$dbh->disconnect()

if ( $Slave_IO_Running eq "No" || $Slave_SQL_Running eq "No" ) {

exit 1

} else {

if ( $Seconds_Behind_Master >$SBM ) {

exit 1

} else {

exit 0

}

}

##################################################################################################

:wq! #保存退出

chmod +x /etc/keepalived/check_slave.pl #添加脚本执行权限

七、设置更新LVS虚拟服务器（VIP）地址的arp记录到网关脚本

vi /etc/keepalived/clean_arp.sh #编辑，添加以下代码

#!/bin/sh

VIP=192.168.21.254

GATEWAY=192.168.21.2

/sbin/arping -I eth0 -c 5 -s $VIP $GATEWAY &>/dev/null

:wq! #保存退出

chmod +x /etc/keepalived/clean_arp.sh #添加脚本执行权限

八、测试LVS+Keepalived是否正常运行

service keepalived restart #在两台LVS主备服务器上重启keepalived

ipvsadm -L #在两台LVS主备服务器上执行此命令，

ip addr show #在两台LVS主备服务器上执行此命令，

#从图中可以看到VIP：192.168.21.254现在指向的是LVS主服务器

1、关闭LVS主服务器的keepalived服务，查看LVS备服务器是否正常接管keepalived

service keepalived stop #在LVS主服务器上运行

此时，在两台LVS主备服务器上执行ip addr show命令，

系统运维 www.osyunwei.com 温馨提醒：qihang01原创内容 版权所有,转载请注明出处及原文链接

可以看到VIP：192.168.21.254现在指向的是LVS备服务器，说明LVS备服务器已经接管keepalived，测试成功。

2、停止MySQL从服务器192.168.21.127上面的主从同步服务

mysql -u root -p #进入MySQL控制台

slave start#停止同步

在LVS备服务器执行ipvsadm -L命令，可以看到MySQL从服务器192.168.21.127已经从负载均衡列表中被剔除了，测试成功。

3、从客户端连接LVS虚拟服务器（VIP）：192.168.21.254

telnet 192.168.21.254 3306 #执行此命令，说明测试成功。

tail -f /var/log/messages #查看LVS日志信息

至此，LVS+Keepalived实现MySQL从库读 *** 作负载均衡配置完成。

主从复制时MySQL自身带的功能

如果要实现负载均衡需要其他来配合，

我现在研究的是用Amoeba来实现读写分离，建议您多到网上检索一些关于mysql负载均衡的帖子。

当然还有人用MySQL Proxy来做读写分离。不知道能不能帮到你。

欢迎分享，转载请注明来源：内存溢出

原文地址: http://outofmemory.cn/zaji/7512949.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
绝对值不等式 绝对值不等式 一级用户组
0 0
上一篇 2023-04-06
下一篇 2023-04-06

发表评论

登录后才能评论

评论列表（0条）

保存