2020-03-29, 14:54
Hey all,
So I recently setup mysql in a windows 10 environment.
I've been playing with the batch script from here:
https://kodi.wiki/view/MySQL/Advanced_notes
Am having some odd issues.
I have it all setup and I can only get it to work/run if I change this line:
IF NOT [%%F]==[performance_schema] ( SET %%F=!%%F:@002d=-! "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "\\REMOTE\KODI_Database\backups\%%F.%backuptime%.sql" ) ELSE (
to:
IF NOT [%%F]==[performance_schema] (SET %%F=!%%F:@002d=-! "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "E:\StreamFiles\Data\SQLBackup\%%F.sql") ELSE (
Removing .%backuptime% at the very end.
If I keep that in the scripts complains it cannot find any files and creates and empty zip file.
The second issue I have is that when I do create a good backup zip the naming convention is always the time of the backup
eg: -02--20-55.zip which to me means Feb 20:55 hrs/mins so this means that each night the backup runs it will overwrite the previous nights backup.
Has anyone got this script running correctly?
Thanks and cheers in advance
If it helps here is my full script:
So I recently setup mysql in a windows 10 environment.
I've been playing with the batch script from here:
https://kodi.wiki/view/MySQL/Advanced_notes
Am having some odd issues.
I have it all setup and I can only get it to work/run if I change this line:
IF NOT [%%F]==[performance_schema] ( SET %%F=!%%F:@002d=-! "C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "\\REMOTE\KODI_Database\backups\%%F.%backuptime%.sql" ) ELSE (
to:
IF NOT [%%F]==[performance_schema] (SET %%F=!%%F:@002d=-! "C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "E:\StreamFiles\Data\SQLBackup\%%F.sql") ELSE (
Removing .%backuptime% at the very end.
If I keep that in the scripts complains it cannot find any files and creates and empty zip file.
The second issue I have is that when I do create a good backup zip the naming convention is always the time of the backup
eg: -02--20-55.zip which to me means Feb 20:55 hrs/mins so this means that each night the backup runs it will overwrite the previous nights backup.
Has anyone got this script running correctly?
Thanks and cheers in advance
If it helps here is my full script:
python::: These lines do not NEED to be edited
set year=%DATE:~10,4%
set day=%DATE:~7,2%
set mnt=%DATE:~4,2%
set hr=%TIME:~0,2%
set min=%TIME:~3,2%
IF %day% LSS 10 SET day=0%day:~1,1%
IF %mnt% LSS 10 SET mnt=0%mnt:~1,1%
IF %hr% LSS 10 SET hr=0%hr:~1,1%
IF %min% LSS 10 SET min=0%min:~1,1%
set backuptime=%mnt%-%day%-%year%-%hr%-%min%
:: User name for DB - NOTE that root credentials are needed for this script.
set dbuser=kodi
:: User password - NOTE that the root credentials are needed for this script.
set dbpass=kodi
:: Path to location where you would like to save the errors log file. For simplicity, I keep mine in the same location as the backups.
set errorLogPath="E:\StreamFiles\Data\SQLBackup\dumperrors.txt"
:: We need to switch to the data directory to enumerate the folders
pushd "C:\ProgramData\MySQL\MySQL Server 5.7\Data"
:: We will dump each database to it's own .sql so you can easily restore ONLY what is needed in the future. We're also going to skip the performance_schema db as it is not necessary.
FOR /D %%F IN (*) DO (
IF NOT [%%F]==[performance_schema] (
SET %%F=!%%F:@002d=-!
"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe" --user=%dbuser% --password=%dbpass% --databases --routines --log-error=%errorLogPath% %%F > "E:\StreamFiles\Data\SQLBackup\%%F.sql"
) ELSE (
echo Skipping DB backup for performance_schema
)
)
::Now to zip all of the .sql files in this folder and move the resulting .zip files to our network location.
"E:\StreamFiles\Data\7za\7za.exe" a -tzip "E:\StreamFiles\Data\SQLBackup\FullBackup.%backuptime%.zip" "E:\StreamFiles\Data\SQLBackup\*.sql"
::Now we'll delete the unzipped .sql files
del "E:\StreamFiles\Data\SQLBackup\*.sql"
::Now we'll delete all zip files older than 30 days. You can adjust the number of days to suit your needs, simply change the -30 to whatever number of days you prefer. Be sure you enter the path to your backup location.
Forfiles -p E:\StreamFiles\Data\SQLBackup\ -s -m *.* -d -30 -c "cmd /c del /q u/path"
popd
exit