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’;
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”
lanoxin price buy generic lanoxin 250mg buy molnupiravir sale
order diamox 250 mg generic purchase diamox generic azathioprine 50mg generic
order digoxin 250 mg online cheap order lanoxin 250mg generic molnunat drug
purchase digoxin generic purchase telmisartan pills molnupiravir 200 mg tablet
order amoxil 500mg generic cheap amoxil for sale ivermectin dosage
buy fosamax 35mg without prescription alendronate for sale online order motrin generic
indocin 50mg uk purchase indomethacin generic buy cenforce 100mg for sale
buy tadacip 20mg sale trimox 500mg tablet order amoxicillin generic
requip 2mg uk order calcitriol pills labetalol 100 mg us
esomeprazole generic clarithromycin cheap cheap lasix 40mg
order tricor 200mg viagra for men sildenafil 50mg oral
[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]
tadalafil 5mg Buy cialis once daily viagra 100mg over the counter
cialis on line cialis daily blue pill for ed
order modafinil 100mg generic stromectol 12mg price cost promethazine
provigil pills order provigil sale cost promethazine 25mg
order accutane 10mg online order ampicillin 250mg generic acillin uk
order deltasone 10mg generic buy amoxicillin 500mg sale cheap amoxil sale
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.
order albuterol 2mg for sale ventolin 4mg uk augmentin 1000mg tablet
albuterol 4mg cost order augmentin 625mg online cheap augmentin 375mg cheap