MySQL双主keepalived安装配置

1． 查看系统是否有mysql已经安装，有的卸载 rpm -qa |grep mysql

rpm -e mysql-libs-5.1.67-1.el6_3.x86_64 –nodeps

2. 软件拷贝到/app下

tar xzvf mysql-5.6.38-linux-glibc2.12-x86_64.tar mv mysql-5.6.38-linux-glibc2.12-x86_64 mysql

3. 建立用户或者授权 groupadd -g 1001 dba

useradd -u 514 -g dba -G root -d /home/appuser appuser usermod -u 514 -g dba -G root -d /home/appuser appuser id appuser

4. 目录及授权

ln -s /app/mysql /usr/local/mysql chown –R appuser:dba /app/mysql chmod -R 755 /app/mysql su – appuser

cd /usr/local/mysql mkdir var log

5. 开始安装

cd /usr/local/mysql

scripts/mysql_install_db

6. 准备mysql启动脚本 exit

cd /usr/local/mysql

cp support-files/mysql.server /etc/rc.d/init.d/mysql chmod +x /etc/rc.d/init.d/mysql chkconfig --add mysql

chkconfig --level 345 mysql on

7. 清理old ibdata file su - mysqladmin

cd /usr/local/mysql/data rm -rf ib*

8. 编辑HOSTS vi /etc/hosts

9. Edit my.cnf for Master and Slave vi /etc/my.cnf 本次双主配置： 第一个主的my.cnf [client] port=3306

socket=/usr/local/mysql/var/mysql.sock

[mysqld] port = 3306

socket = /usr/local/mysql/var/mysql.sock

skip-external-locking

#决定MYISAM表索引处理的速度，尤其是索引读的速度

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

pid-file = /usr/local/mysql/data/mysql.pid

server-id = 1

log-bin = /usr/local/mysql/log/mysql-bin.log binlog_format=mixed binlog-ignore-db=mysql

auto-increment-increment = 2 auto-increment-offset = 1 #启动mysql,不启动复制 skip-slave-start

#最大连接数

max_connections=1000 #不区分大小写

lower_case_table_names=1

#报错日志位置

log_error=/usr/local/mysql/log/mysql-error.log

#独立表空间的数据存放形式，数据和索引分开 innodb_file_per_table=1

第二个主的my.cnf [client] port=3306

socket=/usr/local/mysql/var/mysql.sock

[mysqld] port = 3306

socket = /usr/local/mysql/var/mysql.sock

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

pid-file = /usr/local/mysql/data/mysql.pid

server-id = 2

log-bin = /usr/local/mysql/log/mysql-bin.log binlog_format=mixed binlog-ignore-db=mysql

auto-increment-increment = 2 auto-increment-offset = 2 #启动mysql,不启动复制 skip-slave-start

#最大连接数

max_connections=1000

#不区分大小写

lower_case_table_names=1

#报错日志位置

log_error=/usr/local/mysql/log/mysql-error.log

#独立表空间的数据存放形式，数据和索引分开 innodb_file_per_table=1

10. 移走原来默认的my.cnf su - mysqladmin cd /usr/local/mysql mv my.cnf my.cnf.old

11. 配置环境及启动mysql

[mysqladmin@mysql-01 mysql]$ cd /home/mysqladmin [mysqladmin@mysql-01 ~]$ vi .bash_profile

# .bash_profile

# Get the aliases and functions if [ -f ~/.bashrc ]; then . ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin:/usr/local/mysql/bin

export PATH

. ./.bash_profile

/etc/rc.d/init.d/mysql start

12. 重建系统表 mysql> use mysql use mysql;

drop table if exists innodb_index_stats; drop table if exists innodb_table_stats; drop table if exists slave_master_info; drop table if exists slave_relay_log_info; drop table if exists slave_worker_info;

mysql> show tables;

cd /usr/local/mysql/data/mysql ls *.ibd

innodb_index_stats.ibd innodb_table_stats.ibd slave_relay_log_info.ibd slave_worker_info.ibd rm *.ibd

mysql> use mysql

mysql> source /usr/local/mysql/share/mysql_system_tables.sql mysql> show tables;

13. 重新启动mysql Service mysql restart

14. 建立同步用户和授权

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY '*****'; flush privileges;

15. 配置mysql随系统启动

vi /etc/rc.local ### add mysql start

su - appuser -c \"/etc/rc.d/init.d/mysql start\"

slave_master_info.ibd

####su - mysqladmin -c \"/etc/rc.d/init.d/mysql start --federated\"

16.配置双主复制

查看第1台服务器的master信息：show master status \\G; 得到master_log_file和master_log_pos

在第2台服务器上面按下面配置： stop slave;

change master to

master_host='10.100.8.121',master_user='repl',master_password='repl1',master_log_file='mysql-bin.000017',master_log_pos=120;

上面的host填第1台的ip或者主机名，master_log_file和master_log_pos用上面第1台服务器的 Start slave

Show slave status \\G

根据命令状态查看slave是否有问题，2个线程状态证明正常，否则查看错误原因。 Slave_IO_Running: Yes Slave_SQL_Running: Yes

再反过来配置一次，这样双主复制配置成功。

Keepalived配置

1.安装keepalived依赖包

yum install -y openssl-devel

yum -y install libnl libnl-devel yum install -y libnfnetlink-devel

2.解压安装包及安装keepalived

tar –zxvf keepalived-1.2.20.tar.gz 进入解压后目录

./configure --prefix=/usr/local/keepalived make && make install

3.配置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/ mkdir /etc/keepalived

cp /usr/local/keepalived/etc/keepalived/keepalived.conf /etc/keepalived/ cp /usr/local/keepalived/sbin/keepalived /usr/sbin/ echo \"/etc/init.d/keepalived start\" >> /etc/rc.local cd /etc/keepalived/

第1个服务器上面编辑keepalived.conf如下： ! Configuration File for keepalived global_defs {

notification_email {

lsenlin@tianan-insurance.com }

notification_email_from ops@wangshibo.cn smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MASTER-HA }

vrrp_script chk_mysql_port {

script \"/app/keepalive_check/chk_mysql.sh\" interval 2

weight -5 fall 2 rise 1 }

vrrp_instance VI_1 { state BACKUP

interface eth23

mcast_src_ip 10.100.8.122 virtual_router_id 51 priority 99 advert_int 1 authentication { auth_type PASS auth_pass 1111 }

virtual_ipaddress { 10.100.8.124 }

track_script { chk_mysql_port } }

第2个服务器上面编辑keepalived.conf如下： ! Configuration File for keepalived

global_defs {

notification_email {

lsenlin@tianan-insurance.com }

notification_email_from ops@wangshibo.cn smtp_server 127.0.0.1 smtp_connect_timeout 30 router_id MASTER-HA }

vrrp_script chk_mysql_port { #检测mysql服务是否在运行。有很多方式，比如进程，用脚本检测等等

script \"/app/keepalive_check/chk_mysql.sh\" #这里通过脚本监测 interval 2 #脚本执行间隔，每2s检测一次

weight -5 #脚本结果导致的优先级变更，检测失败（脚本返回非0）则优先级 -5

fall 2 #检测连续2次失败才算确定是真失败。会用weight减少优先级（1-255之间）

rise 1 #检测1次成功就算成功。但不修改优先级 }

vrrp_instance VI_1 { state MASTER

interface eth23 #指定虚拟ip的网卡接口 mcast_src_ip 10.100.8.121

virtual_router_id 51 #路由器标识，MASTER和BACKUP必须是一致的 priority 101 #定义优先级，数字越大，优先级越高，在同一个vrrp_instance下，MASTER的优先级必须大于BACKUP的优先级。这样MASTER故障恢复后，就可以将VIP资源再次抢回来

advert_int 1 authentication { auth_type PASS auth_pass 1111 }

virtual_ipaddress { 10.100.8.124 }

track_script { chk_mysql_port } }

编辑/app/keepalive_check/chk_mysql.sh #!/bin/bash

counter=$(netstat -na|grep \"LISTEN\"|grep \"3306\"|wc -l) if [ \"${counter}\" -eq 0 ]; then /etc/init.d/keepalived stop Fi

赋予执行权限：

chmod 755 chk_mysql.sh

4：启动keepalived /etc/init.d/keepalived start