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