This entry is not exclusive to a particular version of MySQL, but can be used by anyone, including MariaDB databases. It’s about executing database backups using mysqldump, and where I’ll tell you my experience, and some of the Windows scripts to automate the tasks, which always comes in handy.
In my case, I usually have several scripts depending on the needs, but they all have the same objective: to back up MySQL or MariaDB databases.
A script to backup MySQL-MariaDB databases
The script I present you in this entry is the one I’m using to work with my thesis, and the objective is to ask which database is going to be backed up, and proceed to do it separating the structure from the data. In case you need another configuration, just adjust the parameters of mysqldump to suit.
The first thing you need to prepare is:
- Where is your mysqldump file (set mysqldump)
- The IP address to your server (or the name)
- The name of the text file that saves summary of what has been done
- a temporal directory
In my case, the main objective of the script has to split the backup in two files: the structure (STRU_) on one and the data (DATA_) in another. In the middle, I must empty the temporal directory, because it is the place where several backups are stored for each database. If the directory doesn’t exist, it creates it.
The script
This is the code I am using. You are free yo use it, and make all modifications that you need to fit to you. Save if as a .bat file and you can use from the command line, or whatever you want or need.
@echo off
rem Version 2.3.0 - 20200923 - Backup
SET mysqldump="D:\xampp\mysql\bin\mysqldump.exe" --defaults-extra-file=D:\xampp\config.cfg
echo you_database
echo you can add much more
SET /p BDD=Base de datos?
SET fecha=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%
SET FH=%time:~0,2%%time:~3,2%%time:~6,2%
SET mkdirectorio=d:\backup
set rutadir1=%mkdirectorio%\%fecha%\STRU
set rutadir2=%mkdirectorio%\%fecha%\DATA
set opcion1=--skip-comments --no-data --add-drop-database --add-drop-table --routines --events
set opcion2=--skip-comments --complete-insert --extended-insert
@if exist %mkdirectorio% goto saltando
mkdir %mkdirectorio%
@ECHO ON
@ECHO Creado ... %mkdirectorio%
@ECHO OFF
:saltando
rem Elimina archivos Backup temporal
cd %mkdirectorio%
@%mysqldump% %opcion1% --result-file="%rutadir1%_%BDD%.sql" --databases "%BDD%"
@%mysqldump% %opcion2% --result-file="%rutadir2%_%BDD%.sql" --databases "%BDD%"
cd ..
echo %rutadir1%_%BDD%.sqlTo end with, I have included several databases in the “echoes” section to remember them, and I divided the process in two: structure for one side, and data on the other.
When the process is finish, you have a remember of where your backup is ready!
And that’s all.