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=F3 Mola

Re: mysqldump blocks httpd server

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

Re: mysqldump blocks httpd server

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=F3 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

Re: mysqldump blocks httpd server

>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.

Re: mysqldump blocks httpd server

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.

Re: mysqldump blocks httpd server

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/en/replication-howto.htm l

And here is a howto as well:

http://www.howtoforge.com/mysql_database_replication

Good luck!

Jonathan

Re: mysqldump blocks httpd server

Thanks for your help!

Antoni