企业私有云之mariadb集群高可用
上篇文章介绍了openstack组件rabbitmq高可用，现在介绍其另一个重要组件mysql高可用。 我是本次选择mysql的版本是mariadb，集群方法是galera cluster多主集群。 其实也有很多其他方案，如pxc、mha等等，选择galera的原因是安装方便，使用与维护也方便，多主模式任何一个节点挂了都可以在另外节点查看数据，同时openstack各组件也支持配置集群方式的配置。 简介 MariaDBGaleraCluster是一套在mysqlinnodb存储引擎上面实现multi-master及数据实时同步的系统架构，业务层面无需做读写分离工作，数据库读写压力都能按照既定的规则分发到各个节点上去。在数据方面完全兼容MariaDB和MySQL。 特性 (1).同步复制Synchronousreplication (2).Active-activemulti-master拓扑逻辑 (3).可对集群中任一节点进行数据读写 (4).自动成员控制，故障节点自动从集群中移除 (5).自动节点加入 (6).真正并行的复制，基于行级 (7).直接客户端连接，原生的MySQL接口 (8).每个节点都包含完整的数据副本 (9).多台数据库中数据同步由wsrep接口实现 不过局限性也有很多，比如复制仅支持innode，节点必须是3个等等，具体看官网介绍。 下面是安装 环境 系统centos 7.1 /etc/hosts是 node1192.168.1.18 node2192.168.1.20 node3192.168.1.19 一、安装基础库（所有节点运行） yum-yinstallmakecmakebcgccgcc-c++autoconflibjpeglibjpeg-devellibpnglibpng-develfreetypefreetype-devellibxml2libxml2-develzlibzlib-develglibcglibc-develglib2glib2-develbzip2bzip2-develncursesncurses-develcurlcurl-devele2fsprogse2fsprogs-develkrb5-devellibidnlibidn-developensslopenssl-develnss_ldapopenldapopenldap-developenldap-clientsopenldap-serverslibxslt-devellibevent-develntplibtool-ltdlbisonlibtoolvim-enhancedtarwgetreadline-devellibyaml-develpatchtelnetlrzszsysstatscreenpartedrsynclibselinux-pythondmidecodentpdatesaropenssh-clientsman 二、配置mariadb源（所有节点运行） cat>/etc/yum.repos.d/mariadb.repo<<EOF #MariaDB10.1CentOSrepositorylist-created2016-05-2407:59UTC #http://mariadb.org/mariadb/repositories/ [mariadb] name=MariaDB baseurl=http://yum.mariadb.org/10.1/centos7-amd64 gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB gpgcheck=1 EOF 三、安装（所有节点运行） yuminstallMariaDB-serverMariaDB-client-y 四、配置 1、在node1里运行 主要是修改bind-address、wsrep_cluster_name、wsrep_node_address、wsrep_node_name [root@mysql-cluster-1my.cnf.d]#cat/etc/my.cnf.d/server.cnf|grep-v'^#'|sed'/^$/d' [server] [mysqld] collation-server=utf8_general_ci init-connect='SETNAMESutf8' character-set-server=utf8 skip-name-resolve skip-host-cache open_files_limit=65535 max_connections=5000 bind-address=192.168.1.18 binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 innodb_file_per_table character-set-server=utf8 [galera] wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_provider_options="pc.recovery=TRUE;gcache.size=300M" wsrep_cluster_address='gcomm://' wsrep_cluster_name='ck-galera' wsrep_node_address='192.168.1.18' wsrep_node_name='m-1' wsrep_sst_method=rsync [embedded] [mariadb] [mariadb-10.1] 启动 systemctlenablemariadb systemctlstartmariadb 初始化 [root@mysql-cluster-1log]#mysql_secure_installation NOTE:RUNNINGALLPARTSOFTHISSCRIPTISRECOMMENDEDFORALLMariaDB SERVERSINPRODUCTIONUSE!PLEASEREADEACHSTEPCAREFULLY! InordertologintoMariaDBtosecureit,we'llneedthecurrent passwordfortherootuser.Ifyou'vejustinstalledMariaDB,and youhaven'tsettherootpasswordyet,thepasswordwillbeblank, soyoushouldjustpressenterhere. Entercurrentpasswordforroot(enterfornone): OK,successfullyusedpassword,movingon... SettingtherootpasswordensuresthatnobodycanlogintotheMariaDB rootuserwithouttheproperauthorisation. Setrootpassword?[Y/n]y Newpassword: Re-enternewpassword: Passwordupdatedsuccessfully! Reloadingprivilegetables.. ...Success! Bydefault,aMariaDBinstallationhasananonymoususer,allowinganyone tologintoMariaDBwithouthavingtohaveauseraccountcreatedfor them.Thisisintendedonlyfortesting,andtomaketheinstallation goabitsmoother.Youshouldremovethembeforemovingintoa productionenvironment. Removeanonymoususers?[Y/n]y ...Success! Normally,rootshouldonlybeallowedtoconnectfrom'localhost'.This ensuresthatsomeonecannotguessattherootpasswordfromthenetwork. Disallowrootloginremotely?[Y/n]n ...skipping. Bydefault,MariaDBcomeswithadatabasenamed'test'thatanyonecan access.Thisisalsointendedonlyfortesting,andshouldberemoved beforemovingintoaproductionenvironment. Removetestdatabaseandaccesstoit?[Y/n]y -Droppingtestdatabase... ...Success! -Removingprivilegesontestdatabase... ...Success! Reloadingtheprivilegetableswillensurethatallchangesmadesofar willtakeeffectimmediately. Reloadprivilegetablesnow?[Y/n]y ...Success! Cleaningup... Alldone!Ifyou'vecompletedalloftheabovesteps,yourMariaDB installationshouldnowbesecure. ThanksforusingMariaDB! 查询集群数量 [root@mysql-cluster-1log]#mysql-uroot-p-e"showstatuswhereVariable_namelike'wsrep_cluster_size'" Enterpassword: +--------------------+-------+ |Variable_name|Value| +--------------------+-------+ |wsrep_cluster_size|1| +--------------------+-------+ 可以看到只有一个 查看当前集群ip [root@mysql-cluster-1log]#mysql-uroot-p-e"showstatuswhereVariable_namelike'wsrep_incoming_addresses'" Enterpassword: +--------------------------+-------------------+ |Variable_name|Value| +--------------------------+-------------------+ |wsrep_incoming_addresses|192.168.1.18:3306| +--------------------------+-------------------+ 2、在node里配置 同样修改bind-address、wsrep_cluster_name、wsrep_node_address、wsrep_node_name [root@mysql-cluster-2log]#grep-v'^#'/etc/my.cnf.d/server.cnf|sed'/^$/d' [server] [mysqld] collation-server=utf8_general_ci init-connect='SETNAMESutf8' character-set-server=utf8 skip-name-resolve skip-host-cache open_files_limit=65535 max_connections=5000 bind-address=192.168.1.20 binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 innodb_file_per_table character-set-server=utf8 [galera] wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_provider_options="pc.recovery=TRUE;gcache.size=300M" wsrep_cluster_address='gcomm://192.168.1.18' wsrep_cluster_name='ck-galera' wsrep_node_address='192.168.1.20' wsrep_node_name='m-2' wsrep_sst_method=rsync [embedded] [mariadb] [mariadb-10.1] 启动 systemctlenablemariadb systemctlstartmariadb 查看集群数量 [root@mysql-cluster-2log]#mysql-uroot-p-e"showstatuswhereVariable_namelike'wsrep_cluster_size'" Enterpassword: +--------------------+-------+ |Variable_name|Value| +--------------------+-------+ |wsrep_cluster_size|2| +--------------------+-------+ 可以看到已经有2个节点了 查看集群ip [root@mysql-cluster-2log]#mysql-uroot-p-e"showstatuswhereVariable_namelike'wsrep_incoming_addresses'" Enterpassword: +--------------------------+-------------------------------------+ |Variable_name|Value| +--------------------------+-------------------------------------+ |wsrep_incoming_addresses|192.168.1.20:3306,192.168.1.18:3306| +--------------------------+-------------------------------------+ 可以看到node1与node2都在里面 3、在node3里配置 修改跟node1与node2一样 [root@mysql-cluster-3~]#grep-v'^#'/etc/my.cnf.d/server.cnf|sed'/^$/d' [server] [mysqld] collation-server=utf8_general_ci init-connect='SETNAMESutf8' character-set-server=utf8 skip-name-resolve skip-host-cache open_files_limit=65535 max_connections=5000 bind-address=192.168.1.19 binlog_format=row default_storage_engine=InnoDB innodb_autoinc_lock_mode=2 innodb_file_per_table character-set-server=utf8 [galera] wsrep_on=ON wsrep_provider=/usr/lib64/galera/libgalera_smm.so wsrep_provider_options="pc.recovery=TRUE;gcache.size=300M" wsrep_cluster_address='gcomm://192.168.1.18,192.168.1.20' wsrep_cluster_name='ck-galera' wsrep_node_address='192.168.1.19' wsrep_node_name='m-3' wsrep_sst_method=rsync [embedded] [mariadb] [mariadb-10.1] 启动 systemctlenablemariadb systemctlstartmariadb 查看集群数量 [root@mysql-cluster-3~]#mysql-uroot-p-e"showstatuswhereVariable_namelike'wsrep_cluster_size'" Enterpassword: +--------------------+-------+ |Variable_name|Value| +--------------------+-------+ |wsrep_cluster_size|3| +--------------------+-------+ 查看集群ip [root@mysql-cluster-3~]#mysql-uroot-p-e"showstatuswhereVariable_namelike'wsrep_incoming_addresses'" Enterpassword: +--------------------------+-------------------------------------------------------+ |Variable_name|Value| +--------------------------+-------------------------------------------------------+ |wsrep_incoming_addresses|192.168.1.19:3306,192.168.1.20:3306,192.168.1.18:3306| +--------------------------+-------------------------------------------------------+ 可以看到3个节点都在里面了。 下一步大家如果想测试，可以在任意一个节点里创建数据库与表，插入数据后，在另外节点里查看是否有对应信息，我这里就不列举了。
