Syntax for using mysqlcheck to optimize?

[beansbaxter]

At the user level, DA has an option under MySQL to optimize the database.

How to perform this functionality at the command line for large databases?

mysqlcheck command?

Thanks.

 

[zEitEr]

Hello,

For a correct syntax you might check the official docs: https://dev.mysql.com/doc/refman/8.0/en/mysqlcheck.html

 

[beansbaxter]

Got it.

I saw in a very very very old post from @smtalk about using the DA admin password to accomplish this, but wasn't sure about this still being applicable today.

I was able to accomplish it from the command line and it worked good: mysqlcheck -o DB_NAME -u USER_NAME -p

Thank you.

 

[zEitEr]

You might add this:

 --defaults-file=/usr/local/directadmin/conf/my.cnf

 

[Wanabo]

I use a cron for optimizing mysql tables.
\etc\cron.daily\sqloptimize.sh

#!/bin/bash
MAILTO=root
mysqlcheck -uda_admin -p`grep "^passwd=" /usr/local/directadmin/conf/mysql.conf | cut -d= -f2` --auto-repair --optimize --all-databases

Edit: Fixed typo in crond.daily, should be cron.daily

 

[zEitEr]

I use a cron for optimizing mysql tables.

For others who might want the same, I would not recommend doing this without understanding all possible consequences. You might face long running locks on big tables and issues with scripts, sites, etc. Before setting the same crontask, you are advised to test online and see how fast the process goes and whether or not it causes downtimes and/or high load on your server.