MySQL Dump

Collection of some MySQL commands, very useful to create and restore MySQL-Backups.

Dump only some tables of a database:

1	mysqldump -u root -p db1 wrp_comments wrp_posts > dump.sql

Create dump:

1	mysqldump -uUSERNAME -p DBNAME > DBNAME.sql

Restore/Insert dump:

1	mysql -D DBNAME -p < DBNAME.sql

A tiny backup script for your cron to dump all databases:

!/bin/bash
 
DUMPDIR="/var/backups/mysqldumps/"
MUSER="root"
MPASS="pasword"
DBLIST="
db01
db02
db03
anotherdb
"
 
# Remove old dumps
rm -f $DUMPDIR*.sql
 
# Optimize db's
for database in $DBLIST; do
    mysql --user=$MUSER --password=$MPASS $database -e 'show tables' | awk '{print "optimize table " $1 ";"}' | mysql --user=$MUSER --password=$MPASS $database &gt;/dev/null
done
 
# Create dump's
for database in $DBLIST; do
    mysqldump --user=$MUSER --password=$MPASS $database &gt;$DUMPDIR$database.sql
done

For very large databases you can use this previous written post: Dump locked Tables

MySQL Dump

Categories

Archives

Categories

Archives

Tags

Leave a Reply