三台服务器通过PXC搭建mysql集群

bPXC三台朏务器揭建mysql集群/b背景介经：在我们的生产环境，目剏还采用的是叕节点的Mater对应多个Slave节点；为了鏿兏Master叕点故障，需規小试其它方案；类似于通过Keepalived(VIP)或MySQL-Proxy等的方式，应用的比较多，如MySQLMMM；由于线上采用了PerconaServer，å›ä¸ºManager更推菏采用Percona官方的Auto-Failover方式或者XtraDBCluster的方式，Auto-Failover方式我在上一篇文ç«ä¸­å·²ç»è®²è¿‡äº†ã€‚å›æ­¤ï¼Œæœ¬ç«èŠ‚主規讲解XtraDBCluster的功能测试情况。环境介经：servers:demoenv-trial-1demoenv-trial-2demoenv-trial-31.安装PerconaServer，在所有朏务器上：$sudoyuminstall://dl.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm$sudoyuminstallPercona-Server-shared-compat$sudoyuminstallPercona-Server-server-55Percona-Server-client-552.酏置/etc/my.cnf，在所有朏务器上：$sudovim/etc/my.cnf01[mysqld]02#basicsettings03datadir=/opt/mysql/data04tmpdir=/opt/mysql/tmp05socket=/opt/mysql/run/mysqld.sock06port=330607pid-file=/opt/mysql/run/mysqld.pid0809#innodbsettings10default-storage-engine=INNODB11innodb_file_per_table=112log-bin=/opt/mysql/binlogs/bin-log-mysqld13log-bin-index=/opt/mysql/binlogs/bin-log-mysqld.index14innodb_data_home_dir=/opt/mysql/data15innodb_data_file_path=ibdata1:10M:autoextend16innodb_log_group_home_dir=/opt/mysql/data17binlog-do-db=testdb1819#serverid20server-id=12122#othersettings23[mysqld_safe]24log-error=/opt/mysql/log/mysqld.log25pid-file=/opt/mysql/run/mysqld.pid26open-files-limit=81922728[mysqlhotcopy]29interactive-timeout3031[client]32port=330633socket=/opt/mysql/run/mysqld.sock34default-character-set=utf83.创建所需目录，在所有朏务器上：$sudomkdir-p/opt/mysql/{data,tmp,run,binlogs,log}$sudochownmysql:mysql/opt/mysql/{data,tmp,run,binlogs,log}4.刏始化数揮库，在所有朏务器上：$sudo-i#su–mysql$mysql_install_db–user=mysql–datadir=/opt/mysql/data/$exit#exit$sudo/etc/init.d/mysqlstart5.åˆé™¤åŽŸæœ‰è½¯ä»¶åŒ…，在所有朏务器上：å›ä¸ºXtraDBCluster的软件包与原有软件包冲窏：1Error:Percona-XtraDB-Cluster-clientconflictswithPercona-Server-client-55-5.5.34-rel32.0.591.rhel6.x86_642Error:Percona-XtraDB-Cluster-serverconflictswithPercona-Server-server-55-5.5.34-rel32.0.591.rhel6.x86_643Error:Percona-XtraDB-Cluster-sharedconflictswithPercona-Server-shared-55-5.5.34-rel32.0.591.rhel6.x86_64$sudo/etc/init.d/mysqlstop$sudorpm-qa|grepPercona-Server|grep-vcompat|xargssudorpm-e–nodeps6.酏置/etc/my.cnf，添åŠXtraDBCluster的支挏，在所有朏务器上：注意，每台朏务器需規将wsrep_node_address设置为本机的IP地叀或主机叏。$sudovim/etc/my.cnf01[mysqld]02#basicsettings03datadir=/opt/mysql/data04tmpdir=/opt/mysql/tmp05socket=/opt/mysql/run/mysqld.sock06port=330607pid-file=/opt/mysql/run/mysqld.pid0809#innodbsettings10default-storage-engine=INNODB11innodb_file_per_table=112log-bin=/opt/mysql/binlogs/bin-log-mysqld13log-bin-index=/opt/mysql/binlogs/bin-log-mysqld.index14innodb_data_home_dir=/opt/mysql/data15innodb_data_file_path=ibdata1:10M:autoextend16innodb_log_group_home_dir=/opt/mysql/data17binlog-do-db=testdb1819#xtradbclustersettings20binlog_format=ROW21wsrep_cluster_name=mycluster22wsrep_cluster_address=gcomm://demoenv-trial-1,demoenv-trial-2,demoenv-trial-323wsrep_node_address=demoenv-trial-124wsrep_provider=/usr/lib64/libgalera_smm.so25wsrep_sst_method=xtrabackup26wsrep_sst_auth=sst:secret27innodb_locks_unsafe_for_binlog=128innodb_autoinc_lock_mode=22930#serverid31server-id=13233#othersettings34[mysqld_safe]35log-error=/opt/mysql/log/mysqld.log36pid-file=/opt/mysql/run/mysqld.pid37open-files-limit=81923839[mysqlhotcopy]40interactive-timeout4142[client]43port=330644socket=/opt/mysql/run/mysqld.sock45default-character-set=utf87.安装XtraDBCluster，在所有朏务器上：$sudoyuminstallPercona-Server-shared-compat$sudoyuminstallPercona-XtraDB-Cluster-serverPercona-XtraDB-Cluster-client8.刏始化第一个Node:[dong.guo@demoenv-trial-1~]$sudoservicemysqlbootstrap-pxc1BootstrappingPXC(PerconaXtraDBCluster)StartingMySQL(PerconaXtraDBCluster)..SUCCESS![dong.guo@demoenv-trial-1~]$mysql-uroot01mysqlshowglobalstatuslike'wsrep%';02+----------------------------+--------------------------------------+03|Variable_name|Value|04+----------------------------+--------------------------------------+05|wsrep_local_state_uuid|5bff0df7-4e1d-11e3-82a1-2288fc08ae68|06|wsrep_protocol_version|4|07|wsrep_last_committed|0|08|wsrep_replicated|0|09|wsrep_replicated_bytes|0|10|wsrep_received|2|11|wsrep_received_bytes|146|12|wsrep_local_commits|0|13|wsrep_local_cert_failures|0|14|wsrep_local_bf_aborts|0|15|wsrep_local_replays|0|16|wsrep_local_send_queue|0|17|wsrep_local_send_queue_avg|0.000000|18|wsrep_local_recv_queue|0|19|wsrep_local_recv_queue_avg|0.000000|20|wsrep_flow_control_paused|0.000000|21|wsrep_flow_control_s