--opt

[qxh]

What does the --opt do when doing a mysqldump please?

Thanks,
Scott.

[Babylon]

Well, if this makes any sense ( :p ):

Same as --quick --add-drop-table --add-locks --extended-insert --lock-tables. Should give you the fastest possible dump for reading into a MySQL server.
Don't buffer query, dump directly to stdout. Add a drop table before each create statement Add LOCK TABLES before and UNLOCK TABLE after each table dump. (To get faster inserts into MySQL.) Use the new multiline INSERT syntax. (Gives more compact and faster inserts statements.) Lock all tables before starting the dump. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. Please note that when dumping multiple databases, --lock-tables will lock tables for each database separately. So using this option will not guarantee your tables will be logically consistent between databases. Tables in different databases may be dumped in completely different states.

[qxh]

OK, thanks. So it's better? I'm going to be setting up some cron jobs later and was wondering if it's best to use opt or not please? Thanks.

[Babylon]

i'd say using --opt you get the best sql dump :)
I've got a PHP script i run as a cron job somewhere which executes mysqldump using that --opt, then gzips the file to save space. It also deletes backups made over 7 days ago... It's quite handy :D

[qxh]

Cool, that sounds good. :D I'll most likely add them later, thanks for your help!

[qxh]

OK, when dumping a database it asks for the password, so I'm not sure what command to use for a cron job?

mysqldump --opt -ugamewiza_vb -p gamewiza_vb > /home/gamewiza/vb.sql

[seanf]

OK, when dumping a database it asks for the password, so I'm not sure what command to use for a cron job?

mysqldump --opt -ugamewiza_vb -p gamewiza_vb > /home/gamewiza/vb.sqlmysqldump --opt -ugamewiza_vb -pPASSWORD_HERE gamewiza_vb > /home/gamewiza/vb.sql

You might want to compress the file too:

mysqldump --opt -uyour_username -pyour_password database_name | gzip > /path/to/dump/file.gzSean :)

[qxh]

Gzip = Perfecto! Thanks!

[qxh]

OK, now when adding cron jobs via cPanel it overwrites the other I've just added, so does anyone know how to do it via SSH, I've heard of "crontab -e" or something.

Does that mean I do:

crontab -e 0 0 * * * mysqldump --opt -ugamewiza_vb -p123456 gamewiza_vb | gzip > /home/gamewiza/backups/gamewiza_vb/daily-00-00.gz

Thanks.

[Babylon]

When you go to the Cron section in the CPANEL there should see your existing cron jobs plus one extra blank box where you can add another cron job. Press save, repeat the process ;) Tad slow, but that's CPANEL.

Just a warning, if your cron job happens to use quotes CPANEL also screws that up ;)

I'm not sure how to add a cron job using SSH and i don't currently have a server which allows me access to it :mad: !

[qxh]

Ok, thanks. Yeah, that's what it's like in cPanel, though it overwrites it (trust me, I'm an expert when it comes to cpanel which is why I'm on support at 2 hosting companies). ;)

[Babylon]

Ok, thanks. Yeah, that's what it's like in cPanel, though it overwrites it (trust me, I'm an expert when it comes to cpanel which is why I'm on support at 2 hosting companies). ;)
Ah ;) I'm not a big time user of CPANEL so i've yet to find more of the nastly little quirks it has (found enough already :p ) :D

Shame it's like the standard with most hosting companies but i guess it's 'user-friendly' ...

[qxh]

Yup..

Anyhow, anyone know the SSH to do that please?

Thanks!

[Raz Meister]

login to ssh as the user your want the cron to run as.

At the prompty try 'crontab -e'. This will bring up the default editor with all your cron jobs.

Enter your cron line appropriately. Then save.

[qxh]

Thanks, I can't find where to type the new one though, LOL sorry.

[qxh]

* bump * :)

[Steve Machol]

It opens it in your default editor. Just edit it like any other text file. Make sure the cron job stays on it's own line.

[qxh]

I've ended up just changing cPanel style thru root, oh well, works now. Thanks Steve and everyone else!