Monday, September 12, 2011

Import/Export MySQL Databases

Well to be honest, this is quite trivial but useful nevertheless so I will get straight to the point:


In order to export a DB into a file:
#mysqldump -u username -p{password} database_name > dbfile.sql
   
In a scenario where multiple DB's need a backup solution
A simple script like this can be implemented:

#!/bin/bash
for i in `mysql -e "show databases;" |egrep -v '^Database$'`; do
#dump and backup to remote server
mysqldump -Q |gzip -cf >/backupdir/db_${i}-`date + %F`.sql.gz
rsync -avz -e ssh /backupdir/. server:/remote_dir/. 
done


To import an exported DB from a file:

# mysql -u username -p db_name < dbfile.sql 


No comments: