Backup with mysqldump takes hours, database is not that big

Hello, I've been having some problems making database backups. I've searched all around and I notice that to most people it only takes a few minutes to backup huge databases (1+ gb).

My database is around 350mb and it can take up to 4-5 hours to backup, which I guess it not normal at all.

My server has a dual Xenon with 4gb ram, using mysql 5.0.27-1.fc6 and php 5.1.6-3.6.fc6. I am using Fedora Core6 with Plesk.

The database is around 350mb, with around 1.1 million rows. To backup I use

mysqldump -uroot -p database > backup.sql

Code (markup):

As I said, the above takes hours. I guess it should take minutes?
What am I doing wrong?

 

should take seconds IMO

I have 'dumped' over a million rows in a matter of seconds before.

Try one or both of the following.

1. Login to your database (mysql -uuser -ppassword)
use (my database);
select count(*) from thebiggesttable;

and take a look at the response time, depends on the number of records, but it should be within a couple of seconds.

2. you could try running the mysqldump from another server (with a good net connection) which would drag the data down - but that might take a while, but maybe not 4 or 5 hours.

have fun

 

+----------+
| count(*) |
+----------+
| 138733 |
+----------+
1 row in set (0.00 sec)

Seems fast enough for me.

 

I would say

Next is a mysql dump from another machine.

Is it your sever or a shared/hosted one?

 

My forum database is 259 Tables, 861511 Records, 426.7 MB and takes 17 minutes to backup with my mysqldumper.

Taking 4 - 5 hours to back up a 350mb database is NOT normal.

If you are looking for a better solution, check out - http://www.mysqldumper.de/en/ It takes a little while to set up because you have to encrypt a .htaccess file to protect the back up directories. But once you have it up and going, it works great. This program lets you backup, download and restore your databases with a single user interface.

Try that program and see if it does a better job.

 

That script wont even load when I try to use my database, works for the smaller ones tho.

 

What is the value for max_allowed_packet in mysql configuration file (my.ini/my.cnf)? If it is small, try increasing the value and restart mysql.

Set the value near 100MB under [mysqldump] section of configuration file

max_allowed_packet=100M

Code (markup):