mysql+MHA+keepalived-Loading
Mysql版本mysql-5.5.35.tar.gzMHA节点版本mha4mysql-node-0.54-0.el6.noarch.rpm
MHAmanager版本mha4mysql-manager-0.54.tar.gz
Keepalived版本keepalived-1.2.9.tar.gz
主：192.168.2.220
从：192.168.2.221
VIP:192.168.2.222
主+从分别安装
1、配置安装编译环境
主从均安装
yum groupinstall "Development Tools" -y
主从均安装安装epel源
rpm -ivh http://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm
主mysql安装
yum install -y perl-DBD-MySQL
从mysql+MHAmanager安装
yum install -y perl-DBD-MySQL perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
主从均安装
yum -y install vim wget gcc-c++ ncurses ncurses-devel cmake make bison openssl openssl-devel gcc* libxml2 libxml2-devel curl-devel libjpeg* libpng* freetype*
2、建立mysql所需账号、目录及权限
useradd -d /usr/local/mysql/ mysql #创建一个Mysql用户，指定家目录到/use/local目录下。
mkdir /usr/local/mysql/data
mkdir /usr/local/mysql/log #新建mysql下data和log子目录
chown -R mysql:mysql /usr/local/mysql/data/
chown -R mysql:mysql /usr/local/mysql/log/
chmod 750 /usr/local/mysql/data
chmod 750 /usr/local/mysql/log #修改目录的所属者以及所属组
3、解压并安装mysql
tar xf mysql-5.5.35.tar.gz
cd mysql-5.5.35
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DEXTRA_CHARSETS=all \
-DWITH_MYISAM_STORAGE_ENGINE=1 \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_MEMORY_STORAGE_ENGINE=1 \
-DWITH_FEDERATED_STORAGE_ENGINE=1 \
-DWITH_READLINE=1 \
-DENABLED_LOCAL_INFILE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data/ \
-DMYSQL_USER=mysql \
-DMYSQL_TCP_PORT=3306 \
-DSYSCONFDIR=/etc \
-DWITH_SSL=yes
make & make install
编译注解：
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \ #安装目录
-DDEFAULT_CHARSET=utf8 \ #默认字符
-DDEFAULT_COLLATION=utf8_general_ci \ #校验字符
-DEXTRA_CHARSETS=all \ #安装所有扩展字符集
-DWITH_MYISAM_STORAGE_ENGINE=1 \ #安装myisam存储引擎
-DWITH_INNOBASE_STORAGE_ENGINE=1 \ #安装innodb存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \ #安装archive存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \ #安装blackhole存储引擎
-DWITH_MEMORY_STORAGE_ENGINE=1 \ #安装memory存储引擎
-DWITH_FEDERATED_STORAGE_ENGINE=1 \ #安装frderated存储引擎
-DWITH_READLINE=1 \ #快捷键功能
-DENABLED_LOCAL_INFILE=1 \ #允许从本地导入数据
-DMYSQL_DATADIR=/usr/local/mysql/data/ \ #数据库存放目录
-DMYSQL_USER=mysql \ #数据库属主
-DMYSQL_TCP_PORT=3306 \ #数据库端口
-DSYSCONFDIR=/etc \ #MySQL配辑文件
-DWITH_SSL=yes #数据库SSL
4.编写mysql配置项：
vi /etc/my.cnf
# CLIENT #
port = 3306
socket = /tmp/mysql.sock
# GENERAL #
user = mysql
default_storage_engine = InnoDB
socket = /tmp/mysql.sock
pid_file = /var/run/mysqld/mysqld.pid
# MyISAM #
key_buffer_size = 32M
myisam_recover = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
skip_name_resolve
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now = 1
innodb = FORCE
innodb_strict_mode = 1
# DATA STORAGE #
datadir = /usr/local/mysql/data
# CACHES AND LIMITS #
key_buffer_size = 256M
max_allowed_packet = 32M
sort_buffer_size = 16M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
thread_stack = 8M
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 2M
max_connections = 2048
thread_cache_size = 512
open_files_limit = 65535
table_definition_cache = 1024
table_open_cache = 2048
# INNODB #
innodb_log_files_in_group = 2
innodb_log_file_size = 64M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 128M
# LOGGING #
log-error=/usr/local/mysql/log/error.log
general_log=1
general_log_file=/usr/local/mysql/log/mysql.log
slow_query_log=1
slow_query_log_file=/usr/local/mysql/log/slowquery.log
log-output=FILE
skip-external-locking
local-infile=1
# BINARY LOGGING #
log-bin =/usr/local/mysql/log/bin.log
expire_logs_days = 30
sync_binlog = 1
5.将mysql的库文件路径加入系统的库文件搜索路径中
利用ldconfig导入系统库
echo "/usr/local/mysql/lib" >> /etc/ld.so.conf.d/mysql.conf
ldconfig
6.输出mysql的头文件到系统头文件
ln -s /usr/local/mysql/include/mysql /usr/include/mysql
7.进入安装路径,初始化配置脚本
cd /usr/local/mysql
scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data
8.复制mysql启动脚本到系统服务目录
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
9.系统启动项相关配置
chkconfig --add mysqld#添加开机启动服务
chkconfig mysqld on#设置mysql启动
10.启动mysql
service mysqld start
注：编译时若socket路径自定义为/var/lib/mysql/mysql.sock，这里需要创建一个mysql接口的软链接，防止登陆后台或安装论坛报错.
# ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock
vim /etc/profile
配置mysql环境变量
export PATH=$PATH:/usr/local/mysql/bin
加载环境变量
. /etc/profile
11. 设置初始账户,并登陆后台:
/usr/local/mysql/bin/mysqladmin -u root password 111111 #设置密码
进入mysql
mysql -u root -p111111 #连接数据库
-----------------------------------
mysql> grant all privileges on *.* to root@'%'>
mysql> flush privileges; #刷新
mysql> show variables; #查看mysql设置.
12、配置主从同步
主mysql配置
# vi /etc/my.cnf
添加
-----------------
# Replication Master Server
log-bin = /usr/local/mysql/log/bin.log
# ServerID
server-id = 1
# 忽略mysql系统库复制
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
------------
重启服务
# service mysqld restart
登录mysql后台
# mysql -u root -p111111
查看此刻登录账号
> select user();
在master为slave添加同步帐号
> grant replication slave on *.* to 'slave'@'192.168.2.221'>
查看创建的用户
> select user.host from mysql.user;
查看权限
> show grants for 'slave'@'192.168.2.221';
mysql锁表只读(其他账户登录mysql后无法进行写表操作防止备份数据库后主mysql表更新导致和从数据库内容不一致)
> flush tables with read lock;
查看锁表倒计时时间
> show variables like '%timeout%';
------------------------
....
wait_timeout| 28800
------------------------
将master的数据库表全部备份导出并传送到slave服务器上。
/usr/local/mysql/bin/mysqldump -u root -p111111 --opt --flush-logs --all-database > /root/allbak.sql
cd ~
scp allbak.sql root@192.168.2.221:/root/allbak.sql
查看mysql偏移量(数据库如果有写操作偏移值会递增)
# mysql -u root -p111111 -e "show master status"
----------------------
+------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------+----------+--------------+------------------+-------------------+
| bin.000009 | 120 || | |
+------------+----------+--------------+------------------+-------------------+
----------------------
保证FILE列和Position列与从库配置一致
从mysql配置mysql slave
修改mysql配置文件,候选主服务器log-bin必须开启
# vi /etc/my.cnf
添加
--------------------
# Replication Slave Server
log-bin = /usr/local/mysql/log/bin.log
server-id=2
# 只读
read-only
# 忽略mysql系统库复制
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema
binlog-ignore-db=test
---------------------
重启服务
service mysqld restart
恢复server的数据库到slave
/usr/local/mysql/bin/mysql -u root -p111111 < /root/allbak.sql
配置连接同步到server端
# mysql -u root -p111111
> stop slave;
> reset slave;
> change master to master_host='192.168.2.220',master_user='slave',master_password='111111',master_log_file="bin.000009",master_log_pos=120;
> start slave;
> show slave status;
同步设置好后执行
> set global read_only=1;
> set global>
注master_log_file表示从主数据库哪个bin-log文件开始同步
master_log_pos表示从该bin-log文件哪条记录点开始同步
需与主库偏移值保持同步
回到主mysql数据库解锁mysql master
mysql -u root -p111111
> unlock tables;
最后登陆从mysql后台查看主从连接状态
mysql -u root -p111111 -e "show slave status\G"
找到这五行如下则主从配置成功
------------------------
Slave_IO_State: Waiting for master to send event
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Read_Master_Log_Pos: 120
Relay_Master_Log_File: bin.000009
13、部署MHA
MHA节点包含三个脚本，依赖perl模块。
save_binary_logs:保存和复制当掉的主服务器二进制日志。
apply_diff_relay_logs:识别差异的relay log事件，并应用于其他salve服务器。
purge_relay_logs:清除relay log文件。
需要在所有mysql服务器上安装MHA节点，MHA管理服务器也需要安装。MHA管理节点模块内部依赖MHA节点模块。MHA管理节点通过ssh连接管理mysql服务器和执行MHA节点脚本。MHA节点依赖perl的DBD::mysql模块。
安装MHA节点（主从都要安装）
rpm -ivh mha4mysql-node-0.54-0.el6.noarch.rpm
安装MHA manager （目前规定manager部署在slave服务器上192.168.2.221）
rpm -ivh mha4mysql-manager-0.54-0.el6.noarch.rpm
建立ssh无密码登录环境
主mysql（192.168.2.220）
ssh-keygen -t rsa
ssh-copy-id -i .ssh/id_rsa.pub root@192.168.2.221
从mysql（192.168.2.221）
ssh-keygen -t rsa
ssh-copy-id -i .ssh/id_rsa.pub root@192.168.2.220
ssh-copy-id -i .ssh/id_rsa.pub root@192.168.2.221
因为mha运行时需要用到/usr/bin下的两个mysql命令（貌似路径写死了，更改/etc/profile对mha无效），所以建立两个软连接
ln -s /usr/local/mysql/bin/mysqlbinlog /usr/bin/mysqlbinlog
ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql
部署mha
cd mha4mysql-manager-0.54
mkdir /etc/mha
将manager的配置文件和脚本考到/etc/mha目录下
cp -r samples/* /etc/mha/
vim /etc/mha/conf/masterha_default.cnf
user=root
password=111111
ssh_user=root
repl_user=slave
repl_password=111111
master_binlog_dir= /usr/local/mysql/data,/usr/local/mysql/log
remote_workdir=/usr/local/mha
ping_interval=1
# master_ip_failover_script= /etc/mha/scripts/master_ip_failover
# shutdown_script= /etc/mha/scripts/power_manager
report_script= /etc/mha/scripts/send_report
master_ip_online_change_script= /etc/mha/scripts/master_ip_online_change
vim /etc/mha/conf/app1.cnf
manager_workdir=/usr/local/mha
manager_log=/usr/local/mha/manager.log
hostname=192.168.2.220
candidate_master=1
hostname=192.168.2.221
candidate_master=1
测试ssh连接
masterha_check_ssh --global_conf=/etc/mha/conf/masterha_default.cnf--conf=/etc/mha/conf/app1.cnf
测试复制
masterha_check_repl --global_conf=/etc/mha/conf/masterha_default.cnf--conf=/etc/mha/conf/app1.cnf
启动mha
nohup masterha_manager --global_conf=/etc/mha/conf/masterha_default.cnf--conf=/etc/mha/conf/app1.cnf
也可在screen命令中启动
在备节点，从节点,执行定期删除中继日志
00 00 * * * /usr/local/bin/purge_relay_logs -host=192.168.2.221 -user=root -password=111111 -disable_relay_log_purge >> /usr/local/purge_relay_logs.log 2>&1
安装keepalived
tar zxvf keepalived-1.2.7.tar.gz
cd keepalived-1.2.7
./configure --prefix=/usr/local/keepalived --with-kernel-dir=/usr/src/kernels/2.6.32-431.el6.x86_64
make && make install
设置keepalived开机启动脚本
cp /usr/local/keepalived/etc/rc.d/init.d/keepalived /etc/rc.d/init.d/
cp /usr/local/keepalived/etc/sysconfig/keepalived /etc/sysconfig/
cp /usr/local/keepalived/sbin/keepalived /usr/sbin/
chkconfig keepalived on
mkdir /etc/keepalived
vim /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
47532732@qq.com
}
notification_email_from monitor@mchina.cn
smtp_server smtp.exmail.qq.com
smtp_connect_timeout 30
router_id mha
}
vrrp_script check_run {
script "/etc/keepalived/check_mysql.sh"
interval 1
}
vrrp_instance VI_1 {
state BACKUP
interface eth0
virtual_router_id 51
priority 100 #master要高于slave，slave上的设置为90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
check_run
}
virtual_ipaddress {
192.168.2.222
}
}
vim /etc/keepalived/check_mysql.sh
#!/bin/bash
MYSQL=/usr/local/mysql/bin/mysql
MYSQL_HOST=127.0.0.1
MYSQL_USER=root
MYSQL_PASSWORD=
CHECK_TIME=3
#mysqlis working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=1
else
MYSQL_OK=0
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 0 ] &&[ $CHECK_TIME -eq 0 ]
then
pkill keepalived
exit 1
fi
sleep 1
done
chmod +x /etc/keepalived/check_mysql.sh
主从均启动keepalived
查看虚拟ip地址是否生效
ip addr
注意
如果master上的mysql服务停止后，master上的keepalived服务也将停止，并将虚拟ip：192.168.2.222漂移给slave服务器。
如果master上的mysql服务停止后，安放在slave上的masterha_manager进程会把slave上的mysql服务提升为master，read_only会自动变为off，relay_log_purge会变为on。并且mysql从slave提升为master动作后，masterha_manager进程会自动关闭。
在masterha_manager的工作目录/usr/local/mha/下会产生app1.failover.complete文件。如果想恢复原来主mater的mysql，并恢复原来的主从+mha+keepalived构架，需要重新部署原master的mysql数据库（停掉web服务，主还原备主上的最新的数据库，并重新部署主从），并且需要删除app1.failover.complete文件，否则可能会导致切换不成功。
恢复后在从（如果就两台服务器的话，这里指的是备主那台服务器）mysql里执行以下命令重新同步
> stop slave;
> reset slave;
> change master to master_host='192.168.2.220',master_user='slave',master_password='111111',master_log_file="bin.000009",master_log_pos=120;
> start slave;
> show slave status;
同步设置好后执行
> set global read_only=1;
> set global>
注master_log_file表示从主数据库哪个bin-log文件开始同步
master_log_pos表示从该bin-log文件哪条记录点开始同步
需与主库偏移值保持同步
页:
[1]