Re: Updating DB Locally
From: Gordon Burditt (gordonb.6l3qa_at_burditt.org)
Date: 06/21/04
- Next message: Google Mike: "Can't Write Text File"
- Previous message: CJ Llewellyn: "Re: Any girls?"
- In reply to: Paige: "Re: Updating DB Locally"
- Next in thread: Paige: "Re: Updating DB Locally"
- Reply: Paige: "Re: Updating DB Locally"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: 21 Jun 2004 19:33:41 GMT
>I am the only one that changes the database entries. There is no form
>info being directly submitted to the db and no one else touches it. So
>at all times, the db on my computer is the most up-to-date one.
>
>I'm sorry but I don't know the difference between overwriting and
>synchronizing a database.
If the "production" database is truly read-only, there is no difference.
If there are modifications to both databases (say, there is a 'last
used' field changed when a user accesses the web site, or the user
can change his/her password), then you have to merge the changes
for true synchronization, and overwriting just wipes out all the
changes made in the production database from the web and replaces
them from the master copy. Real synchronization requires intelligent
information about what fields are updated on which database for
what purpose. And if the tables are designed poorly, it may be
impossible to do right at all. Overwriting is always possible
(assuming the system is up and you don't run out of disk space,
etc.) but in practice it may be a disaster (lost updates).
Since your updates seem to be entered in one place only, you might
consider setting up replication if you are willing to have changes
made on the test server appear almost instantly on the production
server (which could ruin its use as a 'test' server, or it might
just eliminate the chore of propagating updates entirely).
>I only know that I'd like to be able to upload
>the latest db info from my computer to the server and have that one
>overwrite, replace, update (or whatever terminology fits) the one that's
>on the server.
I'd look at various options of mysqldump. You don't have to delete
and re-create the tables if the schema didn't change (-t, no table
creation info). You can also lock the tables so all the updates
get done, from the point of view of the web server, all at once.
>As it stands right now, the only way I can accomplish this is to either
>drop the entire server database and reinstall it using the updated copy
>or else I have to edit all the entries in the .sql backup I make to
>delete existing entries and mark the ones I changed as UPDATE instead of
>INSERT. Neither way is particularly appealing, thus my inquiry as to an
>easieer way to do this.
I don't understand why ignoring warnings about inserting duplicate
keys is a problem. I'm presuming that you have UNIQUE keys set up
so re-inserting the same data you already have won't result in
duplicate records. The mysql command-line utility has the -f option,
keep going on SQL error, which will take care of stopping on hitting
an already-existing record.
mysqldump also permits adding a WHERE clause to the data you dump,
so if there is a time stamp (or some other means you can identify
new records, like an autoincrement sequence number if you only ADD
new records and never change existing ones) on the records, you can
just dump stuff since the last dump and load those. Of course you
have to keep track of this info for the next dump/copy.
Is the volume of data such that uploading all of it each time is a
problem?
Gordon L. Burditt
- Next message: Google Mike: "Can't Write Text File"
- Previous message: CJ Llewellyn: "Re: Any girls?"
- In reply to: Paige: "Re: Updating DB Locally"
- Next in thread: Paige: "Re: Updating DB Locally"
- Reply: Paige: "Re: Updating DB Locally"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
