Mycat MySQLGalera读写分离验证安装手册

Mycat+MySQL Galera读写分离验证作者菜菜-李梦嘉56335443部署MySQL Galera安装环境安装前准备安装gcc、gcc-c++# yum install gcc gcc-c++安装boost-devel# yum install boost-devel安装scons check-devel openssl-devel# yum install scons check-devel openssl-devel安装libaio# yum install libaio安装perl、perl-devel# yum install perl perl-devel安装rsync、lsof# yum install rsync lsofMySQL Galera安装安装含wsrep Patch的MySQL# tar zxvf mysql-wsrep-5.6.27-25.12-linux-x86_64.tar.gz# mv mysql-wsrep-5.6.27-25.12-linux-x86_64 /usr/local/mysql# groupadd mysql# useradd -r -g mysql mysql# chown -R mysql:mysql .# ./scripts/mysql_install_db --no-defaults --datadir=/usr/local/mysql/data --user=mysql # chown -R root .# chown -R mysql data# ln -s /usr/local/mysql/bin/* /usr/sbin安装Galera复制插件# tar zxvf galera-3-25.3.13.tar.gz# cd galera-3-25.3.13# scons# cp garb/garbd /usr/local/mysql/bin/# cp libgalera_smm.so /usr/local/mysql/lib/plugin/配置MySQL Galera# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld# mkdir -p /var/lib/mysql# chown mysql:mysql /var/lib/mysql# vi /etc/f[client]port = 3306socket = /var/lib/mysql/mysql.sock[mysqld_safe]log-error = /var/lib/mysql/mysql.logpid-file = /var/lib/mysql/mysql.pid[mysqld]wsrep_node_name = node1wsrep_provider = /usr/local/mysql/lib/plugin/libgalera_smm.so#wsrep_provider_options ='gcache.size=1G;socket.ssl_key=my_key;socket.ssl_cert=my_cert' #wsrep_slave_threads=16wsrep_sst_method = rsyncport = 3306socket = /var/lib/mysql/mysql.sockuser = mysqlbasedir = /usr/local/mysqldatadir = /usr/local/mysql/datadefault_storage_engine=InnoDB#innodb_buffer_pool_size=1G#innodb_log_file_size=256Minnodb_autoinc_lock_mode=2innodb_locks_unsafe_for_binlog=1innodb_flush_log_at_trx_commit=0innodb_doublewrite=0innodb_file_per_table=1binlog_format=ROWlog-bin=mysql-binserver-id=101relay-log=mysql-relay-bin#read_only=1log-slave-updates=1MySQL Galera启动与关闭初次启动节点# /usr/local/mysql/bin/mysqld_safe --wsrep_cluster_address=gcomm:// >/dev/null &或# service mysqld start --wsrep_cluster_address=gcomm://查看mysql启动端口# netstat -plantu | grep mysqldtcp 0 0 0.0.0.0:4567 0.0.0.0:* LISTEN 3656/mysqld注:1)”gcomm://”是特殊的地址,仅仅是galera cluster初始化启动时候使用,再次启动的时候需要使用具体的IP地址.2) 端口4567是wsrep使用的默认端口.该端口的防火墙设置规则应该和3306的一样.关闭# mysqladmin -uroot -p shutdown或# service mysqld stop添加MySQL Galera新节点添加新节点的时候,新接入的节点叫Joiner,给Joiner提供复制的节点叫Donor.新的节点接入需要:1) 安装带wsrep patch的MySQL版本2) 安装Galera复制插件3) 配置好新节点的MySQL(参考Donor的f)4) 配置或启动的gcomm://的地址是需要使用donor的IP.接入节点galera2mysqld_safe --wsrep_cluster_address="gcomm://192.168.1.250:4567,192.168.1.248:4567" >/dev/null &接入节点galera3service mysqld start --wsrep_cluster_address="gcomm://192.168.1.250:4567,192.168.1.248:4567"修改galera节点的wsrep_cluster_addressservice mysql restart --wsrep_cluster_address="gcomm://192.168.1.248:4567,192.168.1.249:4567"同步验证在其中一个节点上创建账号mysql> create user 'mallcai'@ '192.168.1.%' identified by 'XXXXXX';mysql> grant all privileges on *.* to 'mallcai'@ '192.168.1.%' with grant option;mysql> flush privileges;在其他节点查看账号同步情况mysql> select user,host from er;+---------+-------------+| user | host |+---------+-------------+| root | 127.0.0.1 || root | ::1 || | galera || root | galera || | localhost || root | localhost |+---------+-------------+7 rows in set (0.00 sec)MyCat配置MySQL Galera读写分离方案一schema.xml配置说明：Balance=1 所有readHost和standby writeHost参与读负载WriteType=0 所有写操作发送到配置的第一个writeHost，第一个挂了切换到第二个writeHost SwitchType=3 基于MySQL galary cluster的切换机制，心跳语句为show status like 'wsrep%'配置所有节点为writeHost，其他节点作为writeHost的readHost查看所有host的状态创建测试表并插入数据选择192.168.1.248作为writeHost，而非按配置文件中的顺序选择192.168.1.250 Create操作Insert操作运行3次查询3个节点都负载了select操作hostR2 192.168.1.249hostR5 192.168.1.248hostR6 192.168.1.250关闭192.168.1.248上的mysql进程查看节点状态再次插入数据查看mycat.log日志，选择192.168.1.249作为写入节点运行2次select查询alive的节点都负载了select操作hostR2 192.168.1.249关闭192.168.1.249上的mysql进程查看节点状态插入数据alive的192.168.1.250作为writeHost运行select查询alive的192.168.1.250作为readHost重启关闭节点查看节点状态，节点加入成功插入数据仍然将最后一次的192.168.1.250作为writeHost总结：1、Mycat选取一个writeHost作为写节点，所有节点都作为readHost；2、如果writeHost失效，选择另一个writeHost作为写节点，其余节点作为readHost；3、除非全部writeHost失效，否则业务不会中断，安全性较高；4、并未达到读写分离的目的。

方案二schema.xml配置dataNode配置保持不变192.168.1.250作为writeHost192.168.1.249作为192.168.1.250的readHost 192.168.1.248作为192.168.1.250的备用writeHost查看节点状态创建表并插入数据选择192.168.1.250作为writeHostCreate操作Insert操作运行2次查询readHost和备用writeHost都负载select操作hostM2 192.168.1.248hostR1 192.168.1.249关闭192.168.1.250上的mysql进程查看节点状态插入数据备用writeHost192.168.1.248提升为写节点运行2次select查询此时192.168.1.250的readHost192.168.1.249已经不能分担读操作，查询都由192.168.1.248处理重启192.168.1.250的mysql查看节点状态插入数据writeHost仍然为192.168.1.248运行查询备writeHost和readHost分担select查询hostM1 192.168.1.250hostR1 192.168.1.249总结：1、所有节点正常时，writeHost作为写节点，备用writeHost和readHost作为读节点；2、当主writeHost失效时，备用writeHost同时负担读和写，主writeHost下的readHost无法负担读；3、当主writeHost失效时，备writeHost压力过大。