mysql双主+keepalived实现mysql高可用并为ambari 与hive 提供服务
mysql双主
同步初始状态
1、将主服务器要同步的数据库加锁，避免同步时发生改变:
use database_name;
flush tables with read lock;
这里加锁了，可能会影响业务，不加锁也可以详见在这里
2、使用mysqldump工具导出数据:
mysqldump -uroot -pxxx database_name >database_name.sql
mysqldump -uroot -pxxx -R database_name >database_name.sql # -R参数表示同时备份函数
3、将初始数据导入从数据库:
create database database_name;
use database_name;
source database_name.sql;
完成以上操作后，两个服务器就有一样的初态了。
4、备份完成并开始同步后，解锁数据库:（这里先不执行等开始同步）
unlock tables;
配置
主服务my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
datadir = /data02/mysql
# port = .....
#主主复制配置:
auto_increment_offset=2
auto_increment_increment=2
log_bin_trust_function_creators=1
expire_logs_days=30
server_id = 1
socket =/tmp/mysql.sock
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
log_bin = mysql-bin
log_bin_index = mysql-bin.index
# STATEMENT (statement-based replication, SBR) [default]
# ROW (row-based replication, RBR)
# MIXED (mixed-based replication, MBR)
binlog_format = ROW
# less than 1G [default] more than 4kb
max_binlog_size = 1G
expire_logs_days = 0
# 0 [default]
#配置从库上的更新操作是否写入二进制文件，如果这台从库，还要做其他从库的主库，那么就需要打这个参数，以便从库的从库能够进行日志同步
sync_binlog = 1
log-slave-updates
#中继日志文件
relay_log = mysql-relay-bin
#都注释掉表示同步所有库
#需要复制的数据库名，如果有多个数据库，需要重复设置此参数，每个数据库一行
#replicate-do-db=ambari
#replicate-do-db=hive
#replicate-do-db=ranger
#不需要复制的数据库，如果有多个数据库，需要重复设置此参数，每个数据库一行
#replicate-ignore-db=mysql
#replicate-ignore-db=information_schema
#replicate-ignore-db=performance_schema
#replicate-ignore-db=sys
slow_query_log = 1
slow_query_log_file = mysql-slow.log
log_error = mysql.err
log_output = 'FILE,TABLE'
max_connections = 16384
max_user_connections = 0
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
lower_case_table_names=1
# default
innodb_default_row_format=DYNAMIC
innodb_file_per_table=ON
innodb_large_prefix=ON
pid-file=/tmp/mysql.pid
从配置my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html
# *** DO NOT EDIT THIS FILE. It's a template which will be copied to the
# *** default location during install, and will be replaced if you
# *** upgrade to a newer version of MySQL.
[mysqld]
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
# These are commonly set, remove the # and set as required.
# basedir = .....
datadir = /data02/mysql
# port = .....
auto_increment_offset=1
auto_increment_increment=2
log_bin_trust_function_creators=1
expire_logs_days=30
server_id = 2
socket =/tmp/mysql.sock
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER
log_bin = mysql-bin
log_bin_index = mysql-bin.index
# STATEMENT (statement-based replication, SBR) [default]
# ROW (row-based replication, RBR)
# MIXED (mixed-based replication, MBR)
binlog_format = ROW
# less than 1G [default] more than 4kb
max_binlog_size = 1G
expire_logs_days = 0
# 0 [default]
sync_binlog = 1
log-slave-updates
relay_log = mysql-relay-bin
#replicate-do-db=ambari
#replicate-do-db=hive
#replicate-do-db=ranger
#replicate-ignore-db=mysql
#replicate-ignore-db=information_schema
#replicate-ignore-db=performance_schema
#replicate-ignore-db=sys
slow_query_log = 1
slow_query_log_file = mysql-slow.log
log_error = mysql.err
log_output = 'FILE,TABLE'
max_connections = 16384
max_user_connections = 0
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
lower_case_table_names=1
# default
innodb_default_row_format=DYNAMIC
innodb_file_per_table=ON
innodb_large_prefix=ON
pid-file=/tmp/mysql.pid
登录mysql创建用于同步的用户账号:
可在备份之前在主服务上做一次就行，全库备份的话；
在10.218.12.17上执行：
mysql -uroot -p
CREATE USER 'repuser'@'%'
grant replication slave on *.* to 'repuser'@'10.218.12.21' identified by 'repuser';
flush privileges;
select user,host from mysql.user;
show grants for repuser@'10.218.12.21';
在10.218.12.21上执行：
mysql -uroot -p
CREATE USER 'repuser'@'%'
grant replication slave on *.* to 'repuser'@'10.218.12.17' identified by 'repuser';
flush privileges;
select user,host from mysql.user;
show grants for repuser@'10.218.12.17';
服务器开启同步:
在2个master上执行：
flush logs;
show master status\G
master_host ： 主服务IP
master_user: 主服务器允许复制的用户名
master_password: 密码
master_log_file ： 当前日志文件的名称 在主服务器上用 show master status\G 查看相关信息
master_log_pos ： 当前日志文件的位置 在主服务器上用 show master status\G 查看相关信息
在2个slave上执行：
change master to master_host='10.218.12.17',master_user='repuser',master_password='repuser',master_log_file=' mysql-bin.000001',master_log_pos= 154,master_port=3306
启动slave同步进程
在两个mysql服务器上执行
mysql>start slave;
mysql>show slave status\G
查看这两个进程是否yes
Slave_IO_Running: yes
Slave_SQL_Running: yes
在主服务上：
unlock tables;
问题：
如果出现’Could not find first log file name in binary log index file’报错Slave_IO_Running: NO
client机器停止slave：stop slave;
master查看：
show master status\G；
flush logs;
show master status\G；
因为刷新日志file的位置会+1，即File变成为:mysqld-bin.000011
client机器修改位置：
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000005',MASTER_LOG_POS=154;
start slave;
show slave status\G
注意：
如果是做升级，请将mysql相关服务全部关停，不然会出现Slave_SQL_Running:no 的情况；
关闭ambari-server 和hive 所有服务
keepalived
安装
yum install -y keepalived
配置
主使用的mysql上配置
cat /etc/keepalived/keepalived.conf
vrrp_script check_mysql {
script "/usr/sbin/check_mysql.sh"
interval 2
weight 2
}
vrrp_instance MYSQL_HA {
state MASTER
interface eth0
virtual_router_id 50
priority 100
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass password123
}
track_script {
check_mysql
}
virtual_ipaddress {
192.168.1.24/24 dev eth0
}
}
次要使用的mysql上配置：
cat /etc/keepalived/keepalived.conf
vrrp_script check_mysql {
script "/usr/sbin/check_mysql.sh"
interval 2
weight 2
}
vrrp_instance MYSQL_HA {
#state MASTER
state BACKUP
#注意使用ifconig 找好网卡有的是bond0
interface eth0
virtual_router_id 50
priority 90
#nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass password123
}
track_script {
check_mysql
}
virtual_ipaddress {
192.168.1.24/24 dev eth0
}
}
两个mysql主机配置：
cat /usr/sbin/check_mysql.sh
#!/bin/sh
a=`ps -aux|grep mysqld|wc -l`
if ["$a" -lt 3]
sudo systemctl stop keepalived
fi
启动keepalived
sudo systemctl start keepalived
sudo systemctl status keepalived
问题
1.keepalive master挂掉重启后，服务回切，担心丢数据，客户要求不回切，而且本机连接始终会是本机。
2.hive 连接vip后，mysql浮动引起，时而连上，时而time out，现在就让mysql不浮动。
解决修改keepalived配置
virtual_router_id 51 #分组，路由标识，同一个虚拟组一定要相同
priority 100 #优化级，两个都是backup的时候，谁的优化级大，谁就是主
nopreempt #两个都是backup的时候，不指明就不抢占资源
配置成丛丛，保持2个keepalived 配置一样就可以了
vrrp_script check_mysql {
script "/etc/keepalived/check_mysql.sh"
interval 2
weight 2
fall 2
rise 1
}
vrrp_instance VI_1 {
#state MASTER
state BACKUP
interface bond0
virtual_router_id 51
priority 10
nopreempt
advert_int 1
authentication {
auth_type PASS
auth_pass password123
}
track_script {
check_mysql
}
virtual_ipaddress {
10.218.12.200
}
}
查看 ip：
ip a
200应该只在一个机器上，这就是正确的
在看mysql连接
两台都定位在一个mysql正常了
扫一扫
221
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
