Using an existing MySQL instance

Background

SlimServer version 6.5.0 and later includes its own /embedded/ MySQL instance. SlimServer will start and stop this automatically, create and populate the SQL database it contains automatically, and generally look after it without any direct involvement from the user being necessary.

If you already have MySQL installed then there is no problem because this embedded MySQL instance will happily run alongside an existing MySQL server - they will not clash because SlimServer's version has been configured to use a different set of resources to those of a standard MySQL instance (in particular, the /port number/ it listens on has been changed from the well-known default of TCP/3306 to TCP/9092).

If you already have an existing MySQL installation, however, you may want to avoid using the extra resources that also running SlimServer's version will use by asking SlimServer to use your instance. Whilst this isn't a configuration officially supported by Slim Devices, many people use SlimServer very successfully this way. This page tells you how to do this.

Applicability

Note that this procedure applies to SlimServer version 6.5.0 and later; if you have an older version of SlimServer then please see the MySQLReplaceSQLite page instead.

Pre-Requisites

This guide assumes:

1. You have already installed SlimServer, but it is not running. It makes no difference whether you've previously been using the embedded MySQL instance or not.
2. You have an existing MySQL installation that is working and running. Whilst SlimServer is reported to work with MySQL version 4.1, most people are using a 5.x version.

Creating the SlimServer Database and User

First you need to create the SlimServer database and user that will hold and own the SlimServer database. You can do this using a front-end such as phpMyAdmin, or you can use the /mysql/ command-line interface - this guide assumes the latter as it's universally available on all MySQL platforms.

# mysql -u admin_user -p
Enter password: admin_password
Welcome to the MySQL monitor. 
Commands end with ; or \g.
Your MySQL connection id is 103 to server version: 5.0.24a-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> create database slimserver;
Query OK, 1 row affected (0.22 sec)
mysql> grant all on slimserver.* to slimserver identified by 'slimserver_password';
Query OK, 0 rows affected (0.13 sec)
mysql> flush privileges;
Query OK,
mysql> quit

• Replace /admin_user/ with the name of a MySQL database administrator (such as /root/). Remember that the password is the one you've configured for that user in the database - in the example above that might be a different password to your system /root/ user's password.
• Replace /slimserver_password/ with the password you want to assign to this database user. Remember this password because you'll need it in a little while.
• Remember to flush!

Configuring SlimServer to Use Your Database

Next you need to edit your server preferences file (SqueezeCenter >=7: SqueezeCenterDefaultLocations, SlimServer <7.0 : SlimServerPreferencesFile). Make sure that SlimServer isn't running at this point or you'll lose your changes because SlimServer will overwrite your edits.

Edit the preferences file in a text editor that preserves plain text file contents, such as /notepad/ on Windows, or /vi/ or /emacs/ on Linux or Mac OS X. Comments in the preferences file will not be preserved, so don't depend on a commented out configuration item being there after you start SqueezeCenter.

Look for the line containing /dbusername/ and then enter the following settings (replacing any of those lines that may already be present):

dbsource: dbi:mysql:database=slimserver
dbusername: slimserver
dbpassword: slimserver_password

• Replace /slimserver_password/ with the password you assigned to the MySQL user you created earlier.

Startup Dependencies

Your system may be set up to start Windows and MySQL automatically when the system is starting. It is therefore important to configure SlimServer so that it only starts after your MySQL instance has been started.

Different Linux distributions will have different techniques for defining dependencies between system services. Feel free to add instructions for your Linux distribution if not already included below.

Linux Startup Dependencies (Fedora)

Fedora startup ordering is controlled by the links in /etc/rc.d directory by chkconfig, which is set up by chkconfig, by lines specified in the /etc/init.d/slimserver file. The scripts are run in numerical order. S20abc is run before S30xyz, for example. See the chkconfig man page for further details.

As of SlimServer v6.5, the relevant chkconfig configuration lines are:

# chkconfig: 345 80 30
# description: Slim streaming MP3 server

This states that slimserver should be started at runlevels 3, 4, and 5, at startup sequence 80 and at shutdown sequence 30.

To be certain that mysqld starts prior to slimserver, mysqld's startup sequence should be less than slimserver's startup sequence, and mysqld's shutdown sequence should be greater than slimserver's shutdown sequence.

As of mysql-5.0.22-1.FC5.1, the relevant chkconfig configuration lines are:

# chkconfig: - 64 36
# description:  MySQL database server.

In this default configuration, mysqld is started at 64 and stopped at 36. slimserver is started at 80 and stopped at 30. As a result, mysqld (64) is started before slimserver (80), and slimserver (30) is stopped before mysqld (36).

Thus, no changes are necessary on Fedora Core5 with mysql-5.0.22-1.FC5.1 for startup ordering.

Linux Startup Dependencies (Gentoo)

This assumes you've create system scripts similar to those documented in GentooStartupScripts.

Simply add a /"need mysql"/ to the /depend()/ function at the top of the script - it should look something like the following:

depend() {    need net    need mysql    after localmount}

FreeBSD Startup Dependencies

Using the slimserver or squeezecenter ports install, a startup script is installed in /usr/local/etc/rc.d/.

Because Squeezecenter is now depending on your mysql instance, do the following.

Open this script with a text editor and add 'mysql' to the REQUIRE: statement. Like so:

#!/bin/sh
#
# $FreeBSD: ports/audio/squeezecenter/files/squeezecenter.sh.in,v 1.10 2008/04/04 15:48:14 brooks Exp $
#

# PROVIDE: squeezecenter
# REQUIRE: LOGIN mysql
# KEYWORD: shutdown

If you have added slimserver_enable="YES" for 6.x or squeezecenter_enable="YES" for 7.x to /etc/rc.conf, squeezecenter will start automatically at boot. Also note that this file will be overwritten when you upgrade your squeezecenter port, and above will have to be repeated.

Windows Startup Dependencies

Add the following value to your Registry using the standard Windows tool /regedit/:

Key: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\slimsvc\Value
Name: DependOnService
Value Type: REG_MULTI_SZ
Value Data: MySQL

Mac OS X Startup Dependencies

Mac OS X has no dependencies. Just download the 'Standard' DMG file from:

http://dev.mysql.com/downloads/mysql/5.0.html

Install the PreferencePane, and start MySQL.

Currently the MySQL instance included with Mac OS X Server will start up before Squeezecenter. This results in Squeezecenter not starting at all. You might have to set it to "Never start automatically" and start Squeezecenter manually.

Starting Up

That should be all that's required. Now simply start SlimServer as normal and it should create the database schema and rescan your music library automatically.

Troubleshooting

Everyone's system configuration can be different and so you may encounter problems. The following subsections will hopefully cover some of the problems you're likely to encounter.

/If you've had trouble following this guide, please describe your problem and how you solved it below so others can benefit./

General Debugging

Enabling SlimServer logging should be your first step to diagnosing problems. Start the server using the following debugging flags to get some useful output:

--d_server --d_info --d_import --d_sql --d_mysql

Rescanning Finds No Music (mysql socket errors in log)

This problem is described in this bug, and relates to an error such as the following in the log:

ERROR: DBI Connection failed: DBI connect('database=slimserver','slimserver',...)
failed: Can't connect to local MySQL server through socket'/var/lib/mysql/mysql.sock' (13)
at /usr/local/slimserver/server/CPAN/DBIx/Class/Storage/DBI.pm line 652

The default socket path is often /var/run/mysqld/mysqld.sock for Linux or /tmp/mysql.sock for Mac OS X. The best way to resolve this (since it's not where it's trying to look) is to use the following command which you will find in the directory where the mysqld binary is installed:

# mysql_config --socket

Now, armed with this information, stop SlimServer, edit your (SqueezeCenter >=7: SqueezeCenterDefaultLocations, SlimServer <7.0 : SlimServerPreferencesFile), and add the following to the end of your /dbsource/ entry (note the semicolon at the front that will form a separator between the current setting and this extra information):

;mysql_socket=/var/run/mysqld/mysqld.sock

Obviously replacing the socket pathname with the one that the /mysql_config/ command revealed.

Now restart the server and all should be well.

Client Fails to Authenticate

This issue is described in this article, and has a similar symptom to the previous item about MySql socket errors:

ERROR: DBI Connection failed: DBI connect('database=slimserver','slimserver',...)
failed: Can't connect to local MySQL server through socket'/var/lib/mysql/mysql.sock' (13)
at /usr/local/slimserver/server/CPAN/DBIx/Class/Storage/DBI.pm line 652

The difference here is that the MySql socket resolves correctly, and the underlying problem is that the client cannot authenticate.

In some MySql installations it is necessary to qualify the username with localhost when granting permissions (refer to instructions above):

mysql> grant all on slimserver.* to 'slimserver'@'localhost' identified by 'slimserver_password';
Query OK, 0 rows affected (0.13 sec)

You may need to remove the slimserver database and user if you need to start over to create a database with the right permissions:

# mysql -u admin_user -p
Enter password: admin_password
Welcome to the MySQL monitor. 
Commands end with ; or \g.
Your MySQL connection id is 103 to server version: 5.0.24a-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> drop database slimserver;
mysql> drop user slimserver;
mysql> quit