MySQLMM+KeepAlived高可用架构部署测试

1、MySQL MM+KeepAlived高可用架构部署测试马飞2017-2-23一、环境准备1.1 操作系统信息安装三台 CentOS6.7 64 位操作系统。采用最小安装模式。sed -i s/=enforcing/=disabled/g /etc/selinux/configservice itpables stopchkconfig iptables off1.2 配置 NTP同步时间yum -y install ntpvi /etc/ntp.conf增加 3 个时间服务器西南地区网络中心西北地区网络中心东北地区网络中心启动 ntp 服务systemctl start ntpdsystem

2、ctl enable ntpd查看状态ntpq -p1.3 配置角色信息主机名IP 地址描述Server idDb100Master11Db201Master22Db302Slave131.4 服务角色及描述IP 地址角色描述00ReadWriter应用程序连接该IP 对主库进写请求01ReadWriter应用程序连接该IP 对主库进写请求02Read Only应用程序连接到该IP 处理读请求40ReadWriter应用程序连接到该虚 IP

3、处理读写请求 （不直联100，101），通过连接140 实现高可用。1.5 配置 /etc/hosts在其中添加所有的主机信息：rootdb1 mysql# more /etc/hostslocalhost localhost.localdomain localhost4 localhost4.localdomain4:1localhost localhost.localdomain localhost6 localhost6.localdomain6b3二、安装MySQL2.1 卸载 rpm 包roothadoop61 # rpm -qa | grep -i mysql2.2 收集 mysq

4、l 对应的文件夹roothadoop61 # find / -name mysql/var/lib/mysql/var/lib/mysql/mysql2.3 删除 mysql 对应的文件夹rm -rf /var/lib/mysql2.4 下载 mysql 安装程序2.5 解压 mysql 安装程序-C /usr/localcd /usr/local2.6 添加用户和组groupadd mysqluseradd -r -g mysql mysql2.7 安装数据库进入安装mysql 软件目录：cd /usr/local/mysql修改当前目录拥有者为mysql 用户：chown -R mysql

5、:mysql ./安装数据库：mkdir -p /data/mysql/datachown -R mysql.mysql /data/mysql#若是最小安装系统，还需要安装以下包：yum -y install perl perl-develyum -y install libaio./scripts/mysql_install_db-basedir=/usr/local/mysql5.6-datadir=/data/mysql/data-user=mysql修改配置文件：cp /usr/local/mysql5.6/f/etc/f# vi /etc/fmysqldbasedir=/usr/l

6、ocal/mysqldatadir= /data/mysql/dataport=3306sql_mode=NO_ENGINE_SUBSTITUTIOcharacter-set-server=utf8mysqldefault-character-set=utf8clientdefault-character-set=utf8启动脚本放到开机初始化目录cp support- /etc/init.d/mysql启动 mysql 服务：service mysql start查看 mysql 进程：ps -ef|grep mysql三、修改MySQL配置db1,db2,db3 三台主机上分别建立如下目录

7、mkdir /var/log/mysql & chown -R mysql:mysql /var/log/mysql/3.1 db1 配置mysqldbasedir=/usr/local/mysqldatadir=/data/mysql/dataport=3306sql_mode=NO_ENGINE_SUBSTITUTIOcharacter-set-server=utf8server_id = 1log_bin = /var/log/mysql/mysql-bin.logrelay_log = /var/log/mysql/mysql-relay-binrelay_log_index = /v

8、ar/log/mysql/mysql-relay-bin.indexexpire_logs_days = 10max_binlog_size = 1024Mlog_slave_updates = 1auto-increment-increment = 2auto-increment-offset =1binlog_format = MIXEDreplicate-ignore-db = mysqlbinlog-ignore-db= mysqlmysqldefault-character-set=utf8clientdefault-character-set=utf83.2 db2 配置mysql

9、dbasedir=/usr/local/mysqldatadir=/data/mysql/dataport=3306sql_mode=NO_ENGINE_SUBSTITUTIOcharacter-set-server=utf8server_id = 2log_bin = /var/log/mysql/mysql-bin.logrelay_log = /var/log/mysql/mysql-relay-binrelay_log_index = /var/log/mysql/mysql-relay-bin.indexexpire_logs_days = 10max_binlog_size = 1

10、024Mlog_slave_updates = 1auto-increment-increment = 2auto-increment-offset = 2binlog_format = MIXEDreplicate-ignore-db = mysqlbinlog-ignore-db= mysqlmysqldefault-character-set=utf8clientdefault-character-set=utf83.3 db3 配置mysqldbasedir=/usr/local/mysqldatadir=/data/mysql/dataport=3306sql_mode=NO_ENG

11、INE_SUBSTITUTIOcharacter-set-server=utf8server_id = 3log_bin = /var/log/mysql/mysql-bin.logrelay_log = /var/log/mysql/mysql-relay-binrelay_log_index = /var/log/mysql/mysql-relay-bin.indexexpire_logs_days = 10max_binlog_size = 1024Mlog_slave_updates = 1binlog_format = MIXEDreplicate-ignore-db = mysql

12、binlog-ignore-db= mysqlread-onlymysqldefault-character-set=utf8clientdefault-character-set=utf8四、 MySQL复制配置从 master-001 复制到 master-002 从 master-002 复制到 slave-0014.1 配置 db1 作为主复制到db21）配置 db1由于这里是配置db1 做为源数据，所以不需要设置change master 和启动 slave 进程#在 db1 上创建复制用户#从 db1 上导出数据mysqldump -master-data=2 -single-tr

13、ansaction -R -triggers -A all.sql#拷贝 all.sql 至 db2,db3scp all.sql rootdb2:/usr/local/mysql5.6scp all.sql rootdb3:/usr/local/mysql5.62）配置 db2#将 all.sql 导入 db2,db3 从库mysql -f -default-character-set=utf8 show slave statusG* 1. row *Slave_IO_State: Waiting for master to send eventMaster_User: replicatio

14、nMaster_Port: 3306Connect_Retry: 10Master_Log_File: mysql-master-002-bin.000001Read_Master_Log_Pos: 120Relay_Log_File: mysql-master-001-bin.000004Relay_Log_Pos: 294Relay_Master_Log_File: mysql-master-002-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_DB: mysqlR

15、eplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 120Relay_Log_Space: 472Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Master_SSL_C

16、ert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 2Master_UUID: 3d3b9f4f-f74f-11e5-9a30-005056b324c4Master_Info_File: /opt/mysql/data/SQL

17、_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:Auto_Posi

18、tion: 01 row in set (0.00 sec)注意 :Slave_IO_Running: YesSlave_SQL_Running: Yes4.2 配置 db1 作为主复制到db31）配置 db3指定change master 为 db1, 表示从 db1 复制到db3#ssh 到 db3 服务器mysql -u root -p#在 mysql 控制台录入#这个是在f 中没有使用GTID(MySQL5.6新特性 )下的命令：CHANGE MASTER TOMASTER_USER=repl,MASTER_PASSWORD=repl,MASTER_PORT=3306,MASTER_L

19、OG_FILE=mysql-bin.000007,MASTER_LOG_POS=120,MASTER_CONNECT_RETRY=10;# 这个是在f 中使用 GTID(MySQL5.6新特性 )下的命令CHANGE MASTER TOMASTER_USER=repl,MASTER_PASSWORD=repl,MASTER_PORT=3306,MASTER_AUTO_POSITION = 1;#重置 resetreset slave;#启动 slavestart slave;#查看 slave 状态show slave statusG#结果如下mysql show slave statusG4

20、.3 配置 db2 作为主复制到db11）配置 db1指定change master 为 db2,表示从 db2 复制到db1#ssh 到 master-001 服务器mysql -u root -p#在 mysql 控制台录入，这个是在f 中没有使用GTID(MySQL5.6新特性 )下的命令CHANGE MASTER TOMASTER_USER=repl,MASTER_PASSWORD=repl,MASTER_PORT=3306,MASTER_LOG_FILE=mysql-bin.000007,MASTER_LOG_POS=173312,MASTER_CONNECT_RETRY=10;#这

21、个是在f 中使用 GTID(MySQL5.6新特性 )下的命令CHANGE MASTER TOMASTER_USER=repl,MASTER_PASSWORD=repl,MASTER_PORT=3306,MASTER_AUTO_POSITION = 1;#重置 resetreset slave;#启动 slavestart slave;#查看 slave 状态show slave statusG#结果如下mysql show slave statusG* 1. row *Slave_IO_State: Waiting for master to send eventMaster_User: r

22、eplicationMaster_Port: 3306Connect_Retry: 10Master_Log_File: mysql-master-002-bin.000001Read_Master_Log_Pos: 120Relay_Log_File: mysql-master-001-bin.000004Relay_Log_Pos: 294Relay_Master_Log_File: mysql-master-002-bin.000001Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB:Replicate_Ignore_D

23、B: mysqlReplicate_Do_Table:Replicate_Ignore_Table:Replicate_Wild_Do_Table:Replicate_Wild_Ignore_Table:Last_Errno: 0Last_Error:Skip_Counter: 0Exec_Master_Log_Pos: 120Relay_Log_Space: 472Until_Condition: NoneUntil_Log_File:Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_SSL_CA_File:Master_SSL_CA_Path:Mas

24、ter_SSL_Cert:Master_SSL_Cipher:Master_SSL_Key:Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: NoLast_IO_Errno: 0Last_IO_Error:Last_SQL_Errno: 0Last_SQL_Error:Replicate_Ignore_Server_Ids:Master_Server_Id: 2Master_UUID: 3d3b9f4f-f74f-11e5-9a30-005056b324c4Master_Info_File: /opt/mysql/data/maste

25、SQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update itMaster_Retry_Count: 86400Master_Bind:Last_IO_Error_Timestamp:Last_SQL_Error_Timestamp:Master_SSL_Crl:Master_SSL_Crlpath:Retrieved_Gtid_Set:Executed_Gtid_Set:

26、Auto_Position: 01 row in set (0.00 sec)注意 :Slave_IO_Running: YesSlave_SQL_Running: Yes4.4 MM 环境测试在 db1 上，增删改表空间，表，字段，内容可以同步到db2,db3在 db2 上，增删改表空间，表，字段，内容可以同步到db1,db3五、 keepalived安装5.1、软件下载（1）软件下载（2）内核参数修改注意：修改修改操作系统内核参数“” #修改内核参数“”vi /etc/sysctl.conf#使配置生效sysctl -p功能：设置或重新设置联网功能，如IP 转发、 IP 碎片去除以及源路由

27、检查等。用户只需要编辑 /etc/sysctl.conf文件，即可手工或自动执行由 sysctl控制的功能。（3）系统环境操作系统角色IP 地址Keepalived 版本CentOS6.7主角色001.2.7CentOS6.7主角色011.2.7CentOS6.7备角色025.2、软件安装（1）解压软件（2）安装工具yum install -y gcc openssl-devel popt-devel若是本地挂载光盘镜像则使用如下方法：yum -disablerepo=* -enablerepo=c6-mediainstall

28、-y gcc openssl-devel popt-devel安装上述工具编译有以下警告信息：configure: WARNING: keepalived will be built without libnl support.Keepalived configuration-Keepalived version: 1.2.7Compiler: gccCompiler flags: -g -O2Extra Lib: -lpopt -lssl -lcryptoUse IPVS Framework: YesIPVS sync daemon support : YesIPVS use libnl:

29、NoUse VRRP Framework: YesUse VRRP VMACSNMP supportUse Debug flags: Yes: No: Nos解决办法：yum -y install libnl*Keepalived configuration-Keepalived version: 1.2.7Compiler: gccCompiler flags: -g -O2Extra Lib: -lpopt -lssl -lcrypto -lnlUse IPVS Framework: YesIPVS sync daemon support : YesIPVS use libnl: YesU

30、se VRRP Framework: YesUse VRRP VMAC: YesSNMP support: NoUse Debug flags: No#将包下载至本地并进行安装mkdir /root/libnl3yum install -downloadonly -downloaddir=/root/libnl3 libnl*cd /root/libnl3rpm -ivh *.rpm（3）编译安装软件./configuremake & make installconfigure.txt（4）软件配置cp /usr/local/etc/rc.d/init.d/keepalived /etc/in

31、it.d/ cp /usr/local/etc/sysconfig/keepalived /etc/sysconfig/ chmod +x /etc/init.d/keepalivedchkconfig -add keepalivedchkconfig keepalived onmkdir /etc/keepalivedln -s /usr/local/sbin/keepalived /usr/sbin/主节点配置文件 :/etc/keepalived/keepalived.confcheck_oracle_status.sh备节点配置文件：keepalived.confcheck_oracl

32、e_status.sh（5）启动服务rootdb9 keepalived# service keepalived start正在启动 keepalived：确定 rootdb9 keepalived# service keepalived statuskeepalived (pid30799)正在运行 .rootdb9 keepalived# ps -ef | grep keeproot307991 0 13:25 ?00:00:00 keepalived -Droot30800 30799013:25 ?00:00:00 keepalived -Droot30802 30799013:25

33、?00:00:00 keepalived -Droot30879 20806013:25 pts/0 00:00:00 grep keep（6）配置日志文件#修改 keepalived配置文件/etc/sysconfig/keepalived KEEPALIVED_OPTIONS=-D -d -S 0#修改系统日志配置文件vi /etc/rsyslog.conflocal0.*/var/log/keepalived.log#重启系统日志服务/etc/init.d/rsyslog restart#查询 keepalived日志more /var/log/keepalived.log5.3、安装问

34、题1）启动 keepalived 服务keepalived已死，但是 subsys被锁停止 keepalived：失败 问题原因：在启动前没有置/etc/keeplivied/keeplivied.conf文件。（2）删除虚拟网卡地址3）删除 keepalivedfind / -name keepalived（4） 脚本不能启动原因主从节点，以下脚本无执行权限chmod u+x /usr/local/script/check_oracle_status.sh5.4、软件测试（1）主节点启动服务service keepalived start查看 VIP 情况查看服务运行情况：service k

35、eepalived status查看主节点日志情况more /var/log/keepalived.log（2）从节点启动服务service keepalived start查看 VIP 情况：查看服务运行情况：service keepalived status查看主节点日志情况more /var/log/keepalived.log六、 keepalived配置1） db01 上 keepalived.conf 文件内容rootdb1 mysql# more /etc/keepalived/keepalived.conf#! Configurationkeepalivedglobal_def

36、s router_id db1#修改为自己的主机名vrrp_instance VI_1 state BACKUP#都修改成BACKUPinterface eth0virtual_router_id 60 priority 100#默认 51 主从都修改为60#优先级（ 1-254 之间），另一台改为90，备用节点必须比主节点优先级低。advert_int 1nopreemptauthentication #不抢占资源，意思就是它活了之后也不会再把主抢回来#设置验证信息，两个节点必须一致auth_type PASSauth_pass 1111virtual_ipaddress delay_loo

37、p 6lb_algo wrrlb_kind DR#LVS算法#LVS模式persistence_timeout 50 #会话保持时间protocol TCPweight 1notify_down /usr/local/script/mysql.sh #检测到服务down 后执行的脚本TCP_CHECK connect_timeout 10nb_get_retry3#连接超时时间#重连次数connect_port 3306#健康检查端口2） db02 上 keepalived.conf 文件内容rootdb2 mysql# more /etc/keepalived/keepalived.conf

38、#! Configurationkeepalivedglobal_defs router_id db2#修改为自己的主机名vrrp_instance VI_1 state BACKUP#都修改成BACKUPinterface eth0virtual_router_id 60 priority 80#默认 51 主从都修改为60#在 mysql-ha1 上 LVS上修改成100advert_int 1authentication auth_type PASSauth_pass 1111virtual_ipaddress delay_loop 6lb_algo wrrlb_kind DRpersistence_timeout 50protocol TCPweight