Automated mysql table duplication between servers, via SQL

Question

The problem: I need to backup a few servers databases (sometimes a single table, other times all tables in the database) and also in once case replicate a database on a nightly basis.

So this is database to database copying. You may say, use mysql replication however I'm trying to achieve this without using mysql replication as I don't always have access to configure this on remotely hosted servers.

To clarify, these are servers at different locations and not in the same location. I'm aware of many solutions when the tables exist in databases on the same server but options seem much more limited when this isn't the scenario.

I need this to run on a schedule I can setup, say once a day in a totally automated fashion, copying and overwriting the target tables with the data from the source.

I'm aware through the research I've done of a windows commercial product that can do this (SQLyog) but I wanted to see if anyone knows of alternatives and maybe for Linux as well as Windows.

Answer 1

1) Create this backupdb.sh script which copies a remote MySQL database into your local database:

#!/bin/bash

DBUSER=$1
DBPASSWORD=$2
DBSNAME=$3
DBNAME=$4
DBSERVER=$5
LOCALDBUSER=$6
LOCALDBPASSWORD=$7
LOCALDBSERVER=$8

fCreateTable=""
fInsertData=""
echo "Copying database ... (may take a while ...)"

DBCONN="-h ${DBSERVER} -u ${DBUSER} --password=${DBPASSWORD}"
LOCALDBCONN="-h ${LOCALDBSERVER} -u ${LOCALDBUSER} --password=${LOCALDBPASSWORD}"

echo "DROP DATABASE IF EXISTS ${DBNAME}" | mysql ${LOCALDBCONN}
echo "CREATE DATABASE ${DBNAME}" | mysql ${LOCALDBCONN}
for TABLE in `echo "SHOW TABLES" | mysql $DBCONN $DBSNAME | tail -n +2`; do
        createTable=`echo "SHOW CREATE TABLE ${TABLE}"|mysql -B -r $DBCONN $DBSNAME|tail -n +2|cut -f 2-`
        fCreateTable="${fCreateTable} ; ${createTable}"
        insertData="INSERT INTO ${LOCALDBNAME}.${TABLE} SELECT * FROM ${DBSNAME}.${TABLE}"
        fInsertData="${fInsertData} ; ${insertData}"
done;
echo "$fCreateTable ; $fInsertData" | mysql $LOCALDBCONN $DBNAME

(written by jozjan and modified)

2) Write a script backupalldbs.sh to call it for every database you want to backup, example:

#!/bin/bash
backupdb.sh adam iwg74e9R thedb thedb server.example.com localuser localpassword localserver
backupdb.sh nico of2D9872 adb adb server.example.org localuser localpassword localserver
...

3) Finally call that script with Linux' cron scheduler.

Written for Linux, but making it run on Windows should not be too difficult after you install bash for Windows.