How to take all database backup same as db name

Question

I am using MySQL on centos7. I have 50 databases Like database1, database2...., database50.

How can I set a cronjob for take a dump every day of all database same as database name [ Like database1.sql, database2.sql .... database50.sql ] using single command or script.

Please provide some adequate solution that will be appreciated.

Thanks.

[mysqldump-with-db-in-a-separate-file](https://stackoverflow.com/questions/10867520/mysqldump-with-db-in-a-separate-file) — Paul Spiegel, Jul 25 '19 at 08:02

score 0 · Answer 1 · answered Jul 27 '19 at 00:08

0

Convert the current date to an integer number of days since some starting date.
Take that modulo 50. This gives you 0 .. 49.
Add 1 and concatenate. Now you have database1 .. database50. Put that in the shell variable db
mysqldump ... $db >$db.sql

answered Jul 27 '19 at 00:08

Rick James

135,179
13
127
222

score -1 · Answer 2 · answered Jul 25 '19 at 08:25

I am using this script

#! /bin/bash

# MySQL database backup (databases in separate files) with daily, weekly and monthly rotation

# Sebastian Flippence (http://seb.flippence.net) originally based on code from: Ameir Abdeldayem (http://www.ameir.net)
# You are free to modify and distribute this code,
# so long as you keep the authors name and URL in it.

# Modified by IVO GELOV

# How many backups do you want to keep?
MAX_DAYS=5

# Date format that is appended to filename
DATE=`date +'%Y-%m-%d'`
DATSTR=`date '+%Y%m%d' -d "-$MAX_DAYS days"`

# MySQL server's name
SERVER=""

# Directory to backup to
BACKDIR="/var/db_arhiv/mysql"

#----------------------MySQL Settings--------------------#

# MySQL server's hostname or IP address
HOST="localhost"

# MySQL username
USER="user"

# MySQL password
PASS="password"

# List all of the MySQL databases that you want to backup, 
# each separated by a space. Or set the option below to backup all database
DBS="db1 db2"

# Set to 'y' if you want to backup all your databases. This will override
# the database selection above.
DUMPALL="y"


# Custom path to system commands (enable these if you want use a different 
# location for PHP and MySQL or if you are having problems running this script)
MYSQL="/usr/local/mysql/bin/mysql"
MYSQLDUMP="/usr/local/mysql/bin/mysqldump" 


function checkMysqlUp() {
    $MYSQL -N -h $HOST --user=$USER --password=$PASS -e status > /dev/null
}
trap checkMysqlUp 0

function error() {
  local PARENT_LINENO="$1"
  local MESSAGE="$2"
  local CODE="${3:-1}"
  if [[ -n "$MESSAGE" ]] ; then
    echo "Error on or near line ${PARENT_LINENO}: ${MESSAGE}; exiting with status ${CODE}"
  else
    echo "Error on or near line ${PARENT_LINENO}; exiting with status ${CODE}"
  fi
  exit "${CODE}"
}
trap 'error ${LINENO}' ERR

# Check backup directory exists
# if not, create it
if  [ ! -e "$BACKDIR/$DATE" ]; then
    mkdir -p "$BACKDIR/$DATE"
    echo "Created backup directory (${BACKDIR}/${DATE})"
fi

if  [ $DUMPALL = "y" ]; then
    echo "Creating list of databases on: ${HOST}..."

    $MYSQL -N -h $HOST --user=$USER --password=$PASS -e "show databases;" > ${BACKDIR}/dbs_on_${SERVER}.txt

    # redefine list of databases to be backed up
    DBS=`sed -e ':a;N;$!ba;s/\n/ /g' -e 's/Database //g' ${BACKDIR}/dbs_on_${SERVER}.txt`
fi

echo "Backing up MySQL databases..."

#cd ${LATEST}
for database in $DBS; do
  if [ ${database} = "information_schema" ] || [ ${database} = "performance_schema" ] || [ ${database} = "pinba" ]
  then
    continue
  fi
    echo "${database}..."
    $MYSQLDUMP --host=$HOST --user=$USER --password=$PASS --default-character-set=utf8 --routines --triggers --lock-tables --disable-keys --force --single-transaction --allow-keywords --dump-date $database > ${BACKDIR}/${DATE}/${SERVER}$database.sql
done

if  [ $DUMPALL = "y" ]; then
    rm -f ${BACKDIR}/dbs_on_${SERVER}.txt
fi

# dump privileges
$MYSQL -N -h $HOST --user=$USER --password=$PASS --skip-column-names -A -e "SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user" | $MYSQL -N -h $HOST --user=$USER --password=$PASS --skip-column-names -A > ${BACKDIR}/${DATE}/${SERVER}_grants.sql

# delete older files
for x in `find ${BACKDIR}/20* -type d`
do
    xd=`basename "${x//-/}"`
    if [[ $xd < $DATSTR ]]
    then
        rm -rf "$x"
    fi
done

echo "MySQL backup is complete"

How to take all database backup same as db name

2 Answers2