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: [email protected]
# @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 {} \;
See MySQL Backup