Why is mysqldump failing?

SELECT count(*) from table gives me 3296869 rows.

The table only contains 4 columns, storing dropped domains. I tried to dump the sql through:

$backupFile = $dbname . date("Y-m-d-H-i-s") . '.gz';
$command = "mysqldump --opt -h $dbhost -u $dbuser -p $dbpass $dbname | gzip > $backupFile";

However, this just dumps an empty 20 KB gzipped file. My client is using shared hosting so the server specs and resource usage aren't top of the line.

I'm not even given ssh access or access directly to the database so I have to make queries through PHP scripts I upload via FTP ( SFTP isn't an option, again ).

Is there some way I can perhaps sequentially download portions of it, or pass an argument to mysqldump that will optimize it?

I came across http://jeremy.zawodny.com/blog/archives/000690.html which menti开发者_运维技巧ons the -q flag and tried that but it didn't seem to do anything differently.

EDIT: Just came up with the idea that perhaps mysqldump isn't installed and it only does the gzip, but system isnt complaining.. trying to error check..

EDIT #2: which mysqldump returned /usr/bin/mysqldump so it's there.

---

Try to use this command (without spaces):

$command = "mysqldump --opt -h $dbhost -u$dbuser -p$dbpass $dbname | gzip -c > $backupFile";

In your case mysqldump trying to dump table $dbname from database $dbpass and dont finds it.

Also you can use flag --single-transaction if you use InnoDB tables.

---

I have actually seen better results on shared hosts using a php script like this: http://davidwalsh.name/backup-mysql-database-php.

Simply put, shared hosts are not friendly with this kind of stuff. If mysqldump is on the system, then it might be auto-killed once the system sees the CPU/RAM start to spike. It's less likely to kill a php process, but still might. YMMV.

You should also try msqldump without gzip. It could easily be that gzip is the culprit here. Shared hosts are very strange sometimes.

---

This is how I backup I DB:

mysqldump -h $dbhost -u $dbuser -p $dbpass $dbname | bzip2 -c > /tmp/databasebackup.sql.bz2

And this is how I restore DB from a backup file:

bunzip2 < databasebackup.sql.bz2 | mysql -u$dbuser -p
or:
mysql -u$dbuser -p < databasebackup.sql

Hope it helps.