mysql dba系统学习（18）mysql主从复制的实现 mysql dba系统学习（19）配置mysql+lvs+keeplived实现Mysql读操作的负载均衡
来源：互联网 发布：网页病毒检测软件 编辑：程序博客网 时间：2024/04/27 20:12
1
2
3
4
5
6
7
8
9
10
11
12
13
[root@test1 ~]# yum install libtermcap libtermcap-devel imake autoconf automake libtool m4[root@test1 ~]# useradd -M -s /sbin/nologin mysql[root@test1 ~]# mkdir /usr/local/mysql[root@test1 ~]# tar zxvf mysql-5.1.70.tar.gz[root@test1 ~]# cd mysql-5.1.70[root@test1 mysql-5.1.70]# ./configure --prefix=/usr/local/mysql --enable-assembler --with-client-ldflags=-all-static --with-unix-socket-path=/tmp --with-charset=utf8 --enable-thread-safe-client --with-pthread --without-debug --with-big-tables --enable-community-features --enable-profiling --enable-local-infile --with-fast-mutexes --with-plugins=partition,federated,ndbcluster,innobase,csv,blackhole,myisam,innodb_plugin[root@test1 mysql-5.1.70]#make[root@test1 mysql-5.1.70]#strip sql/mysqld[root@test1 mysql-5.1.70]#makeinstall[root@test1 mysql-5.1.70]# cp support-files/my-medium.cnf /etc/my.cnf[root@test1 mysql-5.1.70]# /usr/local/mysql/bin/mysql_install_db --user=mysql[root@test1 mysql-5.1.70]# echo "PATH=\"/usr/local/mysql/bin:\$PATH\"">> /etc/profile[root@test1 mysql-5.1.70]# export PATH="/usr/local/mysql/bin:$PATH"解决办法I：
1.首先停掉Slave服务：slavestop
1.首先停掉Slave服务：slavestop
2.到主服务器上查看主机状态：
记录File和Position对应的值。
mysql>showmasterstatus;
+------------------+-----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+-----------+--------------+------------------+
|mysql-bin.000020|135617781|||
+------------------+-----------+--------------+------------------+
1rowinset(0.00sec)
记录File和Position对应的值。
mysql>showmasterstatus;
+------------------+-----------+--------------+------------------+
|File|Position|Binlog_Do_DB|Binlog_Ignore_DB|
+------------------+-----------+--------------+------------------+
|mysql-bin.000020|135617781|||
+------------------+-----------+--------------+------------------+
1rowinset(0.00sec)
3.到slave服务器上执行手动同步：
mysql>changemasterto
>master_host='192.168.46.131',
>master_user='chenzhongyang',
>master_password='chenzhongyang',
>master_port=3306,
>master_log_file='mysql-bin.000020',
>master_log_pos=135617781;
1rowinset(0.00sec)
mysql>slavestart;
1rowinset(0.00sec)
再次查看slave状态发现：
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
...
Seconds_Behind_Master:0
mysql>changemasterto
>master_host='192.168.46.131',
>master_user='chenzhongyang',
>master_password='chenzhongyang',
>master_port=3306,
>master_log_file='mysql-bin.000020',
>master_log_pos=135617781;
1rowinset(0.00sec)
mysql>slavestart;
1rowinset(0.00sec)
再次查看slave状态发现：
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
...
Seconds_Behind_Master:0
1
2
3
4
5
6
7
8
9
10
11
12
13
[root@test1 ]# yum install -y openssl-devel[root@test1 ~]# wget http://www.keepalived.org/software/keepalived-1.2.1.tar.gz[root@test1 ~]# tar zxvf keepalived-1.2.1.tar.gz[root@test1 ~]# cd keepalived-1.2.1[root@test1 keepalived-1.2.1]# ./configure --prefix=/usr/local/keepalived[root@test1 keepalived-1.2.1]# make && make install[root@test1 ~]# cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/[root@test1 ~]# cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/init.d/[root@test1 ~]# mkdir /etc/keepalived[root@test1 ~]# cp /usr/local/keepalived/etc/keepalived /keepalived.conf /etc/keepalived/[root@test1 ~]# ln -s /usr/local/keepalived/sbin/keepalived /usr/sbin/[root@test1 ~]# service keepalived startStarting keepalived: [ OK ]1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
! Configuration File for keepalivedglobal_defs {notification_email {root@localhost}notification_email_from root@localhostsmtp_server 192.168.46.131smtp_connect_timeout 30router_id LVS_MASTER}vrrp_instance VI_1 {state MASTER //备库为BACKUPinterface eth2 //通过的网卡virtual_router_id 51priority 100 //备库为99advert_int 1authentication {auth_type PASSauth_pass 1111}virtual_ipaddress {192.168.46.100}}virtual_server 192.168.46.100 3306 {delay_loop 30lb_algo rrlb_kind DRpersistence_timeout 50protocol TCPreal_server 192.168.46.131 3306 {weight 1MISC_CHECK{misc_path "/etc/keepalived/check_slave.pl 192.168.46.131"misc_dynamic}}real_server 192.168.46.130 3306 {weight 1MISC_CHECK{misc_path "/etc/keepalived/check_slave.pl 192.168.46.130"misc_dynamic}}}}1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
#!/usr/bin/perl -wuse DBI;use DBD::mysql;# CONFIG VARIABLES$SBM = 120;$db = "test";$host = $ARGV[0];$port = 3306;$user = "root";$pw = "mysql";# 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;}}1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
[root@test1 ~]# yum install -y ipvsadm[root@test1 ~]# lsmod |grep ip_vs[root@test1 ~]# modprobe ip_vs[root@test1 ~]# lsmod |grep ip_vsip_vs 122241 0这里不能靠进程来判断是不是加载了ip_vs模块，因为这里就是一个命令，没有产生进程。vim /etc/init.d/lvsdrrip#!/bin/bash#DR serverVIP=192.168.46.100case "$1" instart)echo "start LVS of DR"/sbin/ifconfig lo:0 $VIP broadcast $VIP netmask 255.255.255.255 up/sbin/route add -host $VIP dev lo:0echo "1" > /proc/sys/net/ipv4/conf/lo/arp_ignoreecho "2" > /proc/sys/net/ipv4/conf/lo/arp_announceecho "1" > /proc/sys/net/ipv4/conf/all/arp_ignoreecho "2" > /proc/sys/net/ipv4/conf/all/arp_announce;;stop)echo "stop LVS of DR"/sbin/ifconfig lo:0 downecho "0" > /proc/sys/net/ipv4/conf/lo/arp_ignoreecho "0" > /proc/sys/net/ipv4/conf/lo/arp_announceecho "0" > /proc/sys/net/ipv4/conf/all/arp_ignoreecho "0" > /proc/sys/net/ipv4/conf/all/arp_announce;;*)echo "Usage:$0 {start|stop}"esacexit11
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[root@test3 ~]# vim /etc/init.d/lvsdr#!/bin/bashVIP=192.168.46.100RIP1=192.168.46.131RIP2=192.168.46.130case "$1" instart)echo "start LVS of DirectorServer DR"/sbin/iptables -F/sbin/ipvsadm -C/sbin/ifconfig eth0:0 $VIP broadcast $VIP netmask 255.255.255.0 up/sbin/ipvsadm -A -t $VIP:3306 -s rr/sbin/ipvsadm -a -t $VIP:3306 -r $RIP1 -g/sbin/ipvsadm -a -t $VIP:3306 -r $RIP2 -g/sbin/ipvsadm;;stop)echo "stop LVS of DirectorServer DR"echo "0" >/proc/sys/net/ipv4/ip_forward/sbin/ipvsadm -C/sbin/ifconfig eth0:0 down;;*)[root@test3 ~]#chown +x /etc/init.d/lvsdrfrom: http://wolfword.blog.51cto.com/4892126/1290938
http://wolfword.blog.51cto.com/4892126/1291868
0 0
- mysql dba系统学习（18）mysql主从复制的实现 mysql dba系统学习（19）配置mysql+lvs+keeplived实现Mysql读操作的负载均衡
- mysql dba系统学习（3）mysql的启动停止
- mysql dba系统学习（16）mysql的mysqldump备份
- mysql dba系统学习（12）mysql的数据文件 mysql dba系统学习（13）mysql的体系结构
- mysql dba系统学习（16）mysql的mysqldump备份 mysql dba系统学习（17）mysql的备份和恢复的完整实践
- mysql dba系统学习（20）mysql存储引擎MyISAM
- mysql dba系统学习（21）mysql存储引擎InnoDB
- LVS+Keepalived实现mysql的负载均衡
- mysql dba系统学习（2）了解mysql的源码目录及源文件
- mysql dba系统学习（4）mysql的多实例multi启动停止
- MHA+Lvs+Keepalived实现MySQL的高可用及读负载均衡_2（MySQL）
- mysql dba系统学习-数据库事务详解
- MHA+Lvs+Keepalived实现MySQL的高可用及读负载均衡_4（Lvs+Keepalived）
- mysql dba系统学习（22）数据库事务详解
- mysql dba系统学习（5）二进制日志binlog之一
- LVS+Keepalived实现MySQL从库读操作负载均衡
- LVS+Keepalived实现MySQL从库读操作负载均衡
- LVS+Keepalived实现MySQL从库读操作负载均衡
- 随笔之java笔试2
- 【Modern OpenGL】第一个三角形
- 时间转换
- maven导入jar包慢的问题
- linux基础命令
- mysql dba系统学习（18）mysql主从复制的实现 mysql dba系统学习（19）配置mysql+lvs+keeplived实现Mysql读操作的负载均衡
- LeetCode 2016 307
- 2016.12.21 今天的收获
- 移动通信（Mobile Communication）
- mysql dba系统学习（16）mysql的mysqldump备份 mysql dba系统学习（17）mysql的备份和恢复的完整实践
- LeetCode 2016 134
- 第3章 基本的bash shell命令
- 程序：输出蛇形代码
- BFS.h1728单方向优先搜索
