Bash Script to Check, Repair, Optimise and Backup MySQL database

I have a VPS (Virtual Private Server) and I have just move another domain (steakovercooked.com) onto this. I find it useful to have a script that can automatically check, repair, optimise and backup the MySQL databases. It is also nice to set this running at crontab so you don’t need to manually do it every time.

To optimise all databases:

mysqlcheck -hlocalhost -uuser -ppassword --optimize --all-databases

To repair all databases if errors:

mysqlcheck -hlocalhost -uuser -ppassword --auto-repair --check --all-databases

To backup a database and save it into *.SQL files (plain text)

mysqldump --opt -hlocalhost -uuser -ppassword database > database.sql

To compress the files into gzip format, using:

mysqldump --opt -hlocalhost -uuser -ppassword database | gzip > database.sql.gz

Then, you can put the above commands into a script file (make sure you chmod +x it) and you can use command crontab -e to edit the cron jobs (e.g. let it run once per hour).

DevOps / Site Reliability Engineering

• How to Clean Up NVM Node Versions Except One?
• Monitoring 28 VPS Machines including a Raspberry Pi with Nezha Dashboard
• Python/Bash Script to Print the Optimized Parameters for MySQL Servers
• Learn to Manage Your MySQL Database with a Python Script
• A Simple PHP Command Line Tool to Convert MySQL Tables from MyISAM to InnoDB in Specified Database
• How to Print MySQL Table Summary using PHP?
• Secure the Linux Server by Disallow the Remote Root Login (SSH and FTP and MySQL database)
• Bash Script to Check, Repair, Optimise and Backup MySQL database
• Duplicate a MySQL table - Copy Table / Duplicate Database / PHP Script
• MySQL server stopped due of out of memory exception on Ubuntu VPS
• Running Apache Server (PHP + MySQL) on Raspberry PI
• How to Optimise SQL Queries? Quick Tips
• Recovery Models in SQL Server
• Database Optimisation Script in PHP

–EOF (The Ultimate Computing & Technology Blog) —