My SQL Galera Setup

By Animesh KumarUncategorized

Reference : https://blog.laimbock.com/2014/07/08/howto-setup-mariadb-galera-cluster-10-on-centos/

root@150.236.10.8 (Login to the Server using Root user)
1.configure the /etc/hosts
150.236.10.8 db1
150.236.10.9 db2
150.236.10.10 db3

now put SELinux into permissive mode on all nodes
2. setenforce 0

3. configure the firewall
lokkit –port 3306:tcp
lokkit –port 4444:tcp
lokkit –port 4567:tcp
lokkit –port 4568:tcp

4.download rpm sofware from
http://yum.mariadb.org/10.0/rhel6-amd64/

5.Configure yam
6.configure createrepo (yum install createrepo)
7.now createrepo in the directory where rpm’s are kept
createrepo -v .
8.change in /etc/yum.repos.d/local.repo
sample
[root@minbalx056v yum.repos.d]# cat local.repo
[local]
name=Red Hat Enterprise Linux
baseurl=file:///media
enable=1
gpgcheck=0

[mariadba]
name = MariaDB
baseurl = file:///DB/software
gpgkey=file:///DB/software/RPM-GPG-KEY-MariaDB
gpgcheck=1
/DB/software/RPMS_NEW/software
————————————————————————————-
for galrea cluster installation (run it on every node)

9.yum install –y MariaDB-Galera-server MariaDB-client galera
yum remove –y MariaDB-Galera-server MariaDB-client galera

yum remove MariaDB*server MariaDB-client galera

10.service mysql start (all nodes)
11.mysql_secure_installation –for securing database root password. on all nodes
12.login into database
mysql -u root -p

run below grant privilage to root
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’%’ IDENTIFIED BY ‘bharti123’ WITH GRANT OPTION;

——–
for cluster setup
change in below fines
before going to chnage the file,please stop the mysql DB
service mysql stop
1./etc/my.cnf.d/mysql-clients.cnf
2./etc/my.cnf.d/server.cnf

———————

mysql_install_db –user=mysql –ldata=/var/lib/mysql/
————————————————————————————–

150.236.10.8,150.236.10.9,150.236.10.10

service mysql start –wsrep-cluster-address=gcomm://node2

mysql -u cluster_user -p

service mysql start –wsrep-new-cluster

mysql -u root -p -e”show status like ‘wsrep%'”

/DB/mysql/db_logfiles/

service mysql start –wsrep-cluster-address=gcomm://150.236.10.9

wsrep_cluster_status | Disconnected

datadir=/DB/mysql/db_files

/DB/mysql

service mysql start –wsrep-new-cluster

mysql_install_db –user=mysql –ldata=/var/lib/mysql/

update user set password=PASSWORD(“bharti123”) where User=’root’;

How to Change default MySQL Data Directory in Linux


cp -rap /var/lib/mysql /DB/mysql

chown mysql.mysql /DB/mysql

cp -rap /DB/mysql /DB/datadir/mysql
cp -rap /var/lib/mysql /DB/datadir

chown root.root /DB/datadir
chown mysql.mysql /DB/datadir
general_log_file=/DB/logdir/mysql
log-error=/DB/logdir/mysql
pid-file=/DB/logdir/mysql

datadir=/DB/datadir/mysql
————————
mkdir -p /DB/datadir
cp -rap /DB/mysql /DB/datadir
cp -rap /DB/mysql /DB/datadir
————–
#datadir=/var/lib/mysql
datadir=/DB/mysql
#deprecated: log=/var/lib/mysql/mysqld.log
general_log=1
general_log_file=/DB/logdir/mysql/mysqld.log
log-error=/DB/logdir/mysql/mysqld.err
pid-file=/DB/logdir/mysql/mysqld.pid

chown root.root /DB/logdir/
———————————-
Change from rsync to xtrabackup – avoid read-only Donor during SST
————
http://support.severalnines.com/entries/22610387-Change-from-rsync-to-xtrabackup-avoid-read-only-Donor-during-SST

Advantage of using xtrabackup

The main advantage of using xtrabackup to synchronize the nodes is that the Donor is writeable during the synchronization process.

Bug: the web interface will still show the Donor being “read-only” despite the wsrep_sst_method is set to xtrabackup.
Setup
Install xtrabackup

You need to install xtrabackup on all nodes in the cluster.

On each node you can run:

s9s_backup –install -i <clusterid>
#for example:
s9s_backup –install -i 1
#verify it is installed:
which innobackupex
#the above should print out the following if everything is okay:
/usr/bin/innobackupex
# ubuntu/debian:
apt-get install nc
# redhat/centos:
yum install nc

s9s_backup requires internet access to Percona’s yum or apt repositories.

If you don’t have s9s_backup, you can download it here: http://www.severalnines.com/downloads/cmon/s9s_backup and place it in
/usr/bin on each node.

Read more about s9s_backup here http://support.severalnines.com/entries/22358923-backup-s9s-backup-and-installing-xtrabackup
On all nodes download and install (PXC users should not have to do this step):

https://launchpad.net/codership-mysql/5.5/5.5.28-23.7/+download/wsr…

and place the wsrep_sst_xtrabackup in {mysql_basedir}/bin

Ubuntu/Debian (most likely location):

/usr/local/mysql/bin

Redhat/Centos:

/usr/bin/

Then do:

chmod u+x /usr/bin/wsrep_sst_xtrabackup
chown mysql:mysql /usr/bin/wsrep_sst_xtrabackup
#or
chmod u+x /usr/local/mysql/bin/wsrep_sst_xtrabackup
chown mysql:mysql /usr/local/mysql/bin/wsrep_sst_xtrabackup

Update Configuration – Subscription Customers

Go into Config Mgmt , and update each my.cnf file and set:

wsrep_sst_method=xtrabackup

When saving, the configuration file will be exported to the node in question.

Now, the next time a node crashes and needs to do an SST the xtrabackup method will be used.
Update Configuration – Community

On all nodes edit the my.cnf file and set:

wsrep_sst_method=xtrabackup

Now, the next time a node crashes and needs to do an SST the xtrabackup method will be used.
——————————————————————

mail -s “This is the subject” animesh.kumar@sha-infotech.com
Hi someone

yum -y groupinstall “prod tools”

./configure –prefix=/DB/monit –without-pam

make

make install

mkdir /etc/monit.d/

service mysql bootstrap

——————————————
https://mediatemple.net/community/products/dv/204645100/installing-monit

./configure –prefix=/usr –without-pam
./configure –prefix=/DB/monit –without-pam

ps -aufx | grep mysql

https://mediatemple.net/community/products/dv/204645100/installing-monit

http://severalnines.com/blog/howto-online-upgrade-mariadb-galera-cluster-55-mariadb-10
http://severalnines.com/blog/howto-online-upgrade-mariadb-galera-cluster-55-mariadb-10

MySQL server PID file could not be found! [FAILED]
Starting MySQL.. [ OK ]
http://www.webhostingtalk.com/showthread.php?t=1424779
/DB/datadir/mysql/mysql-bin

For DR setup below needs to change on master
gtid_mode=ON
log_bin=binlog
log_slave_updates=1
enforce_gtid_consistency
expire_logs_days=7
server_id=2 # 1 for master1, 2 for master2, 3 for master3
binlog_format=ROW
For DR setup below needs to change on slave
gtid_mode=ON
log_bin=binlog
log_slave_updates=1
enforce_gtid_consistency
expire_logs_days=7
server_id=101 # 101 for slave?
binlog_format=ROW
replicate_do_db=sbtest
slave_net_timeout=60

system upgrade from
from
MariaDB [(none)]> show databases;
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘wsrep_provider_version’;
MariaDB [(none)]> SHOW GLOBAL VARIABLES LIKE ‘version’;
MariaDB [(none)]> SHOW GLOBAL STATUS LIKE ‘wsrep_provider_version’;

GRANT REPLICATION SLAVE on *.* to ‘slave_user’@’%’ IDENTIFIED BY ‘bharti123′;

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO repl@’10.0.0.%’ IDENTIFIED BY ‘replpass’;

GRANT REPLICATION SLAVE ON *.* TO ‘replicate’@’192.168.1.121’ IDENTIFIED BY PASSWORD ‘*BF6F715A6EBFE63005BEB705C’ |
+-
gtid_mode=ON
enforce_gtid_consistency=true
log_slave_updates=true

/DB/datadir/mysql/ib_logfile0

SET GLOBAL gtid_mode = ON;
SET GLOBAL enforce_gtid_consistency = ON;

enforce_gtid_consistency

gtid_mode=ON
enforce_gtid_consistency=ON
————–
backup configurtion:-
important steps to do every node

1.find /var/lib/mysql -type d -exec chmod 770 “{}” \;
2.yum install libev-4.15
3.yum install libev-4.15-1.el6.rf.x86_64.rpm
4.yum remove perl-DBD-MySQL
5.yum install perl-DBD-MySQL

6.rpm -ivh percona-xtrabackup-2.3.4-1.el6.x86_64.rpm
7.rpm -ivh percona-xtrabackup-debuginfo-2.3.4-1.el6.x86_64.rpm
8.rpm -ivh percona-xtrabackup-test-2.3.4-1.el6.x86_64.rpm
9.backup

socket=/var/lib/mysql/mysql.sock
xtrabackup –backup –datadir=/DB/datadir/mysql/ –target-dir=/DB/db_backup > /DB/db_backup/backup.log
innobackupex –user=root –password=bharti123 /db_backup/db_backup

innobackupex –defaults-file=/etc/my.cnf.d/server.cnf /db_backup/db_backup
innobackupex /db_backup/db_backup

increamenteal backup:-

1.innobackupex –/db_backup/db_backup
2.innobackupex –incremental /db_backup/db_backup –incremental-basedir=/db_backup/db_backup/2016-04-19_18-00-51
3.innobackupex –incremental /db_backup/db_backup –incremental-basedir=/db_backup/db_backup/2016-04-19_18-03-58
————————————————

Preparation
1.innobackupex –apply-log –redo-only /db_backup/db_backup/2016-04-19_13-44-13

2. Incremental Preparation
innobackupex –apply-log –redo-only /db_backup/db_backup/2016-04-19_13-44-13 –incremental-dir=/db_backup/db_backup/2016-04-19_13-45
-39

3. Incremental Preparation
innobackupex –apply-log –redo-only /db_backup/db_backup/2016-04-19_13-44-13 –incremental-dir=/db_backup/db_backup/2016-04-19_13-50
-55

Retoration
innobackupex –copy-back

———————————–
10.restore
innobackupex –apply-log –defaults-file=/tmp/mysql_bak/my.cnf –ibbackup=xtrabackup_innodb55 /tmp/mysql_bak/2012-07-26_13-53-43/

innobackupex –apply-log –defaults-file=/etc/my.cnf.d/server.cnf –ibbackup=xtrabackup_innodb55 /db_backup/db_backup/2016-04-13_15-
39-19/

innobackupex –apply-log –ibbackup=xtrabackup_innodb55 /db_backup/db_backup/2016-04-13_15-39-19/

innobackupex –apply-log –ibbackup=xtrabackup_innodb55 /db_backup/db_backup/2016-04-13_17-14-30/

innobackupex –copy-back –ibbackup=xtrabackup_innodb55 /db_backup/db_backup/2016-04-13_15-39-19/

restore scnerio:-
1.Restore from taken backup:-

I.copy back the files–when dropping the database
a. service mysql stop
b.delete or move the datadir files (example mv /DB/datadir/mysql /DB/datadir/mysql_old_3

c.innobackupex –copy-back –ibbackup=xtrabackup_innodb55 /db_backup/db_backup/2016-04-18_14-03-47/

d.change the owner of /DB/datadir/mysql (chown -R mysql:mysql mysql)

e.service mysql start –wsrep-new-cluster

f.mysql -u root -p

2.restoring the table ,when dropped

a.innobackupex –apply-log –ibbackup=xtrabackup_innodb55 /db_backup/db_backup/2016-04-13_16-37-35/

start in safe mode mysqld_safe –user=root

——–
loading the data into mysql———
mysql> create database ClassicModels;
mysql> use ClassicModels;
mysql> source create_classicmodels.sql;

Load the table contents:

mysql> source load_classicmodels.sql;

Exit from mysql:

mysql> quit;

###Replication

log-bin=/DB/datadir/mysql
log-bin=mysql-bin

[client]
user=root
password=bharti123
host = localhost

—————–
check if you can connect to required TCP ports manually
example :-nmap -sT -p 3306,4567 10.0.2.15

nmap -sT -p 3306,4567 150.236.10.8

One thought on “My SQL Galera Setup”

Leave a Reply

Your email address will not be published. Required fields are marked *

− 2 = 5