MySQLDump Broken

[feldon27]

Any time I run MySQLDump, no matter what parameters it puts, it gives me this bizarre error:

mysqldump: unknown option '--pico httpd.conf'

[feldon27]

Not good!!

mysql -u username -p dbname > file.sql
doesn't work either!!!

I do nightly MySQL backups and none of it has worked since CPAN changed/broke a few weeks ago!!!

[feldon27]

Thank God for MySQLAdmin. That helps me backup my smaller DB but I also have a much bigger DB (400MB).

[feldon27]

For some reason, the backup part of /etc/my.cnf changed from:

[mysqldump]
quick
max_allowed_packet = 16M

to

[mysqldump]
quick
max_allowed_packet = 16M
pico httpd.conf

I guess there's always the possibility of fat-fingered typing.

[AndyReed]

mysqldump: unknown option '--pico httpd.conf'

I suggest you check this page out for more information on how to "properly" use the command mysqldump:
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html

[feldon27]

No matter what I did, any time I typed mysqldump, regardless of parameters including something straightforward as:

mysqldump --opt -u feldon23 -p feldon23_dbname > backup.sql
or
mysql -u feldon23 -p feldon23_dbname > backup.sql

it would spit out the --pico httpd.conf error.

Anyway, it was all a case of operator error. I'm very familiar with mysqldump (when it's not screwed up by a bad config) and I have automated backup scripts that backup, gzip, and then delete the backup from 2 days ago every night:

fname=forumbackup-`eval date +%Y-%m-%d`.sql
echo "Backing up forum..."
mysqldump --opt -quick -u feldon23 -p ******** feldon23_forum > /home/feldon23/backups/$fname
echo "Gzipping..."
gzip /home/feldon23/backups/$fname
a=`eval date +%d`
a=`expr $a - 2`
fname=forumbackup-`eval date +%Y-%m-`$a.sql.gz
rm /home/feldon23/backups/$fname
fname=forumbackup-`eval date +%Y-%m-`0$a.sql.gz
rm /home/feldon23/backups/$fname

[AndyReed]

OK!

Before you backup your forum DB, run

mysqlcheck -s -u root -p databasename

To make sure that there's no corrupted tables. You may ignore HEAP tables like cp sessions and sessions table messages.

Now, if there're corrupted tables, you can repair'em with the following command:

mysqlcheck -q -r -u root -p DB_NAME
then rerun
mysqlcheck -s -u root -p DB_NAME

When done, try this command and see what's gonna happen:
mysqldump --quick --add-drop-table --add-locks --extended-insert --lock-tables --opt -uroot -pPASSWORD_HERE NAME_OF_DB > db-sqldump.sql

If you wish to restore
mysql -uroot -pPASSWORD_HERE NAME_OF_DB < db-sqldump.sql

[trparky]

Try removing that "pico httpd.conf" line from your /etc/my.cnf and then try.

[AndyReed]

For some reason, the backup part of /etc/my.cnf changed from:

[mysqldump]
quick
max_allowed_packet = 16M
pico httpd.conf

I guess there's always the possibility of fat-fingered typing.

Very good point trparky I thought feldon27 said that was a backup of /etc/my.cnf.

[feldon27]

When I said the 'backup part' of my.cnf, I meant, of course, the [mysqldump] section of /etc/my.cnf. There isn't another MySQL config file I should be editing instead of /etc/my.cnf is there?

Of course I have removed the pico httpd.conf part and resumed automated backups of all my databases and have already downloaded copies of all of the gzipped backups. It amazes me how few people do off-site backup to their PC.