0

I have a central server and several (around 50) remote servers. I want to transfer some log data from each of the servers to the central server every night. They all run Linux, and the logs are stored in MySQL. I have a ssh access to all servers.

What is the best (easiest, safest, most reliable...) practice of transferring the data from remote servers to the central server?

thanks

mustafa
  • 3,605
  • 7
  • 34
  • 56
  • @danihp do you mean creating a db dump and transferring it via scp? It was my first idea, but there might be a wiser way. I don't want to transfer whole table every day. I only need to transfer the new lines. – mustafa Dec 01 '11 at 21:08
  • ok, I answered with this new information. – dani herrera Dec 01 '11 at 21:18

3 Answers3

1

Depending on your needs and the time you want to put into this, I have been using this script for a long time to backup databases.

It's a low-cost strategy that is tried and tested, very flexible and quite reliable.

Stephane Gosselin
  • 9,030
  • 5
  • 42
  • 65
0

You can export new lines to a csv file. Like this:

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE timestamp > lastExport

Then transfer it via scp and import it whit mysqlimport If database is innoDB you should import first referenced tables.

dani herrera
  • 48,760
  • 8
  • 117
  • 177
0

In general it is easiest to dump it with mysqldump and load it back in on all the duplicate servers. You can use some of the many options to mysqldump to control things such as locking, MVCC snapshot, which tables, and other options.

CSV is more difficult than mysqldump because you need to make sure you agree on how to terminate fields, how to escape etc.

MarkR
  • 62,604
  • 14
  • 116
  • 151
  • is it possible to dump a selected list of records with ´mysqldump´? like ´... WHERE timestamp > lastExport´? – mustafa Dec 02 '11 at 08:22
  • It is possible to dump a selection of records by specifying the --where option. You can only do this if you dump one table at a time, of course. – MarkR Dec 03 '11 at 07:21