Essential MySQL Administration Bash Scripts for Linux: Backup, Optimize, Monitor & More

In Linux environments, routine MySQL administration can be streamlined with Bash scripts. The following examples cover ten essential tasks, each presented as a self‑contained script that you can adapt by replacing placeholder values such as your_mysql_user, your_mysql_password, your_database_name, and the backup directory path.

Backup database:

#!/bin/bash
BACKUP_DIR="/path/to/backup/directory"
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"
DATE=$(date +%Y%m%d%H%M%S)

mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE > $BACKUP_DIR/$DATABASE-$DATE.sql

Optimize and repair tables:

#!/bin/bash
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"

mysqlcheck -u $MYSQL_USER -p$MYSQL_PASSWORD --auto-repair --optimize $DATABASE

Delete expired backup files (keep last 7 days):

#!/bin/bash
BACKUP_DIR="/path/to/backup/directory"
DAYS_TO_KEEP=7

find $BACKUP_DIR -type f -mtime +$DAYS_TO_KEEP -exec rm {} \;

Monitor MySQL process list:

#!/bin/bash
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

mysqladmin -u $MYSQL_USER -p$MYSQL_PASSWORD processlist

Show extended MySQL status information:

#!/bin/bash
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

mysqladmin -u $MYSQL_USER -p$MYSQL_PASSWORD extended-status

Display size of each database (in MB):

#!/bin/bash
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"

mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "SELECT table_schema AS 'Database Name', SUM(data_length + index_length) / 1024 / 1024 AS 'Size (MB)' FROM information_schema.TABLES GROUP BY table_schema;"

Export only the schema (no data) of a database:

#!/bin/bash
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"

mysqldump -u $MYSQL_USER -p$MYSQL_PASSWORD --no-data $DATABASE > schema.sql

Import a previously exported schema file:

#!/bin/bash
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"
DATABASE="your_database_name"

mysql -u $MYSQL_USER -p$MYSQL_PASSWORD $DATABASE < schema.sql

Lock all tables for a consistent backup:

#!/bin/bash
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "FLUSH TABLES WITH READ LOCK;"

Unlock tables after the backup is complete:

#!/bin/bash
MYSQL_USER="your_mysql_user"
MYSQL_PASSWORD="your_mysql_password"

mysql -u $MYSQL_USER -p$MYSQL_PASSWORD -e "UNLOCK TABLES;"

Before using these scripts, replace all placeholder values (e.g., your_mysql_user , your_mysql_password , your_database_name , /path/to/backup/directory ) with the actual credentials and paths for your environment.