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 lsof

MySQL 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 = 3306

socket = /var/lib/mysql/mysql.sock

[mysqld_safe]

log-error = /var/lib/mysql/mysql.log

pid-file = /var/lib/mysql/mysql.pid

[mysqld]

wsrep_node_name = node1

wsrep_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=16

wsrep_sst_method = rsync

port = 3306

socket = /var/lib/mysql/mysql.sock

user = mysql

basedir = /usr/local/mysql

datadir = /usr/local/mysql/data

default_storage_engine=InnoDB

#innodb_buffer_pool_size=1G

#innodb_log_file_size=256M

innodb_autoinc_lock_mode=2

innodb_locks_unsafe_for_binlog=1

innodb_flush_log_at_trx_commit=0

innodb_doublewrite=0

innodb_file_per_table=1

binlog_format=ROW

log-bin=mysql-bin

server-id=101

relay-log=mysql-relay-bin

#read_only=1

log-slave-updates=1

MySQL Galera启动与关闭

初次启动节点

# /usr/local/mysql/bin/mysqld_safe --wsrep_cluster_address=gcomm:// >/dev/null &

或

# service mysqld start --wsrep_cluster_address=gcomm://

查看mysql启动端口

# netstat -plantu | grep mysqld

tcp 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.

接入节点galera2

mysqld_safe --wsrep_cluster_address="gcomm://192.168.1.250:4567,192.168.1.248:4567" >/dev/null &

接入节点galera3

service mysqld start --wsrep_cluster_address="gcomm://192.168.1.250:4567,192.168.1.248:4567"

修改galera节点的wsrep_cluster_address

service 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 |