Export MySQL databases to file..

Needed to export MANY databases, but only based on wildcard% search.
This also could be used to backup databases!
Here is the bash script I came up with...
Update: added another script to be table specific.

------------------------------------------------------------------------

backup_db.sh

------------------------------------------------------------------------

#!/bin/bash

# http://codeinthehole.com/writing/mysqldump-with-wildcard-table-matching/


if [ $# -lt 1 ]
then
    echo "Usage: DB (auto add the wildcard at the end)"
    exit 1
fi;

database=$1
echo "# $database DUMP" > $database.sql

echo "SELECT DISTINCT TABLE_SCHEMA FROM information_schema.TABLES where TABLE_SCHEMA LIKE '$database%'" | mysql -pmypass --database=information_schema | while read -r line
do
    echo "$line";
    mysqldump --databases $line  -pmypass >> $database.sql
done;

gzip $database.sql

------------------------------------------------------------------------

backup_tables.sh

------------------------------------------------------------------------


#!/bin/bash

# http://codeinthehole.com/writing/mysqldump-with-wildcard-table-matching/


if [ $# -lt 1 ]
then
    echo "Usage: ./backup_tables.sh {DB} {TABLE} (auto add the wildcard at the end)"
    exit 1
fi;

database=$1
table=$2
echo "# $database $table DUMP" > $database.$table.sql

echo "SELECT \`TABLE_SCHEMA\` as \`db\`, \`TABLE_NAME\` as \`table\` FROM information_schema.TABLES WHERE \`TABLE_SCHEMA\` LIKE '$database%' AND \`TABLE_NAME\` LIKE '$table%'" | mysql -pmypass --database=information_schema | while read db table
do
    echo "$line";
    mysqldump -pmypass  --hex-blob --triggers --databases $db --tables $table >> $database.sql
done;

gzip $database-$table.sql
-----------------------------------------------------------------------------------

------ UNZIPPING AND IMPORTING TO SERVER ------


gunzip -c {filename}.sql.gz | mysql -u root -pmypass --force


Popular Posts