By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,930 Members | 1,404 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,930 IT Pros & Developers. It's quick & easy.

mysqldump locking issue

P: n/a
ok I want to do a dump daily of my database, this works fine, however
the problem I have is that while the dump is executing itself, I cant
access my website cause the thing locks tables I think.

Is there a way to dump and allow queries to happen at the same time
without them being put on hold?

thanks.

Aug 10 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
jmichel wrote:
ok I want to do a dump daily of my database, this works fine, however
the problem I have is that while the dump is executing itself, I cant
access my website cause the thing locks tables I think.

Is there a way to dump and allow queries to happen at the same time
without them being put on hold?

thanks.
The short answer is no. Your best bet would be to setup replication and
then backup the slave. That way, you can lock the tables on the slave
while you do the backup and not affect your website visitors.

-Steven
Aug 10 '06 #2

P: n/a

Steven Musumeche wrote:
The short answer is no. Your best bet would be to setup replication and
then backup the slave. That way, you can lock the tables on the slave
while you do the backup and not affect your website visitors.

-Steven
What if I did this:

cp -Rp /var/lib/mysql/mydatabase /var/lib/mydatabase_backup
and then do the mysqldump on the mydatabase_backup database

I tested it, it seems to work, but do you see any problems with doing
that?

all my tables are MyISAM

Aug 11 '06 #3

P: n/a
The only problem with that method is that you cannot be sure that the
data files aren't modified while you are copying them. If they are, you
could have a corrupt or inconsistent backup. This is why MyISAM tables
have to be locked before you do a copy of the database files. You can
use FLUSH TABLES WITH READ LOCK and then UNLOCK TABLES to accomplish this.

-Steven

jmichel wrote:
Steven Musumeche wrote:
>The short answer is no. Your best bet would be to setup replication and
then backup the slave. That way, you can lock the tables on the slave
while you do the backup and not affect your website visitors.

-Steven

What if I did this:

cp -Rp /var/lib/mysql/mydatabase /var/lib/mydatabase_backup
and then do the mysqldump on the mydatabase_backup database

I tested it, it seems to work, but do you see any problems with doing
that?

all my tables are MyISAM
Aug 11 '06 #4

P: n/a
jmichel wrote:
cp -Rp /var/lib/mysql/mydatabase /var/lib/mydatabase_backup
This is safe to do only if the MySQL Server is shut down.

Filesystem-level commands like cp don't coordinate with MySQL's internal
locking and caching. So if there are data rows that MySQL Server hasn't
yet written to the files, you risk getting an incomplete copy of the
files. You could even get an inconsistent state of the database (e.g. a
row in a child table referencing a row in a parent table that hasn't
been written to disk yet).

Regards,
Bill K.
Aug 11 '06 #5

P: n/a

"jmichel" <jm********@infogt2000.comwrote in message
news:11**********************@b28g2000cwb.googlegr oups.com...
>
Steven Musumeche wrote:
>The short answer is no. Your best bet would be to setup replication and
then backup the slave. That way, you can lock the tables on the slave
while you do the backup and not affect your website visitors.

-Steven

What if I did this:

cp -Rp /var/lib/mysql/mydatabase /var/lib/mydatabase_backup
and then do the mysqldump on the mydatabase_backup database

I tested it, it seems to work, but do you see any problems with doing
that?

all my tables are MyISAM
4
Aug 14 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.