Howto dump & restore tables and databases

Dump

To dump specific table(s):

mysqldump --add-drop-table -u user -pPASSWORD dbfoo table1 table2 > dump_foo

If you want to import a dump made with mysql 5 into mysql 4 you should pass the following parameters to mysqldump:

mysqldump --add-drop-table --compatible=mysql4 -u user -pPASSWORD dbfoo table1 table2 > dump_foo

Dump multiple tables named with a prefix

Task to do: dump all tables within your database 'dbname' with prefix 'wp_*' (i.e. everything wordpress in that case):

# 1st step: gather all tablenames:
mysql -B dbname --skip-column-names -e "select table_name from information_schema.tables where table_schema not like 'information_schema' and table_name like 'wp\_%';" | tr '\n' ' ' > tables.txt
 
# 2nd step: do the dump:
mysqldump dbname $(cat tables.txt) | bzip2 > dbname_wp.dump.bz2

Restore

Import the dump:

mysql -u USER -pPASSWORD dbfoo <  dump_foo

If the dump is compressed with bzip2 then the following would work:

bzcat dump_foo.bz2 | mysql -u USER -pPASSWORD dbfoo

Initialize database

The purpose of this script is to drop a development testdatabase and load some data from production to play with.

#!/bin/bash
# replace local DB with a fresh dump of remote DB
# DANGEROUS - think twice before doing this!
 
DEST=/tmp
 
ssh root@host.tld "mysqldump -ppassword dbname table1 table2" > $DEST/dbname.dump
 
if [ -e $DEST/dbname.dump ]; then
        echo "drop database dbname_dev; create database dbname_dev" | mysql
        # import
        cat $DEST/dbname.dump | mysql dbname_dev
        rm $DEST/dbname.dump
fi

Backup script

#!/bin/bash
 
# Login
MYSQLPW="password";
MYSQLUSER=root;
MYSQLHOST=192.168.0.10;
 
# read database names
declare -a DATABASES;
DATABASES=( $(mysql -h $MYSQLHOST -u $MYSQLUSER -p$MYSQLPW -Bse 'show databases') )
DATABASE_EXCLUDES=( performance_schema information_schema );
 
# where do we save the dumps?
str_BACKUPDEST=/var/backup/mysql;
 
# remove excluded databases from backupset
index=0;
for i in ${DATABASES[*]}; do
   # in excludes list?
   for e in ${DATABASE_EXCLUDES[*]}; do
       if [ $e == $i ]; then
         # kick it!         
         unset DATABASES[$index];
       fi
   done
   ((index++));
done
 
# now do the backup
 
# uncomment to see what databases will be backed up:
# echo ${DATABASES[@]};
 
for i in ${DATABASES[*]}; do
   # move the last dump to *.dump.old.bz2
   if test -e ${str_BACKUPDEST}/${i}.dump.bz2; then      
      mv ${str_BACKUPDEST}/${i}.dump.bz2 ${str_BACKUPDEST}/${i}.dump.old.bz2;
   fi
   mysqldump -h ${MYSQLHOST} -u ${MYSQLUSER} -p${MYSQLPW} --events --ignore-table=mysql.events $i > ${str_BACKUPDEST}/"${i}.dump";
 
   # compress the dump to save space, fire pbzip2 with two threads and a max memory usage of 200M
   # pbzip2 will comsume slightly more space on disk but it is much faster due to its multi-thread
   # capability compared to bzip2
   pbzip2 -p2 -m200 ${str_BACKUPDEST}/"${i}.dump"
done
mysql/dump_restore.txt · Last modified: 2014-06 by tb
Driven by DokuWiki Recent changes RSS feed Valid CSS Valid XHTML 1.0 ipv6 ready