943,685 Members | Top Members by Rank

Ad:
  • MySQL Discussion Thread
  • Unsolved
  • Views: 922
  • MySQL RSS
Sep 7th, 2009
0

mysqldump

Expand Post »
hello everyone.

i have a program that writes into a mysql database, and at some time, i have to do some sort of database rotation
that's like a dump followed by a truncate (at least it's how I see it)
the problem is that while i do the dump, that program is very likely to write into the database and after the dump finishes, if i truncate the tables i might lose some lines (the ones added during the dump)
i know i can set mysqldump to lock the tables, but after the dump ends it releases them and the problem of the program inserting lines before the truncate starts appears once more (it may add even a few lines before it actually does the truncate, and then I lose them with the truncate)

practically i want to backup a database (periodically) to a file and then clear the database, without losing any intermediate data inserted into it

is there any way to solve this?
Last edited by andrei4002; Sep 7th, 2009 at 9:44 am.
Similar Threads
Reputation Points: 10
Solved Threads: 0
Newbie Poster
andrei4002 is offline Offline
4 posts
since Nov 2008
Sep 7th, 2009
0

Re: mysqldump

If you are using a different user for the dump, than you could disallow priviliges to the general user, while dumping/truncating, and reset priviliges afterwards.

"FLUSH TABLES WITH READ LOCK" may be a better option. But haven't used it myself.
Last edited by pritaeas; Sep 7th, 2009 at 10:46 am.
Sponsor
Featured Poster
Reputation Points: 550
Solved Threads: 728
Bite my shiny metal ass!
pritaeas is offline Offline
4,164 posts
since Jul 2006
Sep 9th, 2009
0

Re: mysqldump

Firstly is there any criteria as to on which you could separate the data ?
Say for example if there is any auto_increment field you could certainly use this to tuncate only those rows with id <= last_id, if not is there any timestamp value which could be used in the same way.
If there are such fields it would most rightly serve you to take the dump without locking the tables and then truncating accordingly, since locks are always going to hinder performance. Also if the dump takes a lot of time to finish, other insert/update processes might get a "lockwait timeout exceed"

If the above is not feasible due to absence of such a column on which rows could easily be separated, then what you could do is before starting with the mysqldump, create a table identical to the original one with the 'CREATE TABLE %tablename%... AS SELECT * from %originaltablename%' syntax then run mysqldump on this table and then finally drop the duplicate table. This wouldn't require locking at all (apart from the one that the mysql server itself would enforce while creating the table) and inserts could go on on the original table while you are taking the mysldump of the duplicate table.

If both the above mentioned options aren't feasible to you, you could use explicit locking on tables during the mysqldump and truncate commands and release them only after the truncate command has finished.
Reputation Points: 485
Solved Threads: 89
Posting Shark
verruckt24 is offline Offline
944 posts
since Nov 2008

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in MySQL Forum Timeline: Common SQL problem, difficult solution?
Next Thread in MySQL Forum Timeline: which is better MYSQL or MYSQLI?





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC