1

I have a registration form, which is exactly the same for several locations. Each of them has a REG column, which is unique for each inserted row. Basically, each data input has a different REG number for it, which is auto incremented.

The location's form references a location's table which has the same structure for each individual location.

However, we have one central database which pulls all the data from several locations. This central database has the job of manipulating and populate ing all the location's databases.

Let's say, I have 3 locations, A, B and C. All those locations are using same database structure to save the data of each individual location.

What I need help on here, is how must I change or configure the database table or column so I can "restore" each individual database from the different location's into one single database on the central location. If the structure is same, then the previous data will be replaced by the new one when restore/add to the database in the central location.

I have a column called CODE which stored specific hard code for each location, it is just a simple character either A, B or C based on where the location of the database is saved.

So, at the specific time, from 3 different locations, the form table will be backed up and sent to the central database. And there, it will be restored or added to the database to populate all from those 3 locations.

Any idea and little help, please? Thanks in advance.

Here the database layout I would like to be done. Layout And the engine is InnoDB.

0

Let me first suggest an approach that may not work for you, but it seems the ideal approach for saving bandwidth and resources:

Multi-source replication is a relatively new feature, available on MariaDB 10 and MySQL 5.7 (not yet released). It is used precisely for the case you are talking about: merging data from different servers into one, typically for analytical purposes.

Here it is an overview of how it works for MariaDB 10 and for MySQL 5.7. If the tables have the same name, and you set the proper filters, you could have the table almost up-to-date in real time without having to do imports and exports every single time (which will become more and more inefficient each time). Even if you do not want that, it would be just easier to start and stop the replication each time, unless the tables are completely rewritten between synchronisations.

As writing to the same table could become insecure (for the data integrity)- depending on the kind of DML queries that you execute at each location-, you could alternatively write to separate tables and use a VIEW to see it as a single table. This may or may not be better for performance depending on the queries executed on the central location.

If this does not work for you (for example, because you cannot use those versions of MySQL- after all, this is a relatively new features), I can suggest alternative methods.

EDIT:

As you seem to not be able to use a stable internet connection (although that wouldn't be a problem, replication is asynchronous and it will work even if it stops very frequently), I will assume you want to do the transfer via a USB drive.

My second recommendation would be to backup the table or tables using CSV if the data is not too big, and in binary format if they are relatively big.

For CSV format, you can use mysqldump --tab or mydumper and then import them back with mysqlimport/myloader.

If CSV format is too slow, I would recommend you to use something like Percona XtraBackup that, combined with InnoDB, allows you to export tables in binary format and then reimport them separately (it requires Percona Server or MySQL 5.6 on the importing node and innodb_file_per_table). You can first import each table separately and then merge it into a single table as partitions with EXCHANGE PARTITION. That would allow you to do the export/import at nearly disk speed.

  • Thanks for your information, jynus. Really appreciated that, I will learn that new thing. However, in my case, not because we like to replicate, but since the location itself on another "client" are remote, and no adequate internet connection, we just want to "pull" those databases from each location into single central location to manipulate the data further more. That's why we need to manually perform backup on each location and send this backup to central, and will be restore / added there. – dhicom Aug 14 '14 at 1:45
  • @dhicom I have updated the answer with an alternative method. – jynus Aug 15 '14 at 8:40
  • The problem for me is, when this backup being restore into single database, it will trigger an error since the id is already taken / there. I need to know is this possible to backup without the primary key column (id). I've read some information around in these few days to figure this out. Seems that's my only solution for now. Do you have any ideas how to do that with simple one? What I have read that I should "clone" or "dump" the database, then get rid of the primary key one by one. But, since I have many of table like that, it would be not practical doing that manually, each time. – dhicom Aug 15 '14 at 12:51
0

Given that the other answer might be a problem for you, I would suggest you try pt-archiver instead specifically using the --columns option, of course in combination with --source and --dest option you may be able to pull this off somehow. Good luck!

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.