MySQL-MariaDB backups. How to automatize it
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.
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%.sql
To 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.