MySQL backup all databases

To backup all databases on a MySQL server, run

mysql -e "show databases" | egrep -v "(-|Database|mysql|information_schema|performance_schema|phpmyadmin)"  > /tmp/sok-dbs.txt
for db in `cat /tmp/sok-dbs.txt`; do /usr/bin/mysqldump --events --routines --triggers $db > "${db}.sql"; done

This will create sql backup file for each database in your MySQL server on the current directory.

The first command will create a file with all database names. Second command loop through the database names and create backup using mysqldump command.

#!/bin/bash
# MySQL Backup Scipt
# Author: admin@ServerOk.in
# @daily /usr/serverok/backup-mysql.sh > /var/log/backup-mysql.log 2>&1

MYSQL_USER="root"
MYSQL_PW="PASSWORD_HERE"

BACKUP_BASE_DIR="/backup/mysql/"
BACKUP_DATE="$(date +%Y%m%d-%H%M%S)"
BACKUP_DIR="${BACKUP_BASE_DIR}/${BACKUP_DATE}"

if [ ! -d $BACKUP_DIR ]; then
    mkdir -p $BACKUP_DIR
fi

if [ ! -d $BACKUP_DIR ]; then
    echo "Backup folder not found"
    exit 1
fi

# Backup MySQL Database

mysql -u ${MYSQL_USER} -p"${MYSQL_PW}" -e "show databases" | egrep -v "(-|Database|information_schema|performance_schema|phpmyadmin|sys)"  > /tmp/sok-dbs.txt

for db in `cat /tmp/sok-dbs.txt`
do
    /usr/bin/mysqldump -u ${MYSQL_USER} -p"${MYSQL_PW}" --events --routines --triggers $db > "${BACKUP_DIR}/${db}.sql";
done

# Remove .sql files older than 7 days
find "${BACKUP_BASE_DIR}" -mindepth 2 -maxdepth 2 -type f -name '*.sql' -mtime +7 -exec rm -f {} \;

# Remove empty directories
find "${BACKUP_BASE_DIR}" -mindepth 1 -type d -empty -exec rmdir {} \;