Showing posts with label mariadb. Show all posts
Showing posts with label mariadb. Show all posts

Tuesday 29 September 2015

Backup MariaDB using xtrabackup on CentOS7

Percona XtraBackup is an open-source hot backup utility for MariaDB/MySQL-based servers that doesn’t lock your database during the backup. It can back up data from InnoDB, XtraDB, and MyISAM tables on MariaDB/MySQL servers.
This guide looks into how to setup xtrabackup backup system for MariaDB on CentOS7, and how to full backup an MariaDB, and then restore the DB.

First thing, ensure the minimum setup for database config file:
[root@centos7 ~]# vi /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

#
# This group is read by the server
#
[mysqld]
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[root@centos7 ~]# vi /etc/my.cnf.d/server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid
Start MariaDB/Mysql
[root@centos7 ~]# service mysql start
Install percona repository, and install xtrabackup
[root@centos7 ~]# yum install https://www.percona.com/redir/downloads/percona-release/redhat/latest/percona-release-0.1-3.noarch.rpm
[root@centos7 ~]# yum list|grep percona
[root@centos7 ~]# yum install percona-xtrabackup
We will create a backup directory
[root@centos7 ~]# mkdir /root/backup
Installation done. To proceed with Full backup,we need to issue the innobackupex, with the DB admin user and password, and also the backup directory.
[root@centos7 ~]# innobackupex --user=root --password=PASSWORD /root/backup
To get the backup information for a particular backup, we can check the xtrabackup_checkpoints file.
[root@centos7 backup]# cat /root/backup/2015-09-29_02-01-17/xtrabackup_checkpoints
We have done the Full backup. To restore a Full backup, we have to stop the MariaDB/Mysql server first.
[root@centos7 ~]# service mysql stop
Move the current mysql data directory to somewhere.
[root@centos7 ~]# rm -fr /var/lib/mysql_old
[root@centos7 ~]# mv /var/lib/mysql/ /var/lib/mysql_old
Issue the following command to restore.
[root@centos7 ~]# innobackupex --copy-back /root/backup/2015-09-29_02-01-17/
Change back the ownership of the mysql data directory.
[root@centos7 ~]# chown mysql:mysql /var/lib/mysql -R
And lastly, start the MariaDB/Mysql service.
[root@centos7 ~]# service mysql start

Wednesday 23 September 2015

Setup MariaDB Galera Cluster 10.0 On CentOS7

This guide looks into how to setup MariaDB Galera Cluster on CentOS7. MariaDB Galera Cluster is a synchronous multi-master cluster for MariaDB. It is is an easy-to-use, high-availability solution, which provides high system uptime, no data loss and scalability for future growth. It only supports the XtraDB/InnoDB storage engines

Features:
Synchronous replication
Active-active multi-master topology
Read and write to any cluster node
Automatic membership control, failed nodes drop from the cluster
Automatic node joining
True parallel replication, on row level
Direct client connections, native MySQL look & feel

Benefits:
The above features yield several benefits for a DBMS clustering solution, including:
No slave lag
No lost transactions
Both read and write scalability
Smaller client latencies

For this setup, we have 2 nodes.(ie 192.168.1.11,192.168.1.12.) MariaDB will first setup in (.11) and start in bootstrap mode. After (.11) has started, (.12) will setup next. Once the MariaDB service on (.12) started, Galera process will sync database data and transactions from (.11) to (.12), and after in sync, MariaDB in (.12) will turn on. Both servers will be multi masters and in sync constantly.

To begin with the setup, for simplicity, we will switch off selinux and firewall.

[root@centos7-11 ~]#systemctl stop firewalld
[root@centos7-11 ~]#systemctl disable firewalld
[root@centos7-11 ~]#vi /etc/selinux/config
disabled
[root@centos7-11 ~]#reboot
We will add a repository from MariaDB.

[root@centos7-11 ~]# vi /etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Install the package using YUM.
[root@centos7-11 ~]# yum clean all
[root@centos7-11 ~]# yum install MariaDB-Galera-server
Setup MariaDB.(At this point,the service is still call mysql.)
[root@centos7-11 ~]# service mysql start
[root@centos7-11 ~]# mysql_secure_installation
Stop the mysql service to adjust the MariaDB config file.
[root@centos7-11 ~]# service mysql stop
[root@centos7-11 ~]# vi /etc/my.cnf
!includedir /etc/my.cnf.d
Add replication parameters to server.cnf
[root@centos7-11 ~]# vi /etc/my.cnf.d/server.cnf 
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=192.168.1.11
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
We can start the MariaDB on first node.(.11)
[root@centos7-11 ~]# service mysql start
By now, this node has setup to be the first master database. We shell proceed to setup the second master database.

Similarly, we will switch off selinux and firewall.

[root@centos7-12 ~]#systemctl stop firewalld
[root@centos7-12 ~]systemctl disable firewalld
[root@centos7-12 ~]vi /etc/selinux/config
disabled
[root@centos7-12 ~]reboot
We will add a repository from MariaDB.

[root@centos7-12 ~]# vi /etc/yum.repos.d/mariadb.repo

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/10.0/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Install the package using YUM.
[root@centos7-12 ~]# yum clean all
[root@centos7-12 ~]# yum install MariaDB-Galera-server
At this point, we just need to configure the MariaDB config files
[root@centos7-12 ~]# vi /etc/my.cnf
!includedir /etc/my.cnf.d
Add replication parameters to server.cnf
[root@centos7-12 ~]# vi /etc/my.cnf.d/server.cnf
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://192.168.1.11
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=192.168.1.12

#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
We can start the MariaDB on second node.(.12)
[root@centos7-12 ~]# service mysql start
And thats it, 2 node multi masters MariaDB clusters.

Friday 24 October 2014

How To Install MySQL On CENTOS 7

Below instructions are steps to install mysql server on CentOS 7.

The default replacement for mysql server is MariaDB. MariaDB is a community-developed fork of the MySQL relational database management system. For whatever reasons you might like to install the previous MySQL server, this guide walk you through the process of the installation. The steps:

Setup MySQL repository
[root@localhost ~]# sudo rpm -Uvh http://dev.mysql.com/get/mysql-community-release-el7-5.noarch.rpm
Install Server:
[root@localhost ~]# yum install mysql-server
Start Server:
[root@localhost ~]# systemctl start mysqld
Enable Server on boot:
[root@localhost ~]# systemctl enable mysqld
Change root password:
[root@localhost ~]# mysqladmin -u root password

search iomeweekly