Essential MySQL Ops Scripts for Linux: Backup, Restore, Monitoring & Automation

MySQL is a widely used open‑source relational database, and routine Linux administration tasks such as backup, performance tuning, and monitoring can be automated with scripts. The article presents a collection of essential MySQL operational scripts with detailed examples.

Connecting to MySQL

Use the mysql client to connect: mysql -u username -p The command prompts for the password of the specified MySQL user.

Backup and Restore

Backup

Run mysqldump to export a database:

mysqldump -u username -p dbname > backup.sql

Restore

Import the dump file with:

mysql -u username -p dbname < backup.sql

Database Maintenance

Optimize Tables

Improve query performance by running:

mysql -u username -p -e "OPTIMIZE TABLE table_name;"

Clean Binary Logs

Remove old binary logs to free disk space:

mysql -u username -p -e "PURGE BINARY LOGS BEFORE 'date';"

Monitoring and Performance Tuning

Monitor Status

Use SHOW STATUS and SHOW VARIABLES to inspect performance metrics. Example for query cache hit rate:

mysql -u username -p -e "SHOW STATUS LIKE 'Qcache_hits';"
mysql -u username -p -e "SHOW STATUS LIKE 'Qcache_inserts';"

Explain Queries

Analyze execution plans with EXPLAIN:

mysql -u username -p -e "EXPLAIN SELECT * FROM table_name WHERE condition;"

Automation with Cron

Schedule regular backups via cron:

0 2 * * * mysqldump -u username -p dbname > /path/to/backup.sql

Security Practices

Protect Sensitive Information

Store passwords in configuration files or environment variables and reference them in scripts:

#!/bin/bash
# Read MySQL password from a secure file
MYSQL_PASSWORD=$(cat /path/to/mysql_config)
mysql -u username -p$MYSQL_PASSWORD dbname < backup.sql

Replication Management

Check Slave Status

Run:

SHOW SLAVE STATUS;

Switch Master/Slave

Example script to stop the slave, reset it, change master, and start replication:

# On master
mysql -u username -p -e "STOP SLAVE;"
# On slave
mysql -u username -p -e "RESET SLAVE;"
mysql -u username -p -e "CHANGE MASTER TO MASTER_HOST='new_master_host', MASTER_USER='replication_user', MASTER_PASSWORD='password';"
mysql -u username -p -e "START SLAVE;"

User and Permission Management

Create User and Grant Privileges

CREATE USER 'new_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost';
FLUSH PRIVILEGES;

Password Policy

Adjust my.cnf to enforce a stronger password policy:

[mysqld]
validate_password.policy=LOW
validate_password.length=8
validate_password.number_count=1
validate_password.special_char_count=1

Automated Backup Script

#!/bin/bash
# Set backup directory and filename
backup_dir="/path/to/backups"
backup_file="backup_$(date +%Y%m%d).sql"
mkdir -p $backup_dir
mysqldump -u username -p dbname > $backup_dir/$backup_file
# Optional: delete backups older than 7 days
find $backup_dir -type f -mtime +7 -delete

By combining these scripts, administrators can streamline MySQL maintenance, improve performance, ensure data safety, and reduce manual effort.