My SQL Galera Setup

Posted on Animesh KumarPosted 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

311 thoughts on “My SQL Galera Setup”

  12. [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]

    Reply

  19. 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.

    Reply

  59. I am sorting out relevant information about gate io recently, and I saw your article, and your creative ideas are of great help to me. However, I have doubts about some creative issues, can you answer them for me? I will continue to pay attention to your reply. Thanks.

    Reply

  64. 7 Key Things to Know About Restoring Used and Antique Furniture (and Signs It Might Not Be Worth It) We’re growing because community banking has come full circle to be the full service answer to 21st Century banking. Over three decades and a growing net worth that is now in the billions, we have never compromised our vision of authentic local banking, even as we expanded into the South’s iconic cities, from Nashville to Memphis. Harford’s population in 1982 was 149,551. The latest census, from July 2021, put the population at 262,977. So that’s at least 113,000 more people living — and flushing toilets — on what had been farmland and forest, near tributaries of the Chesapeake Bay and the bay itself. Add to that all the commercial development that has taken place (shopping centers, office parks, manufacturing plants, self-storage facilities, gas stations and warehouses), and it’s no wonder that Harford countians have started to rebel.
    https://www.chordie.com/forum/profile.php?id=1622532
    For example, donated furniture that is in good used condition or better should not be evaluated at some fixed rate such as 15% of the cost of new replacement furniture. When the furniture is contributed, it may be out of style or in poor condition, therefore having little or no market value. On the other hand, it may be a desirable antique, the value of which could not be determined by using any formula. If you have access to the Multiple Listing Service, you can easily pull up a list of comps. You’re looking for properties that have sold in the last three to six months – real estate markets move so quickly that older sale prices will be out of date. Pay attention to the address of your comparable properties. Location is a key element in real estate appraisal, due to factors like transportation and school quality, so you’re looking for properties in the same neighborhood and ideally within a couple of streets of the subject property.

    Reply

  72. no prescription required pharmacy [url=https://cheaponlinepharmacy.online/]cheapest pharmacy for prescriptions without insurance[/url] best no prescription pharmacy

    Reply

  89. At the beginning, I was still puzzled. Since I read your article, I have been very impressed. It has provided a lot of innovative ideas for my thesis related to gate.io. Thank u. But I still have some doubts, can you help me? Thanks.

    Reply

  95. amoxicillin 875 125 mg [url=https://amoxicillin.science/]buy amoxicillin online uk[/url] where to get amoxicillin online without prescription

    Reply

  98. At the beginning, I was still puzzled. Since I read your article, I have been very impressed. It has provided a lot of innovative ideas for my thesis related to gate.io. Thank u. But I still have some doubts, can you help me? Thanks.

    Reply

  110. At the beginning, I was still puzzled. Since I read your article, I have been very impressed. It has provided a lot of innovative ideas for my thesis related to gate.io. Thank u. But I still have some doubts, can you help me? Thanks.

    Reply

  115. It’s stressful enough taking care of small children. I choose to [url=http://metforminv.com/]buy metformin online no prescription[/url] to eliminate extra stress.

    Reply

  143. Although there’s plenty to do with the kids, Las Vegas is essentially an adult playground and many hotels won’t even allow you to book a room if you’re not at least 21. The minimum drinking age is as rigorously enforced as it is for gambling, if not more so. It’s common practice for people in their 30s to get carded. Don’t expect to get into bars or clubs without valid ID. RULES – Students Playing Strip Poker, Things Get Crazy! 12 min We know you’re no surgeon, but you’ll do a little operation! We’ll mix this kids’ game with some stripping! If you’re looking for the game, check it out on Amazon. Follow the rules as if you’re playing the game in normal mode. Since you’ve gotta remove something from the patient, without touching the sides, if you succeed, you’ve got the chance to make someone remove a piece of clothing, or put something yourself on. Otherwise, you may also fail. If you do touch the sides, you have to remove something. Whatever happens, please, do not kill the patient! 
    http://sunginew.com/bbs/board.php?bo_table=free&wr_id=22638
    Old-school land casino machines deserve their category due to physical design and complex cabinet manufacturing. We’ll focus on free slots launched without requiring an internet connection to play for fun. These slots can be an offline casino or videogame part, excluding Flash. All offline slots must be downloaded to your PC or mobile device first loaded in a browser or installed as an application. Reuters could not reach the women for comment, you can upload it. I’ve also waited more than 30 minutes in a line that snaked its way through a casino, hot roll slot machines international variants of the game show have been aired in around 160 countries worldwide. So, each stands in a class of its own because of slight rule variations. The simulation on the left is typical of games resulting in ruin and gives some insight into the mechanisms that cause ruin, the machines earn up to 80 percent of the club’s revenue. However, including unlike ten-value cards like J-Q. There are plenty of versions of these games as well, K-J.

    Reply

  214. أكبر شركة لانتاج أنابيب البولي ايثيلين و يو بي سي ومستلزماتها للمنتجات الصناعية في عيراق

    Reply

  237. بعد قراءة مقالك ، ذكرني ببعض الأشياء التي درستها من قبل حول البوابة الإلكترونية. المحتوى مشابه لمحتوياتك ، لكن تفكيرك خاص جدًا ، وهو ما أعطاني فكرة مختلفة. شكرًا لك..

    Reply

  269. Very nice post. I just stumbled upon your blog and wanted to say that I’ve really enjoyed browsing your blog posts. In any case I’ll be subscribing to your feed and I hope you write again soon!

    Reply

Leave a Reply

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

30 ÷ = 10