Saturday, October 14, 2017

How to create MariaDB Galera Cluster in CentOS 6.x



Here I will show how we can create a galera cluster of three nodes in Cent OS 6.x. This will bring you an idea of the setup and configurations and I am sure you can create the same in other CentOS or RHEL distributions.

1. Setup MariaDB
Setup Yum Repo at /etc/yum.repos.d/MariaDB.repo
For CentOS 6.x use the following yum repo configuration.
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
To install MariaDB using Yum, run the commands below :
yum clean all
yum install MariaDB-Galera-server MariaDB-client galera
service mysql start
mysql_secure_installation
Run the command
mysql -uroot -p
to check whether the MariaDB server is running successfully or not.
Once you succeed in installing MariaDB, stop the service for now by using the command
service mysql stop
Do the same for all the three nodes of your galera cluster.

2. Configuring MariaDB
Go to /etc/my.cnf and check for the following configurations. Here I am considering that you are going to involve three nodes in the galera cluster... and the IP addresses are 101.10.101.10, 111.11.111.11, 121.12.121.12.
Here is my /etc/my.cnf configured for Galera Cluster

# MySQL configuration file
#
# Comments copied from MySQL documenation at:
# http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
#
# NOTE: primary things to change in this file for a new ERS installation are
# typically:
# * server-id (if you have a replicated system).
# * log-bin (should be active if you have a replicated system).
# * innodb_buffer_pool_size, key-buffer-size (depending on the amount of free RAM).
#
# $Id$
 
[mysqld]
 
general-log
general-log-file=/var/lib/mysql/mysql.log
log-output=file
 
# Set the default storage engine (table type) for tables.
default-storage-engine=InnoDB
 
# The server ID. This value is set by the --server-id option. It is used for
# replication to enable master and slave servers to identify themselves
# uniquely.
#
server-id=10
 
# The directory used for temporary files and temporary tables.
#
tmpdir=/var/tmp
 
# Whether the binary log is enabled - should be enabled on master if
# replication is used.
#
#log-bin
 
# Set on slave if you have replication enabled.
#
#master-host=
#master-user=
#master-password=
 
# Replicate these databases.
#
# WARNING:
#
# To specify multiple databases you must use multiple instances of this
# option. Because database names can contain commas, if you supply a comma
# separated list then the list will be treated as the name of a single
# database.
#
#replicate-do-db=Refill
#replicate-do-db=Billing
 
# Additional memory pool that is used by InnoDB to store metadata
# information.  If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS.  As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
#
innodb_additional_mem_pool_size = 16M
 
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system.  Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
#
innodb_buffer_pool_size = 2G
 
# InnoDB stores data in one or more data files forming the tablespace.
# If you have a single logical drive for your data, a single
# autoextending file would be good enough. In other cases, a single file
# per device is often a good choice. You can configure InnoDB to use raw
# disk partitions as well - please refer to the manual for more info
# about this.
#
#innodb_data_file_path = ibdata1:10M:autoextend
 
# InnoDB tries to keep the number of operating system threads concurrently
# inside InnoDB less than or equal to the limit given by this variable. Once
# the number of threads reaches this limit, additional threads are placed into
# a wait state within a FIFO queue for execution. Threads waiting for locks are
# not counted in the number of concurrently executing threads.
#
# The correct value for this variable is dependent on environment and workload.
# You will need to try a range of different values to determine what value
# works for your applications. A recommended value is 2 times the number of
# CPUs plus the number of disks.
#
innodb_thread_concurrency = 8
 
 # If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.
#
# ERS 3.14 does not use db transactions, so this can be set to 0.
#
innodb_flush_log_at_trx_commit = 0
 
# The size of the buffer InnoDB uses for buffering log data. As soon as
# it is full, InnoDB will have to flush it to disk. As it is flushed
# once per second anyway, it does not make sense to have it very large
# (even with long transactions).
#
innodb_log_buffer_size = 8M
 
# Size of each log file in a log group. You should set the combined size
# of log files to about 25%-100% of your buffer pool size to avoid
# unneeded buffer pool flush activity on log file overwrite. However,
# note that a larger logfile size will increase the time needed for the
# recovery process.
#
#innodb_log_file_size = 512MB
 
# Total number of files in the log group. A value of 2-3 is usually good
# enough.
#
#innodb_log_files_in_group = 2
 
# You can store each InnoDB table and its indexes in its own file. This feature
# is called “multiple tablespaces” because in effect each table has its own
# tablespace.
#
# Using multiple tablespaces can be beneficial to users who want to move
# specific tables to separate physical disks or who wish to restore backups of
# single tables quickly without interrupting the use of other InnoDB tables.
#
innodb_file_per_table
 
# If a write to the binary log causes the current log file size to exceed the
# value of this variable, the server rotates the binary logs.
#
max_binlog_size=256M
 
# If max_relay_log_size is 0, the value of max_binlog_size applies to relay
# logs as well.
#
max_relay_log_size=0
 
# The maximum size of the temporary file that MySQL is allowed to use while
# re-creating a MyISAM index (during REPAIR TABLE, ALTER TABLE, or LOAD DATA
# INFILE).  If the file size would be larger than this value, the index is
# created using the key cache instead, which is slower.
#
myisam_max_sort_file_size=5G
 
# If the temporary file used for fast MyISAM index creation would be larger
# than using the key cache by the amount specified here, prefer the key cache
# method.  This is mainly used to force long character keys in large tables to
# use the slower key cache method to create the index.
#
#myisam_max_extra_sort_file_size=5G
 
# The size of the buffer that is allocated when sorting MyISAM indexes during
# a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
#
myisam_sort_buffer_size=512M
 
# The maximum size of one packet or any generated/intermediate string.  It
# should be as big as the largest BLOB you want to use.  The protocol limit
# for max_allowed_packet is 16MB before MySQL 4.0 and 1GB thereafter.
#
max_allowed_packet=16M
 
# key_buffer_size is the size of the buffer used for index blocks.  The key
# buffer is also known as the key cache.
#
key_buffer_size=1G
 
# The amount of memory allocated for caching query results.
#
query_cache_size=0
 
# The number of seconds the server waits for activity on a non-interactive
# connection before closing it. This timeout applies only to TCP/IP
# connections, not to connections made via Unix socket files, named pipes, or
# shared memory.
#
wait_timeout=604800
 
# Log connections and SQL statements received from clients to this file.
#
#log
 
# Whether slow queries should be logged.
# "Slow" is determined by the value of the long_query_time variable.
#
#log-slow-queries
#long-query-time=1
#log-long-format
#log=/var/log/mysqld.log
#log-queries-not-using-indexes
 
# The server default character set. This variable was added in MySQL 4.1.1.
# NOTE! This variable should be utf8 from ERS version 3.9 and up.
#
character_set_server=utf8
 
# The number of simultaneous client connections allowed. This variable should
# be set to at least 1000 starting with ERS 3.10.
#
max_connections=1000
 
# The number of open tables for all threads. Increasing this value increases
# the number of file descriptors that mysqld requires. You can check whether
# you need to increase the table cache by checking the Opened_tables status
# variable. If the value of Opened_tables is large and you don't do FLUSH
# TABLES often (which just forces all tables to be closed and reopened), then
# you should increase the value of the table_cache variable.
#
#table_cache=1024
 
[mysql]
user=root
default-character-set=utf8
 
[mariadb]
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://101.10.101.10,111.11.111.11,121.12.121.12
wsrep_cluster_name='cluster1'
wsrep_node_address='101.10.101.10'
wsrep_node_name='db1'
wsrep_sst_method=rsync
wsrep_sst_auth=username:password
Here the "wsrep_sst_auth" is the username:password of my MariaDB server. Make sure you put proper values at username:password against "wsrep_sst_auth" property.
The same /etc/my.cnf should be pasted in each of the nodes, just we have to change the values of "wsrep_cluster_name" and "wsrep_node_address".

3. Get It Started
The very first node, that is the "db1" should be started with below command
/etc/init.d/mysql bootstrap
The other nodes should be started with the normal command like
/etc/init.d/mysql start
While starting the other nodes, if you find the message like 'Starting MySQL....SST in progress, setting sleep higher. SUCCESS! ', you are DONE !!! 👍
To check if the Galera Cluster is running properly or not...
Go to the "db1" and enter -
mysql -uroot -p
Run the query -
MariaDB [(none)]> show status like 'wsrep%';
find the below row
......
wsrep_incoming_addresses | 101.10.101.10:3306,111.11.111.11:3306,121.12.121.12:3306
......


No comments:

Post a Comment