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 --user=USERNAME --password=PASSWORD DATABASE >DATABASE.sql |
Restore/Insert dump:
1 | mysql -D DATABASE -p < DATABASE.sql |
A tiny backup script for your cron to dump all databases:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | !/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 >/dev/null done # Create dump's for database in $DBLIST; do mysqldump --user=$MUSER --password=$MPASS $database >$DUMPDIR$database.sql done |
For very large databases you can use this previous written post: Dump locked Tables
0 Responses to “MySQL Dump”