View source for Setting up a dedicated Rivendell MySQL and audio store server

In larger Rivendell deployments where many Rivendell workstations are working together it may be worthwhile to setup a dedicated server machine to act as the Rivendell MySQL and audio store server.  This server does not require actually having Rivendell installed on it, instead the MySQL database can be created by a client workstation.

This process is similar to [[http://rivendell.tryphon.org/wiki/index.php/Remote_Client_Access Setting Up Rivendell for Remote Client Access]].
These directions describe how to setup such a configuration in a Debian Etch (v4.0) environment, and should work on other distributions with minor changes.



== Prepare your Rivendell server for MySQL ==
Ensure that the mysql server is installed and running.

 aptitude install mysql-server
 
 /etc/init.d/mysql start

== Configure MySQL to listen on all network interfaces on the Rivendell server ==
For security reasons the MySQL server only listens on the loopback interface (lo 127.0.0.1) by default.  In order to accept connections from remote machines it must be configured to listen on other interfaces also.

 $EDIT /etc/mysql/my.cnf
 # change the value of bind-address from 127.0.0.1 to 0.0.0.0

 /etc/init.d/mysql restart


== Create or modify a MySQL user account with root permissions from remote hosts on the Rivendell server ==
Unlike regular UNIX accounts which are comprised only of a userid, MySQL accounts are comprised of the combination of userid and hostname.  As an example, the MySQL account root@localhost is different from the account root@clientmachine.  By default MySQL accounts are created only for the host 'localhost'.  

In order to create the Rivendell database from a remote workstation an account with privileges to create a new database and tables must be temporarily created or modified on the server.  The account can be deleted or modified back to its original state after the tables are created.

Modify the root@localhost MySQL account to work from any host by replacing the hostname with the SQL wildcard %.

 # On the server connect to the MySQL daemon as the root MySQL user.
 mysql -u root -p mysql

 # replace the hostname localhost with %
 UPDATE user SET host = '%' WHERE user = 'root' AND host = 'localhost';
 FLUSH PRIVILEGES;


== Configure MySQL to log more on the server (optional) ==
While setting things up and debugging them, it may be useful to get some additional logging from the MySQL daemon.

 $EDIT /etc/mysql/my.cnf
 # uncomment the logging entry
 log             = /var/log/mysql/mysql.log

 /etc/init.d/mysql restart


== Test connecting to the MySQL server from a workstation ==

A simple "telnet" test will confirm that the MySQL server is listening on the network port.  A sample command for using the name of the MySQL server as "mysqlserver" would be as below.  Look for a banner to be returned, then exit.

 telnet mysqlserver 3306

The next test is to connect using a tool such as the "mysql" commandline client from a workstation.
 mysql -u root -p -h mysqlserver

== Create the Rivendell database from a workstation ==
The next steps will be to create the database on the Rivendell server from a workstation.

=== Configure Rivendell /etc/rd.conf on the workstation to point to the Rivendell server for MySQL ===

To configure the /etc/rd.conf file on the workstation, one can either use the debconf dialogs or edit the file manually.  Only the hostname needs to be updated to match the Rivendell server, there is no need to change the user used to connect (as the root user account created above will only be used during database creation, and after that a regular rivendell user will be used).

a) use debconf as the root use to update the /etc/rd.conf file
 dpkg-reconfigure rivendell
 # at the question "Enter the hostname of the Rivendell MySQL server:" enter the name of the rivendell server.

b) alternatively, if editing the /etc/rd.conf file manually instead of using the debconf dialogs, then:
 $EDIT /etc/rd.conf
 # in the [mySQL] section, set Hostname = to the name of the Rivendell server

=== Run rdadmin on the workstation to create the Rivendell database ===

Run the rdadmin utility to create the MySQL database on the Rivendell server.  A dialog requesting a user name and password for a MySQL account with administrative privileges will popup.

 rdadmin

== UNDO MySQL user account with root permissions from remote hosts on the Rivendell server ==
Now that the database is created, the MySQL user with root permissions from remote hosts is no longer needed, so it can be returned to its original configuration.

Modify the root@% MySQL account to work from only the localhost.

 # On the server connect to the MySQL daemon as the root MySQL user.
 mysql -u root -p mysql

 # replace the hostname localhost with %
 UPDATE user SET host = 'localhost' WHERE user = 'root' AND host = '%';
 FLUSH PRIVILEGES;

== UNDO Configure MySQL to log more on the server (optional) ==
Turn off the extra logging of MySQL queries unless it's still needed.

 $EDIT /etc/mysql/my.cnf
 # uncomment the logging entry
 log             = /var/log/mysql/mysql.log

 /etc/init.d/mysql restart

== Configure server to share the audio store via network filesystems (NFS and SAMBA) ==
<font size=3 color=red>It is not recommended to share /var/snd via SAMBA as it does not have the proper file permissions to work with Rivendell and Linux properly!</font>
Configure the server to share audio storage via network filestems such as NFS (for *NIX clients) and SAMBA (for Windows clients).

First install the required packages:
 aptitude install nfs-kernel-server
 aptitude install samba

For these filesystems to work in a multi-user network environment, there needs to be a common user directory on the server and the workstations.  This could be an LDAP server, an NIS server, or /etc/passwd and /etc/group files copied on all machines to be the same.
FIXME: finish explaining how to setup one of these.

*temp refs. 
http://www.debuntu.org/ldap-server-and-linux-ldap-clients
https://help.ubuntu.com/community/SettingUpNISHowTo
http://www.howtoforge.com/perfect-server-ubuntu-10.10-maverick-meerkat-ispconfig-2

Alternatively, user permissions could be ignored and all access could be done without checking user permissions as a single user (such as the system account "nobody").
 mkdir /var/snd
 chown nobody.nogroup /var/snd
 chmod 2775 /var/snd

Configure NFS to share the audio store directory without checking permissions from any machine on the subnet (assuming a subnet and netmask of 192.168.1.0/255.255.255.0).
 $EDIT /etc/exports
 # add a line like the following
 /var/snd   192.168.1.0/255.255.255.0(rw,sync,all_squash,subtree_check)

Run the NFS command to re-export the new filesystems
 exportfs -r -v



Configure SAMBA to share audio store directory without checking permissions from any machine on the subnet (assuming a subnet and netmask of 192.168.1.0/255.255.255.0).
 $EDIT /etc/samba/smb.conf
 # set the following global options
 security = share
 # add a share section for the audio store
 [snd]
    comment = Rivendell audio store
    path = /var/snd
    writable = yes
    force user = nobody
    force group = nogroup
    public = yes
    printable = no
    create mask = 0770
    directory mode = 0770
    force create mode = 0770
    force directory mode = 0770

Check configuration file for errors
 testparm

Reload SAMBA to pick up the new configurations
 /etc/init.d/samba reload


{{Languages|Setting up a dedicated Rivendell MySQL and audio store server}}