Category: MySQL

MySQL automatic restore script

We had a .sql export from a MySQL databaseserver from all the databases. In our case the filenames were made up like:

mysqldump-hostname-user_database-D5-22H.sql

So I wrote a little bash script to recreate all the databases and import it’s data:

#!/bin/bash

folder=/var/sqlbackup/

for file in `ls $folder/mysqldump-*.sql`
do
echo $file
IFS='-' read -r -a array <<< "$file"
echo ${array[2]}
echo "create database ${array[2]}" | mysql -u da_admin -ppassword
mysql -u da_admin -pnAcQKhIK ${array[2]} < $file

done

After a little while all databases were full with content again 🙂

Bash script to make MySQL back-ups

This simple but effective script makes a back-up of the target SQL database and deletes databases older then 90 days. I keep the folder synchronized with other systems so the customer can reach his database with 90 days retention himself.

#!/bin/bash

cd /backups

user="mysql_user"
passwd="password"
host="localhost"
db_name="mysql_database"

backup_path="/backups"
date=$(date +"%d-%b-%Y")

umask 177

# dump the database
mysqldump --user=$user --password=$passwd --host=$host $db_name > $backup_path/$db_name-$date.sql

# zip contents
zip $db_name-$date.zip $db_name-$date.sql

# remove old backups
find $backup_path/* -mtime +90 -exec rm {} \;

echo done

 

Of course, instead of setting the username/passwd variables in the script itself you can read the DirectAdmin credentials with:

source /usr/local/directadmin/conf/mysql.conf