what I have to remember…

Standard

backup mysql databases

steps to perform mysql databases backup:
  • put the server  in a read-only state:
    FLUSH TABLES WITH READ LOCK;
    SET GLOBAL read_only = ON;
  • backup data via this script or:
    mysqldump -h host_name --user=user_name --password=password --events --opt --single-transaction db_for_backup | gzip > backup_name.gz
  • turn off read-only mode:
    SET GLOBAL read_only = OFF;
    UNLOCK TABLES;
to restore db from backup:
  • you can use this script
  • or:  mysql -u user "db_name" < backup_name
  • or: gunzip < backup_name.gz | mysql -u user -p password db_name
Standard

mysql_restore.sh

#!/bin/bash

for i in `ls $1/mysqldump*`; do
    db=`sed -n -e 3p $i | awk -F "Database: " {'print $2'}`
    mysql -e "create database \`$db\`"
    echo `mysql -u root "$db" < $i`
    echo "created $db and filled from $i file"
done
Standard

mysql_backup_by_db.sh

#!/bin/bash

read -e -p "Type host for backup, followed by [ENTER]: " -i "127.0.0.1" host
read -e -p "Type username for $host, followed by [ENTER]: " -i "root" user
read -e -p  "Type password for $user, followed by [ENTER]: " -i "" passwd
read -e -p  "Database which should be backuped [ENTER]: " -i "all" db

DB_BACKUP=mysql_dump_$(date +%F)

mkdir $DB_BACKUP  
cd $DB_BACKUP
if [ $db == "all" ] 
then
	for db in $(mysql  -h $host --user=$user --password=$passwd -e 'show databases' -s --skip-column-names|grep -viE '(staging|performance_schema|information_schema)'); do 
		file_name="mysqldump-$db-$(date +%Y-%m-%d).gz";
		mysqldump -h $host --user=$user --password=$passwd --events  --log-error=mysql_dump.log --opt --single-transaction $db | gzip > $file_name;
		[ -s $file_name ] && res=Done || res=FAIL
		echo $db"|"$file_name"|"$res
	done | column -s "|" -t
else 
                file_name="mysqldump-$db-$(date +%Y-%m-%d).gz";
                mysqldump -h $host --user=$user --password=$passwd --events  --log-error=mysql_dump.log --opt --single-transaction $db | gzip > $file_name;
                [ -s $file_name ] && res=Done || res=FAIL
                echo $db"|"$file_name"|"$res
fi        	| column -s "|" -t