MySQL互为主从复制以及主主互备

时间：2022-09-15 11:40:06

MySQL互为主从复制以及主主互备

2015/5/8 10:14:22

实验环境:

主机名 ip地址 操作系统 应用角色
mysql-master 192.168.1.87 Centos 6.6 Master
mysql-slave 192.168.1.198 Centos 6.6 Slave
192.168.1.50 VIP

软件版本:

软件名 软件版本
Mysql 5.1.73
Keepalived 1.2.12

一: 配置MySQL互为主从复制

1.安装MySQL服务

[root@mysql-master ~]# yum -y install mysql-server

2.修改mysql-master配置文件

#修改MySQL配置文件/etc/my.cnf,在"[mysqld]"段添加如下内容：
server-id = 1                                   #节点标识，主从节点不能相同，必须全局唯一。
log-bin=mysql-bin                               #开启MySQL的binlog日志功能，"mysql-bin"表示日志文件的命名格式，会生成如:"mysql-bin.xxxx"
relay-log = mysql-relay-bin                     #定义relay-log日志文件的命名格式。
#复制过滤选项，可以过滤不需要复制的数据库或表，如"mysql.%"表示不复制MySQL库下所有对象。
#于此对应的反选项，"replicate-wild-do-table"表示指定需要复制的数据库或表。
replicate-wild-ignore-table=mysql.% 
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

3.修改mysql-slave配置文件

#修改MySQL配置文件/etc/my.cnf,在"[mysqld]"段添加如下内容：
server-id = 2
log-bin=mysql-bin
relay-log = mysql-relay-bin
replicate-wild-ignore-table=mysql.%
replicate-wild-ignore-table=test.%
replicate-wild-ignore-table=information_schema.%

4.手动同步数据库。

#如果master已经有MySQL数据，那么在主主互备之前，需要将master和slave俩个MySQL的数据进行同步，首先在master备份MySQL数据，执行如下SQL语句：
mysql>flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)
#直接打包压缩或使用mysqldump工具导出数据，操作过程如下：
[root@mysql-master ~]# cd /var/lib/
[root@mysql-master lib]# tar zcvf mysql.tar.gz mysql
[root@mysql-master lib]# scp -r mysql.tar.gz mysql-slave:/var/lib/

5.依次重启master和slave上mysql服务

[root@mysql-master ~]# service mysqld restart
[root@mysql-slave ~]# service mysqld restart

6.创建复制用户并授权。

#在mysql-master上创建复制用户
mysql>grant replication slave on *.* to 'repl_user'@'192.168.1.198' identified by 'repl_passwd';
mysql>show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      262 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#然后在mysql-slave的MySQL库中将master设为自己的主服务器，操作过程如下：
#注："master_log_file"和"master_log_pos",这两个选项的值刚好是master上通过SQL语句"show master status;"查询到的结果
mysql> change master to \
    -> master_host='192.168.1.87',
    -> master_user='repl_user',
    -> master_password='repl_passwd',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=262;
Query OK, 0 rows affected (0.07 sec)
#在mysql-slave启动slave服务，执行如下：
mysql>start slave;
Query OK, 0 rows affected (0.00 sec)
#在mysql-slave查看slave运行状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.87
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 262
               Relay_Log_File: mysql-relay-bin.000001
                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.%,test.%,information_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 262
              Relay_Log_Space: 406
              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: 0
Master_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运行状态，发现一切运行正常，重点关注"Slave_IO_Running"和"Slave_SQL_Running",这两个就是在slave节点上运行的主从复制线程，正常这两个值都应该是YES。另外，还需要注意的是"Slave_IO_State"、"Master_Host"、"Master_Log_File"、"Read_Master_Log_Pos"、"Read_Master_Log_Pos"、"Relay_Log_Pos"、"Relay_Master_Log_File"几个选项，从上述运行状态可以看出MySQL复制的运行原理及执行规律，最后一个"Replicate_Wild_Ignore_Table"，这是在"my.cnf"中添加的，通过此选项可以看出过滤了那些数据库。

##到这里，mysql-master到mysql-slave主从复制已经完成。接下来配置mysql-slave到mysql-master的MySQL主从复制。
1.首先在mysql-slave的mysql库中创建复制用户。
mysql>grant replication slave on *.* to 'repl_user'@'192.168.1.87' identified by 'repl_passwd';
mysql>show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      261 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2.在mysql-master的MySQL库中将mysql-slave设为自己的主服务器
mysql> change master to \
    -> master_host='192.168.1.198',
    -> master_user='repl_user',
    -> master_password='repl_passwd',
    -> master_log_file='mysql-bin.000001',
    -> master_log_pos=261;
Query OK, 0 rows affected (0.04 sec)
#最后，在mysql-master上启动slave服务
mysql>start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.198
                  Master_User: repl_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 261
               Relay_Log_File: mysql-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.%,test.%,information_schema.%
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 269
              Relay_Log_Space: 406
              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: 0
Master_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)
#上述表明双方复制服务运行正常，至此，MySQL双主模式的主从复制已完成。

二: 配置keepalived实现MySQL双主高可用

1.在"mysql-master"和"mysql-slave"安装keepalived (keepalived官方网址: http://www.keepalived.org)

[root@mysql-master ~]# tar zxf keepalived-1.2.12.tar.gz 
[root@mysql-master ~]# cd keepalived-1.2.12
[root@mysql-master keepalived-1.2.12]# ./configure --sysconf=/etc --with-kernel-dir=/usr/src/kernels/2.6.32-504.el6.x86_64 && make && make install
#./configure配置解析:
                    "--sysconf"指定Keepalived配置文件的安装路径,即路径为:"/etc/keepalived/keepalived.conf"
                    "--with-kernel-dir"指定使用内核源码中的头文件,即include目录,只有在使用LVS,在需要用到此参数。

报错区域:

1.checking for gcc... no
checking for cc... no
checking for cl.exe... no
configure: error: in `/root/keepalived-1.2.12':
configure: error: no acceptable C compiler found in $PATH
See `config.log' for more details
解决: yum install gcc -y

2.configure: error: 
  !!! OpenSSL is not properly installed on your system. !!!
  !!! Can not include OpenSSL headers files.            !!!
解决: yum install openssl-devel -y

2.修改 keepalived 配置文件:"/etc/keepalived/keepalived.conf"

###"mysql-master"端的配置文件:"/etc/keepalived/keepalived.conf",内容如下:
#说明:"mysql-slave"端需将"mysql-master"写好的"keepalived.conf"和"check_slave.sh"复制到相应位置,随后将"keepalived.conf"中"priority"值修改为90,由于配置的是不抢占模式，需要去掉"nopreempt"选项。
! 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_script check_mysqld {
    script "/etc/keepalived/mysqlcheck/check_slave.sh 127.0.0.1"        #检测mysql复制状态脚本.
    interval 2
    weight 21
}

vrrp_instance HA_1 {
    state BACKUP                #在"mysql-master"和"mysql-slave"上均设置为BACKUP.
    interface eth0
    virtual_router_id 80
    priority 100
    advert_int 2
    nopreempt                   #不抢占模式，只在优先级高的机器上设置,优先级低的可以不设置.

    authentication {
        auth_type PASS
        auth_pass admin123
    }
    track_script {
    check_mysqld
    }

    virtual_ipaddress {
        192.168.1.50/24 dev eth0        #mysql对外服务ip.
    }
}

脚本区域:

mysql检测脚本放置目录:"/etc/keepalived/mysqlcheck/check_slave.sh"内容如下:

说明:这是用Shell写的检测MySQL复制状态的脚本，给脚本执行权限。

#!/bin/bash
export HOST_IP=$1
export HOST_PORT=3306
export IP=`ifconfig | grep Ethernet -A1 | grep -v Link | awk -F[' ':]+ '{print $4}'`
MYUSER=root
MYPASS="admin123"
MYSQL_PATH=/usr/bin/
MYSQL_CMD="$MYSQL_PATH/mysql -u$MYUSER -p$MYPASS"

MailTitle=""
time1=`date +"%Y%m%d%H%M%S"`
time2=`date +"%Y-%m-%d %H:%M:%S"`
SlaveStatusFile="/tmp/slave_status_${HOST_PORT}.${time1}.log"
echo "------------------Begins at :"$time2 > $SlaveStatusFile
echo "" >> $SlaveStatusFile

#get slave status
$MYSQL_CMD -e "show slave status\G" >> $SlaveStatusFile
#get io_thread_status, sql_thread_status
SlaveStatus=($($MYSQL_CMD  -e "show slave status\G" | egrep "_Running"|awk '{print $NF}'))
echo "" >> $SlaveStatusFile

if [ "${SlaveStatus[0]}" = "No" ] || [ "${SlaveStatus[1]}" = "No" ]
then
    echo "Slave $IP $HOST_PORT is down !" >> $SlaveStatusFile
    MailTitle="[ERROR] Slave replication is down on $IP $HOST_PORT !"
fi

if [ -n "$MailTitle" ]
then
    source /home/lizhiqiang/maillist.cfg
    cat ${SlaveStatusFile} | /bin/mail -s "$MailTitle" -c "$Mail_Address_MysqlStatus_cc" $Mail_Address_MysqlStatus
    /etc/init.d/keepalived stop
fi

3.完成以上配置,启动keepalived服务. (正常情况下VIP地址应运行在"mysql-master")

[root@mysql-master mysqlcheck]# /etc/init.d/keepalived start

报错区域:

[root@mysql-master mysqlcheck]# /etc/init.d/keepalived start
正在启动 keepalived：/bin/bash: keepalived: command not found
解决:
[root@mysql-master mysqlcheck]# cp /usr/local/sbin/keepalived /usr/sbin/
[root@mysql-master mysqlcheck]# /etc/init.d/keepalived start
正在启动 keepalived：                                      [确定]

三: 测试MySQL主从同步功能.

1.在远程客户端通过VIP登陆测试。

[root@mysql-master mysqlcheck]# mysql -u root -p -h 192.168.1.50
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| haha               |
| lizhiqiang         |
| mysql              |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> show variables like "%hostname%";
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| hostname      | mysql-slave |
+---------------+-------------+
1 row in set (0.00 sec)

mysql> use lizhiqiang
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql>

2.数据复制功能测试

#说明: 上述切换到名为"lizhiqiang"的数据库中,经查看无任何信息,以下操作在此库中插入"name"表测试复制。
mysql> create table name
    -> (
    -> id int not null auto_increment,
    -> first_name varchar(30) not null,
    -> last_name  varchar(30) not null,
    -> primary key (id),
    -> index (last_name,first_name)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> show tables;
+----------------------+
| Tables_in_lizhiqiang |
+----------------------+
| name                 |
+----------------------+
1 row in set (0.00 sec)

mysql>
#测试复制结果
[root@mysql-master etc]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
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> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| haha               |
| mysql              |
| test               |
| zabbix             |
+--------------------+
6 rows in set (0.00 sec)

mysql> use lizhiqiang
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+----------------------+
| Tables_in_lizhiqiang |
+----------------------+
| name                 |
+----------------------+
1 row in set (0.00 sec)

mysql> show variables like "%hostname%";
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | mysql-master |
+---------------+--------------+
1 row in set (0.00 sec)

mysql>

四: 测试keepalived实现MySQL故障转移.

说明: 因互为主从,所以如果"mysql-master"的slave是down，那么就指向了192.168.1.198;反之,"mysql-slave"的slave是down,那么就指定了192.168.1.87

1.停止"mysql-slave"的日志接收功能，先停"mysql-master"这边的slave，并重启keeplived服务
mysql> show variables like "%hostname%";
+---------------+--------------+
| Variable_name | Value        |
+---------------+--------------+
| hostname      | mysql-master |
+---------------+--------------+
1 row in set (0.00 sec)

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

#使用"tcpdump vrrp"查看日志信息，如下：
#可以很清楚的看到已经进行的切换.
12:34:29.130483 IP mysql-master > vrrp.mcast.net: VRRPv2, Advertisement, vrid 80, prio 100, authtype simple, intvl 2s, length 20
12:34:31.132067 IP mysql-master > vrrp.mcast.net: VRRPv2, Advertisement, vrid 80, prio 100, authtype simple, intvl 2s, length 20
12:34:33.132822 IP mysql-master > vrrp.mcast.net: VRRPv2, Advertisement, vrid 80, prio 100, authtype simple, intvl 2s, length 20
12:34:33.186314 IP mysql-master > vrrp.mcast.net: VRRPv2, Advertisement, vrid 80, prio 0, authtype simple, intvl 2s, length 20
12:34:33.835215 IP localhost > vrrp.mcast.net: VRRPv2, Advertisement, vrid 80, prio 90, authtype simple, intvl 2s, length 20
12:34:35.836022 IP localhost > vrrp.mcast.net: VRRPv2, Advertisement, vrid 80, prio 90, authtype simple, intvl 2s, length 20
12:34:37.837317 IP localhost > vrrp.mcast.net: VRRPv2, Advertisement, vrid 80, prio 90, authtype simple, intvl 2s, length 20
12:34:39.837814 IP localhost > vrrp.mcast.net: VRRPv2, Advertisement, vrid 80, prio 90, authtype simple, intvl 2s, length 20
12:34:41.839067 IP localhost > vrrp.mcast.net: VRRPv2, Advertisement, vrid 80, prio 90, authtype simple, intvl 2s, length 20
#停掉"mysql-slave"的slave结果相同，这里就不在演示了。
#至此，MySQL双主模式的主从复制和主从互备已完成。
标签：

相关文章