centos lvs+keepalived+mysql实现mysql数据库热备主主复制

来源：互联网发布：驾驶证ps软件编辑：程序博客网时间：2024/06/06 15:52

一、环境：

linux：centos6.6
虚拟ip(vip)：192.168.20.199
mysql master：192.168.20.193
mysql slave：192.168.20.195
（这里只是为了后文方便区分，一个叫master，一个叫slave，其实在主主复制中，都是master也都是slave，没有主从之分）

二、分别在两台服务器上安装同版本mysql

1、安装mysql
# yum -y install mysql-server mysql
# /etc/init.d/mysqld start #启动mysql 注意权限非root使用sudo
# chkconfig mysqld on #设为开机启动
# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf #拷贝配置文件（注意：如果/etc目录下面默认有一个my.cnf，直接覆盖即可）
2、为root账户设置密码
# mysql_secure_installation
回车，根据提示输入Y
输入2次密码，回车
根据提示一路输入Y
最后出现：Thanks for using MySQL!
mysql密码设置完成，重新启动 mysql：
# /etc/init.d/mysqld restart #重启
# mysql -V
mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

三、修改两台mysql配置文件

修改master的配置文件： vi /etc/my.cnf

#在[mysqld]添加如下内容#

server-id = 193 #//只要主从不一样就行 我这里以ip地址后3位方便区分log-bin = mysql-bin #开启二进制日志binlog_format = MIXED #非必需relay-log = mysqld-relay-bin #定义中继日志名，开启从服务器中继日志#binlog-do-db=db_name  #只复制db_name数据库binlog-ignore-db = mysql #不复制mysql数据库binlog-ignore-db = information_schemabinlog-ignore-db = performance_schemareplicate-wild-ignore-table = mysql.% #不复制mysql所有表replicate-wild-ignore-table = information_schema.%replicate-wild-ignore-table = performance_schema.%

修改slave的配置文件： vi /etc/my.cnf#在[mysqld]添加如下内容#

server-id = 195log-bin = mysql-binbinlog_format = MIXED #非必需relay-log = mysqld-relay-binbinlog-ignore-db = mysqlbinlog-ignore-db = information_schemabinlog-ignore-db = performance_schemareplicate-wild-ignore-table = mysql.%replicate-wild-ignore-table = information_schema.%replicate-wild-ignore-table = performance_schema.%

四、手动同步数据库

如果master上已经有数据，那么执行主主互备之前，需要将master和slave上的两个mysql的数据保持同步，具体方法很多，可用工具Navicat for MySQL、phpmyadmin，也可直接命令行mysqldump、source；一般master备份前使用flush table with read lock;进行全局读锁，不能写；然后操作完后unlock tables;释放锁

master上执行

# mysqldump -uroot -p123456 --lock-all-tables --flush-logs hadoop > /data/hadoop.sql

slave上执行

mysql> create database hadoop default character set utf8 collate utf8_general_ci;mysql> use hadoopmysql> source /data/hadoop.sql

五、创建授权复制用户
master执行：

mysql> grant REPLICATION SLAVE ON *.* TO test@'192.168.20.193' IDENTIFIED BY '123456';mysql> flush privileges;

slave执行：

mysql> grant REPLICATION SLAVE ON *.* TO test@'192.168.20.195' IDENTIFIED BY '123456';mysql> flush privileges;

六、启动互为主从的模式
在slave上执行下面命令，注意看Slave_IO_Running和Slave_SQL_Running，必须都是yes

mysql> show master status;+------------------+----------+--------------+---------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |+------------------+----------+--------------+---------------------------------------------+| mysql-bin.000001 |      334 |              | mysql,information_schema,performance_schema |+------------------+----------+--------------+---------------------------------------------+1 row in set (0.00 sec)mysql> change master to master_host='192.168.20.193', master_port=3306, master_user='test',master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=334;Query OK, 0 rows affected (0.06 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.20.193                  Master_User: test                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 334               Relay_Log_File: mysqld-relay-bin.000002                Relay_Log_Pos: 251        Relay_Master_Log_File: mysql-bin.000001             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: mysql.%,information_schema.%,performance_schema.%                   Last_Errno: 0                   Last_Error:                 Skip_Counter: 0          Exec_Master_Log_Pos: 334              Relay_Log_Space: 407              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: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 0               Last_SQL_Error:1 row in set (0.00 sec)

在master上执行下面命令，注意看Slave_IO_Running和Slave_SQL_Running，必须都是yes

mysql> show master status;+------------------+----------+--------------+---------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB                            |+------------------+----------+--------------+---------------------------------------------+| mysql-bin.000001 |      334 |              | mysql,information_schema,performance_schema |+------------------+----------+--------------+---------------------------------------------+1 row in set (0.00 sec)mysql> change master to master_host='192.168.20.195', master_port=3306, master_user='test',master_password='123456', master_log_file='mysql-bin.000001',master_log_pos=334;Query OK, 0 rows affected (0.10 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.20.195                  Master_User: test                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: mysql-bin.000001          Read_Master_Log_Pos: 334               Relay_Log_File: mysqld-relay-bin.000002                Relay_Log_Pos: 251        Relay_Master_Log_File: mysql-bin.000001             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: mysql.%,information_schema.%,performance_schema.%                   Last_Errno: 0                   Last_Error:                 Skip_Counter: 0          Exec_Master_Log_Pos: 334              Relay_Log_Space: 407              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: 0Master_SSL_Verify_Server_Cert: No                Last_IO_Errno: 0                Last_IO_Error:               Last_SQL_Errno: 0               Last_SQL_Error:1 row in set (0.00 sec)

验证主主同步结果：

slave执行下面的sql语句

mysql> create database hadoop default character set utf8 collate utf8_general_ci;Query OK, 1 row affected (0.00 sec)mysql> use hadoop;Database changedmysql> create table user(id int(4) not null primary key auto_increment,name char                                                                                        (20) not null);Query OK, 0 rows affected (0.06 sec)mysql> insert into user values(null,1),(null,2),(null,3);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> delete from user where id=2;Query OK, 1 row affected (0.00 sec)mysql> drop table user;Query OK, 0 rows affected (0.00 sec)mysql> create table user(id int(4) not null primary key auto_increment,name char                                                                                        (20) not null);Query OK, 0 rows affected (0.07 sec)mysql> insert into user values(null,1),(null,2),(null,3);Query OK, 3 rows affected (0.00 sec)Records: 3  Duplicates: 0  Warnings: 0mysql> delete from user where id=2;Query OK, 1 row affected (0.00 sec)mysql> update user set name="在195机器修改" where id=1;Query OK, 1 row affected, 1 warning (0.00 sec)Rows matched: 1  Changed: 1  Warnings: 1mysql> select * from user;+----+----------------------+| id | name                 |+----+----------------------+|  1 | 在195机器修改||  3 | 3                    |+----+----------------------+2 rows in set (0.00 sec)在master上查看mysql> use hadoop;Database changedmysql> show tables;Empty set (0.00 sec)mysql> select * from user;+----+----------------------+| id | name                 |+----+----------------------+|  1 | 在195机器修改 ||  3 | 3                    |+----+----------------------+2 rows in set (0.00 sec)

七、安装lvs+keepalived

cd /usr/src yum install gcc make wget openssl-devel popt-devel libnl libnl-devel kernel-devel ipvsadm -yln -s /usr/src/kernels/2.6.32-642.3.1.el6.x86_64/ /usr/src/linux  wget http://www.keepalived.org/software/keepalived-1.2.7.tar.gz tar zxvf keepalived-1.2.7.tar.gz  cd keepalived-1.2.7  ./configure --with-kernel-dir=/usr/src/kernels/2.6.32-358.2.1.el6.x86_64/make && make install  cp /usr/local/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/  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/

八、配置keepalived
vi /etc/keepalived/keepalived.conf

! Configuration File for keepalivedglobal_defs {   notification_email {     test@sina.com   }   notification_email_from admin@test.com   smtp_server 127.0.0.1   smtp_connect_timeout 30   router_id LVS_DEVEL}vrrp_instance VI_1 {    state BACKUP    interface eth0    virtual_router_id 60    priority 100    advert_int 1nopreempt    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.20.199    }}virtual_server 192.168.20.199 3306  {    delay_loop 6    lb_algo rr    lb_kind DR    nat_mask 255.255.255.0    persistence_timeout 50    protocol TCP    real_server 192.168.20.193 3306 {weight 1notify_down /etc/keepalived/mysqlcheck/mysql.shTCP_CHECK {  connect_port 3306  connect_timeout 3  nb_get_retry 2  delay_before_retry 1}  }}

其中需要注意的是：
state BACKUP #master和slave均配置为BACKUP状态
nopreempt #为不强占模式，slave(slave)不用设置
notify_down #定义监测realserver失败下一步的动作
priority #slave要比master低
创建notify_down的脚本（master和slave都需创建，也可在master创建好，使用scp命令复制到slave，如scp -r mysqlcheck root@192.168.20.195:/etc/keepalived/ ）

mkdir -p  /etc/keepalived/mysqlcheck/cd /etc/keepalived/mysqlcheck/vi mysql.sh#!/bin/bashpkill keepalivedchmod u+x mysql.sh

其实就是杀掉keepalived进程，释放vip

slave的配置文件：

! Configuration File for keepalivedglobal_defs {   notification_email {     test@sina.com   }   notification_email_from admin@test.com   smtp_server 127.0.0.1   smtp_connect_timeout 30   router_id LVS_DEVEL}vrrp_instance VI_1 {    state BACKUP    interface eth0    virtual_router_id 60    priority 90    advert_int 1    authentication {        auth_type PASS        auth_pass 1111    }    virtual_ipaddress {        192.168.20.199    }}virtual_server 192.168.20.199 3306  {    delay_loop 6    lb_algo rr    lb_kind DR    nat_mask 255.255.255.0    persistence_timeout 50    protocol TCP    real_server 192.168.20.195 3306 {weight 1notify_down /etc/keepalived/mysqlcheck/mysql.shTCP_CHECK {  connect_port 3306  connect_timeout 3  nb_get_retry 2  delay_before_retry 1}  }}

master和slave的keepalived都配置好了，分别将keepalived设置为开机启动并启动服务：

chkconfig keepalived on    service keepalived start #查看进程[root@localhost ~]# ps aux | grep keepalivedroot      4696  0.0  0.2 110756  1440 ?        Ss   16:12   0:00 /usr/sbin/keepalived -Droot      4698  0.0  0.6 112992  3036 ?        S    16:12   0:00 /usr/sbin/keepalived -Droot      4699  0.0  0.4 112860  2252 ?        S    16:12   0:00 /usr/sbin/keepalived -Droot      5236  0.0  0.1 103252   864 pts/1    S+   17:04   0:00 grep keepalived

然后

输入ip a命令能看到虚拟ip：192.168.20.199

输入ipvsadm -ln命令在虚拟服务器列表看到自己的ip就是成功的

九、测试(我是在本机window测试)

首先为方便测试在master和slave都开个远程连接帐号open，密码123456

mysql> grant all privileges on *.* to 'open'@'%' identified by '123456';  mysql> flush privileges;

1、本机测试主从同步，通过VIP连接：

进入到mysql的bin目录，使用上面新建的远程帐号远程连接到虚拟ip，然后通过show variables like 'server_id';查看当前使用的是哪台mysql

2、测试keepalived实现mysql故障转移：

可以在master执行，/etc/init.d/mysqld stop，远程一直执行，show vaiables like 'server_id';发现会卡1~3s，server_id变为195，即master变为slave。即使重启master，vip也不会转移，因为采用的是不抢占模式，直到slave的服务挂，才会转移vip

左边是模拟mysql挂掉，右边是本机远程连接vip状况

1 0