0

I have an Ubuntu Server with mysql database on it and I would like to copy its full content (data, users, anything) to another mysql server I have on the network.

How can I obtain this result (automatically, maybe using cron) taking care of security (none should be able to intercept the data)?

Pitto
  • 2,009
  • 10
  • 33
  • 49
  • Your question is unclear. Are you looking to set up true (near) real-time replication or just to transfer periodic database dumps to another host? – EEAA Sep 28 '11 at 14:09
  • Since the production DB is very important I'd prefer not to mess with it and allow the periodic transfer every night. Excuse me for being inaccurate. – Pitto Sep 29 '11 at 12:43

2 Answers2

3

The easiest way would probably to set up a Master/Slave DB scenario where the slave is your backup. If you wanted an out of stream backup, you could stop the slave to snapshot it or dump it without stopping the master which may also suite your needs.

I'm not sure if your criteria to not stop the service on the DB server is because you don't want downtime or for some other reason. If the former, then the Master/Slave and periodically dump the Slave should work.

jdw
  • 3,855
  • 2
  • 17
  • 21
  • I would like the users not to notice that I'm backing up. I'd like to be "stealth" and transfer the complete backup during night so in working hours everything is just perfect. – Pitto Sep 29 '11 at 12:45
  • So that will work. At your leisure, stop the slave, back it up, then restart it. Done deal. The master will continue to function and when the slave comes back online the slave will replay the binary log to catch up on missed transactions. – jdw Sep 29 '11 at 13:14
  • Perfect! Now all I need is... How? :D – Pitto Sep 29 '11 at 13:19
1

You can do this very simply by writing a script that will run via cron. This script would 1) do a mysqldump 2) compress that database dump and then 3) transfer the compressed backup to the slave server using scp. The script could be as simple as:

#!/bin/bash

mysqldump -u root -p<password> --all-databases | gzip -c | cat /path/to/db_dump.sql.gz
scp /tmp/db_dump.sql.gz user@slave:/path/to/

Then you'd just need to import that database dump into mysql on the slave.

EEAA
  • 109,363
  • 18
  • 175
  • 245
  • Without stopping the DB, though, you run the risk of getting a dump that is inconsistent. If there are things waiting to be written when you take the dump, they will not be included which could, in theory, result in foreign key violations and other such inconsistencies. – jdw Sep 29 '11 at 13:15
  • That's helpful indeed, @ErikA! Any answer to @jdw's comment? – Pitto Sep 29 '11 at 13:20
  • @Pitto Well it's accurate. Whether or not it affects you depends on your application. Setting true replication is the best situation, but anything short of that, you're going to end up with a possibly inconsistent dump if there's anything other than `SELECT` activity going on during the dump. – EEAA Sep 29 '11 at 14:17
  • Setting a real replication is an hard task? Can it affect production db performance / reliability? – Pitto Sep 29 '11 at 16:47