Follow us on Social Media:

MySQL Database Administrator

Boost Your Quick Knowledge in MySQL DBA Work, Concepts, Tips and Trick

Monday, 12 November 2018

Restore one database from Single dump of multiple databases

Database backup and recovery is an important task for DBA's and every DBA's wants to work there task without any issue with minimum time.

I'm writing this article because of we had faced a sudden critical issue . Actually due to some mistake , one of our database had dropped by our team member and we have only single mysqldump which executed with --all-database . So I was trying to restore only dropped database from this dump. 

We had tried all possible ways like (by googling and referenced from many tutorials and websites) 

[root@localhost tmp]# mysql -u user -p -D dropdb < /tmp/alldb2.sql ###alldb2.sql my single dump said above

[root@localhost tmp]# mysql -u user -p --one-database dropdb < /tmp/alldb2.sql
etc...but all above ways are wrong . Either it could restore all db's with overwrite or getting error.

After all research and experiment , we had decided that if we have single dump of multiple databases then it could not be possible to restore only specific database from any direct one line command using command-line or any other tools.
But if any one has any other solution / answer so kindly reply us . @comment

To achieve the same please follow below steps.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| getmysqlgroup      |
| mysql              |
| mysqld1_7_224      |
| performance_schema |
| test      |
| test1              |
+--------------------+
7 rows in set (0.00 sec)

Now we are going to take full database dump..

[root@localhost tmp]# mysqldump -uuser -p'pass' --all-databases > /tmp/alldb2.sql

After taking full dump drop test&test1

mysql> drop database test;
mysql> drop database test1;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| getmysqlgroup      |
| mysql              |
| mysqld1_7_224      |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)

Now we want to restore only "test" database from all-database dump(alldb2.sql).

>>Now we extract all the statement only for "test" database.

Follow the below steps one-by-one.

[root@localhost tmp]# grep -n 'Current Database' alldb2.sql
19:-- Current Database: `futuregroup`
132:-- Current Database: `mysql`
986:-- Current Database: `mysqld1_7_224`
1018:-- Current Database: `test`
1049:-- Current Database: `test1`


In above resultset,first column showing statement position , so we are going to pick start position of database "test" and start position of next database. It means that between the position 1018 and 1049 all statement exist only for database "test" and export it into new file (alldbtest.sql).

[root@localhost tmp]# sed -n '1018,1049 p' /tmp/alldb2.sql > /tmp/alldbtest.sql

After that restore this new one with only one database "test".

[root@localhost tmp]# mysql -uroot -p < alldbtest.sql
Enter password:
[root@localhost tmp]# mysql -uroot -p



mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| futuregroup        |
| mysql              |
| mysqld1_7_224      |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)




Thursday, 12 July 2018

Run Multiple Instance of MySQL on CentOS 7 - Unix/Linux


In this article , I will explain you why we exactly need to install multiple instances of MySQL (the well-known mysqld process) running in the same server concurrently in a transparent way, instead of having them executed in separate servers/containers/virtual machines, is not very common. MySQL provides a tool to facilitate the execution of multiple instances called “mysqld_multi”.

For some particular requirement like we have to create a Central Backup Server for 4 different MySQL Servers. To achieve this requirement we can think about Multi-Instance MySQL Server.

I will show you how to Install 4 instances of MySQL on our Backup Server 172.11.2.232

Before start the configuration, we are assuming that you have already installed MYSQL by default with port 3306 and binary files in /usr

Pre Installed MySQL Port – “3306” (default)
Pre Installed my.cnf – “/etc/my.cnf”
Pre Installed Base Directory – “/usr”
Pre Installed Data Directory – “/var/lib/mysql”
Root user for MySQL – “dbuser/root”

Architecture Diagram for 4-Instances:


Precautions:-

First of all stop the pre installed mysqld service
[root@NMNMYSQLTEST01 alok]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service
Modify the default my.cnf as Group My.cnf

New “my.cnf” will looks like as below...
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html
# or Contact us on Here!

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
user       = dbuser
password   = XXXXXXX

[mysqld]
user   = mysql
datadir  = /var/lib/mysql
socket  = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
port  = 3306
skip_name_resolve
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links = 0

# Recommended in standard MySQL setup
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 

[mysqld1]
user  = mysql
pid-file = /var/run/mysqld/mysqld1.pid
socket  = /var/lib/mysql1/mysqld1.sock
port  = 3307
datadir  = /var/lib/mysql1
skip_name_resolve


[mysqld2]
user            = mysql
pid-file        = /var/run/mysqld/mysqld2.pid
socket          = /var/lib/mysql2/mysqld2.sock
port            = 3308
datadir         = /var/lib/mysql2
skip_name_resolve

[mysqld3]
user            = mysql
pid-file        = /var/run/mysqld/mysqld3.pid
socket          = /var/lib/mysql3/mysqld3.sock
port            = 3309
datadir         = /var/lib/mysql3
skip_name_resolve

[mysqld4]
user            = mysql
pid-file        = /var/run/mysqld/mysqld4.pid
socket          = /var/lib/mysql4/mysqld4.sock
port            = 3310
datadir         = /var/lib/mysql4
skip_name_resolve

#[mysqld_safe]
#log-error=/var/log/mysqld.log
#pid-file=/var/run/mysqld/mysqld.pid
Installation Steps for multiple instances:

  • 1 > Create Data directory for all instances.

[root@NMNMYSQLTEST01 alok]# cd /var/lib/
[root@NMNMYSQLTEST01 lib]# mkdir mysql1
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql1
 
[root@NMNMYSQLTEST01 lib]# mkdir mysql2
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql2
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql2
 
[root@NMNMYSQLTEST01 lib]# mkdir mysql3
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql3
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql3
 
[root@NMNMYSQLTEST01 lib]# mkdir mysql3
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql3
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql3
 
[root@NMNMYSQLTEST01 lib]# mkdir mysql4
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql4
[root@NMNMYSQLTEST01 lib]# chmod -R 777 mysql4

  • 2 > Modify new my.cnf as shown above.
  • 3 > Create data directory by copy preinstalled default datadir.
[root@NMNMYSQLTEST01 alok]# cp /var/lib/mysql/* /var/lib/mysql1/
[root@NMNMYSQLTEST01 alok]# cp /var/lib/mysql/* /var/lib/mysql2/
[root@NMNMYSQLTEST01 alok]# cp /var/lib/mysql/* /var/lib/mysql3/
[root@NMNMYSQLTEST01 alok]# cp /var/lib/mysql/* /var/lib/mysql4/


OR
Create data directory using “mysql_install_db”
[root@NMNMYSQLTEST01 alok]# mysql_install_db --user=mysql --datadir=/var/lib/mysql1
[root@NMNMYSQLTEST01 alok]# mysql_install_db --user=mysql --datadir=/var/lib/mysql2
[root@NMNMYSQLTEST01 alok]# mysql_install_db --user=mysql --datadir=/var/lib/mysql3
[root@NMNMYSQLTEST01 alok]# mysql_install_db --user=mysql --datadir=/var/lib/mysql4

This will create default data directory with empty root user. If you create datadir using this command then you will have to further troubleshoot in login (need to create root/dbuser later to use the instance) 
Suggest: Best practice to copy any existing data directory.

  • 4 > Change the Ownership 
[root@NMNMYSQLTEST01 alok]# chown –R mysql:mysql /var/lib/mysql1
[root@NMNMYSQLTEST01 alok]# chown –R mysql:mysql /var/lib/mysql2
[root@NMNMYSQLTEST01 alok]# chown –R mysql:mysql /var/lib/mysql3
[root@NMNMYSQLTEST01 alok]# chown –R mysql:mysql /var/lib/mysql4

  • 5 > Start all/any particular Instances
[root@NMNMYSQLTEST01 alok]# mysqld_multi start
[root@NMNMYSQLTEST01 alok]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is running
MySQL server from group: mysqld4 is running
OR
[root@NMNMYSQLTEST01 alok]# mysqld_multi start 1,2,4
[root@NMNMYSQLTEST01 alok]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld1 is running
MySQL server from group: mysqld2 is running
MySQL server from group: mysqld3 is not running
MySQL server from group: mysqld4 is running

  • 6 > Connect any instance on command line as below.
mysql -u dbuser -p --port=3307 --socket=/var/lib/mysql1/mysqld1.sock
mysql -u dbuser -p --port=3308 --socket=/var/lib/mysql2/mysqld2.sock
mysql -u dbuser -p --port=3309 --socket=/var/lib/mysql3/mysqld3.sock
mysql -u dbuser -p --port=3310 --socket=/var/lib/mysql4/mysqld4.sock

  • 7 > To stop any Instance you have use mysqladmin. Need to shutdown privileges in mysqladmin user.

Mysql> GRANT SHUTDOWN ON *.* TO 'dbuser'@'localhost' IDENTIFIED BY 'XXXXXXXX';

  • 8 > Stop any instance using below command.

mysqladmin -u dbuser -p -P=3306 -S /var/lib/mysql1/mysqld1.sock shutdown
mysqladmin -u dbuser -p -P=3306 -S /var/lib/mysql2/mysqld2.sock shutdown
mysqladmin -u dbuser -p -P=3306 -S /var/lib/mysql3/mysqld3.sock shutdown
mysqladmin -u dbuser -p -P=3306 -S /var/lib/mysql4/mysqld4.sock shutdown


Now 4 multiple instances has been installed successfully.

[root@NMNMYSQLTEST01 alok]# netstat -ntpl | grep mysqld
tcp6       0      0 :::3306           :::*              LISTEN      20926/mysqld
tcp6       0      0 :::3307           :::*              LISTEN      21315/mysqld
tcp6       0      0 :::3308           :::*              LISTEN      22348/mysqld
tcp6       0      0 :::3309           :::*              LISTEN      19325/mysqld
tcp6       0      0 :::3310           :::*              LISTEN      19326/mysqld

Pre installed default port 3306 which can be stopped by “Service mysqld stop”
Manual Installed Instances are 3307, 3308, 3309, 3310
Base directory for all instances are same as 3306
basedir=/usr
bindir=/usr/bin



Saturday, 16 June 2018

Shell Script for MySQL daily backup


Task automation is most important part of all DBA's work for monitoring and scheduling task like Automatic backup, DB health checkup,Daily Checklist , populate & Email alerts , Replication Status , Server Utilization and much more .

In this article, I'm writing a Shell script for automatic MySQL Daily Backup which can be schedule by cron job for automation of their result.
This Script is written in very simple way and logic which you can easily understand.

Features....

  • Automation of Daily Full Backup using Percona XtraBackup
  • No production loss during the backup 
  • Creating a mysql-binlog check-points in every backup so that you can easily use the same for point-in-time recovery.
  • Create Backup Directory if not exist.
  • No repetition of same day backup.
  • Creating a Log File for whole process.
  • Sending Email Alerts for any failure/success backup  
  • Easy-to-Understand 

Limitation...

  • You must have to installed Percona XtraBackup
  • Must have to configure Mail smtp function for sending email alerts
  • Occupy the disk storage if data size is very large

#!/bin/bash -x
#By- Alok Kumar Singh @ www.getmysql.info
#Scheduling Daily Full Backup from Production/Backup Server using Xtrabackup without any table lock or any hamper on production during backup.      #Version- 1.2v
#06-Jun-2018 04:48PM

HOSTIP="191.168.1.100"
emails="query@getmysql.info alok@getmysql.info" ## add multiple email with space
BACKUP_DIR=/home/alok/DailyFullXtraBackup ##this directory must be created with 777 privileges
DATA_DIR=/var/lib/mysql
BACKUP_LOG=/home/alok/backup.log
USER_ARGS=" --user=user --password=password"
DATETIME=$(date +%y%m%d:"%T.%3N")
BAKFOLDER=$BACKUP_DIR/Backup_$(date +%Y%m%d)
TMPFILE="/tmp/XtraBackup-$(date +%y%m%d"%T").$$.tmp"
MailsubjectFAIL="Error-MySQL:DailyFullXtraBackup job Failed on $HOSTIP"
MailsubjectOK="OK-MySQL:DailyFullXtraBackup job Successful on $HOSTIP"

         echo "$DATETIME :: Full backup requesting" >> $BACKUP_LOG
         echo "$DATETIME :: Checking backup dir" >> $BACKUP_LOG
         date
         if [ ! -d $BACKUP_DIR ]
         then
         echo "$DATETIME :: ERROR: the folder $BACKUP_DIR does not exists" >> $BACKUP_LOG
  ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been failed."$'\n'""$'\n'"Reason:  ERROR: the folder $BACKUP_DIR does not exists"
  for address in $emails; do
  echo -e $ERRMSG | mail -s "$MailsubjectFAIL" $address
         done
         exit 1
         fi
  if [ -f $BAKFOLDER.tgz ]
         then
         echo "$DATETIME :: ERROR: one backup ($BAKFOLDER) already exist for current day. Rename/delete it before execute new one." >> $BACKUP_LOG
         ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been failed."$'\n'""$'\n'"Reason: ERROR: one backup ($BAKFOLDER) already exist for current day. Rename/delete it before execute new one."
  for address in $emails; do
  echo -e $ERRMSG | mail -s "$MailsubjectFAIL" $address
         done
  exit 1
  else
  mkdir $BAKFOLDER
         chmod -R 777 $BAKFOLDER
  echo "$DATETIME :: Backup folder created  $BAKFOLDER ..." >> $BACKUP_LOG
         fi
   
        date
        echo "$DATETIME :: Found backup directory $BAKFOLDER" >> $BACKUP_LOG
        echo "$DATETIME :: Starting backup in progress..." >> $BACKUP_LOG
        sudo xtrabackup $USER_ARGS --backup --no-timestamp --target-dir=$BAKFOLDER > $TMPFILE 2>&1
        if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
        echo "$DATETIME :: ERROR:Backup Failed in execution time." >> $BACKUP_LOG
    
        ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been failed."$'\n'""$'\n'"Reason: ERROR:Backup Failed in execution time."
 for address in $emails; do
 echo -e $ERRMSG | mail -s "$MailsubjectFAIL" $address
 done
 cat $TMPFILE
 rm -f $TMPFILE
 exit 1
        fi
        echo "$DATETIME :: Backup done as $BAKFOLDER" >> $BACKUP_LOG
 cat $TMPFILE
        rm -f $TMPFILE
##preparing backup
 echo "$DATETIME :: Backup preparation started..." >> $BACKUP_LOG
 sudo xtrabackup $USER_ARGS --prepare --no-timestamp --target-dir=$BAKFOLDER > $TMPFILE 2>&1
 if [ -z "`tail -1 $TMPFILE | grep 'completed OK!'`" ] ; then
        echo "$DATETIME :: ERROR:Preparation Failed" >> $BACKUP_LOG
        ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been failed."$'\n'""$'\n'"Reason: ERROR:Backup has been done but Backup Preparation Failed."
 for address in $emails; do
 echo -e $ERRMSG | mail -s "$MailsubjectFAIL" $address
        done
 cat $TMPFILE
        rm -f $TMPFILE
        exit 1
        fi
    
        echo "$DATETIME :: Preparation done as $BAKFOLDER" >> $BACKUP_LOG

## Compress Backup 
        cd $BACKUP_DIR && sudo tar --create --gzip --file=Backup_$(date +%Y%m%d).tgz Backup_$(date +%Y%m%d) --remove-files 
## to Uncompress use  tar -xzvf FILENAME.tgz
        echo "$DATETIME :: Compress Successful as $BAKFOLDER.tgz" >> $BACKUP_LOG
   
        ERRMSG="DailyFullXtraBackup Job on server $HOSTIP has been Successful ."$'\n'""$'\n'"Backup Path: $BAKFOLDER.tgz"
 for address in $emails; do
 echo -e $ERRMSG | mail -s "$MailsubjectOK" $address
        done
        cat $TMPFILE
        rm -f $TMPFILE
    

##########################################################
### Copyright@ Alok Kumar Singh with www.getmysql.info ###
##########################################################
For any Query on this script you can contact us or Submit your Query Here!

Please write you valuable comments and feedback in the comment box. Your valuable feedback is highly appreciated .

Please Follow us to update and notify for such type of articles and scripts. 

Saturday, 9 June 2018

MySQL DBA task Automation




If you are using MySQL Community Edition and you want to Automate all your DBA task like DB health checkup, Daily Checklist, populate & Email alerts , Replication Status , Server Utilization and much more ,then you can easily move on Bash Shells Script which is one of powerful consideration to achieve all you task


Saturday, 2 June 2018

MySQL Replication Monitoring Alert Script

I have written and used this script to get MySQL replication status alert on all scenario like Replication Down/ Second_behind_master going up / and again Replication status Up .

You can modify the notification logic as per your convenience.  

Just create /usr/bin/getmysqlrepl.sh blank script file and copy-paste below script. 

#!/bin/bash
# Checks MySQL Replication status.  Sends user(s) a notification of status
# BY- Alok Kumar Singh @ www.getmysql.info
# 31-may-2018 11:26 AM

MasterHost="192.168.1.100"
SlaveHost="192.168.1.101"
emails="alok@getmysql.info" #multiple emails space separated
HOST="sudo mysql --user=user --password=password" ##for your password security we suggest you kindly create seperate user for Alert with all Show/Select Privileges.


SQLresponse=$($HOST -e "show slave status \G" |grep -i "Slave_SQL_Running"|gawk '{print $2}')
IOresponse=$($HOST -e "show slave status \G" |grep -i "Slave_IO_Running"|gawk '{print $2}')
SSTATUS=$($HOST -e "SHOW SLAVE STATUS\G" | egrep "Master_Host|Master_User|Master_Log_File|Slave_IO_Running|Slave_SQL_Running|Last_Errno|Last_Error|Seconds_Behind_Master")
SLAVEBEHIND=$($HOST -e "show slave status \G" |grep -i "Seconds_Behind_Master"|gawk '{print $2}')



if [ "$SQLresponse" == "No" ]
 then
       ERRMSG="Replication on the slave MySQL server($SlaveHost) has stopped working"$'\n'"$SSTATUS"
    DownSubject="Replication Status Down on $SlaveHost"
      status=1
elif [ "$IOresponse" == "No" ]
 then
      ERRMSG="Replication on the slave MySQL server($SlaveHost) has stopped working"$'\n'"$SSTATUS"
      DownSubject="Replication Status Down on $SlaveHost"
   status=1
elif [ "$SLAVEBEHIND" == "NULL" ]
 then
  ERRMSG="The Slave is reporting 'NULL' (Seconds_Behind_Master)"$'\n'"$SSTATUS"
  DownSubject="Replication Status Down on $SlaveHost"
  status=1
elif [ "$SLAVEBEHIND" != 0 ]
 then
    ERRMSG="The Slave is at least $SLAVEBEHIND seconds behind the master (Seconds_Behind_Master)"$'\n'"$SSTATUS" 
 DownSubject="Replication Status Lag Behind $SLAVEBEHIND on $SlaveHost"
 status=1
else
 GoodSubject="Replication Status UP on $SlaveHost"
    GoodMessage="MySQL replication on $SlaveHost is working fine"$'\n'"$SSTATUS"
 status=0
fi

# Sending mail Alert....

if [ $status == 1 ]
 then
      for address in $emails; do
                echo -e $ERRMSG | mail -s "$DownSubject" $address 
                echo "$(date): On $SlaveHost Replication down, sent email to $address"$'\n'"$ERRMSG" >> /home/alok/maillog.log ##writing log file for mail alert
      done
elif [ $status == 0 ]
 then
      for address in $emails; do
             echo -e $GoodMessage | mail -s "$GoodSubject" $address
                echo "$(date): On $SlaveHost Replication is up, Mail sent to $address"$'\n'"$GoodMessage" >> /home/alok/maillog.log
      done
fi

#########################################################
### Copyright@ Alok Kumar Singh with www.getmysql.info###
#########################################################
If you will get any issue , kindly contact us _ Click Here or write us comment.

Sunday, 20 May 2018

How to configure MySQL Replication using Percona XtraBackup without any downtime?

Configure Replication in Consistent Mode
-No Downtime
With
Percona XtraBackup


Before starting the replication with Percona XtraBackup, You will need to check some points and key notes as mentioned below.

Summary: All the things you will need
ü Installing Percona XtraBackup from Percona yum repository (Skip if already installed)
ü Make a backup on Master and prepare it using XtraBackup
ü Move existing Data on Slave to other Folder
ü Copy backed up data to Slave.
ü Configure the Master MySQL server.
ü Configure the Slave MySQL server.
ü Start the replication

All the things you will need:

Setting up a slave for replication with Percona XtraBackup is really a very straightforward procedure. In order to keep it simple, here is a list of the things you need to follow the steps without hassles:
The Master db01 system with a MySQL-based server installed, configured and running. This system will be called The Master, as it is where your data is stored and the one to be replicated. We will assume the following about this system:
  • The MySQL server is able to communicate with others by the standard TCP/IP port;
  • The SSH server is installed and configured;
  • You have a user account in the system with the appropriate permissions;
  • You have a MySQL’s user account with appropriate privileges.
  • Server has binlogs enabled and server-id set up to 1.

The Slave db02 another system, with a MySQL-based server installed on it. We will refer to this machine as The Slave and we will assume the same things we did about The Master, except that the server-id on the Slave is 2.
Percona XtraBackup The backup tool we will use. It should be installed in both computers for convenience.

Note: It is not recommended to mix MySQL variants (Percona Server, MySQL, MariaDB) in your replication setup. This may produce incorrect xtrabackup_slave_info file when adding a new slave.

Install Percona XtraBackup

Testing the repository
Make sure packages are now available from the repository, by executing the following command:

yum list | grep percona

You should see output similar to the following:

percona-xtrabackup-20.x86_64               2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-debuginfo.x86_64     2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-20-test.x86_64          2.0.8-587.rhel5             percona-release-x86_64
percona-xtrabackup-21.x86_64               2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-21-debuginfo.x86_64     2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-22.x86_64               2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-22-debuginfo.x86_64     2.2.13-1.el5                percona-release-x86_64
percona-xtrabackup-debuginfo.x86_64        2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test.x86_64             2.3.5-1.el5                 percona-release-x86_64
percona-xtrabackup-test-21.x86_64          2.1.9-746.rhel5             percona-release-x86_64
percona-xtrabackup-test-22.x86_64          2.2.13-1.el5                percona-release-x86_64

Note:

db01 - Master MySQL Server
db02 - Slave MySQL Server

On db01 only, install Percona XtraBackup using Percona’s repos:

[alok@db01 ~]# yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
[alok@db01 ~]# yum install percona-xtrabackup-24
On db01 only, confirm it installed XtraBackup version 2.3 or newer:
[alok@db01 ~]# xtrabackup –version
Result like...
xtrabackup version 2.4.11 based on MySQL server 5.7.19 Linux (x86_64) (revision id: b4e0db5)
Take XtraBackup and setup MySQL Replication using Percona XtraBackup

On db02 only, rename the existing MySQL datadir, and create a fresh folder:
[alok@db02 ~]# service mysqld stop
[alok@db02 ~]# mv /var/lib/mysql /var/lib/mysql.old
[alok@db02 ~]# mkdir /var/lib/mysql
On db01 only, create the backup, make the snapshot consistent by applying the log, and copy it over to db02 using either scp or rsync:

[alok@db01 ~]# sudo mkdir /home/alok/perconabackup
[alok@db01 ~]# sudo innobackupex --user=root --password=mysql /home/alok/xtra_Bkup_18may/ 
Will get result like…
180518 10:56:37 [00]        ...done
180518 10:56:37 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '2008941'
xtrabackup: Stopping log copying thread.
.180518 10:56:37 >> log scanned up to (2008941)

180518 10:56:37 Executing UNLOCK TABLES
180518 10:56:37 All tables unlocked
180518 10:56:37 Backup created in directory '/home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/'
MySQL binlog position: filename 'mysql-bin.000001', position '118445'
180518 10:56:37 [00] Writing /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/backup-my.cnf
180518 10:56:37 [00]        ...done
180518 10:56:37 [00] Writing /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/xtrabackup_info
180518 10:56:37 [00]        ...done
xtrabackup: Transaction log of lsn (2008941) to (2008941) was copied.
180518 10:56:37 completed OK!

[alok@db01 ~]# sudo innobackupex --user=root --password=mysql --apply-log /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/ 

This will get result like..

InnoDB: Starting crash recovery.
InnoDB: xtrabackup: Last MySQL binlog file position 118445, file name mysql-bin.000001
InnoDB: Removed temporary tablespace data file: "ibtmp1"
InnoDB: Creating shared tablespace for temporary tables
InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
InnoDB: File './ibtmp1' size is now 12 MB.
InnoDB: 96 redo rollback segment(s) found. 1 redo rollback segment(s) are active.
InnoDB: 32 non-redo rollback segment(s) are active.
InnoDB: 5.7.19 started; log sequence number 2010645
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 2011550
180518 11:00:09 completed OK!

Now Copy Backup folder to slave server…
[alok@db01 ~]# sudo scp -r /home/alok/perconabackup/2018-05-17_14-58-31/ alok@192.168.0.201:/var/lib/mysql
OR
[alok@db01 ~]# sudo rsync -r /home/alok/xtra_Bkup_18may/2018-05-18_10-56-35/ alok@192.168.0.201:/var/lib/mysql/
On db02, fix the ownership of the datadir, startup MySQL, and grab the binlog name and position:
[alok@db02 ~]# sudo chown -R mysql:mysql /var/lib/mysql
[alok@db02 ~]# sudo service mysqld start
NOTE: Troubleshooting
Once you will start mysqld service this may happen to fail in start service. Kindly follow the below troubleshooting one-by-one
1. Check /var/lib/mysql folder having chown mysql:mysql if not kindly set to mysql as owner.
2. Check /var/lib/mysql/AAAA.err file having some error like ibdata access permission denied . So provide chmod –R 777 /var/lib/mysql . If still issue follows the next…
3. You will need to disable Selinux at once.
[alok@localhost ~]$ sudo vi /etc/selinux/config
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX= disabled
# SELINUXTYPE= can take one of three two values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected.
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted
4. After that reboot server and check mysql service started. Once checked working fine then again you can revert SELINUX setting as back. 

When MySQL service start successfully then…

[alok@db02 ~]# cat /var/lib/mysql/xtrabackup_binlog_info
db01-bin-log.000001     120
Now on db02, startup slave replication:
[alok@db02 ~]# mysql –u root –p
mysql> CHANGE MASTER TO MASTER_HOST='10.x.x.x', MASTER_USER='repl', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='db01-bin-log.000001', MASTER_LOG_POS=120;
mysql> start slave;
mysql> show slave status\G
...
        Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
...










Please Like and Subscribe us ....

Please visit the website on daily basis , Your New articles will publish at any moment. Please Subscribe us for getting Latest Updates...