My backup method is very similar to saitoh
I have a scheduled backup on my WIN7 server that exports each database separate from a Batch script every 2 days. It then uses the 7zip command line zipper to create a zipped backup and then adds the date to the backup. This allows a complete backup of my Music, Video and Media Portal (TV Schedules) to be backed up.
I have gone through 5 server rebuilds and never lost a database. There are some tricks though but it's all simple (I know little about MYSQL structure but google helps a lot):
Code:
@ECHO OFF
ECHO===============================================
ECHO. XBMC SQL BACKUP UTILITY
ECHO===============================================
SET BACKUPPATH=D:\HTPC_DATA\SQL_Backup\
SET MYSQLPATH=C:\Program Files\MySQL\MySQL Server 5.5\bin
IF NOT EXIST %BACKUPPATH%\ (
MD %BACKUPPATH%
)
"%MYSQLPATH%\mysqldump" -u xbmc -pxbmc xbmc_video75 > %BACKUPPATH%\XBMC_VIDEO75.sql
"%MYSQLPATH%\mysqldump" -u xbmc -pxbmc xbmc_music32 > %BACKUPPATH%\XBMC_MUSIC32.sql
"%MYSQLPATH%\mysqldump" -u xbmc -pxbmc mptvdb > %BACKUPPATH%\MPTVDB.sql
7za.exe a %BACKUPPATH%\%DATE:~7,2%-%DATE:~4,2%-%DATE:~-4%_XBMC.SQL.7z "%BACKUPPATH%\*.SQL" -mx1
DEL %BACKUPPATH%\*.SQL
What you will see is pretty simple. It is a .BAT file that makes the backup and you simply need to fill in the path to SQL, the user and passwords and change the name of the tables to backup in the commands. It then exports to the directory set and then runs the 7zip utility to compress (just needs to be in the same folder as the script).
You can then setup an automated schedule to make a backup in case you ever screw your database.
So, how do you reload a backup?
This is where I always use to get stuck because after installing MYSQL I would try and restore the tables but it would always fail so here is what I do to restore on a NEW SQL install:
- After installing MYSQL you need to run XBMC and let it create a new SQL database. Don't import anything using XBMC, just let it create the tables.
I use this script to import the backups:
Code:
@ECHO OFF
ECHO===============================================
ECHO. XBMC SQL RESTORE UTILITY
ECHO===============================================
::BACKUP FILE LOCATION
SET BACKUPLOCATION=D:\HTPC_DATA\SQL_Backup
SET FILENAME=XBMC_MUSIC18.SQL
:SQL DETAILS
SET SQLEXEPATH=C:\Program Files\MySQL\MySQL Server 5.5
SET IMPORTDBNAME=xbmc_music18
SET IMPORTUSER=xbmc
SET IMPORTPASS=xbmc
:: "BACKUPLOCATION" IS THE PATH TO THE SQL BACKUP FILE. NO TRAIL SLASH.
:: "FILENAME" IS THE NAME OF THE SQL BACKUP FILE WITH EXTENSION.
:: "SQLEXEPATH" IS THE PATH TO THE SQL DIRECTORY ONLY. NO TRAIL SLASH
:: "IMPORTDBNAME" IS THE NAME OF THE DATABASE NAME TO IMPORT FILE TO.
:: **DB MUST BE CREATED BEFORE IMPORTING
:: "IMPORTUSER" IS THE USERNAME TO USETO IMPORT THE DATA
:: "IMPORTPASS" IS THE DATABASE PW FOR THE USER
:: Edit the details above to import an SQL backup file into mySQL database.
:: PROGRAM-----------------------------------------------------------------------------
ECHO.
ECHO This utility will restore an SQL Database backup for the following:
ECHO.
ECHO BACKUP FILE: %BACKUPLOCATION%\%FILENAME%
ECHO TARGET DATABASE: %IMPORTDBNAME%
ECHO.
ECHO If this is not correct, please close this window now and edit the details.
PAUSE
ECHO.
ECHO Are you Sure?
ECHO.
PAUSE
IF NOT EXIST %BACKUPLOCATION%\ (
ECHO ERROR (config): Backup Location Not Found. Please Check Path In File!
PAUSE
)
IF NOT EXIST %SQLPATH%\ (
ECHO ERROR (config): SQL Location Not Found. Please Check Path In File!
PAUSE
)
IF NOT EXIST "%BACKUPLOCATION%\%FILENAME%" (
ECHO ERROR (config): Backup FILE Not Found. Please Check Path In File!
PAUSE
)
"%SQLEXEPATH%\bin\mysql" -h localhost -u %IMPORTUSER% -p%IMPORTPASS% %IMPORTDBNAME% < "%BACKUPLOCATION%\%FILENAME%"
ECHO.
ECHO If no errors are displayed, the import should be a success!
ECHO If any errors are displayed above, these need to be resolved.
ECHO Check SQL Database for imported data.
ECHO.
PAUSE
EXIT
As you can see you just fill in the same details for the table to import too and the file to read from and the locations then run the file. If there is an issue with something you will get some form of error but if all goes well you should get nothing but the last little blurb at the end there and checking MYSQL with HeidiSQL or something you should see the table size reflects your library and XBMC should work as normal.
I am no expert here so can only assist to a basic degree but I aways make sure I know what I am doing and how it all works for WHEN I need to do it all. No point having an SQL database if you cannot recover or care for it when things qo wrong. Hope that helps.
