One line Mysql backup

Roosta21 · 11-28-2007, 04:56 AM

Hi.

I am attempting to backup my Mysql database.. by adding a command to a script

mysqldump --user testuser --password testpassword --all-databases> alldatabases.sql

But when i run this command as above it doesn't back up the databases it just creates an sql file that is about 203 bytes.. with the below info in

Usage: mysqldump [OPTIONS] database [tables]
OR mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

If i run the command without the password

mysqldump --user testuser --all-databases> alldatabases.sql

Which then prompts me for a password after i put in the password it backs up fine and creates 60 meg sql file..

Is this a mysql setting i can change?

bathory · 11-28-2007, 08:35 AM

You must use -p followed immediately with the password (i.e. without a space). The same goes for the --user option. So you can change your command to:

Code:

mysqldump -utestuser -ptestpassword --all-databases> alldatabases.sql

weisso5 · 11-28-2007, 10:47 AM

Another Hint:

When doing all databases dump the file could be really large, the command i use to keep the size down is:

mysqldump -u username -ppassword --all-databases | bzip2 > /mnt/backup/database_backups/mysql_backup.sql

trickykid · 11-28-2007, 12:34 PM

When using --password you need to use it like:

--password=<password>

weisso5 · 11-29-2007, 09:30 AM

I forgot to add to my earlier hint that you can change bzip2 into gzip if you want but the compression will be less.

predatorz · 11-29-2007, 10:01 AM

Script for MySQL backup, works fine.

http://sourceforge.net/projects/automysqlbackup/

Roosta21 · 12-06-2007, 10:23 AM

Guys thank you very much for this advice it worked a treat!