.::: Script backup Daily MariaDB Windows :::.

  

1. check
SHOW VARIABLES LIKE 'datadir';
SHOW VARIABLES LIKE 'basedir';

C:\Windows\system32>mariadb -uroot -proot
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 11
Server version: 10.11.11-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SHOW VARIABLES LIKE 'datadir';
+---------------+--------------------------------------+
| Variable_name | Value                                |
+---------------+--------------------------------------+
| datadir       | C:\Program Files\MariaDB 10.11\data\ |
+---------------+--------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE 'basedir';
+---------------+---------------------------------+
| Variable_name | Value                           |
+---------------+---------------------------------+
| basedir       | C:\Program Files\MariaDB 10.11\ |
+---------------+---------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]>
 

2. create new script

========

@echo off

:: ubah usern n pass
set dbUser=root
set dbPassword="root"
set backupDir="C:\backup\maria"
set mysqldump="C:\Program Files\MariaDB 10.11\bin\mysqldump.exe"
set mysqlDataDir="C:\Program Files\MariaDB 10.11\data"
set zip="C:\Program Files\7-Zip\7z.exe"

:: dapetin tanggal
for /F "tokens=2-4 delims=/ " %%i in ('date /t') do (
    set mm=%%i
    set dd=%%j
    set yy=%%k
)

if %mm%==01 set Month="Jan"
if %mm%==02 set Month="Feb"
if %mm%==03 set Month="Mar"
if %mm%==04 set Month="Apr"
if %mm%==05 set Month="May"
if %mm%==06 set Month="Jun"
if %mm%==07 set Month="Jul"
if %mm%==08 set Month="Aug"
if %mm%==09 set Month="Sep"
if %mm%==10 set Month="Oct"
if %mm%==11 set Month="Nov"
if %mm%==12 set Month="Dec"

set dirName=%dd%_%Month%_%yy%
set fileSuffix=%dd%-%Month%-%yy%

echo "dirName"="%dirName%"


pushd "%mysqlDataDir%"

:: create backup folder if it doesn't exist
if not exist %backupDir%\%dirName%\   mkdir %backupDir%\%dirName%

:: backup
for /d %%f in (*) do (
    if /I NOT "%%f"=="performance_schema" if /I NOT "%%f"=="sys" (
        echo processing database "%%f"
        
        %mysqldump% -u%dbUser% -p%dbPassword% --host="localhost" -CfQq --max-allowed-packet=1G --hex-blob --order-by-primary --single-transaction --routines=true --triggers=true --no-data=false --log-error=%backupDir%\%dirName%\%%f_log.txt %%f > %backupDir%\%dirName%\%%f.sql

        %zip% a -tgzip %backupDir%\%dirName%\%fileSuffix%_%%f.sql.gz %backupDir%\%dirName%\%%f.sql
        del %backupDir%\%dirName%\%%f.sql
    )
)
popd

========

3. run 
 

4. result 
 

5. for retore manual 

"C:\Program Files\7-Zip\7z.exe" x 30-Jan-2026_teguhth.sql.gz -so | "C:\Program Files\MariaDB 10.11\bin\mysql.exe" -uroot -proot -f simpledb

6. for backup manual 

"C:\Program Files\7-Zip\7z.exe" a -tgzip full_teguhth.sql.gz teguhth.sql