Step 1: Add MariaDB Repositories
Create a mariadb repository /etc/yum.repos.d/mariadb.repo using following content in your system. Below repository will work on CentOS 6.x systems, For other system use repository generation tool and add to your system.
For CentOS 6 – 64bit
sudo vim /etc/yum.repos.d/mariadb.repo
[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.3.15/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Step 2: Install MariaDB and Galera
Before installing MariaDB Galera cluster packages, remove any existing MySQL or MariaDB packages installed on system. After that use following command to install on all nodes.
# sudo yum install MariaDB-server MariaDB-client galera
step 3: Initial MariaDB Configuration
After successfully installing packages in above steps do the some initial MariaDB configurations. Use following command and follow the instructions on all nodes of cluster. If will prompt to set root account password also.
# sudo service mysql start
# sudo mysql_secure_installation
1. Just enter when ask for current password
2. Set new password
3. Enter Y and press enter to Remove anonymous user
4. Enter Y and press enter to disable root login remotely.
5. Enter Y and press enter to remove test database
6. Enter Y and press enter to reload privilages
After that create a user in MariaDB on all nodes, which can access database from your network in cluster.
# sudo mysql -u root –p
Enter root password.
MariaDB [(none)]> create user flexadmin;
MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO ‘flexadmin’@’%’ IDENTIFIED BY ‘7v6zQyA7k4cA);’ WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit
Now stop MariaDB service before starting cluster configuration
# sudo service mysql stop
Step 4: Setup MariaDB Galera Cluster on DB1
Lets start setup MariaDB Galera cluster from DB1 server. Edit server.cnf file
sudo vim /etc/my.cnf.d/server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_on=ON
#wsrep_provider=
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
#wsrep_cluster_address=
wsrep_cluster_address=”gcomm://18.212.18.192,54.175.149.71,54.205.28.105″
binlog_format=row
default_storage_engine=InnoDB
query_cache_size=0
innodb_autoinc_lock_mode=2
wsrep_cluster_name=’cluster1′
wsrep_node_address=’18.212.18.192′
wsrep_node_name=’db1′
wsrep_sst_method=rsync
wsrep_sst_auth=flexadmin:7v6zQyA7k4cA);
#
# Allow server to accept connections on all interfaces.
#
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
# this is only for embedded server
[embedded]
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[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://18.212.18.192,54.175.149.71,54.205.28.105″
wsrep_cluster_name=’cluster1′
wsrep_node_address=’18.212.18.192′
wsrep_node_name=’db1′
wsrep_sst_method=rsync
wsrep_sst_auth=flexadmin:7v6zQyA7k4cA);
# This group is only read by MariaDB-10.3 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don’t understand
[mariadb-10.3]
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://18.212.18.192,54.175.149.71,54.205.28.105″
wsrep_cluster_name=’cluster1′
wsrep_node_address=’18.212.18.192′
wsrep_node_name=’db1′
wsrep_sst_method=rsync
wsrep_sst_auth=flexadmin:7v6zQyA7k4cA);
Step 5: On DB2 and DB3
On DB2 and DB3, just change wsrep_node_address and wsrep_node_name values according to DB2 and DB3 IP address. It will be changed at 3 places in server.cnf for both the server.
For DB2, it will be
wsrep_node_address=’54.175.149.71’
wsrep_node_name=’db2’
for DB3, it will be
wsrep_node_address=’54.205.28.105’
wsrep_node_name=’db3’
Step 6: Adding Galera start Command
Command – galera_start
Logfile location – /var/log/galera
******On DB1******
sudo vim /usr/bin/galera_start
HOME=/usr/bin
LOG=/var/log/galera
FLD=`date +%Y-%m-%d`
cd $HOME
if [ ! -d $LOG ]
then
sudo mkdir -pv $LOG
fi
LOGFILE=$LOG/galera_$FLD.txt
sudo touch $LOGFILE
sudo chmod 777 $LOGFILE
mysqld –wsrep-new-cluster >> $LOGFILE 2>&1 &
sudo chmod 777 /usr/bin/galera_start
******On DB2******
sudo vim /usr/bin/galera_start
HOME=/usr/bin
LOG=/var/log/galera
FLD=`date +%Y-%m-%d`
cd $HOME
if [ ! -d $LOG ]
then
sudo mkdir -pv $LOG
fi
LOGFILE=$LOG/galera_$FLD.txt
sudo touch $LOGFILE
sudo chmod 777 $LOGFILE
mysqld >> $LOGFILE 2>&1 &
sudo chmod 777 /usr/bin/galera_start
******On DB3******
sudo vim /usr/bin/galera_start
HOME=/usr/bin
LOG=/var/log/galera
FLD=`date +%Y-%m-%d`
cd $HOME
if [ ! -d $LOG ]
then
sudo mkdir -pv $LOG
fi
LOGFILE=$LOG/galera_$FLD.txt
sudo touch $LOGFILE
sudo chmod 777 $LOGFILE
mysqld >> $LOGFILE 2>&1 &
sudo chmod 777 /usr/bin/galera_start
Step 7: Adding Galera stop Command
Command – galera_stop
For DB1,DB2,DB3
It will same for all 3 machines
sudo vim /usr/bin/galera_stop
PROCESS=`ps -ef|grep mysql|awk -F ” ” {‘print $2’}|tr “\n” ” “`
sudo echo $PROCESS
sudo kill -9 $PROCESS
sudo chmod 777 /usr/bin/galera_stop
Step 7: Start MariaDB galera
On DB1 – execute
galera_start
This command will start DB cluster on DB1 with bootstrap option.
Now start cluster on DB2 and DB3. Execute same command
galera_start
Now for testing, create a test table in DB1. It will automatically create in DB2 and DB3.
Insert data in test table in DB2 or DB3 machine, it will automatically reflect in other machines.
