MYSQL MULTI-MASTER REPLICATION WITH GALERA

• galera最新版
• MySQL-server最新版
• MySQL-client最新版

ダウンロード

# wget https://launchpad.net/galera/2.x/23.2.2/+download/galera-23.2.2-1.rhel5.x86_64.rpm
# wget https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/MySQL-server-5.5.28_wsrep_23.7-1.rhel5.x86_64.rpm
# wget http://mysql.ntu.edu.tw/Downloads/MySQL-5.5/MySQL-client-5.5.30-1.rhel5.x86_64.rpm

インストール

# rpm -ivh galera-23.2.1-1.rhel5.x86_64.rpm
# rpm -ivh MySQL-client-5.5.24-1.rhel5.x86_64.rpm
# rpm -ivh MySQL-server-5.5.23_wsrep_23.6-1.rhel5.x86_64.rpm

先にアンインストールしておく

# rpm -qa | grep mysql
mysql-5.0.77-4.el5_6.6
mysql-server-5.0.77-4.el5_6.6

# rpm -e --nodeps mysql-server-5.0.77-4.el5_6.6
# rpm -e --nodeps mysql-5.0.77-4.el5_6.6

[root@ohana ~]# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

##innodb_buffer_pool_size = 2048M
innodb_additional_mem_pool_size = 20M
thread_concurrency = 48
##innodb_buffer_pool_size = 16G
##innodb_log_file_size = 1946M

sort_buffer_size = 16M
join_buffer_size = 16M
query_cache_size = 128M
query_cache_limit = 4M

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
# WSREP NOTE: additional wsrep configuration is in wsrep.cnf
#
!includedir /etc/mysql/conf.d/

# This file contains wsrep-related mysqld options. It should be included
# in the main MySQL configuration file.
#
# Options that need to be customized:
#  - wsrep_provider
#  - wsrep_cluster_address
#  - wsrep_sst_auth
# The rest of defaults should work out of the box.

##
## mysqld options _MANDATORY_ for correct opration of the cluster
##
[mysqld]

# (This must be substituted by wsrep_format)
binlog_format=ROW

# Currently only InnoDB storage engine is supported
default-storage-engine=innodb

# to avoid issues with 'bulk mode inserts' using autoinc
innodb_autoinc_lock_mode=2

# This is a must for paralell applying
innodb_locks_unsafe_for_binlog=1

# Query Cache is not supported with wsrep
query_cache_size=0
query_cache_type=0

# Override bind-address
# In some systems bind-address defaults to 127.0.0.1, and with mysqldump SST
# it will have (most likely) disastrous consequences on donor node
bind-address=0.0.0.0

##
## WSREP options
##

# Full path to wsrep provider library or 'none'
wsrep_provider=/usr/lib64/galera/libgalera_smm.so

# Provider specific configuration options
#wsrep_provider_options=

# Logical cluster name. Should be the same for all nodes.
wsrep_cluster_name="onapp_db_cluster"

# Group communication system handle
wsrep_cluster_address="gcomm://:4567"

# Human-readable node name (non-unique). Hostname by default.
#wsrep_node_name=

# Base replication <address|hostname>[:port] of the node.
# The values supplied will be used as defaults for state transfer receiving,
# listening ports and so on. Default: address of the first network interface.
#wsrep_node_address=

# Address for incoming client connections. Autodetect by default.
#wsrep_node_incoming_address=

# How many threads will process writesets from other nodes
wsrep_slave_threads=1

# DBUG options for wsrep provider
#wsrep_dbug_option

# Generate fake primary keys for non-PK tables (required for multi-master
# and parallel applying operation)
wsrep_certify_nonPK=1

# Maximum number of rows in write set
wsrep_max_ws_rows=131072

# Maximum size of write set
wsrep_max_ws_size=1073741824

# to enable debug level logging, set this to 1
wsrep_debug=0

# convert locking sessions into transactions
wsrep_convert_LOCK_to_trx=0

# how many times to retry deadlocked autocommits
wsrep_retry_autocommit=1

# change auto_increment_increment and auto_increment_offset automatically
wsrep_auto_increment_control=1

# retry autoinc insert, which failed for duplicate key error
wsrep_drupal_282555_workaround=0

# enable "strictly synchronous" semantics for read operations
wsrep_causal_reads=0

# Command to call when node status or cluster membership changes.
# Will be passed all or some of the following options:
# --status  - new status of this node
# --uuid    - UUID of the cluster
# --primary - whether the component is primary or not ("yes"/"no")
# --members - comma-separated list of members
# --index   - index of this node in the list
wsrep_notify_cmd=

##
## WSREP State Transfer options
##

# State Snapshot Transfer method
wsrep_sst_method=mysqldump

# Address on THIS node to receive SST at. DON'T SET IT TO DONOR ADDRESS!!!
# (SST method dependent. Defaults to the first IP of the first interface)
wsrep_sst_receive_address=172.16.21.176

# SST authentication string. This will be used to send SST to joining nodes.
# Depends on SST method. For mysqldump method it is root:<root password>
wsrep_sst_auth=root:n1eDqUXt

# Desired SST donor name.
#wsrep_sst_donor=

# Protocol version to use
# wsrep_protocol_version=

# /etc/init.d/mysql start
# /usr/bin/mysqladmin -u root password '[Password]'

ユーザ確認

mysql> select host,user,password from mysql.user;

/etc/mysql/conf.d/wsrep.cnf

IPアドレスや、先ほど設定したMySQLのパスワードなどを設定する。

wsrep_cluster_name=[クラスター名称]
wsrep_sst_receive_address=[レプリケートに利用する自分のIPアドレス]
wsrep_sst_auth=[mysqlのrootアカウント]:[mysqlのrootパスワード]

1号機、2号機ともにMySQLを停止し、1号機から2号機へ/var/lib/mysql/をrsyncしておく。

mysql> SET GLOBAL wsrep_cluster_address='gcomm://192.168.100.10:4567';

※2号機から実施
※一文字でも差異があると、同期かけた方がmysqlリスタートを繰り返す。

下記のような感じで起動時に指定して上げている方がうまくいく時がある。

初期ノード

/etc/init.d/mysql start --wsrep_cluster_address="gcomm://:4567" 

セカンダリ

/etc/init.d/mysql start --wsrep_cluster_address="gcomm://192.168.100.10:4567" 

# mysql -u root -pk3fFuGQUm -e 'show status;' | grep -e wsrep_local_state_comment \
> -e wsrep_cluster_size -e wsrep_local_index -e wsrep_ready

後は適当にUpdateやInsertを行なってみて、正常に同期されている事を確認する。