Problems with mysql dump

hyphens causing problem

[dreamcatcher]

Hi Guys,

I have successfully backed up some of my databases using mysqldump. Unfortunately, if I try and run any of the back up files, they fail because of the hyphens.

ie:

-- MySQL dump 9.10
--
-- Host: localhost Database: databasename
-- ------------------------------------------------------
-- Server version4.0.18-standard

rest of backup file...

then when its run:

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '--' at line 2

If I replace the hyphens with hash marks, then it runs fine.

this works ok:

## MySQL dump 9.10
##
## Host: localhost Database: databasename
## ------------------------------------------------------
## Server version4.0.18-standard

rest of backup file...

How can I replace the hyphens with hash marks? I have taken a look on the sql website, but can`t seem to see anything.

Thanks.

[coopster]

What command are you using to run the reload?
And are you moving the file to another server from somewhere? If so, are you using FTP to move the file? Make sure you transfer the file using binary mode to keep the space after the double dashes in place. This is key.

Comment Syntax [dev.mysql.com]

[ZibingsPrez]

Uh...is this a PHP question, or a MySQL question?

[m_shroom]

The first format is how it looks using MySql comand line utilties writes or reads them.

The second format is how phpMyAdmin would read or write them.

[dreamcatcher]

Thanks for the help guys.

What I have actually done is created a little script to backup the databases at the click of a button in my website admin area. I am using sprintf and exec to execute the command and then write the file to a directory. I have then set up a cron job to run it periodically.

exec(sprintf('mysqldump --host=%s --user=%s --password=%s %s -q -l -c --quick --lock-tables --add-drop-table> %s', $host, $user, $password, $database, $file));

This is basically the syntax that writes the data and it seems to work fine. As my databases aren`t too large, I haven`t used gzip. I haven`t included all the code, but you will get the idea.

To then restore the file, I run a similar command:

exec(sprintf('mysql --host=%s --user=%s --password=%s %s < %s', $host, $user, $password, $database, $file));

If I use --skip-comments, then everything works fine, so I could just go with that. However if I decide to incorporate the backup feature in a script, I would prefer the comments in place.

Can I just add select1+1 to the command? Would that work?

[SofterLogic UK]

$sql=preg_replace("/\n\-\-/ig","##",$sql);