mysql双机热备方案

1. 环境规划:

node1(mysql1)	192.168.10.94
node2(mysql2)	192.168.10.95
vip	192.168.10.222
数据库	mysql-5.6.26

2.mysql安装

2.1卸载查看到的包

#查看是否有已安装的mysql，如果有卸载
rpm -qa|grep -mysql

# 通常系统自带mysql-libs，将其卸载
yum remove mysql-libs

2.2安装mysql

tar -xvf MySQL-5.6.35-1.el6.x86_64.rpm-bundle.tar

yum localinstall MySQL-client-5.6.35-1.el6.x86_64.rpm MySQL-server-5.6.35-1.el6.x86_64.rpm MySQL-devel-5.6.35-1.el6.x86_64.rpm 

2.3初始化数据库

进行初始化脚本之前需要先启动mysql服务和复制mysql的随机密码。在初始化总第一步将使用随机密码。

# 查看随机初始密码
cat /root/.mysql_secret
service mysql start
# 初始化mysql
/usr/bin/mysql_secure_installation

初始化：

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

You already have a root password set, so you can safely answer 'n'.

Change the root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!

By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] Y
... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] Y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] Y
... Success!

All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


Cleaning up...

3.3修改mysql配置

yum安装找不到/etc/my.cnf，先将配置文件拷贝到/etc下

cp /usr/share/mysql/my-default.cnf /etc/my.cnf
vim /etc/my.cnf

分别修改node1和node2上的/etc/my.cnf

[mysqld]
#生产环境要把落盘目录放到挂载盘中
#修改此目录需要把源目录（/var/lib/mysql/）下的文件拷贝到此，并授权为mysql:mysql
#chown -R mysql:mysql mysql/
datadir=/data/mysql 
socket=/var/lib/mysql/mysql.sock
user=mysql
#主要两个配置文件区别
server-id=1
log-bin=mysqlbin-log
symbolic-links=0
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid    #需要授权

[client]
default-character-set=utf8 

node2:只需要修改server-id

[mysqld]
#修改此目录需要把源目录（/var/lib/mysql/）下的文件拷贝到此，并授权为mysql:mysql
datadir=/data/mysql 
socket=/var/lib/mysql/mysql.sock
user=mysql
server-id=2
log-bin=mysqlbin-log
symbolic-links=0
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid    #需要授权

[client]
default-character-set=utf8

node1和node2重启mysql

# /etc/init.d/mysql restart

3.4设置主主复制

node1设置：

mysql>grant replication slave on *.* to slave@192.168.10.95 identified by '123456';
mysql> flush privileges;

mysql> change master to master_host='192.168.10.95', master_user='slave', master_password= ‘123456’;

#启动并查看状态
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

显示结果：

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.95
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql2-bin.000003
Read_Master_Log_Pos: 408
Relay_Log_File: mysql1-relay-bin.000004
Relay_Log_Pos: 572
Relay_Master_Log_File: mysql2-bin.000003
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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 408
Relay_Log_Space: 910
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:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 2
Master_UUID: 1343e450-b350-11e5-9ecb-005056b721a6
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
1 row in set (0.00 sec)

ERROR:
No query specified

node2登录mysql

mysql>grant replication slave on *.* to slave@192.168.10.94 identified by '123456';
mysql> flush privileges;

mysql> change master to master_host='192.168.10.94', master_user='slave', master_password= ‘123456’;

#启动并查看状态
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

mysql> show slave status\G;

显示结果：

*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.94
Master_User: slave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql1-bin.000004
Read_Master_Log_Pos: 408
Relay_Log_File: mysql2-relay-bin.000005
Relay_Log_Pos: 572
Relay_Master_Log_File: mysql1-bin.000004
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: 
Last_Errno: 0
Last_Error: 
Skip_Counter: 0
Exec_Master_Log_Pos: 408
Relay_Log_Space: 910
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: 
Replicate_Ignore_Server_Ids: 
Master_Server_Id: 1
Master_UUID: 2b56309a-b350-11e5-9ecb-005056b77a1b
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind: 
Last_IO_Error_Timestamp: 
Last_SQL_Error_Timestamp: 
Master_SSL_Crl: 
Master_SSL_Crlpath: 
Retrieved_Gtid_Set: 
Executed_Gtid_Set: 
Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

出现“Slave_IO_Running: Yes”和“Slave_SQL_Running: Yes”说明成功

如MySQL在主从复制的时候经常遇到错误而导致Slave复制中断，这个时候就需要人工干涉，来跳过这个错误，才能使Slave端的复制，得以继续进行；

跳过错误的方法：

mysql> STOP SLAVE;
mysql > SET GLOBAL  SQL_SLAVE_SKIP_COUNTER=1;
#跳过一个事务，可根据情况设置跳过多个错误。
mysql > SHOW GLOBAL VARIABLES LIKE 'SQL_SLAVE_SKIP_COUNTER';  +------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| sql_slave_skip_counter | 1     |
+------------------------+-------+
mysql > START SLAVE;

3.5测试主主复制

在node1进入mysql：

mysql> create database world;
mysql> use world;
mysql> create table t1 ( id int );
mysql> insert into t1 values (1),(2),(3);

在node2可以看到已经建立的word数据库和数据库中t1表

mysql> use world;
mysql> select * from t1;
+------+
| id  |
+------+
|  1|
|  2 |
|  3 |
+------+
3 rows in set (0.00 sec)
再插入一行数据
mysql> insert into t1 values (4);

在node1可以看到新插入的一条数据

3.keepalived安装

3.1通过yum安装keepalived

yum install keepalived-1.2.13-5.el6_6.x86_64.rpm

3.2修改keepalived配置

在每个节点上备份keepalived配置文件，并设置。

cd /etc/keepalived
mv keepalived.conf keepalived.conf.bak
vim keepalived.conf

Node1设置配置文件：

! Configuration File for keepalived
 
global_defs {
   notification_email {
     root@lkgBlogs.com
   }
   notification_email_from keepalived@localhost  
   smtp_server 127.0.0.1
   smtp_connect_timeout 30
   router_id LVS_DEVEL
}
 
vrrp_instance HA_1 {
    state BACKUP                #master和slave都配置为BACKUP
    interface eth0              #指定HA检测的网络接口
    virtual_router_id 80        #虚拟路由标识，主备相同
    priority 100                #定义优先级，slave设置90
    advert_int 1                #设定master和slave之间同步检查的时间间隔
    nopreempt                   #不抢占模式。只在优先级高的机器上设置即可
    authentication {
        auth_type PASS
        auth_pass 1111
    }
 
    virtual_ipaddress {                 #设置虚拟IP，可以设置多个，每行一个
        192.168.10.222       #MySQL对外服务的IP，即VIP
    }
}
 
virtual_server 192.168.10.222 3306 {
    delay_loop 2                    #每隔2秒查询real server状态
    lb_algo wrr                     #lvs 算法
    lb_kinf DR                      #LVS模式（Direct Route）
    persistence_timeout 50
    protocol TCP
 
    real_server 192.168.10.94 3306 {    #监听本机的IP
        weight 1
        notify_down /etc/keepalived /mysql.sh
        TCP_CHECK {
        connect_timeout 10         #10秒无响应超时
        bingto 192.168.10.222
        nb_get_retry 3
        delay_before_retry 3
        connect_port 3306
        }
    }
 
}

node2服务器只修改priority为90、nopreempt不设置、real_server设置本地IP。

注意：

虚拟路由标识（virtual_router_id），主备相同。如果集群中有多个主从或者主主mysql，则需要将两个集群的virtual_router_id设置成不同的值。

3.3添加mysql脚本

node1和node2上创建/etc/keepalived/mysql.sh: # vim /etc/keepalived/mysql.sh 

#!/bin/bash
pkill keepalived

node1和node2上均执行

chmod +x mysql.sh
# node1和node2均启动keepalived
/etc/init.d/keepalived start

3.5测试

1.通过mysql客户端登录通过VIP11.11.168.222登录MySQL，查看MySQL连接状态

mysql> show variables like 'hostname%';

当杀死一个mysql后，自动转移到另一个mysql上。

2.ip addr