Store amaroK Data in MySQL

By default, amaroK stores information about your [music] collection including listening habits and rankings into a SQLite database file on your filesystem. If you only use amaroK on a single computer, this works fine, but if you run it on multiple computers you might want your information to be persistent across machines. amaroK supports the use of a MySQL database for this type of storage, but it does require a bit of a setup beforehand to use. This hack will describe the steps necessary to migrate to a MySQL collection database.

First, you need root access to a MySQL database to create a new database for amaroK. If you plan on accessing this database from anywhere, you will want to be on an Internet-facing machine that's always on. Log in to the database as root and create the initial database:

$ mysql -p -u root
mysql> CREATE DATABASE amarokdb;
mysql> USE mysql;
mysql> GRANT select, insert, update, delete, create, drop, create \
temporary tables, index ON amarokdb.* TO amarok@localhost IDENTIFIED \
BY 'password_here';
mysql> FLUSH PRIVILEGES;

Replace password_here with the password you want to use for the amarok user. Now that the database is created, click Settings > Configure amaroK and then click Collection from the amaroK configuration window. Change the Collection Database Engine to MySQL and then enter the hostname for your MySQL database along with the name of the database (amarokdb in this example) and the username and password (amarok and the password you chose in this example) Click OK. amaroK will now start using the MySQL database to store its settings.

To import SQLite data into MySQL, the amaroK team created this method, which is not officially supported. First copy your ~/.kde/share/apps/amarok/collection.db file to your database server, and then type:

$ sqlite3 collection.db .dump | \
grep -v "BEGIN TRANSACTION;" | \
grep -v "COMMIT;" | \
perl -ne "s/INSERT INTO \"(.*)\"
VALUES/INSERT INTO \1 VALUES/; print" | \
mysql -u root -p amarok

This tech tip is excerpted from Linux Multimedia Hacks, authored by Linux Journal columnist Kyle Rankin. Published by O'Reilly Media, ISBN: 0-596-10076-0. Copyright 2006. For further information please visit http://www.oreilly.com.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

The tip above didn't work

v6lur's picture

The tip above didn't work (Amarok 1.4.10, MySQL 5.0.51a), but I found this how-to that did: http://amarok.kde.org/wiki/MySQL_HowTo

Amarok

tx3's picture

Originally named amaroK, it was renamed to Amarok in June 2006.

Thanks, saved me a lot of work

Baldylocks's picture

Thanks for that, that saved me a lot of grief. I only started out using mySQL/PHP a couple of weeks ago - that included building a headless Ubuntu server for the first time - and I am loving it. I converted my old Access address and movie databases to mySQL and have a nice "intranet" with search facilities for these now - these old access dbs were the one thing I had not got around to finding replacements for since ditching windows.

Next was a project to ls my music folders regularly and add the results to a mySQL database. I had spent a couple of hours messing with ls and pipes and grep without a lot of success, until I thought "Amarok will have already done this once"

10 minutes later I now have a nice db structure on my server which Amarok is currently populating for me.

Big thanks :-)

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState