MySQL backup all databases
To backup all databases on a MySQL server, run
|
1 2 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
#!/bin/bash # MySQL Backup Scipt # Author: [email protected] # @daily /usr/serverok/backup-mysql.sh > /var/log/backup-mysql.log 2>&1 BACKUP_DIR="/backup/" BACKUP_DATE="$(date +%Y%m%d-%H%M%S)" MYSQL_USER="root" MYSQL_PW="PASSWORD_HERE" 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|mysql|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}/${BACKUP_DATE}-${db}.sql"; done find "${BACKUP_DIR}" -maxdepth 1 -type f -name '*.sql' -mtime +7 -exec rm -f {} \; |
See MySQL Backup