SSH mysql backup

[Joe 90]

Evening, I'm trying to make a backup of a databse via SSH

so, i've connected to the database;

mysql -h mysql14.streamline.net -u USERNAME -p

and then keep tryin this command but it doesn't ask for my password so obviously isn't working;

mysqldump --opt -Q -shuwarrior1 -p shuwarrior1 > /backups/backup.sql

I've never used SSH before so don't really know how to figure it out :/

[Mark]

mysqldump --opt -Q -u username -p database | gzip -9 > backups/backup.sql.gz

username and database as required. Note the spaces after -u and -p. It will ask for the password.

PS - I added on compression for you. May as well do it all in one go. If you don't want that, omit the gzip part.

[Garp]

Originally Posted by |Show|

so, i've connected to the database;

mysql -h mysql14.streamline.net -u USERNAME -p

and then keep tryin this command but it doesn't ask for my password so obviously isn't working;

mysqldump --opt -Q -shuwarrior1 -p shuwarrior1 > /backups/backup.sql

Woah back a bit.. maybe I'm misreading this. Are you runing the mysql command, and then once a mysql prompt is up running the mysqldump command? If so that'll never work, mysqldump should be run from the command line.

SSH on to the server, you should have a command prompt that ends with the "#" symbol.

From there run the mysqldump command like Mark suggested. I tend to do things slightly differently from Mark but it depends on what you're trying to do with the backup, more often than not I'm backing up databases in an environment where it's not too unusual for someone to have messed up a slave ('read only') server.

To try to be a little nicer and make the backup run at a lower priority to standard tasks it's worth prefixing it with "nice", e.g. using Mark's command:

nice mysqldump --opt -Q -u username -p database | gzip -9 > backups/backup.sql.gz

[Mark]

Good spot there, except it'll probably be shared hosting, so the prompt will be '$', not '#'.

PS - Will that 'nice' actually do anything? It won't be mysqldump taking the CPU resources.

[Garp]

Originally Posted by Mark

PS - Will that 'nice' actually do anything? It won't be mysqldump taking the CPU resources.

I think that's one that'll be debated back and forth. Depends on what the server is doing. mysqldump still does require a chunk of processing power whilst the server is working. Most often at the moment I'm dealing with all-in-one systems, full on LAMPs. Dropping the priority on mysqldump seems to help keep Apache far more responsive.

[Joe 90]

yeah its a shared hosting package, so I've got the SSH access on my domains, and then the mysql is on a different server.

So I assumed that the mysqldump command wouldn't be able to backup the server if its being ran from my domain because it doesn't contain anything relating to the mysql server, just a username and database name which has nothing to do with my domain :/

Was a bit confusing using documentation from my host and also vB!