2

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.

  • What interface to you have to access your remote database servers? Do you have remote root? Or only the SQL interface? (meaning the tool would copy the data by querying everything) – Nicolas Raoul May 16 '17 at 7:53
  • Hi it's the latter scenario without remote server root – AdamJones May 16 '17 at 9:20
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.

  • Hi Nicolas, this is very close to what I want but this script appears to be for databases on the same server. – AdamJones May 19 '17 at 16:10
  • Oh, you are right! It will need a little bit of editing. – Nicolas Raoul May 20 '17 at 1:09
  • @AdamJones: Should be fixed now :-) (not tested though) – Nicolas Raoul May 20 '17 at 1:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.