Checking and optimizing tables automatically with mysqlcheck and cron

The mysqlcheck command can check, repair, and optimize tables. When paired with cron, this bit of regular maintenance can be automated. This recipe is only for Linux operating systems.

How to do it…

Let's get started by following the ensuing steps:

1. Create a new user on the server or choose an existing user account. For this recipe, we'll say that we have a user called sysuser created just for this purpose.

2. Create a user in MariaDB that has SELECT and INSERT privileges on all the databases. Those are the privileges that are needed for mysqlcheck. For this recipe, we'll name this user maint.

3. Create a .my.cnf file at /home/sysuser/.my.cnf (or wherever sysuser's home is located) with the following contents:

   [client] 
   user = maint 
   password=maintuserpassword

4. Next, change the mode of the .my.cnf file to only be readable by the sysuser:

   sudo chmod 600 /home/sysuser/.my.cnf
   

5. Add the following lines of code to /etc/cron.d/mariadb (create the file if it doesn't exist):

   # m h dom mon dow user command 
   15 23 * * 1   sysuser /usr/bin/mysqlcheck -A --auto-repair 
   15 23 * * 2-7 sysuser /usr/bin/mysqlcheck -A --optimize 

How it works...

The /etc/cron.d/ folder contains cron snippet files. The cron daemon looks in this folder and executes the commands just as it does for the user crontab files. The one key difference is that because this is a system folder and not a user folder, we need to tell cron which user to run the command as, which we do between the datetime command and the actual command.

When mysqlcheck is run, like other MariaDB utilities, it will automatically check for a .my.cnf file in the home directory of the user running it and will pick up options in the [client] section of that file. This is a perfect place to stick the login information as we can make the file readable only by that user. This way, we don't need to specify the username and password of our database maintenance user on the command line.

Two commands are run by the recipe. The first command runs only once a week, and it checks every database and autorepairs any problems it finds. The second command runs every other day of the week and optimizes the tables in every database.

There's more…

The mysqlcheck program has many options. Refer to https://mariadb.com/kb/en/mysqlcheck/ or run the command with --help for a complete list.

One thing to note is that the --analyze (-a), --check (-c), --optimize (-o), and --repair (-r) options are exclusive. Only the last option on the command line will be used.

Security

Using a nonroot user to run mysqlcheck automatically is a good security precaution. To make the sysuser even more secure, lock the account so that it can't log in. Refer to our distribution documentation for how to do this.