mysqldump blocks httpd server

Discussion:

(too old to reply)

Antoni

2007-03-24 09:03:04 UTC

Hello,

I'm trying to make a daily database backup. When executing "mysqldump -
uxxx -pxxx database > /home/backup/ddbb.sql -q &" the httpd server
gets blocked and my site is "offline" for 30 minutes. I also tried
using mysqlhotcopy, but with no success since it fails with error:
"DBD::mysql::db do failed: Not unique table/alias: 'comments' at /usr/
bin/mysqlhotcopy line 466."

I'd like to make a daily MySQL backup, but without freezing my httpd
server. Any ideas?

Thanks,

Antoni Massó Mola

Jonathan

2007-03-24 12:33:14 UTC

Permalink

Post by Antoni
Hello,
I'm trying to make a daily database backup. When executing "mysqldump -
uxxx -pxxx database > /home/backup/ddbb.sql -q &" the httpd server
gets blocked and my site is "offline" for 30 minutes. I also tried
"DBD::mysql::db do failed: Not unique table/alias: 'comments' at /usr/
bin/mysqlhotcopy line 466."
I'd like to make a daily MySQL backup, but without freezing my httpd
server. Any ideas?
Thanks,
Antoni Massó Mola

Are you perhaps using --locak-all-tables in the parameter list of the
mysqldump command (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html)?

To help us trouble shoot it would help if you would specify the command
line options you use and give us an estimate of the database and system
specs.

To prevent such problems I am not backing up my main server but use
replication to another server. To me it is no problem if the second
server to which the database is replicated is be offline for longer
periods, my main server should not, just like yours, more information
about replication can be gound in the MySQL manual:
http://dev.mysql.com/doc/refman/5.1/en/replication.html

Jonathan

Antoni

2007-03-24 15:29:26 UTC

Permalink

Hello Jonathan,

Im using MySQL 5.0.21-log in a Linux 2.4.33grs-bipiv-ipv4-32 #1 i686
Intel(R) Core(TM)2 CPU T5500 @ 1.66GHz GNU/Linux.

I did not include the --lock-all-tables parameter when running the
mysqldump command. May this parameter be assigned in another MySQL
configuration file?

The cron job executed every day is "mysqldump -uxxx -pxxx database > /
home/backup/ddbb.sql -q &".

I'll use replication as soon as we get the money to purchase a second
server, but for now we need to stick with one server. The only two
possible ways I found for creating a database backup are mysqldump and
mysqlhotcopy. Is there any other way I can use?

Thanks!

Antoni

Post by Jonathan

Are you perhaps using --locak-all-tables in the parameter list of the
mysqldump command (http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html)?
To help us trouble shoot it would help if you would specify the command
line options you use and give us an estimate of the database and system
specs.
To prevent such problems I am not backing up my main server but use
replication to another server. To me it is no problem if the second
server to which the database is replicated is be offline for longer
periods, my main server should not, just like yours, more information
http://dev.mysql.com/doc/refman/5.1/en/replication.html
Jonathan

Gordon Burditt

2007-03-24 17:50:33 UTC

Permalink

Post by Antoni
Im using MySQL 5.0.21-log in a Linux 2.4.33grs-bipiv-ipv4-32 #1 i686
I did not include the --lock-all-tables parameter when running the
mysqldump command. May this parameter be assigned in another MySQL
configuration file?
The cron job executed every day is "mysqldump -uxxx -pxxx database > /
home/backup/ddbb.sql -q &".
I'll use replication as soon as we get the money to purchase a second
server, but for now we need to stick with one server. The only two
possible ways I found for creating a database backup are mysqldump and
mysqlhotcopy. Is there any other way I can use?

Replication. You can run multiple independent instances of MySQL
on the same server (but you need the CPU, memory, and disk resources
to do it) using independent port numbers, data directory locations,
socket, separate mysqld daemons, possibly separate UIDs, etc. You
distinguish which server to talk to by the port number or socket
location.

If load is an issue, it is possible to stop replication during the busy
period, then restart it during a lull, wait for replication to catch
up (SHOW SLAVE STATUS), stop replication again, and then mysqldump.

Antoni

2007-03-24 18:08:16 UTC

Permalink

Thanks for your reply Gordon. Do you know of any tutorial that
explains how to achieve this?

Thanks again,

Antoni

Post by Gordon Burditt

Replication. You can run multiple independent instances of MySQL
on the same server (but you need the CPU, memory, and disk resources
to do it) using independent port numbers, data directory locations,
socket, separate mysqld daemons, possibly separate UIDs, etc. You
distinguish which server to talk to by the port number or socket
location.
If load is an issue, it is possible to stop replication during the busy
period, then restart it during a lull, wait for replication to catch
up (SHOW SLAVE STATUS), stop replication again, and then mysqldump.

Jonathan

2007-03-24 21:23:16 UTC

Permalink

Post by Antoni
Thanks for your reply Gordon. Do you know of any tutorial that
explains how to achieve this?

The manual is pretty clear on how to do it:

http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

And here is a howto as well:

http://www.howtoforge.com/mysql_database_replication

Good luck!

Jonathan

Antoni

2007-03-26 08:01:55 UTC

Permalink

Thanks for your help!

Antoni