You are here

backup mysql database with mysqldump

Backup  databases which have different charset separate files per database.

#!/bin/bash
###
MYSQL='/usr/bin/mysql'
MHOST=localhost
MUSER=user
MPASS=pass
BACKUP='/home/backupuser/mysql-backup'
GZIP='/bin/gzip'
MYSQLDUMP='/usr/bin/mysqldump'
###
DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -S /tmp/mysql.sock -Bse 'show databases')"
for db in $DBS
do
    # echo $db
    FILE=$BACKUP/mysql-$db-$(date +"%A").sql.gz
    CHARSET=`$MYSQL -u $MUSER -h $MHOST -p$MPASS -S /tmp/mysql.sock -Bse "SELECT default_character_set_name FROM information_schema.SCHEMATA  WHERE schema_name = \"$db\";"`
    # echo $CHARSET
    $MYSQLDUMP --defaults-extra-file=/etc/mysql/mysqldump.cnf -C -c -R --opt --add-drop-database --flush-privileges --flush-logs -S /path/mysql.sock --databases $db --default-character-set=$CHARSET|  $GZIP > $FILE
done