trying to setup cron to optimize databases

[Dmacleo]

this is odd, when I (in ssh) manually enter

mysqlcheck -u MY_USERNAME -p --auto-repair --optimize --all-databases

and manually type password it works.

when I enter password as part of the cmd I get this:

mysqlcheck -u MY_USERNAME -p MY_PASSWORD --auto-repair --optimize --all-databases

You should give only options, no arguments at all, with option
--all-databases. Please see mysqlcheck --help for more information.

so why is it ok when manually typing password but not when password is part of the cmd?

[wimg]

Try leaving out that space between -p and password, that should do the trick. It now probably thinks the space is part of the password.

IOW, enter:

mysqlcheck -u MY_USERNAME -pMY_PASSWORD --auto-repair --optimize --all-databases

HTH, kind regards, Wim

[Dmacleo]

I'll give that a shot thanks

[Rhett]

I would recommend the following

# /usr/bin/mysqlcheck -u<username> -p<password> -ro <databasename>

[GreenLinks]

Honestly it is not wise to set this as a cron job , you should only optimise database when the board is turned off .

[Dmacleo]

board database not involved yet, different server, however I was searching here for a thread (there was one) about using cron to turn board offline then online to then run optimize by cron. in the past I had been doing it during known slow times but thats not really best way I know.

have not had a chance (pneumonia sucks) to try the password or other cmd yet.

[Rhett]

The tables lock on optimize as they do on a backup, so unless you have a very large board, it's not going to make a difference imo, both processes will complete quickly and not cause an issue if things are setup properly. If you have a very large board, as most things, different circumstances will apply.

[Dmacleo]

fwiw removing the space was the issue.

[wimg]

fwiw removing the space was the issue.

Yes, I mentioned that. I use a script to do this type of stuff. Bring board offline, optimize, dump, bring board online again, backup all new files. Works well.

Kindest regards, Wim

[Dmacleo]

I just thought I should let you know for sure it was my issue.

I need to figure out best way/time to do it. need to track my nightly backups using Dabackup plugin (on new server) for a few to see how long they take then go from there. most of my stuff is guest viewing so I do have flexibility. so sense starting it during a backup.

[wimg]

If you want to do it from a cron, what you can do is either run it from a task within IP.Board, I guess, or a normal cron.

In the latter case I'd suggest the following, and this is more or less what I do.

First create a maintenance.html page or something like that, and a index_maint.php file which can display the html. Make sure to duplicate your normal index.php file to a index_std.php file or something similar, to have a copy. In the maintenance.html page you can do some nice stuff with make-up, a message, whatever you'd like to display while doing the maintenance/optimizing.

In the cron script you now add the following lines:

1) a line to copy the index_maint.php to the index.php file, overwriting it. This will display the maintenance screen, and nobody can login to the board anymore. Sounds harsh maybe, but it is no different to switching the board offline via the acp.

2) add now any lines for optimizing, backups, and anything else you'd like to do.

3) when done, add a command to copy the index_std.php to the index.php file again (which still displays the maintenance screen) and overwriting it of course.

That's it. Works well. To make sure each command runs completely until it is finished before transfering control to the next one, which is important if you don't want the board online when the database is still being optimized, put a wait statement after each command, on a new line. That should do the trick.

HTH, kind regards, Wim

[Dmacleo]

sounds pretty well designed, thanks.