469,366 Members | 2,247 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,366 developers. It's quick & easy.

mysqldump blocks httpd server

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

Mar 24 '07 #1
6 5934
Antoni wrote:
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
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
Mar 24 '07 #2
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

Jonathan ha escrit:
Antoni wrote:
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

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
Mar 24 '07 #3
>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?
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.

Mar 24 '07 #4
Thanks for your reply Gordon. Do you know of any tutorial that
explains how to achieve this?

Thanks again,

Antoni

Gordon Burditt ha escrit:
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?

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.
Mar 24 '07 #5
Antoni wrote:
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/...ion-howto.html

And here is a howto as well:

http://www.howtoforge.com/mysql_database_replication

Good luck!

Jonathan
Mar 24 '07 #6
Thanks for your help!

Antoni

Mar 26 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Terry A. Haimann | last post: by
7 posts views Thread by Adam Smith | last post: by
reply views Thread by Adam Smith | last post: by
reply views Thread by mcstayinskool | last post: by
3 posts views Thread by Christopher Mouton | last post: by
6 posts views Thread by Todd Cary | last post: by
6 posts views Thread by Robert Blackwell | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.