If you have a MySQL database, most likely you already need or will need to access a backup of your database. The best way to prevent is to maintain a daily (or as needed) backup routine, and that’s exactly what we’ll learn to do in this article. We will learn how to create MySQL backup routines on Windows and Linux, our goal will be to create a script that creates a backup of our database in a zipped file with date stamp and a daily routine to run, our script will also be able to to delete files older than 7 days.
What is MySQL?
I won’t go into too much technical data about the history of MySQL, you can check it out in this link , but in short, MySQL is a database management system (DBMS), which uses the SQL language - Structured Query Language. Structured Query Language . It is currently maintained by Oracle Corporation and distributed under two licenses: LPG and Trade license .
Creating Daily MySQL Backups on Windows
We are going to learn how to create a complete script on Windows, for that you need to have previously installed MySQL and have the user data readable in the database to be copied and we will use the 7zip to compress our files.
First, let’s create the file that will contain our bank access credentials: config.cnf .
# User settings [mysqldump] user=root password=password
And now the file that will perform the entire backup process.
#Set-ExecutionPolicy -ExecutionPolicy Bypass $mysqlpath = “C:Program FilesMySQLMySQL Server 5.5bin” # Path to MySQL installation $backuppath = “C:backups” # Path to store backups $7zippath = “C:Program Files (x86)7-Zip” # Path to install 7zip $config = “C:config.cnf” # Path to file with credentials $database = “blog” # Name of our database $errorLog = “C:error_dump.log” # Path to our log file $days = 7 # Days to keep backup files $date = Get-Date $timestamp = ” ” + $date.day + $date.month + $date.year + ”_” + $date.hour + $date.minute $backupfile = $backuppath + $database + ”_” + $timestamp +“.sql” $backupzip = $backuppath + $database + ”_” + $timestamp +“.zip”
Start the backup process
CD $mysqlpath .mysqldump.exe —defaults-extra-file=$config —log-error=$errorLog —result-file=$backupfile —databases $database /c
Start the 7zip compression process
CD $7zippath .7z.exe to -tzip $backupzip $backupfile
Delete the raw file
Del $backupfile
Delete old files
CD $backuppath $oldbackups = gci *.zip*
for($i=0; $i -lt $oldbackups.count; $i++){ if ($oldbackups[$i] .CreationTime -lt $date.AddDays(-$days)){ $oldbackups[$i] | Remove-Item -Confirm:$false } }
Task Scheduler - Creating the Schedule
To create the schedule in Windows you can use the Task Scheduler.
Creating Daily MySQL Backups on Linux
Let’s now learn how to create a complete script on Linux, for that you need to have previously installed MySQL and have the user data readable in the database to be copied. We will also use the bzip2 to compress our files, if you don’t want to use this functionality, comment out lines 28 and 29.
#!/bin/bash
DB_NAME=‘dbname’ # Database name DB_USER=‘dbuser’ # Database User DB_PASS=‘dbpass’ # Bank password DB_PARAM=‘—add-drop-table —add-locks —extended-insert —single-transaction -quick’ # Parameters for backup https://dev.mysql.com/doc/refman/8.0/en /mysqldump.html
MYSQLDUMP=/usr/bin/mysqldump # Path to mysqldump binary BACKUP_DIR=/backup/mysql # Path to save backups DAYS=7 # How many days of backups do you want to keep
DATE=`date +%Y-%m-%d` BACKUP_NAME=mysql-$DATE.sql BACKUP_TAR=mysql-$DATE.tar BACKUP_BZ2=mysql-$DATE.tar.bz2
echo “Starting the backup process…”
#Generating sql file echo “Backing up the database $DB_NAME to $BACKUP_DIR/$BACKUP_NAME” $MYSQLDUMP $DB_NAME $DB_PARAM -u $DB_USER -p$DB_PASS > $BACKUP_DIR/$BACKUP_NAME
Compressing tar file
echo “Consolidating file into tar…” tar -cf $BACKUP_DIR/$BACKUP_TAR -C $BACKUP_DIR $BACKUP_NAME
Compressing file with bzip2
echo ” — Compressing file in bzip2 …” bzip2 $BACKUP_DIR/$BACKUP_BZ2
Deleting raw files
echo ” — Deleting unnecessary files …” rm -rf $BACKUP_DIR/$BACKUP_NAME
Deleting older files
find /backup/mysql -name “*.tar.bz2” -type f -mtime +$DAYS -exec rm -f {} ;
Save the file as mysql_backup.sh; Give him execute permission with the command chmod +x mysql_backup.sh ; Now run your backup file with ./mysql_backup.sh .
Crontab - Creating the Schedule
To create the routine that runs our daily backup on Linux, let’s use the crontab .
Open your Crontab:
$crontab -e
Now let’s create a schedule that runs every day at 00:00, to do this add the following line at the end of the file:
0 0 * * * sh ~/mysql_backup.sh