[RESOLVED] Mysql on a local and online system -- updating

minifairy

Hello all. I have a database system that I use both on my laptop under indigo perl with php and apache on windows for when I am at locals where I do not have an online access, and the rest of the time I use the same basic database online. So far I have been basically downloading and replacing the local database with the online one and vise a versa each time I need to use it on the local machine. As the database is getting larger this is becoming more and more of a pain. Is there some way that I can 'syncronize' the two databases with a php script? I can't seem to figure out how to access both databases from one script.

Thanks in advance for any help.

sneakyimp

If you want to work on the database at times when you have no online access, then a synchronization step will always be required once you reconnect. If the database is large, this reconnect will always take time no matter which method you use.

Running a PHP script would be much slower than using mysqldump.

There may be some benefit to writing a PHP script which detects just the CHANGED parts of the database but that sounds like real chore to me.

dagon

sounds like Replication is what you want,

minifairy

mysqldump??

I've been just exporting the records from one and using sql to put them in the other once I empty it. With the largest tables I have to break it up into several pieces or it times out.

sneakyimp

Good one, dagon. I had forgotten about that.

minifairy

dagon;10932132 wrote:
sounds like Replication is what you want,

I'll read up on that one thanks.

sneakyimp

actually you never said you were using MySQL. I assumed you were. It's a command-line utility that comes with MySQL which dumps a database very quickly to an SQL file. You can then use the mysql command-line utility to restore the contents to a new database. It requires command-line access to at least one machine and a certain amount of permissions, etc. I've seen it work with enormous files. Keep in mind that the entire contents of the db must be transferred between machines. An incremental solution sounds like the best way to deal with the issue if you have very large databases.

minifairy

dagon;10932132 wrote:
sounds like Replication is what you want,

After a brief look at this I'm thinking it might not work. I don't have control over how the online sql server is configured. It is on a web host. Further it looks like updates can only occur on whichever server you configure as master. Since this changes depending on if I have online access or not seems like that wouldn't work.

Did I miss something?

minifairy

sneakyimp;10932136 wrote:
actually you never said you were using MySQL. I assumed you were. It's a command-line utility that comes with MySQL which dumps a database very quickly to an SQL file. You can then use the mysql command-line utility to restore the contents to a new database. It requires command-line access to at least one machine and a certain amount of permissions, etc. I've seen it work with enormous files. Keep in mind that the entire contents of the db must be transferred between machines. An incremental solution sounds like the best way to deal with the issue if you have very large databases.

Yes I am using mysql. I searched and didn't find the command. I've been using phpmyadmin to export the various affected tables to sql inserts then emptying the associated table on the other machine and running the sql inserts. Only one table is a real problem at the moment but a couple others are getting rather large as well. The one table I have to break up the inserts from the export into about 8-10 groups or it times out. It is sort of a major pain to keep track of where I left off and not duplicate sections etc. I just thought it would be easier to write a script that would do the work. I could add an archive bit or something that I could set or a date that it could compare to identify changes etc. Then it could do it on its own. But I can't get one script to connect to both databases at the same time.

dagon

minifairy;10932137 wrote:
After a brief look at this I'm thinking it might not work. I don't have control over how the online sql server is configured. It is on a web host.

You should seriously consider a dedicated server for such a project over a shared host. Shared hosts are fine for small generic projects but for a specialised project you need greater control.

minifairy

dagon;10932141 wrote:
You should seriously consider a dedicated server for such a project over a shared host. Shared hosts are fine for small generic projects but for a specialised project you need greater control.

Yes well in a perfect world.... The database updates the shop on my website and when I do shows that don't have web access I use it locally. I can't really afford a dedicated web server from a host and I don't have the ability to host one myself with the internet connection that I have. Overall it does work pretty well its just a bit of a pain to get the thing updated before and after shows.

Also it would be nice if I could implement similar systems for people that are not very computer/database savy and that would probably necessitate some sort of an updating process by script, so I was trying to figure out a way to do it.