- MariaDB Cookbook
- Daniel Bartholomew
- 477字
- 2021-07-16 12:19:23
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:
- 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.cnf - 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.
- iOS面試一戰到底
- Java系統分析與架構設計
- Xcode 7 Essentials（Second Edition）
- CentOS 7 Linux Server Cookbook（Second Edition）
- 編譯系統透視：圖解編譯原理
- Swift語言實戰精講
- 第一行代碼 C語言（視頻講解版）
- Building Machine Learning Systems with Python（Second Edition）
- C++編程兵書
- JavaScript程序設計：基礎·PHP·XML
- C指針原理揭秘：基于底層實現機制
- 交互式程序設計（第2版）
- C++程序設計教程
- Python數據預處理技術與實踐
- Java程序設計及應用開發