mysqldump locking issue

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.

Re: mysqldump locking issue

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

Re: mysqldump locking issue

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

Re: mysqldump locking issue

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.

Re: mysqldump locking issue

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
>

Re: mysqldump locking issue

"jmichel" wrote in message
news:1155318547.469581.104600@b28g2000cwb.googlegroups.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