[GLLUG] crossloading a mysql db - improvements to shell script?

[James Courtier-Dutton]

Hi,

Is normal "replication" not possible?
I.e. Where the master database sends the changed only to the slave
database. Saves you having to dump the entire database each time.

Another option could be do a dump from the first database.
This creates a text file.
Do a "diff" of this and the previous one, and just send the "diff"
file across the link.
Then apply is as a "patch" to the previous one at the remote end.
Do a "sha1sum" of both to double check they are now identical, and
then load the file into the remote end.

Another alternative.
Just use "git" source code repository, commit the mysqldump file into
the local git.
Then just do a "git push" to push it across to the remote one.
Then at the remote end do a git pull and then load that into the database.
Git will automatically compress the files, and send the differences
only, and do checksums on them.

There are so many options, that will massively reduce the bandwidth
needed between sites.

James





On 7 April 2014 09:24, tid <td at bloogaloo.co.uk> wrote:
> Hi Folks,
>
> I'm seeking to improve a database crossload from one mysql server to another
> and am looking for any ideas / suggestions.
>
> Previously, the client was using :
>
> -------------------------------------------------
> dump fromserver db1 > file1.sql
> dump fromserver db2 > file2.sql
> cat file1 | mysql toserver db1
> cat file2 | mysql toserver db2
> -------------------------------------------------
>
> which I've speeded ( sped?) up by
>
> -------------------------------------------------
> dump fromserver db1 > file1.sql &
> dump fromserver db2 > file2.sql &
> wait
> cat file1 | mysql toserver db1 &
> cat file2 | mysql toserver db2 &
> wait
> -------------------------------------------------
>
> I can obviously do this:
>
> dump fromserver db1 | mysql toserver db1
>
> at which point the network becomes the bottleneck. Is it possible to improve
> performance by bonding network interfaces? The only fly in the ointment here
> is that the mysql server is an AWS RDS instance, and therefore I can't add
> interfaces to it - only to the server where my script runs.
>
> Any thoughts / suggestions / guffahs of laughter gratefully received.
>
> Cheers,
>
> Tid
>
> _______________________________________________
> GLLUG mailing list
> GLLUG at mailman.lug.org.uk
> https://mailman.lug.org.uk/mailman/listinfo/gllug
>