Backup/Restore script to auto initiate or backup a database.
#!/usr/bin/env bash
set -e
mkdir -p /root/tmp
#migrate "original db params" "new db params" "new db name"
function migrate(){
mysqldump --skip-lock-tables --single-transaction --add-drop-table $1 > /root/tmp/${3}.sql
echo "CREATE DATABASE IF NOT EXISTS ${3};" | mysql $2
mysql --max_allowed_packet=1000M $2 $3 < /root/tmp/${3}.sql
rm -f /root/tmp/${3}.sql
}
migrate "-uolduser -poldpassword -h oldhost olddbname" "-unewuser -pnewpassword -h newhost" "newdbname"
#more migrates here ...
Fork here
The script creates separate directory for every database and bz2 files for every table inside that database.
#!/bin/sh
#edit these
USER=""
PASSWORD=""
MYSQLDIR="/path/to/backupdir"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
echo - Dumping DB structure "$MYSQLDIR"/all.bz2
$MYSQLDUMP --user=$USER --password=$PASSWORD -d --all-databases | bzip2 > "$MYSQLDIR"/all.bz2
echo - Dumping tables for each DB
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | grep -Ev "(Database|information_schema)"`
for db in $databases; do
echo - Creating "$db" DB
mkdir $MYSQLDIR/$db
chmod -R 777 $MYSQLDIR/$db
for tb in `$MYSQL --user=$USER --password=$PASSWORD -N -B -e "use $db ;show tables"`
do
echo -- Creating table $tb
$MYSQLDUMP --opt --delayed-insert --insert-ignore --user=$USER --password=$PASSWORD $db $tb | bzip2 -c > $MYSQLDIR/$db/$tb.sql.bz2
done
echo
done
great help @ http://mansurovs.com/tech/converting-vbulletin-to-utf-8
these are the exact commands that worked for me
