0

I have a database setup online where I take user registrations and provide them a pass to enter the event ( people gathering), Now at the event I also have to perform user registrations, situation is internet is not always stable at the event so I am considering to setup database offline, I found some guides on mysql replications but not getting full picture if its possible the way I want.

At the event I will setup database at my localhost and register users offline, also take new registrations online ( on other server hosting a copy of same database online), users table has an autoincremental index which is going to be a huge problem to sync both databases using mysql replication, when both servers will add a record to the same table ,it will assign the same index id to both databases. Is there something I can do to avoid this issue.

Cœur
  • 37,241
  • 25
  • 195
  • 267
user969068
  • 2,818
  • 5
  • 33
  • 64
  • Suggestion for improving your question - add context. You launch into talking about "the event" as if we should know what that means. Only later can we guess that you mean some sort of physical gathering of people at some place. – Lightness Races in Orbit Mar 31 '18 at 17:25

3 Answers3

0

Well, master-master replication does exist, and may suit your purposes, but it has some drawbacks.

I think you should consider taking registrations in a different form when on-site, then insert them into your main database when you get home. This is a pretty common way to do things.

If you really need to it with MySQL, come up with a "merge" tool that can re-create the users created off-site, on demand; as you pointed out, you'll need to account for different auto-increment IDs, but that's not necessarily an actual problem. It just needs to be dealt with.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
  • the issue with re-creating the user is it will re-create the ID when I merge database, when a user is registered we have to print the ID on his/her entrance card. – user969068 Mar 31 '18 at 17:32
  • @user969068: Will you be printing IDs at the event, or do you send them later? Best to just take people's registration _requests_ at the event, and complete the registration later when you have proper office access. Mail out the IDs when done. User IDs shouldn't be auto-increment like this anyway. – Lightness Races in Orbit Mar 31 '18 at 17:35
  • there are various reasons we want to maintain ID's in both databases offline/online, we will also have new registrations and users modifying their account data online and offline. We have barcode scanners attached with ID's, its really complicated situation due possible threat of no internet connection during the event. We already arranged 2 ADSL 1 4G but still I wanted to be sure if their is any possible solution, I just found some info here so trying to figure out what he meant with `auto_increment_increment= 2` https://stackoverflow.com/questions/1517687/mysql-writing-to-slave-node – user969068 Mar 31 '18 at 17:53
  • @user969068: Well, you simply can't do that. If user IDs are unique and authoritative, and can only be generated in one place at one time, then you can only create new users at one place in one time. It's as simple as that. Either temporarily "move" your system (disable the internet system and have _only_ your laptop system, during the event), or have your users merely _request_ to register at the event but satisfy the request later. It sounds like you have overcomplicated it with the barcode scanners. – Lightness Races in Orbit Mar 31 '18 at 17:54
  • Do you have any idea what this guy is saying about? `auto_increment_increment= 2` https://stackoverflow.com/a/16490874/969068 – user969068 Mar 31 '18 at 17:55
  • @user969068: Yes, it adds an artificial "stride" to auto_increment indexes, different on each master, to prevent collisions, but if I understand correctly (you have physical technology in one place that needs to be used) that doesn't really help you. – Lightness Races in Orbit Mar 31 '18 at 17:55
  • This all being said, if you have 2 ADSL lines I don't really see the problem. Are you expecting this service not to be available for some reason? And/or is it the end of the world if it goes down for an hour or two? Could you live with it? At some point you have to weight up the actual risks _versus_ the time you're spending trying to mitigate against those risks. – Lightness Races in Orbit Mar 31 '18 at 17:57
  • And you _still_ shouldn't be using auto-increment IDs as user IDs. – Lightness Races in Orbit Mar 31 '18 at 17:57
  • well we have over 50k+ visits in 2 days at the event so without bar code it is a mess, and also if we share free wifi you can just imagine the speed issues we'll get. – user969068 Mar 31 '18 at 18:00
  • What's mobile data like there? And what does the barcode actually do? – Lightness Races in Orbit Mar 31 '18 at 18:05
  • well that is the option I am also considering, using mobile data, to just have 10+ mobile cards data for backups. we will have like 16 computers/barcode scanners....bardcode just output submit member id in a form to get user details, there are various members with some missing information which we will be able to track. The issue is we don't have time to even enter ID due to huge rush so barcode processing is always faster for us. – user969068 Mar 31 '18 at 18:10
  • @user969068: What does the barcode represent? The ID itself? Something else? – Lightness Races in Orbit Mar 31 '18 at 18:15
  • it represent ID of the user – user969068 Mar 31 '18 at 18:15
  • @user969068: So it's not an auto-increment ID generated when the user registers. It's an auto-increment ID generated when you print off the registration form. That changes everything. It means you have already "reserved" the ID before you even left home, and what you do at the event is irrelevant. – Lightness Races in Orbit Mar 31 '18 at 18:17
  • no, it's an auto-increment ID (Primary Key) generated when the user registers – user969068 Mar 31 '18 at 18:19
  • Then what does the barcode/form have to do with it? You're talking in circles and (seemingly) contradicting yourself. Sorry, I can't help any further without more concrete information of your setup. Good luck! – Lightness Races in Orbit Mar 31 '18 at 18:19
0

If your primary concern is that you have 2 systems that need to generate id that are unique from each other without coordination, there's a couple of things you can:

  • Use auto_increment_increment and auto_increment_offset. Simply put 1 of your 2 servers might use even id's and the other one odd ones.
  • Use a different key, maybe a natural key (email address?)
  • Use something like a UUID (or GUID if you're in the microsoft world. They're really the same thing).
Evert
  • 93,428
  • 18
  • 118
  • 189
-2

you can dump the database from server to local from terminal like below.

Run command : mysqldump -h hostname -u username -ppassword databasename > C:\path_to_file

Rahul Gupta
  • 13
  • 6