My SQL Galera Setup

Posted on Posted in Uncategorized

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

21 thoughts on “My SQL Galera Setup

  1. [url=https://bratuart.kzworld.info/brawl-stars-logic-1-brawl-stars-animation/a4qAkK99sGaLh6U][img]https://i.ytimg.com/vi/3YKZKDK0WWs/hqdefault.jpg[/img][/url]

    BRAWL STARS LOGIC #1 – BRAWL STARS [url=https://bratuart.kzworld.info/brawl-stars-logic-1-brawl-stars-animation/a4qAkK99sGaLh6U]ANIMATION[/url]

  2. I am absolutely blown away by the quality of the content on this blog. It’s clear that the author puts an incredible amount of effort into researching and writing each post, and the results are truly impressive. I particularly appreciate the author’s commitment to providing balanced and unbiased perspectives on a variety of topics. It’s refreshing to read content that doesn’t try to push a specific agenda or viewpoint. The author’s writing is both informative and engaging, and I always come away with new insights and knowledge. Overall, this blog is an exceptional resource for anyone interested in expanding their knowledge on a variety of subjects.

Leave a Reply

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

6 × = 24