Mysql: mysql Enterprise backup (MEB) script

Here is the script to backup Mysql database using MEB. 

copy this below code in to script and edit to the correct directory paths and run it.

#########################################################################

#

COMMAND=$0

PGM=${COMMAND##*/}

DATE=$(date "+%Y%m%d%H")

hostname=`hostname -s`

MAILTO=XXXxx@gmail.com

PWD=/opt/scripts  # put script dir path if you want to run from "crontab"

#PWD=`pwd`      # enable this and disable above one if you want to run in nohup mode.

repdir=$PWD

LOG=$repdir/dblogs

##*********** Below list of changes are req: 

conf_file=/etc/my.cnf

BACKUP_USER=root

BACKUP_PASS=`cat /opt/.orapasswd/.backupusr`

bkp_type=$1

ret_time=$2

Logfile=mysql-MEB_"$bkp_type"_"$hostname"_$DATE.log

log_ret=15

echo " started $DATE" >>$LOG/$Logfile 

#### Changes are done:

##***************** run Active file:

if [ -f $LOG/active_file ]

 then

   echo "previous mysql backup is still running. confirm before running again" | mailx -s "Mysql-Backup: $hostname - Failed for $bkp_type backup" $MAILTO

   exit 0

fi

## Validate Log directory:

if [ ! -d $LOG ]; then

mkdir  $LOG

else 

/usr/bin/find $LOG -type f -mtime +$log_ret -exec rm -f {} \;

fi

#******* Checking the input parameter: 

if [ $# = 0 ]

then

echo "Please enter the first parameter for backup type (FULL) and second parameter as a number in days for backup retention" |tee -a $LOG

exit 1

fi

##************ setting the backup-dir path for the Backup location:

bkup_dir=/backup_folder/$hostname/meb/$bkp_type

if [ ! -d $bkup_dir ]; then

mkdir -p $bkup_dir 

fi

echo " Mysql-backup: $bkp_type files are located under $bkup_dir" >>/$LOG/$Logfile

echo ""

####### End of backup-dir path setup

touch $LOG/active_file

##************ Running Backup from above inputs:

echo "Running $bkp_type Backup from above inputs"

if [ $bkp_type = 'FULL' ] ; then

   /opt/mysql/meb-4.1/bin/mysqlbackup --defaults-file=$conf_file --port=3306 --protocol=tcp --user=$BACKUP_USER --password=$BACKUP_PASS --with-timestamp --backup-dir=$bkup_dir backup-and-apply-log >>$LOG/$Logfile 2>&1

else

echo "Incorrect input parameter - please enter a correct backup type" |tee -a $LOG/$Logfile

fi

####

 

##********* check Any errors in the logfile:

error_chk=$(cat $LOG/$Logfile | awk 'BEGIN{buf=""} /[0-9]:[0-9][0-9]:[0-9]/{buf=$0} /WARNING|warnings|ERROR/{print buf,$0}' | wc -l)

if [[ $error_chk -eq 0 ]]

  then

     echo "Mysql Backup for backup-type $bkp_type completed successfully - no errors $error_chk " >>$LOG/$Logfile

   else

    echo "Mysql Backup for backup-type $bkp_type was unsuccessful - errors $error_chk. check and run again" >>$LOG/$Logfile

   echo "Mysql Backup for backup-type $bkp_type completed but has errors - $error_chk . please check" | mailx -s "Mysql-Backup - $bkp_type : Completed with errors" $MAILTO

fi

####

##******** remove run active file:

if [ -f $LOG/active_file ]

 then

rm $LOG/active_file 

echo " removed run-active file" |tee -a $LOG/$Logfile

fi

echo "Mysql-backup: All steps are done" |tee -a $LOG/$Logfile

exit 0;

###################################################

hope this helps........................................

Mysql release version EOL support details..

To check release version EOL support:

http://www.oracle.com/us/support/library/lifetime-support-technology-069183.pdf


To check release versions: similar

https://dev.mysql.com/doc/relnotes/mysql/8.0/en/

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/


Hope this helps....

Mysql upgrade from 5.7.XX to 5.7.27

Below you can find the steps to upgrade Mysql 5.7.xx to 5.7.27.

Here we are using In-Place Upgrade. which means we are upgrading the mysql version on the existing server only which is completely different from logical upgrade.

1)

First download right version of Mysql software. do this.

Go to Patches and downloades -> click product or family -> enter Mysql in product and check release you want(5.7.27) -> platform that you need (here it is linux) -> select description  and enter 5.7.27 -> search.

download the zip version(here it is linux)

p30076436_570_Linux-x86-64.zip

2)  Unzip p30076436_570_Linux-x86-64.zip

3) check what rpm's are installed on your server for mysql by doing this
rpm -qa  mysql* or use rpm -V mysql*

4) when you are about to upgrade. Take VM snapshot or take a backup of mysql data.

5) login into mysql and check below variables setting.

show variables like 'innodb_fast_shutdown';

6) run below command to set below variable.
mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"

7)
mysqladmin -u root -p  shutdown   or systemctl stop mysqld  (as root)
ps -ef| grep mysqld

8) use below command to upgrade all the rpm's(with dependencies). check the list you have to upgrade and edit the below command.  run as "root "

rpm -Uhv mysql-commercial-{server,libs,libs-compat,client,common,embedded,embedded-compat}-5.7.27-1.1.el7.x86_64.rpm

9) once rpms are installed with no errors. start mysql 

systemctl start mysqld    --- as root (you can use sudo commands if you have to)

monitor mysqld.log... if you see this below messages.. ignore for now and proceed to next step.

(  output from mysqld.log

 [Warning] InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

[Warning] InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

)

10) run mysql upgrade command and monitor all tables. they should resturn "OK" and "Upgrade process completed successfully"  in the end.

mysql_upgrade -u root -p

12) do this step only if you seeing the above similar warnings only after mysql_upgrade.

 drop table mysql.innodb_index_stats;

 drop table mysql.innodb_table_stats;

and run the mysql_upgrade -u root -p

12) Post checks after upgrade.

select version();

show databases;

select count(*) from table_name;

13). bounce mysql.

Hope this helps....