所需环境：2台服务器+1个VIP地址，VIP作外部访问数据库使用。
本文中所用到的服务器为：172.19.2.158和172.19.2.160；VIP为172.19.2.161
操作系统为：CentOS 6.5（跟redhat6.5一样）
***************************************************************************************************
*****************************************
mysql 安装
**********************************************
***************************************************************************************************
1.下载系统对应rpm包(当前系统为CentOS6.5):
mysql-community-client-5.7.19-1.el6.x86_64.rpm
mysql-community-common-5.7.19-1.el6.x86_64.rpm
mysql-community-libs-5.7.19-1.el6.x86_64.rpm
mysql-community-server-5.7.19-1.el6.x86_64.rpm
2.使用rpm -qa|grep mysql命令查看当前系统中是否存在mysql的包。
如存在，使用rpm -e --nodeps 命令将对应的包卸载
3.安装mysql
rpm -ivh mysql-community-common-5.7.19-1.el6.x86_64.rpm
rpm -ivh mysql-community-libs-5.7.19-1.el6.x86_64.rpm
rpm -ivh mysql-community-client-5.7.19-1.el6.x86_64.rpm
rpm -ivh mysql-community-server-5.7.19-1.el6.x86_64.rpm
4MySQL的默认安装位置
/var/lib/mysql/ #数据库目录
/usr/share/mysql #配置文件目录
/usr/bin #相关命令目录
/etc/init.d/mysqld #启动脚本
5.初始化mysql以及设置密码
[root@localhost ~]# service mysqld start #启动mysql
[root@localhost ~]# cat /root/.mysql_secret #查看root账号密码
# Password set for user 'root@localhost' at 2017-07-20 16:30:12
ewBa8LKkumQn
[root@localhost ~]#mysql -u root –p ewBa8LKkumQn
mysql> SET PASSWORD = PASSWORD('Abc_123456'); #修改密码为Abc_123456
****如果没有root用户的初始密码，可以修改my.cnf，加上
skip-grant-tables，重启mysqld服务进
入无密码模式，修改mysql库下的user表，update user set authentication_string=password('123456') where user='root';
（在5.7.19版本中用户密码字段为authentication_string，其他有的版本为password字段）
退出mysql，将my.cnf中的skip-grant-tables删除，重启mysqld服务，即可用修改之后的root密码登陆
****
6.
允许远程登陆
mysql> select host,user,authentication_string from user;
+-----------+---------------+-------------------------------------------+
| host | user | authentication_string |
+-----------+---------------+-------------------------------------------+
| localhost | root | *D6711BADD6AFBA33B6F31F9134644ADEE758255B |
| localhost | mysql.session | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
| localhost | mysql.sys | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE |
+-----------+---------------+-------------------------------------------+
mysql> update user set host='%' where user='root' and host='localhost'; #设置为可以远程登陆
mysql> flush privileges;
mysql> exit
7.设置开机自启动
[root@localhost ~]# chkconfig mysqld on
[root@localhost ~]# chkconfig --list|grep mysqld
mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off
8.修改字符集和数据存储路径
配置/etc/my.cnf文件,修改数据存放路径、mysql.sock路径以及默认编码utf-8.
[client]
default-character-set=utf8
[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
#(注意linux下mysql安装完后是默认：表名区分大小写，列名不区分大小写； 0：区分大小写，1：不区分大小写)
lower_case_table_names=1
#(设置最大连接数，默认为 151，MySQL服务器允许的最大连接数16384; )
max_connections=1000
[mysql]
default-character-set = utf8
9查看当前字符集
show variables like '%collation%';
show variables like '%char%';
***************************************************************************************************
**********************************
keepalived安装及配置
******************************************
***************************************************************************************************
一、mysql主主备份环境配置：
1.172.19.2.158上mysql配置修改
a) 编辑/etc/my.cnf文件，
在[mysqld]中加如server-id=172192158 和log-bin=mysql-bin
b) 重启mysql，创建授权用户：
mysql>
grant replication slave on *.* to 'replication'@'%' identified by 'Abc_123456';
#replication 为用户名 Abc_123456为该用户的密码
c)
查看master状态
mysql>
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 436 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
上面的File和Postion是另一台服务器所需要用到的信息
2.172.19.2.158上mysql配置修改
a) 编辑/etc/my.cnf文件，
在[mysqld]中加如server-id=172192158 和log-bin=mysql-bin
b) 重启mysql，创建授权用户：
mysql>
grant replication slave on *.* to 'replication'@'%' identified by 'Abc_123456'; #replication 为用户名 Abc_123456为该用户的密码
c)
查看master状态
mysql>
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 436 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
上面的File和Postion是另一台服务器所需要用到的信息
3.设置主备
a) 登陆到
172.19.2.158上执行
mysql>
change master to master_host='172.19.2.160',master_user='replication',master_password='Abc_123456',master_log_file='mysql-bin.000001',master_log_pos=436;
b) 查看备份状态：
mysql>
show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.19.2.160
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 436
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
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:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 436
Relay_Log_Space: 531
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: 172192160
Master_UUID: 22ef1cd3-6dc4-11e7-8692-0050568c6ba9
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 more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
c) 登陆到
172.19.2.160上执行
mysql>
change master to master_host='172.19.2.158',master_user='replication',master_password='Abc_123456',master_log_file='mysql-bin.000002',master_log_pos=436;
d) 查看备份状态：
mysql>
show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 172.19.2.158
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 436
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000002
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: 436
Relay_Log_Space: 531
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: 172192158
Master_UUID: 711791c2-6db4-11e7-9b3f-0050568ce1ac
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 more updates
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
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
问题：
1.出现Slave_IO_Running:No 和Slave_SQL_Running:No的情况
mysql> stop slave;
将设置主备步骤重新做一边 然后再重新启动slave即可解决
mysql> start slave;
二、安装keepalived
1
、安装
keepalived
yum install keepalived
2.
创建健康检查脚本
MySql.sh
，当
mysql
关闭时，会自动执行这个脚本，关闭
keepalived
脚本内容如下：
#pkill keepalived
#!/bin/bash
MYSQL=/usr/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD="Abc_123456"
CHECK_TIME=3
#mysql is working MYSQL_OK is 0 , mysql down MYSQL_OK is 1
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p $MYSQL_PASSWORD -e "show status;" > /dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=0
else
MYSQL_OK=1
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 0 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 1 ] && [ $CHECK_TIME -eq 1 ]
then
/etc/init.d/keepalived stop
exit 1
fi
sleep 1
done
#!/bin/bash
MYSQL=/usr/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD="Abc_123456"
CHECK_TIME=3
#mysql is working MYSQL_OK is 0 , mysql down MYSQL_OK is 1
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p $MYSQL_PASSWORD -e "show status;" > /dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=0
else
MYSQL_OK=1
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 0 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 1 ] && [ $CHECK_TIME -eq 1 ]
then
/etc/init.d/keepalived stop
exit 1
fi
sleep 1
done
3、
172.19.2.158的配置文档
/etc/keepalived/keepalived.conf
如下：
global_defs {
notification_email { #当出错时，发送邮件给谁
xx@123.com
}
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-ha
}
vrrp_instance VI_1 {
state MASTER #一台配置为master 一台为backup
interface eth0 #需要绑定的网卡
virtual_router_id 51 #ID，两台机器的ID必须一样
priority 100 #优先级，另一台改为90 ，优先级高的自动竞选为master
advert_int 1
nopreempt #不抢占，只在优先级高的机器上设置即可，优先级低的机器不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.19.2.161
#VIP地址
}
}
virtual_server
172.19.2.161
3306 {
delay_loop 2 #每隔2秒检查一次real_server状态
lb_algo rr #LVS算法
lb_kind DR #LVS模式
persistence_timeout 60 #会话保持时间
nat_mask 255.255.255.0 #掩码
protocol TCP
real_server
172.19.2.158
3306 {
weight 1
notify_down /home/dataprocess/MySQL.sh #检测到服务down后执行的脚本
TCP_CHECK {
connect_timeout 10 #连接超时时间
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
connect_port 3306 #健康检查端口
}
}
}
4、
172.19.2.160的配置文档
/etc/keepalived/keepalived.conf
如下：
global_defs {
notification_email {
}
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MySQL-ha
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.19.2.161
}
}
virtual_server
172.19.2.161
3306 {
delay_loop 2
lb_algo rr
lb_kind DR
persistence_timeout 60
nat_mask 255.255.255.0
protocol TCP
real_server
172.19.2.160
3306 {
weight 1
notify_down /home/dataprocess/MySQL.sh
TCP_CHECK {
connect_timeout 10
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
7、启动keepalived：
service keepalived start
可以在master机器上，运行ip a，可以查看到VIP的绑定
keepalived的日志：/var/log/message
8.使用mysql -u root -p -h 172.19.2.161登陆mysql
[app@localhost ~]$ mysql -u root -p -h 172.19.2.161
输入show variables like 'server_id' 查看当前使用的mysql
mysql> show variables like 'server_id' ;
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| server_id | 172192158 |
+---------------+-----------+
1 row in set (0.00 sec)
关闭当前节点 再次查看
mysql> show variables like 'server_id' ;
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| server_id | 172192160 |
+---------------+-----------+
1 row in set (0.00 sec)
节点已经自动切换，集群成功。
**在节点切换过程中可能会出现找不到链接的情况，但是稍等一下就可以了。
mysql> show variables like 'server_id' ;
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| server_id | 172192160 |
+---------------+-----------+
1 row in set (0.00 sec)
mysql> show variables like 'server_id' ;
ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> show variables like 'server_id' ;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 20301
Current database: *** NONE ***
+---------------+-----------+
| Variable_name | Value |
+---------------+-----------+
| server_id | 172192158 |
+---------------+-----------+
1 row in set (0.02 sec)