湖北网站建设,北京 公司网站 备案中 开通访问,视频门户网站建设方案,网络营销的特点举例Mysql双主双活keepalived实现高可用文件夹1、前言... 42、方案... 42.1、环境及软件... 42.2、IP规划... 42.3、架构图... 43、安装设置MYSQL半同步... 54、Keepalived实现MYSQL的高可用... 111、前言近期研究了下高可用的东西#xff0c;这里总结一下mysql主主双活的架构方案…Mysql双主双活keepalived实现高可用文件夹1、前言... 42、方案... 42.1、环境及软件... 42.2、IP规划... 42.3、架构图... 43、安装设置MYSQL半同步... 54、Keepalived实现MYSQL的高可用... 111、前言近期研究了下高可用的东西这里总结一下mysql主主双活的架构方案总体上提高服务的高可用性。出现故障也不须要手动切换。提高总体的维护效率。确定改造的话仅仅须要让他们的程序中使用vip地址就能够实现起来比較easy。2、方案本案例先使用两台linux做双机MASTER-MASTER高可用(兴许能够考虑增加仅仅读SLAVER,用于提高查询性能)採用MYSQL5.6.x的半同步实现数据复制和同步。使用keepalived来监控MYSQL和提供VIP及浮动。不论什么一台主机宕机都不会影响对外提供服务(vip能够浮动)保持服务的高可用。注 keepalived之间使用vrrp组播方式通信使用的IP地址是192.168.150.2012.1、环境及软件操作系统RedHat 5.5Keepalivedkeepalived-1.2.12.tar.gzMYSQLMySQL-server- mysql-5.0.77-4.el5_4.2.x86_64.rpm MySQL-client- mysql-5.0.77-4.el5_4.2.x86_64.rpm2.2、IP规划名称IP备注VIP192.168.150.201由KEEPALIVED产生的虚拟IP也是前端client使用的IPMYSQL_DB_MASTER1192.168.150.145数据库1serverMYSQL_DB_MASTER2192.168.150.146数据库2server2.3、架构图3、安装设置MYSQL半同步3.1、安装MYSQL这里选择的是使用社区版的MYSQL请在www.mysql.com上下载相应操作系统的安装文件这里依据官方的建议使用RPM包方式安装。也能够进行yum安装。安装假设是默认Redhat5.5安装后# 使用yum 安装 yum install mysqlyum install mysql-server……默认安装是在/usr以下。所以my.cnf是在/usr/my.cnf这里为了习惯使用方法复制到/etc/文件夹下# mv /usr/my.cnf /etc/标准方式改动password# mysqladmin -u root -p oldPassword password# New password:# Confirm new password:或# mysqladmin -u root -p password newPasswordSQL直接更新方式改动password初始化rootpassword常见问题及处理error: Access denied for user rootlocalhost (usingpassword: NO)# /etc/init.d/mysql stop# mysqld_safe --usermysql --skip-grant-tables--skip-networking # mysql -u root mysqlmysql UPDATE user SETPasswordPASSWORD(newpassword) where USERroot;mysql FLUSH PRIVILEGES;mysql quit# /etc/init.d/mysql restart# mysql -uroot -pEnter password: mysql设置网络訪问权限MYSQL的用户訪问策略是须要指定用户从某IP能够訪问那些权限。详细使用方法请參考grant命令。这里为了简单起见直接设置root用户在整个局域内(192.168.150.*)都能够訪问数据库。# mysql -uroot -p******mysql GRANT ALL PRIVILEGES ON *.* TO root192.168.150.% IDENTIFIEDBY password WITH GRANT OPTION;mysqlFLUSH PRIVILEGES;这样设置后就能够通过内网的其它机器訪问数据库了。改动数据文件路径mysql安装后。默认的数据存储路径在/var/lib/mysql下。这里改动为/data/mysql# service mysql stop# mkdir /data/mysql# cp -r /var/lib/mysql/* /data/mysql# chown -R mysql:mysql /data/mysqlvi /etc/my.cnf改动里面的參数[mysqld]datadir /data/mysqlsocket /data/mysql/mysql.sock# 顺便配置下MYSQL使用mysql用户启动usermysql保存退出为/data/mysql/mysql.sock建立指向/var/lib/mysql/mysql.sock的软连接# ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock启动MYSQL并測试# service mysql start# mysql -uroot -p******mysql最可能出现的问题(假设你是Redhat6.x或CentOS6.x)selinux 照成服务无法正常启动报错Starting MySQL. ERROR! The server quit without updatingPID file (/data/mysql/xxxxx.pid).验证问题方法# setenforce 0# service mysql startStarting MySQL. SUCCESS!假设能正常启动解决这个问题。就确定是selinux造成的问题了。解决的方法直接禁用vim /etc/selinux/config改动SELINUXdisabled保存后重新启动操作系统生效如 果出现故障一般解决这个问题的方式是查看MYSQL的日志文件一般提示会比較清楚或者依据错误信息区GOOGLE都能够找到答案。日志文件:${datadir}/${hostname}.err, 本例中改动了数据文件夹。所以日志文件是/data/mysql/acooly1.errOK,本节安装MYSQL完毕然后另外一台server的MYSQL安装全然同样(半同步的设置有小不同那以下会说明)。请參照以上说明安装另外一台MYSQL服务(MYSQL_DB_MASTER2)。3.2、设置MYSQL双向半同步本方案中为了实现故障转移功能。在利用半同步复制能力的同一时候对总体方案进行了调整例如以下1. MYSQL_DB_MASTER1 和MYSQL_DB_MASTER2互为主备。即MYSQL_DB_MASTER1时MYSQL_DB_MASTER2的 主。MYSQL_DB_MASTER2是MYSQL_DB_MASTER1的主通过半同步实现双向的同步复制(注意这里不会出现冲突由于备机仅仅会同步不是本机server_id的bin-log日志。通过两台机器的server_id隔离须要同步的bin-log)2. 通过Keepalived实现MYSQLserver的监控和VIP的浮动保证同一时间仅仅有一台MYSQLserver可用。3. 该方案中能够兼容后期的扩容增加多台SALVE与两台MASTER异步同步实现数据的仅仅读查询实现读写分离提高总体性能。配置/etc/my.cnf分别改动/etc/my.cnf配置打开bin-log功能和设置server_idvi /etc/my.cnf/etc/my.conf代码[mysqld]# MYSQL_DB_MASTER1设置为1。MYSQL_DB_MASTER2设置为2server_id1log_binmysql-bin安 装官方文档说明主(MYSQL_DB_MASTER1)须要设置rpl_semi_sync_master_enabled1和rpl_semi_sync_master_timeout1000。备(MYSQL_DB_MASTER2)须要设置 rpl_semi_sync_slave_enabled1这里由于是双向同步。我都没有设置经測试没有设置也没有关系(临时没有深究兴许在研究下)。配置MYSQL_DB_MASTER1与MYSQL_DB_MASTER2的主备同步在MYSQL_DB_MASTER1上操作配置MYSQL_DB_MASTER1为MYSQL_DB_MASTER2的主。Mysql_db_master1代码# mysql-uroot -pEnterpassword:......-- 建立复制用户replication同意内网内其它机器(訪问本机全部数据库)mysql grant replication slave on *.* torepdb01192.168.150.% identified by 123456;QueryOK, 0 rows affected (0.00 sec)-- 查看master的bin-log状态用户设置SLAVE同步的起点mysqlshow master status;-------------------------------------------------------------------------------| File | Position | Binlog_Do_DB |Binlog_Ignore_DB | Executed_Gtid_Set |-------------------------------------------------------------------------------| mysql-bin.000013 | 120 | | | |-------------------------------------------------------------------------------1 row in set (0.02 sec) 这里注意记录下当前bin-log文件名mysql-bin.000013和位置120。备机设置的时候须要用到。在MYSQL_DB_MASTER2上操作配置Mysql_db_master2代码--设置SLAVE的MASTER和開始同步的文件位置mysqlchange master tomaster_host192.168.150.145,master_userrepdb01,master_password123456,master_log_filemysql-bin.000003,master_log_pos120;--启动同步mysqlstart slave;mysqlshow slave status\G;***************************1. row ***************************Slave_IO_State: Waiting formaster to send eventMaster_Host:192.168.150.145Master_User: repdb01Master_Port: 3306Connect_Retry: 10Master_Log_File:mysql-bin.000013Read_Master_Log_Pos: 120Relay_Log_File: acooly2-relay-bin.000020Relay_Log_Pos: 283Relay_Master_Log_File:mysql-bin.000013Slave_IO_Running: YesSlave_SQL_Running: Yes……1 row in set (0.02sec)Slave_IO_Running: Yes和Slave_SQL_Running:Yes表示SLAVE已经正常启动并监听MASTER的数据发送事件。处理同步。配置MYSQL_DB_MASTER2与MYSQL_DB_MASTER1的主备同步全部配置操作与上一节仅仅是把主备关系交换。OK假设全部的配置成功启动MASTER1和MASTER2两个数据库分别从两端创建数据库。表和更新表数据另外一边都会实时同步。也能够在两台机器中分别配置/etc/my.cnf210.146[rootrac2 keepalive]# cat /etc/my.cnf[mysqld]datadir/var/lib/mysqlsocket/var/lib/mysql/mysql.sockusermysql# Default to using old password format for compatibilitywith mysql 3.x# clients (those using the mysqlclient10 compatibilitypackage).old_passwords1# Disabling symbolic-links is recommended to preventassorted security risks;# to do so, uncomment this line:# symbolic-links0old_passwords1lower_case_table_names1default-character-setutf8default-storage-engineinnodbmax_connect_errors 100000#innodb_buffer_pool_size 8Gmax_connections 500default-character-setutf8server-id2#log-binmysqlbininnodb_flush_log_at_trx_commit1sync_binlog1init_connectSET NAMES utf8log-binmysqlbinmaster-host192.168.150.145master-userrepdb01master-pass123456master-connect-retry60replicate-do-dbdb01replicate-do-dbspringmaster-port3306slave-net-timeout60[mysqld_safe]log-error/var/log/mysqld.logpid-file/var/run/mysqld/mysqld.pid[client]default-character-setutf8210.145 上面的配置[rootrac1 ~]# cat /etc/my.cnf[mysqld]datadir/var/lib/mysqlsocket/var/lib/mysql/mysql.sockusermysql# Default to using old password format for compatibilitywith mysql 3.x# clients (those using the mysqlclient10 compatibilitypackage).old_passwords1# Disabling symbolic-links is recommended to preventassorted security risks;# to do so, uncomment this line:# symbolic-links0old_passwords1lower_case_table_names1default-character-setutf8default-storage-engineinnodbmax_connect_errors 100000#innodb_buffer_pool_size 8Gmax_connections 500default-character-setutf8#binlog_formatmixedserver-id1master-host192.168.150.146master-userrepdb01master-pass123456master-connect-retry60replicate-do-dbdb01replicate-do-dbspringinit_connectSET NAMES utf8log-binmysqlbin[mysqld_safe]log-error/var/log/mysqld.logpid-file/var/run/mysqld/mysqld.pid[client]default-character-setutf8两个配置完毕后重新启动数据库配置完毕。4、Keepalived实现MYSQL的高可用前面已经完毕MYSQL的双向实时同步的配置。本节使用Keepalived实现MYSQL双机的高可用故障转移。通过VIP实现同一时候仅仅有一台MYSQL提供服务。4.1、配置KEEPALIVED两台MYSQLserver上的配置基本同样(除了keepalived.conf的个别配置外)详细操作配置例如以下设置keepalived的主配置文件/etc/keepalived/keepalived.conf实现本机的MYSQL的监控和VIP浮动。安装keepalivedtar –zxvf keepalived-1.1.20.tar.gzcd keepalived-1.1.20./configure --prefix/usr/local/keepalived/makemake installln -s /usr/local/keepalived/etc/keepalived /etc/ln -s /usr/local/keepalived/etc/rc.d/init.d/keepalived/etc/rc.d/init.d/ln -s /usr/local/keepalived/etc/sysconfig/keepalived/etc/sysconfig/ln -s /usr/local/keepalived/bin/genhash /bin/ln -s /usr/local/keepalived/sbin/keepalived /sbin/configure时注意Use IPVS Framework、IPVS sync daemon support 、Use VRRP Framework要返回yes。否则无法关联ipvs功能master1 上的配置145vi /etc/keepalived/keepalived.conf! Configuration File for keepalivedglobal_defs {router_id Mysql-HA}# 定义MYSQL监控的脚本。每2秒监控一次详细脚本请见以下vrrp_scriptcheck_mysql {script/root/keepalive/keepalived_check_mysql.shinterval 2}vrrp_sync_groupVG1 {group {KEEPALIVED_MYSQL}}vrrp_instanceKEEPALIVED_MYSQL {# 注意两台MYSQLserver都设置为BACKUPstateBACKUPinterface eth0virtual_router_id 20# # MASTER1设置为100则MASTER2设置为稍低如90priority 100advert_int 1# 仅仅有MASTER1设置不抢夺控制权nopreemptauthentication {auth_type PASSauth_pass abcd1234}track_script {check_mysql}virtual_ipaddress {192.168.150.201}}146上面的配置! Configuration File for keepalivedglobal_defs {router_id Mysql-HA}#定义MYSQL监控的脚本每2秒监控一次详细脚本请见以下vrrp_scriptcheck_mysql {script/root/keepalive/keepalived_check_mysql.shinterval 2}vrrp_sync_groupVG1 {group {KEEPALIVED_MYSQL}}vrrp_instanceKEEPALIVED_MYSQL {#注意两台MYSQLserver都设置为BACKUPstateBACKUPinterface eth0virtual_router_id 20#MASTER1设置为100 MASTER2设置为90priority 90advert_int 1authentication {auth_type PASSauth_pass abcd1234}track_script {check_mysql}virtual_ipaddress {192.168.150.201}}检查本机MYSQL服务是否正常假设MYSQL服务不可用则停止KEEPALIVED服务由备机的KEEPALIVED接管并提供VIP(指向备机)/root/keepalive/keepalived_check_mysql.sh代码[rootrac2 keepalive]# cat /root/keepalive/keepalived_check_mysql.sh#!/bin/bashMYSQL/usr/bin/mysqlMYSQL_HOSTlocalhost#MYSQL_USERroot#MYSQL_PASSWORDrootLOG_FILE/root/keepalive/check_mysql.logCHECK_TIME3#mysqlis working MYSQL_OK is 1 , mysql down MYSQL_OK is 0MYSQL_OK1functioncheck_mysql_helth (){$MYSQL -h $MYSQL_HOST -e showstatus; /dev/null 21if [ $? 0 ] ;thenMYSQL_OK1elseMYSQL_OK0fireturn $MYSQL_OK}while [$CHECK_TIME -ne 0 ]dolet CHECK_TIME - 1check_mysql_helthif [ $MYSQL_OK 1 ] ; thenCHECK_TIME0echo date --datetoday %Y-%m-%d%H:%M:%S - [INFO] - mysqlavailable: success[$MYSQL_OK] $LOG_FILEexit 0fiif [ $MYSQL_OK -eq 0 ] [$CHECK_TIME -eq 0 ]then/etc/init.d/keepalived stopecho date --datetoday %Y-%m-%d%H:%M:%S - [INFO] - mysqlinvaild. keepalived stop. $LOG_FILEexit 1fisleep 1done4.2、验证故障转移配置完毕后。分别启动两台server的mysql和keepalived服务分别启动MYSQL_DB_MASTER1和MYSQL_DB_MASTER2详细命令例如以下# service mysql startStarting MySQL.......... SUCCESS!# service keepalived startStarting keepalived: [ OK ]在MYSQL_DB_MASTER1查看VIPIp a[rootrac1 keepalive]# ip a1: lo: mtu 16436 qdiscnoqueuelink/loopback00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8 scope host loinet6 ::1/128scope hostvalid_lftforever preferred_lft forever2: eth0: mtu 1500qdisc pfifo_fast qlen 1000link/etherbe:0c:3f:74:0f:b8 brd ff:ff:ff:ff:ff:ffinet192.168.150.145/24 brd 192.168.150.255 scope global eth0inet 192.168.150.201/32 scope global eth0inet192.168.150.245/24 brd 192.168.150.255 scope global secondary eth0:2inet6fe80::bc0c:3fff:fe74:fb8/64 scope linkvalid_lftforever preferred_lft forever3: eth1: mtu 1500qdisc pfifo_fast qlen 1000link/etherca:d9:c2:ab:89:c8 brd ff:ff:ff:ff:ff:ffinet10.10.10.145/24 brd 10.10.10.255 scope global eth1inet6fe80::c8d9:c2ff:feab:89c8/64 scope linkvalid_lftforever preferred_lft forever4: sit0: mtu 1480 qdisc nooplink/sit 0.0.0.0brd 0.0.0.0210.201 在145 上面从另外一台机器用vip地址登陆mysql -h 192.168.150.201 -utest -p查看时连接那一台机器mysql show variables like %host%- ;----------------------| Variable_name | Value |----------------------| hostname | rac1 |----------------------1 row in set (0.00 sec)然后停止rac1上的mysql依旧查看连接的状态mysql show variables like %host%;ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 4274Current database: *** NONE ***----------------------| Variable_name | Value |----------------------| hostname | rac2 |----------------------1 row in set (3.09 sec)mysqlmysql show variables like %host%;----------------------| Variable_name | Value |----------------------| hostname | rac2 |----------------------1 row in set (0.00 sec)发现已经转移到mysql_db_master2上面[rootrac1 keepalive]# service keepalived statuskeepalived is stopped查看keepalive的状态以及mysql的检查日志查看日志 [rootrac1 keepalive]# catcheck_mysql.log2014-11-05 18:29:37 - [INFO] - mysql available:success[1]2014-11-05 18:29:42 - [INFO] - mysql invaild.keepalived stop.2014-11-05 18:29:43 - [INFO] - mysql invaild.keepalived stop.发现服务已经停止[rootrac2 keepalive]# ip a 在主机2上查看ip情况1: lo: mtu 16436 qdiscnoqueuelink/loopback00:00:00:00:00:00 brd 00:00:00:00:00:00inet127.0.0.1/8 scope host loinet6 ::1/128scope hostvalid_lftforever preferred_lft forever2: eth0: mtu 1500qdisc pfifo_fast qlen 1000link/ether42:1c:5e:e1:1c:3e brd ff:ff:ff:ff:ff:ffinet192.168.150.146/24 brd 192.168.150.255 scope global eth0inet 192.168.150.201/32 scope global eth0能够看到VIP(210.201)已经切换到MYSQL_DB_MASTER2从新启动MYSQL_DB_MASTER1的MYSQL服务和KEEPALIVED服务。可是这个时候是不会从新浮动到MASTER1的。由于我们配置 KEEPALIVED的时候都是配置的BACKUP。并且MASTER1配置了不抢占。这与我们设计是相符的。(仅仅有等MASTER2出现问题的情况才从新有MASTER1接管)这样的方式能够避免一台机器出现故障手动切换才干使服务恢复。提高系统的高可用性。兴许多台双活实验进行中敬请期待。
