Using an existing MySQL instance
From SqueezeboxWiki
Contents |
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:
- 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.
- 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