Browsed by
Tag: Database

Updating XBMC database (OpenELEC)

Updating XBMC database (OpenELEC)

XBMCWhen you use XBMC as media centre, it sometimes can be useful to be able to update the XBMC database outside XBMC. XBMC uses SQLite as database which can be managed with a tool like SQLiteSpy.

But when XBMC is still running, the database will be locked and is in read-only mode. XBMC should be closed in order to be able the access the database in write mode.

In this post I will show you how  XBMC can be closed when running OpenELEC.

These are the steps to unlock the XBMC database:

  1. Get shell access to OpenELEC
  2. Because XBMC will automatically restart when it is closed, we need to disable XBMC.
    Execute the following command: touch /var/lock/xbmc.disabled
  3. Now we can stop XBMC. First we need to find the PID of XBMC by executing this command: ps | grep xbmc. Now execute the following command: kill {pid}

Now XBMC is closed and the database is fully accessable. You can find the database(s) here: \\openelec\Userdata\Database (*.db).

When you are finished updating the database, you can restart XBMC by executing the following command: rm/var/lock/xbmc.disabled. XBMC should now start automatically. If this does not happen, you should reboot OpenELEC. Just execute this command to do so: reboot -d 0.

In this XBMC wiki you can find information about the database schema.

Restore MySQL database from dump file

Restore MySQL database from dump file

In a previous post I described how you can backup your MySQL database(s). This article tells you how to restore a database using the dump file created by the backup command.

Just start a command box and execute the following command, enter the password for the given user and wait for the command to complete.

[text]
mysql -u username -p database < dumpfile.sql
[/text]

Substitute ‘username’, ‘database’ and ‘dumpfile.sql’ with your own data.

Look at the MySQL reference manual for all the possible options for the mysql command.

Backup a MS Sql Server database via command line

Backup a MS Sql Server database via command line

When you use an express version of MS Sql Server you don’t have the option to use the Management Studio to create a maintenance plan for automatically perform database backups. But you can still automatically backup your databases by using the osql command-line tool.

Here’s a simple command to create a backup of a database. You only have to put in your database name and backup location.

[text]
Remark: uses trusted connection to connect to database server
osql -E -Q “Backup database databasename to disk=’c:\backup\databasename.bak’ with init if @@error != 0 raiserror (‘Error during backup.’, 16, 127)”
[/text]

This command uses a trusted connection, but you can also give a username/password to the command. For more information about the parameters that can be used look at the MSDN osql utility page.  This command is normally executed at the computer where MS Sql Server is installed, but it is also possible to run the command from another computer. The only thing you need is the osql command to be available on that computer. Always keep in mind that the backup location which is given to the command should be a location which is present on the computer which is running the MS Sql Server, because the SQL command is always run on the MS Sql Server computer.

Scheduled backup of multiple MySQL databases

Scheduled backup of multiple MySQL databases

When you have one or more MySQL databases you probably want to create a backup at a regular basis. This article shows you how this can be done by using a simple DOS-script.

First thing to do is to create a text file with the names of your databases you want to backup in it. In this example I named the file ‘mysql_databases.txt‘:

[text]
database1
database2
database3
[/text]

Next thing to do is creating the DOS-script. The highlighted lines show which lines to change so it matches your own situation. The ‘d:\scripts‘ directory is where the script is stored and where other usefull tools are stored. More on that later on.

[text highlight=”4-9,11,25″]
@echo off
title Dump MySql databases

SET BUPDIR=d:\backup\mysql_dbexport
SET LOGFILE=dump_mysql_dbs.log
SET DBSLIST=mysql_databases.txt
SET USER=user
SET PWD=password
SET MAILTO=your@email.com

cd /d d:\scripts

echo ———————  > %LOGFILE%
echo MySql database backup >> %LOGFILE%
echo ——————— >> %LOGFILE%

REM Make backup of database given mysql_databases.txt file
for /F %%d IN (%DBSLIST%) DO (
echo %date% %time% >> %LOGFILE%
echo Dumping database %%d
echo Dumping database %%d >> %LOGFILE%
mysqldump –user=%USER% –password=%PWD% –result-file=%BUPDIR%\%%d.sql %%d >> %LOGFILE%
echo ——————— >> %LOGFILE%)

REM Insert code for further processing here

echo %date% %time% >> %LOGFILE%
echo ——————— >> %LOGFILE%
echo         D O N E       >> %LOGFILE%
echo ——————— >> %LOGFILE%

rem pause
[/text]

Now you have a script which can be scheduled so you will always have a backup of your database(s).

If you also want a copy of the backup being sent to you by e-mail, you can extent the script with the following code. Just put the following code in place of the ‘REM Insert code for further processing here’ line.

[text]
REM Zip sql-dumps
7-zip\7za a %BUPDIR%\mysql_dbexport.zip %BUPDIR%\*.sql

REM Mail the log- and zip-file
mailto -a %MAILTO% -s Backup done (databases) #dt -bf %LOGFILE% -f %BUPDIR%\mysql_dbexport.zip
[/text]

Remark: The above script is using 7-zip as a command-line compressing tool. The mailto command is a home made tool, but I’m sure the internet will provide you with a mailto tool that also does the trick.