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.

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
0 0 votos
Nota do Artigo
Subscribe
Notify of
guest
0 Comentários
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x