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.
Let's get started by following the ensuing steps:
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
sysusercreated just for this purpose.Create a user in MariaDB that has
SELECTandINSERTprivileges on all the databases. Those are the privileges that are needed formysqlcheck. For this recipe, we'll name this usermaint.Create a
.my.cnffile at/home/sysuser/.my.cnf(or wherever sysuser's home is located) with the following contents:[client] user = maint password=maintuserpassword
Next, change the mode of the
.my.cnffile to only be readable by the sysuser:sudo chmod 600 /home/sysuser/.my.cnfAdd 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
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.
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.