Using MySQL to manage your music collection on Amarok
If you use Amarok and have a large music collection have you ever thought about using a database to manage your collection? This article will describe how to configure MySQL and Amarok to allow for the integration of one of the world's most popular databases to one of Linux's most popular media players.Works on any Linux Distro! This article covers Debian & Ubuntu, Fedora and ArchLinux. If you still have some questions after reading this article please try our new LinuxCareer Forum.
- any Linux distribution that supports MySQL .(I will use ArchLinux and cover Debian (and its derivatives including Ubuntu) and Fedora for this demonstration.)
- Amarok installed and configured for regular usage
- You listening to your music library.
- You may need to consulate your Distro's documentation on MySQL in the case you run into any issues. However, please feel free to leave comments and I will gladly help to the best of my ability.
- Add mysql deamon to startup scripts, refer to your Distro's documentation
We will start off by downloading MySQL with your package manager of choice (YUM, APTITUDE, PACMAN). Since I use ArchLinux, I will use pacman and issue the following command
# pacman -S mysql
on Debian or Ubuntu you would use
# aptitude install mysql-server mysql-client
on Fedora you would use
# yum install mysql-server
On Fedora you may also need mysql-client, post your results in the comments section.
On archLinux I would then issue the following commands to start mysqld daemon and run script to configure mysql security.
# rc.d start mysqld && mysql_secure_installation
On Debian or Ubuntu you would run:
# /etc/init.d/mysqld start && mysql_secure_installation
On Fedora run:
# service mysqld start
After running mysql_secure_installation run the following command on any Linux distro:
mysql -u root -p
after entering your password you should be presented with the following text: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server version: 5.5.16-log Source distribution Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
4.1. Setting up your database
To create a database and working Amarok user to connect to MySQL run these commands at the mysql> prompt:
CREATE DATABASE databaseName; GRANT ALL PRIVILEGES ON databaseName.* TO 'UserName'@'MachineName' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
So basically its a 3 steps process. First you create the database with the create database command. I would give the database a human readable name like amarok_DB so you can actually remember it or understand what the database is whenever you issue a show databases command on your server. After creating the database you run a GRANT command to give access to a user. At this step you also establish the user's password. Replace information in this example for your own database, user, password. So
CREATE DATABASE amarok_DB; GRANT ALL PRIVILEGES ON amarok_DB.* TO 'DJ_312'@'localhost' IDENTIFIED BY '23ij12i312nk3ko2'; FLUSH PRIVILEGES;
could be a more realistic command sequence. The FLUSH PRIVILEGES command ensures that the GRANT command are activated. The 'localhost' bit is a security precaution and ensures that "DJ_312" can only access the database from the localhost.
The screen shot above should basically show what you have entered into mysql.
5. Configuring Amarok
Lets start Amarok
In Amarok access the database settings found under:
Settings->Configure Amarok->database .
You should see a dialog box similar to the one below:
Make sure "Use external MySQL database" is enabled. Server name should be localhost. Replace the username, password, and database fields with the information you used for your own database. Press OK and voila! Give Amarok a couple of minutes (or longer) to create the entries.
6. TMI: Extra Stuff to Soak in!
If you are really curious to see what it creates: Looking at the database schema using a database administration program like Adminer, I get the following tables:
the tracks table (lower right) is not completely listed is has like 15 attributes!
You can also get this information in mysql by issuing commands like
# show tables //show list of tables
# describe table_name //show individual table content.
A lot of these tables are self explanatory and help make Amarok the great music player that it is.
After Amarok is finished it will list your collection: Now just add some songs to your playlist and start jamming to your music collection! Time to enjoy your library!