Check and Backup Databases

Luke Hebert

January 24, 2022 - 2 minutes read - 296 words

When you manage a batch of user databases in MySQL, running a manual check and dump on each one gets tedious fast. The two one-liners below loop over every database matching a prefix, run an integrity check, and produce a timestamped SQL dump.

Check User Databases

This loop queries the server for all databases whose name starts with example_, then runs mysqlcheck in read-only mode (-ro) against each one. Results are appended to check.log so you can review them later.

for i in $(mysql -e "show databases;" | grep 'example_' | awk '{print $1}'); do
  echo "Checking $i"
  mysqlcheck -ro "$i" >> check.log
done

Note

The -r flag performs a read-only check (no writes to the database), and -o optimizes the tables after the check. If you only want to verify integrity without optimizing, drop the -o.

Backup User Databases

This second loop does the same discovery step but calls mysqldump to write a full SQL dump for each database into the current directory. The filename includes a date stamp so you can keep multiple backups side by side.

for i in $(mysql -e "show databases;" | grep 'example_' | awk '{print $1}'); do
  echo "Dumping $i"
  mysqldump "$i" > "./$i.5.17.2011.sql"
done

Important

Replace the hardcoded date in the filename with a dynamic value (for example, $(date +%Y-%m-%d)) if you plan to run this on a schedule. Also make sure the target directory has enough free space before you start, since a full dump can be large.

Tips

Run both loops as a user that has SELECT, SHOW DATABASES, and LOCK TABLES privileges.
Redirect stderr to a log file (2>> errors.log) if you want to capture warnings separately.
For very large databases, consider adding --single-transaction to mysqldump to avoid locking tables during the dump.