Hack22.Store amaroK Data in MySQL

Hack 22. Store amaroK Data in MySQL

amaroK has the option to store its collection data in a remote MySQL database. Use this feature to create a centralized database of your listening habits so they follow you from computer to computer.

By default, amaroK stores information about your 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 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 it 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 the for 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.

2.11.1. Transfer SQLite Data to MySQL

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