Backup MySQL - Dump DBs, Users, Grants (Windows Batch Script)

backup_mysql.bat

@echo off

set mysql_usr=root
set mysql_pwd=

set pasta_mysql_bin=C:\xampp\mysql\bin
set pasta_backup=C:\xampp\mysql\backup\sql

set dia=%date:~0,2%
set mes=%date:~3,2%
set ano=%date:~6,4%


if not exist "%pasta_backup%\_users" mkdir "%pasta_backup%\_users"
"%pasta_mysql_bin%\mysql.exe" -u%mysql_usr% -p%mysql_pwd% -s -N -e "SELECT CONCAT('CREATE USER \'', User, '\'@\'', Host, '\' IDENTIFIED BY \'', authentication_string, '\'\;' ) AS User FROM mysql.user WHERE User NOT LIKE 'mysql.%%' AND User <> 'root' AND User <> 'pma';" > "%pasta_backup%\_users\users_%ano%%mes%%dia%.sql"
"%pasta_mysql_bin%\mysql.exe" -u%mysql_usr% -p%mysql_pwd% -s -N -e "SHOW DATABASES" | for /f "usebackq" %%a in (`findstr /v /r "mysql$ phpmyadmin$ information_schema$ performance_schema$"`) do @echo CREATE DATABASE IF NOT EXISTS %%a; >> "%pasta_backup%\_users\users_%ano%%mes%%dia%.sql"
"%pasta_mysql_bin%\mysql.exe" -u%mysql_usr% -p%mysql_pwd% -s -N -e "SELECT CONCAT('SHOW GRANTS FOR \'',user,'\'@\'',host,'\';') FROM mysql.user WHERE User NOT LIKE 'mysql.%%' AND User <> 'root' AND User <> 'pma';" > "%pasta_backup%\_users\users_grants.sql"
"%pasta_mysql_bin%\mysql.exe" -u%mysql_usr% -p%mysql_pwd% < "%pasta_backup%\_users\users_grants.sql" >> "%pasta_backup%\_users\users_%ano%%mes%%dia%.sql"
findstr /v /i /b "Grants for " "%pasta_backup%\_users\users_%ano%%mes%%dia%.sql" > "%pasta_backup%\_users\users_tmp.sql"
for /f "delims=" %%a in ('findstr /b GRANT "%pasta_backup%\_users\users_tmp.sql"') do echo %%a; >> "%pasta_backup%\_users\users_tmp.sql"
findstr /v /e ' "%pasta_backup%\_users\users_tmp.sql" > "%pasta_backup%\_users\users_%ano%%mes%%dia%.sql"
del "%pasta_backup%\_users\users_grants.sql"
del "%pasta_backup%\_users\users_tmp.sql"

"%pasta_mysql_bin%\mysql.exe" -u%mysql_usr% -p%mysql_pwd% -s -N -e "SHOW DATABASES" | for /f "usebackq" %%a in (`findstr /v /r "mysql$ phpmyadmin$ information_schema$ performance_schema$"`) do @mkdir "%pasta_backup%\%%a" 2>nul & "%pasta_mysql_bin%\mysqldump.exe" %%a -u%mysql_usr% > "%pasta_backup%\%%a\%%a_%ano%%mes%%dia%.sql"

for /d %%a in ("%pasta_backup%\*") do (
	cd /d "%%a"
	for /f "skip=3 delims=" %%f in ('dir /b /o-d /a-d *.sql') do del "%%f"
)
cd /d "%~dp0"