Ygthkb

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.


And the engine is InnoDB.

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.

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!