Copying a single sql table from one server to another

[stubarny]

Hello,

 

I am trying to copy a single sql table from a mysql database on one server to another mysql database on another server.

 

I need to do this twice a day so really would like an automated php/sql cronjob solution - please could you tell me if this is possible?

 

Thanks for your help,

 

Stu

[smoseley]

mysqldump remotedb > /path/to/file

scp remote:/path/to/file /localpath/to/file

mysql localdb < /localpath/to/file

[smoseley]

Or you could cluster the second DB as a slave to keep it in sync with the main DB.

[smoseley]

mysqldump remotedb > /path/to/file

scp remote:/path/to/file /localpath/to/file

mysql localdb < /localpath/to/file

 

Duh... What am I saying!?  You could just add this to your daily crontab:

 

mysqldump -h remotehostname -u remoteuser -p remotepassword remotedbname | mysql -u localuser -p localpassword localdbname

 

In fact, I would add a shell script that does the following in sequence (grabbed the date stuff online... may need some checking).... and add this to your crontab

 

#!/usr/bin/ksh

# GET TODAY'S DATE
date '+%m %d %Y' | 
{ 
read MONTH DAY YEAR
TODAY="$YEAR-$MONTH-$DAY"
DAY=`expr "$DAY" - 1` 
case "$DAY" in 
        0) 
           MONTH=`expr "$MONTH" - 1` 
                case "$MONTH" in 
                        0) 
                           MONTH=12 
                           YEAR=`expr "$YEAR" - 1` 
                        ;; 
                esac 
        DAY=`cal $MONTH $YEAR | grep . | fmt -1 | tail -1` 
esac 

# GET YESTERDAY'S DATE
YESTERDAY="$YEAR-$MONTH-$DAY" 
}

# CREATE A NEW DB FOR TODAY
mysql -u localuser -p localpassword -e'CREATE DATABASE `dbname-$TODAY`'

# COPY DATA FROM REMOTE
mysqldump -h remotehostname -u remoteuser -p remotepassword remotedbname | mysql -u localuser -p localpassword 'dbname-$TODAY'

# DROP DB FROM YESTERDAY
mysql -u localuser -p localpassword -e'DROP DATABASE `dbname-$YESTERDAY`'

[stubarny]

Many thanks, that's great!

[fenway]

Many thanks, that's great!

Unless it's InnoDB.

[smoseley]

Unless it's InnoDB.

How do you figure?  Did you look at the latest version I made?  It's creating a new DB backup every day.

[fenway]

Did you specify "single-transaction"? Otherwise, your snapshot is inconsistent.