MySQLdump results in lots of commented lines, no real content

Question

I am trying to write a PHP program to automatically created backups of MySQL tables as sql files on the server:

$backup = "$path/$tablename.sql";
$table = "mydbname.mytablename";

exec(
  sprintf(
    '/usr/bin/mysqldump --host=%s --user=%s --password=%s %s --quick --lock-tables --add-drop-table > %s',
    $host,
    $user,
    $password,
    $table,
    $backup
  )
);

All that I get in the resulting .sql file is this:

-- MySQL dump 10.10
--
-- Host: localhost    Database: mydbname.mytablename
-- ------------------------------------------------------
-- Server version   5.0.27

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

It seems fishy that the results list Database:mydbname.mytablename.

I don't know where to start looking. My host, in helping my set up the program initially, said I would need to deactivate safe_mode (was already done) and give access to the binary (I don't know what this means).

Since the host takes two days to reply every time I ask a question, I'd like to resolve this myself if possible.

Answer 1

Hi,

You mysqldump command is like this :

/usr/bin/mysqldump --host=HOST --user=USER --password=PASSWORD TABLE --quick --lock-tables --add-drop-table

Looking at the manual of mysqldump, I would say that it will think that TABLE is actually the name of your database (quoting) :

There are three general ways to invoke mysqldump:

shell> mysqldump [options] db_name [tables]
shell> mysqldump [options] --databases db_name1 [db_name2 db_name3...]
shell> mysqldump [options] --all-databases

Apparently, you have to put the name of the database before the name of the table you want to dump.

So, for instance, something like this might do the trick :

/usr/bin/mysqldump --host=HOST --user=USER --password=PASSWORD DBNAME TABLE --quick --lock-tables --add-drop-table

Hope this helps !

EDIT : I actually made a quick test, using "databasename.tablename", like you did :

mysqldump --user=USER --password=PASSWORD --no-data DBNAME.TABLENAME

I'm getting the same kind of output you have... But I also have an error :

mysqldump: Got error: 1102: Incorrect database name 'DBNAME.TABLENAME' when selecting the database

Which, I'm guessing, is going to the error output, and not the standard output (you are only redirecting that second one to your file)

If I'm using :

mysqldump --user=USER --password=PASSWORD --no-data DBNAME TABLENAME

Everything works OK : no error, and I have the dump.