keepalived主从都有VIP

一：实验环境

操作系统：CentOS release 6.5 (Final)

mysql双主复制+keepalive实现了高可用：

master1:10.192.203.201

master2:10.192.203.202

vip 10.192.203.203

虚拟ip当前在master1上。程序写入的是vip。

 

二：实验步骤

2.1 升级master2

2.1.1 备份master2

show databases;查看下都有哪些库

mysqldump --single-transaction -u root -p-A > /download/bak/all.bak_20161125

检查备份文件，确保无误。

2.1.2 记录binlog位置

#在master1上加个读锁, 记录master1此刻写入的 File和Position:

flush tables with read lock; #锁定master1,不允许写入

show master status ;

#停止master2的slave:

stop slave;

show slave status \G; #记录读取和执行的binlog位置

#在master1上unlock tables;

 

之所以进行如上步骤，是为了升级完成master2，导入备份的数据后，知道应该从哪个位置开始复制，从而和master1保持一致。

2.1.3 卸载master2

卸载脚本内容如：

mysqldir='/data/mysql'
basedir='/usr/local/mysql'
cnf=/etc/my.cnf
password='123456'
 
#1：关闭数据库
mysqladmin -u root -p$password shutdown
killall -u mysql
 
#2：删除用户和组
userdel mysql
groupdel mysql
 
#3：删除目录
rm -rf $mysqldir
rm -rf $basedir
 
#4:取消开机自动启动
rm -rf /etc/rc.d/init.d/mysql
chkconfig --del mysql
 
#5:删除 PATH
#root用户
sed -i'/^PATH=/s/:\/usr\/local\/mysql\/bin//' /root/.bash_profile
source /root/.bash_profile
#mysql用户
sed -i'/^PATH=/s/:\/usr\/local\/mysql\/bin//' /home/mysql/.bash_profile
source /home/mysql/.bash_profile
 
#6：取消防火墙端口
#修改文件/etc/sysconfig/iptables
#删除-A INPUT -m state--state NEW -m tcp -p tcp --dport 3306 -j ACCEPT
sed -i '/-A INPUT -m state --state NEW -mtcp -p tcp --dport 3306 -j ACCEPT/d' /etc/sysconfig/iptables
 
service iptables restart

2.1.4 安装5.6.33版本mysql

2.1.4.1 安装脚本内容

#记得先将mysql安装包上传至$dir目录下
#配置文件my.cnf上传master2-sample-my.cnf,并根据实际情况修改下innodb_buffer_pool_size取值,记得根据实际的安装目录修改log-bin,relay-log,relay-log-index,log-bin取值
#注意，mysqldir假如不是/data/mysql,要改成其他目录（如/database/mysql）的话，需要修改该脚本中这一行sed  -i'47s/datadir=/datadir=\/database\/mysql/g' /etc/rc.d/init.d/mysqld
 
#定义目录
basedir='/usr/local/mysql'
      
mysqldir='/data/mysql' 
      
datadir=$mysqldir
 #binlogdir=$mysqldir/binlog
    
cnf=/etc/my.cnf
 
dir='/download/'
 
socket='/tmp/mysql.sock'
 
port='3306'
 
filename='mysql-5.6.33-linux-glibc2.5-x86_64'
 
password='123456'
 
password2=\'$password\'
 
date=`date "+%y%m%d"`
 
#修改配置文件
mv /etc/my.cnf /etc/my.cnf_bak_$date
mv $dir'master2-sample-my.cnf' /etc/my.cnf
 
#建用户
groupadd mysql
useradd -g mysql mysql
      
#安装依赖包
yum install libaio -y
     
#解压
cd $dir 
if ( test -s $filename )
then
   echo '已经解压过，无需重复解压'
else
   tar -xvf $filename.tar.gz
fi     
 
 #拷贝解压后的mysql目录到系统的本地软件目录:
cp $filename $basedir -r
 
#新建目录
#mkdir -p $datadir
#mkdir -p $binlogdir
mkdir -p $mysqldir
chown -R mysql:mysql $basedir
chown -R mysql:mysql $mysqldir
 
#建立基本库
$basedir/scripts/mysql_install_db--defaults-file=$cnf --user=mysql --basedir=$basedir --datadir=$datadir
#设置开机自动启动
cp $basedir'/support-files/mysql.server''/etc/rc.d/init.d/mysqld'
#修改该文件datadir
#已经知道datadir=在第47行
sed -i '47s/datadir=/datadir=\/data\/mysql/g' /etc/rc.d/init.d/mysqld
 
chmod +x /etc/rc.d/init.d/mysqld
 
chkconfig --add mysqld
 
chkconfig mysqld on
 
#配置PATH
#root用户
sed -i'/^PATH=/s/$/:\/usr\/local\/mysql\/bin/' /root/.bash_profile
source /root/.bash_profile
 
#mysql用户
sed -i '/^PATH=/s/$/:\/usr\/local\/mysql\/bin/'/home/mysql/.bash_profile
source /home/mysql/.bash_profile
#启动数据库
 
service mysqld start
 
#开放防火墙端口
#开放3306端口，插入到这一行-AINPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT后面
sed -i '/-A INPUT -m state --state NEW -mtcp -p tcp --dport 22 -j ACCEPT/s/$/\n-A INPUT -m state --state NEW -m tcp -ptcp --dport 3306 -j ACCEPT/' /etc/sysconfig/iptables
 
service iptables restart
#公司的防火墙是关闭的，所以这里就不开放端口了。
 
#修改mysql root 密码
#根据执行该脚本最后输出的信息('建立基本库'中的root密码)来修改,改成$password,需要交互执行
#mysql 5.6.33默认密码为空
mysqladmin -u root  password $password
 
 
#待新建一个用于远程访问的用户,密码和本地用户一致。
$basedir/bin/mysql -u root -p$password -e"grant all privileges on *.* to 'root'@'%' identified by  $password2"

2.1.4.2 配置文件master2-sample-my.cnf内容

由于是从5.5版本升级至5.6版本，为了避免低版本从库复制高版本主库时发生错误：

Got fatal error 1236 from master when reading data from binary log: 'Slave can not handle replication events with the checksum that master is configured to log;

需要确保在配置文件中添加参数：binlog_checksum=none

 master2-sample-my.cnf文件内容：

[client]
port           =3306
socket                =/tmp/mysql.sock
[mysqld]
port           =3306
socket                =/tmp/mysql.sock
binlog_checksum=none
innodb_buffer_pool_size = 请设置成合适的值G
server-id = 2
max_connections = 5000
max_connect_errors = 300
max_allowed_packet = 64M
binlog_format=row
slow_query_log
long_query_time = 2
sync_binlog=1
innodb_support_xa=1
innodb_file_per_table=1
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
relay-log=/data/mysql/master2-relay-bin
relay-log-index=/data/mysql/master2-relay-bin.index
log-bin=/data/mysql/master2-mysql-bin
log_slave_updates
[mysqldump]
# Do not buffer the whole result set inmemory before writing it to
# file. Required for dumping very largetables
quick
 
max_allowed_packet = 64M
 
[mysql]
no-auto-rehash
 
# Only allow UPDATEs and DELETEs that usekeys.
#safe-updates
 
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
 
[mysqlhotcopy]
interactive-timeout
 
[mysqld_safe]
open-files-limit = 8192

2.1.5 恢复数据

导入备份的数据

mysql -u root -p < all.bak_20161125

2.1.6 制造测试数据

在master1上插入几条测试数据

mysql> use sds;
Database changed
mysql> show tables;
Empty set (0.00 sec)
 
mysql> create table t(id int);
Query OK, 0 rows affected (0.06 sec)
 
mysql> insert into t(id)values(1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0  Warnings: 0
 
mysql> select  * from t;
+------+
| id  |
+------+
|   1 |
|   2 |
|   3 |
+------+
3 rows in set (0.02 sec)

2.1.7 将master2指向master1

master_log_file ,master_log_pos需要和2.1.2步骤中showmaster status ;的结果保持一致：

change master tomaster_log_file='mysql-bin.000003',master_log_pos=1109,master_host='10.192.203.201',master_user='RepUser',master_password='beijing',master_port=3306;
mysql> start slave;
Query OK, 0 rows affected (0.03 sec)
 
show slave status \G;检查下Slave_IO_Running，Slave_SQL_Running是否都为Yes。
 
#验证下是否将差异数据同步了过来
mysql> use sds;
Database changed
mysql> show tables;
+---------------+
| Tables_in_sds |
+---------------+
| t             |
+---------------+
1 row in set (0.00 sec)
 
mysql> select * from t;
+------+
| id  |
+------+
|   1 |
|   2 |
|   3 |
+------+
3 rows in set (0.01 sec)

说明master2同步成功。

2.2 升级master1

2.2.1 漂移vip到master2机器

由于我在自己机器上配置了监控mysql定时任务（每分钟监控一次mysql状态，如果mysql宕机，将该机器上的heartbeat进程杀掉），因此升级master2，肯定会导致heartbeat被关闭。因此需要先启动master2的heartbeat进程：

service heartbeat start

#关闭master1进程：

service heartbeat stop

#在maser2上使用ip addr命令验证虚拟IP是否漂移成功。

[root@slave2 download]# ip addr
1: lo: <LOOPBACK,UP,LOWER_UP> mtu16436 qdisc noqueue state UNKNOWN
   link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
   inet 127.0.0.1/8 scope host lo
   inet6 ::1/128 scope host
      valid_lft forever preferred_lft forever
2: eth0:<BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen1000
   link/ether 08:00:27:04:05:16 brd ff:ff:ff:ff:ff:ff
   inet 10.192.203.202/24 brd 10.192.203.255 scope global eth0
   inet 10.192.203.203/24 brd 10.192.203.255 scope global secondary eth0
   inet6 fe80::a00:27ff:fe04:516/64 scope link
      valid_lft forever preferred_lft forever
3: eth1:<BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen1000
   link/ether 08:00:27:3a:ec:3c brd ff:ff:ff:ff:ff:ff
   inet 10.0.0.2/24 brd 10.0.0.255 scope global eth1
   inet6 fe80::a00:27ff:fe3a:ec3c/64 scope link
      valid_lft forever preferred_lft forever

看到了vip：10.192.203.203，说明漂移成功。

2.2.2 备份master1

略

2.2.3 记录binlog位置

逻辑步骤请参考2.1.2。

2.2.4 卸载master1

卸载脚本内容同“2.1.3 卸载master2”一样。

2.2.5 安装5.6.33版本mysql

2.2.5.1 安装脚本内容

注意：和安装master2脚本只有一个地方不同：

mv $dir'master2-sample-my.cnf' /etc/my.cnf

mv $dir'master1-sample-my.cnf' /etc/my.cnf

 

--以下是安装脚本内容：

#记得先将mysql安装包上传至$dir目录下
#配置文件my.cnf上传master1-sample-my.cnf,并根据实际情况修改下innodb_buffer_pool_size取值,记得根据实际的安装目录修改log-bin,relay-log,relay-log-index,log-bin取值
#注意，mysqldir假如不是/data/mysql,要改成其他目录（如/database/mysql）的话，需要修改该脚本中这一行sed  -i'47s/datadir=/datadir=\/database\/mysql/g' /etc/rc.d/init.d/mysqld
 
#定义目录
basedir='/usr/local/mysql'
      
mysqldir='/data/mysql' 
       
datadir=$mysqldir
 #binlogdir=$mysqldir/binlog
    
cnf=/etc/my.cnf
 
dir='/download/'
 
socket='/tmp/mysql.sock'
 
port='3306'
 
filename='mysql-5.6.33-linux-glibc2.5-x86_64'
 
password='123456'
 
password2=\'$password\'
 
date=`date "+%y%m%d"`
 
#修改配置文件
mv /etc/my.cnf /etc/my.cnf_bak_$date
mv $dir'master1-sample-my.cnf' /etc/my.cnf
 
#建用户
groupadd mysql
useradd -g mysql mysql
      
#安装依赖包
yum install libaio -y
     
#解压
cd $dir 
if ( test -s $filename )
then
   echo '已经解压过，无需重复解压'
else
   tar -xvf $filename.tar.gz
fi     
 
 #拷贝解压后的mysql目录到系统的本地软件目录:
cp $filename $basedir -r
 
#新建目录
#mkdir -p $datadir
#mkdir -p $binlogdir
mkdir -p $mysqldir
chown -R mysql:mysql $basedir
chown -R mysql:mysql $mysqldir
 
#建立基本库
$basedir/scripts/mysql_install_db--defaults-file=$cnf --user=mysql --basedir=$basedir --datadir=$datadir
#设置开机自动启动
cp $basedir'/support-files/mysql.server''/etc/rc.d/init.d/mysqld'
#修改该文件datadir
#已经知道datadir=在第47行
sed -i '47s/datadir=/datadir=\/data\/mysql/g' /etc/rc.d/init.d/mysqld
 
chmod +x /etc/rc.d/init.d/mysqld
 
chkconfig --add mysqld
 
chkconfig mysqld on
 
#配置PATH
#root用户
sed -i'/^PATH=/s/$/:\/usr\/local\/mysql\/bin/' /root/.bash_profile
source /root/.bash_profile
 
#mysql用户
sed -i'/^PATH=/s/$/:\/usr\/local\/mysql\/bin/' /home/mysql/.bash_profile
source /home/mysql/.bash_profile
#启动数据库
 
service mysqld start
 
#开放防火墙端口
#开放3306端口，插入到这一行-AINPUT -m state --state NEW -m tcp -p tcp --dport 22 -j ACCEPT后面
sed -i '/-A INPUT -m state --state NEW -mtcp -p tcp --dport 22 -j ACCEPT/s/$/\n-A INPUT -m state --state NEW -m tcp -ptcp --dport 3306 -j ACCEPT/' /etc/sysconfig/iptables
 
service iptables restart
#公司的防火墙是关闭的，所以这里就不开放端口了。
 
#修改mysql root 密码
#根据执行该脚本最后输出的信息('建立基本库'中的root密码)来修改,改成$password,需要交互执行
#mysql 5.6.33默认密码为空
mysqladmin -u root  password $password
 
 
#待新建一个用于远程访问的用户,密码和本地用户一致。
$basedir/bin/mysql -u root -p$password -e"grant all privileges on *.* to 'root'@'%' identified by  $password2"

2.2.5.2 配置文件master1-sample-my.cnf内容

#master1-sample-my.cnf与master2-sample-my.cnf配置文件内容只有几个地方不同：

server-id ,relay-log,relay-log-index

 配置文件内容：

[client]
port           =3306
socket                =/tmp/mysql.sock
[mysqld]
port           =3306
socket                =/tmp/mysql.sock
binlog_checksum=none
innodb_buffer_pool_size = 请设置成合适的值G
server-id = 1
max_connections = 5000
max_connect_errors = 300
max_allowed_packet = 64M
binlog_format=row
slow_query_log
long_query_time = 2
sync_binlog=1
innodb_support_xa=1
innodb_file_per_table=1
replicate-ignore-db = mysql
replicate-ignore-db = information_schema
relay-log=/data/mysql/master1-relay-bin
relay-log-index=/data/mysql/master1-relay-bin.index
log-bin=/data/mysql/master1-mysql-bin
log_slave_updates
[mysqldump]
# Do not buffer the whole result set inmemory before writing it to
# file. Required for dumping very largetables
quick
 
max_allowed_packet = 64M
 
[mysql]
no-auto-rehash
 
# Only allow UPDATEs and DELETEs that usekeys.
#safe-updates
 
[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
 
[mysqlhotcopy]
interactive-timeout
 
[mysqld_safe]
open-files-limit = 8192

 

2.2.6 恢复数据

导入备份的数据

mysql -u root -p < all.bak_20161125

2.2.7 制造测试数据

略

2.2.8 将master1指向master2

思路和“2.1.7 将master2指向master1”一样。

change master tomaster_log_file='master2-mysql-bin.000003' ,master_log_pos=489108,master_host='10.192.203.202',master_user='RepUser',master_password='beijing',master_port=3306;
 mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

 最后检查下master1,master2上的从库是否都正常。

发现master2上的slave报错：

Last_IO_Error: error connecting to master'RepUser@10.192.203.201:3306' - retry-time: 60 retries: 33

 我手动在master1上重建了下复制用户，然后stop slave;start slave;没有再报这个错误。

也不知道为什么会报这个错误。备份文件里明明也能看到这个复制用户的插入记录呀。

 

start slave时又报了一个新的错误:

Last_IO_Error: Got fatal error 1236 frommaster when reading data from binary log: 'Could not find first log file namein binary log index file'

在master1上flush logs;

show master status;

在master2上重新指定binlog位置,开始复制即可。

 

2.3 修改参数binlog_checksum

起初，安装mysql 5.6.33的时候，特意将binlog_checksum设置为none，原因请参考：

 

现在master1，master2版本都变成了5.6.33了。因此现在需要注释掉配置文件里的binlog_checksum=none，然后重启mysql即可。这里也顺便将vip切换回master1。

master1:

注释掉配置文件里的binlog_checksum=none，然后重启mysql;

启动master1的heartbeat;

 

master2:

注释掉配置文件里的binlog_checksum=none，然后重启mysql;

重启master2的heartbeat;

 

在master1上执行ip addr命令验证是否vip漂移成功。

 

检查该参数是否修改成功：

mysql> show variables like '%checksum%';

+---------------------------+--------+

| Variable_name             | Value  |

+---------------------------+--------+

| binlog_checksum           | CRC32  |

| innodb_checksum_algorithm | innodb |

| innodb_checksums          | ON     |

| master_verify_checksum    | OFF   |

| slave_sql_verify_checksum | ON     |

+---------------------------+--------+