Comprehensive n00bs Guide on Installing MySQL (and unMENU) to an unRAID Server


Recommended Posts

 

The Comprehensive n00bs Guide on Installing MySQL (and unMENU) to an unRAID Server

 

 

1. Assumptions

 

The only assumption we will make is that you already have some sort of network attached storage set up that is running unRAID.  If that's not the case, I doubt you'd be reading this thread, but if for some reason you have nothing, here is an interesting little youtube video to watch about building an unRAID server:

 

 

2. Installing unMENU

 

The first thing you need to make sure is that you have unMenu installed.  unMENU is a web application that gives you additional options when viewing unRAID through a web browser.  To access unRAID from a computer on your network, you will type the IP address of your unRAID server into your web browser as shown below:

 

WbjJKRN.jpg

 

The IP address of my unRAID server is 192.168.1.3.  After installing unMENU, you can then add :8080 after the IP address.  This will bring you to a more comprehensive and detailed screen which is what we call "unMENU".

 

The important thing we're looking for is an additional tab called Pkg Manager.  We will get to this later.  Installing unMENU is easy, but many of the online directions assume you have certain things done already that you may not. 

 

First, download unMENU here:

 

https://code.google.com/p/unraid-unmenu/downloads/list

 

This is a zip file with one total file.  Unzip the file somewhere to your desktop and don't forget where you put it.  Now open up windows explorer.  Click on network.  The name of your unRAID server should be listed.  Click it.  Double click on the flash folder.  Add a new folder named "unmenu"

 

c3rqHWg.jpg

 

Take the file that you placed on your desktop that was unzipped and place it in the unMENU folder.

 

Next is where the directions get shady if you're new to this.  It's very easy, but explanations assume you know the basics.  I'll assume you know very little.  You now need to be able to access the command prompt for your unRAID server.  I find the easiest way to do this is to install a program called Putty.

 

http://www.chiark.greenend.org.uk/~sgtatham/putty/download.html

 

After installing putty, you will have a configuration screen similar to what is as shown below:

 

3S1LgWW.jpg

 

In the host name field type in the IP address of the unRAID server.  Under port, type 23.  Make sure you select "Telnet".  Click open.  You will then be brought to your typical black and white command prompt screen.  Type in "root" when prompted for a password.  Next, we will follow instructions from Joe. L's thread:

 

On 3/8/2010 at 4:07 PM, Joe L. said:

type:

cd  /boot/unmenu

unmenu_install -i -d /boot/unmenu

 

 

 

When typing the above command, make sure it says "root@tower:~#" before typing the command.  Tower is the name of your unraid server so if you changed it then it will be different.  If it says something after the colon with a slash, like /config (not sure why this would happen, but I guess it could) use the command "cd.." to move yourself up a directiory.  Continue to type "cd.." until your screen says "root@tower:~#"  Then type the above commands.

 

After a few seconds, unMENU is installed. 

 

You then need t start it running.  To do that, type:

./uu

 

We can temporarily exit putty and return to our webrowser where we can type in the IP address of our server followed by :8080.  If all was done right, we will see the following screen:

 

wwJOure.jpg

 

3. Installing MySQL

 

At this point, the tutorial is pretty straight forward that was created by hernandito back in 2011.  I will quote up until there have been modifications:

 

Quote

 

Step 2 - Installing and configuring MySQL

 

This is derived mostly from the Lifehacker guide at http://lifehacker.com/#!5634515/how-to-synchronize-your-xbmc-media-center-across-every-room-in-the-house but modified to implement in unRAID.

 

To install MySQL go into unMenu and click on Pkg Manager. Scroll down the list and click on the button for "MySQL Database Server". Download the package. Click on "Edit Configuration Variables". 

 

Enter the Hostname of your unRAID server - mine is the default "Tower" (without quotes)

 

Disk Label: is where you want MySQL to install its files - I chose "disk1"

 

MySQL Password: enter a password of your choice. This is not the same password that you use to log in to unRAID. Remember the password.

 

First Database: accept the default

 

Database User: accept "root" default

 

Database User Password: enter another password of your choice it is likely you will never have to enter this password.

 

Custom Parameters: leave blank.

 

Click on "Save Values" and then click on "Restart Using New values". You also want to enable to re-install on reboot.

 

4. Configuring MySQL

 

Below is a continuation from the same thread mentioned above.  When mentioning a telnet session, use Putty as done before:

 

Quote

Open up a telnet session into your unRAID server and at the command prompt enter:


mysql -u root -p
 

 

Type the MySQL Password you entered above.

 

Now every step here is the same as the Lifehacker guide. At the terminal enter:

 

CREATE USER 'xbmc' IDENTIFIED BY 'xbmc';
 

 

You should get a Query OK message

 

At this point, there are some places to deviate from the original instructions. 

 

As shown in the lifehacker post.....

http://lifehacker.com/#!5634515/how-to-synchronize-your-xbmc-media-center-across-every-room-in-the-house

...it is very important to also type the following command:

 

GRANT ALL ON *.* TO 'xbmc';
 

 

This gives the user, xbmc, the proper access credentials.

 

To check to see if you properly created the user, type the following while still in the MySQL command prompt:

 

SELECT host,user from mysql.user;

 

 

You should get results like shown on this image:

 

0fUlhWa.jpg

 

Unlike the tutorial mentioned before, we will not create any tables for the database.  We will allow XBMC to populate these fields when XBMC opens for the first time after installing MySQL. 

 

5. Advancedsettings.xml File

 

We will now head to the final step which is creating the advancedsettings.xml file which is the file that tells XBMC how to access the MySQL database on your unraid server.

 

When I added MySQL to the unRAID server, I started my library from scratch.  It is currently very small, so I had no need to import/export my library.  Because I did not do this, I cannot advise how to do so.  My advice is to start the library from scratch and let it repopulate.  As long as you're willing to clear your library and allow XBMC to repopulate and scrape new images, then proceed to step #2 on the following web page:

 

http://wiki.xbmc.org/index.php?title=HOW-TO:Share_libraries_using_MySQL/Setting_up_XBMC

 

To summarize, you will open up notepad and copy and paste the following:

 

<advancedsettings>

    <videodatabase>

        <type>mysql</type>

        <host>***.***.***.***</host>

        <port>3306</port>

        <user>xbmc</user>

        <pass>xbmc</pass>

    </videodatabase>

 

    <musicdatabase>

        <type>mysql</type>

        <host>***.***.***.***</host>

        <port>3306</port>

        <user>xbmc</user>

        <pass>xbmc</pass>

    </musicdatabase>

 

    <videolibrary>

          <importwatchedstate>true</importwatchedstate>

    </videolibrary>

</advancedsettings>

 

Where the stars are, you will replace the stars with the IP Address of your unRAID server.  Save this file as 'advancedsettings.xml'.  This saved file needs to be placed on any and ALL devices which use XBMC that you would like to access the MySQL database on your unRAID server .  It needs to be placed into the user data folder of any device wishing to sync to this database.  Below is a link showing where each folder is depending on which operating system you're running on that device:

 

http://wiki.xbmc.org/index.php?title=Userdata_folder

 

For windows, copy and paste %appdata%\XBMC\userdata\ into windows explorer address bar and it will take you directly to the user data folder.  Place the advanced settings file in this folder and you will be good to go!

 

pxio7IC.jpg

 

You should now be able to open up XBMC, click on videos, click on files, click add videos, click browse, scroll to "Windows network (SMB)", choose the name of your unraid server, and then select the drives where your movie files are located.

 

As long as you've put the advancedsettings.xml file onto all devices that will be reading off of this database that now exists on your unRAID server, each device should have each library and all information associated with the library synced.

 

Hope this helps!  If there are any questions I will do my best to assist, but I'm sure most others will be of more assistance.  No question is a dumb question.  Don't forget, we all start somewhere!

 

Edited by Joe L. to add the "./uu" command to start unMENU.

Edited by Living Legend
Link to comment
  • Replies 80
  • Created
  • Last Reply

Top Posters In This Topic

Noob question; after cd/boot/unmenu I get 'no such file or directory' In fact, any cd/ command returns this. Pwd returns 'root'. Any troubleshooting tips? The directories are on the flash!

 

Edit:fixed, used "cd .." to move up directories and found the boot directory

 

Link to comment

Next stupid question, I did the install, did "./uu" but when I go to the URL get error "tower:8080/user_scripts might be down or unavailable".  Any ideas? There's no such file in the directory, does unmenu normally generate it?

 

Update: all files are there, but http://tower:8080 does not work, I need to enter the plan IP e.g http://192.168.1.3:8080/which I wonder if it's related tto my android phone and tablet browser. I'm going to look for a solution...

Link to comment

Next stupid question, I did the install, did "./uu" but when I go to the URL get error "tower:8080/user_scripts might be down or unavailable".  Any ideas? There's no such file in the directory, does unmenu normally generate it?

 

Update: all files are there, but http://tower:8080 does not work, I need to enter the plan IP e.g http://192.168.1.3:8080/which I wonder if it's related tto my android phone and tablet browser. I'm going to look for a solution...

 

"tower" in this example is the default name of the unraid servier.  Did you change yours?  Mine is called "media" so I cannot use "http://tower:8080" either.  In the guide that I made I make sure to advise using your own IP followed by a colon and then 8080 to avoid any confusion on this front.

Link to comment

If you install on an array drive then the drive containing the mysql files will need to spin up whenever it is accessed and this will also spin up the parity drive. If you install to a cache drive and it is also an SSD then you won't ever have a drive spindown issue.

 

The spindown can be an issue because your XBMC client can actually load before the db does and then the db will not be accessible. To combat this you can put a sleep on XBMC when it is loading to give your db time to spinup.

Link to comment

While I am not sure how long everything is taking, if you are installed on an array drive and your array drives are slow then that can make it slower. Figure you need to spin up at least two drives when you access mysql. However once its up and running your shouldn't see it running slow. Sounds like you may have something else going on.

Link to comment

While I am not sure how long everything is taking, if you are installed on an array drive and your array drives are slow then that can make it slower. Figure you need to spin up at least two drives when you access mysql. However once its up and running your shouldn't see it running slow. Sounds like you may have something else going on.

 

Hmmm, I have the DB installed on my cache drive, but even after the drive that the content is on is spun on I get very long waits. I was reading through some other threads and found many with the same issues. Did you see any performance difference after setting up the DB? I'm seeing 30+ seconds to start or resume a movie. When everything is local it's almost instant.

Link to comment

I setup my db at the same time as my unraid server and my times have always been slightly longer than when I had the db and the files on the same machine as my htpc. When you say local is that what you are referring to? What are you using for a cache drive? The speed of the drive could be directly related to your speed issues. There are also a few tweaks in the xbmc wiki that you can apply to help like not using name resolution. If you haven't performed these tweaks I would recommend you do so.

Link to comment

I setup my db at the same time as my unraid server and my times have always been slightly longer than when I had the db and the files on the same machine as my htpc. When you say local is that what you are referring to? What are you using for a cache drive? The speed of the drive could be directly related to your speed issues. There are also a few tweaks in the xbmc wiki that you can apply to help like not using name resolution. If you haven't performed these tweaks I would recommend you do so.

 

Local meant that the DB is on the xbmc machine (default setup). I'm using a 200GB 7200RPM drive as my cache, which I feel should be fast enough but if an SSD would make things that much faster I may switch. I've looked at the tweaks. I'll have to do some testing I guess.

 

You used the DB from unmenu which is an older version if I recall correctly. Are you running XBMC Frodo or Eden?

Link to comment

I setup my db at the same time as my unraid server and my times have always been slightly longer than when I had the db and the files on the same machine as my htpc. When you say local is that what you are referring to? What are you using for a cache drive? The speed of the drive could be directly related to your speed issues. There are also a few tweaks in the xbmc wiki that you can apply to help like not using name resolution. If you haven't performed these tweaks I would recommend you do so.

 

Local meant that the DB is on the xbmc machine (default setup). I'm using a 200GB 7200RPM drive as my cache, which I feel should be fast enough but if an SSD would make things that much faster I may switch. I've looked at the tweaks. I'll have to do some testing I guess.

 

You used the DB from unmenu which is an older version if I recall correctly. Are you running XBMC Frodo or Eden?

 

I'm confused as well.  What do you mean the DB is on the XBMC machine?  The purpose of this tutorial is to setup the MySQL database on your unRAID server.  Your XBMC devices, whether they are home PCs, raspberry pis, HTPCs, etc. are all synced to the same database on your unRAID server.  I personally have mine running on one of the disks on the array.  I have no issues with it.  I bought a cheap 64 GB SSD and will be adding that as a cache drive and installing the database to there.

Link to comment

I setup my db at the same time as my unraid server and my times have always been slightly longer than when I had the db and the files on the same machine as my htpc. When you say local is that what you are referring to? What are you using for a cache drive? The speed of the drive could be directly related to your speed issues. There are also a few tweaks in the xbmc wiki that you can apply to help like not using name resolution. If you haven't performed these tweaks I would recommend you do so.

 

Local meant that the DB is on the xbmc machine (default setup). I'm using a 200GB 7200RPM drive as my cache, which I feel should be fast enough but if an SSD would make things that much faster I may switch. I've looked at the tweaks. I'll have to do some testing I guess.

 

You used the DB from unmenu which is an older version if I recall correctly. Are you running XBMC Frodo or Eden?

 

I'm confused as well.  What do you mean the DB is on the XBMC machine?  The purpose of this tutorial is to setup the MySQL database on your unRAID server.

 

I understand, but I was comparing the speed of not having it on the unRAID server. If you don't do a sql on unRAID all of that metadata and other data gets stored locally on the XBMC. I was just trying to make a speed comparison between the two options. Sorry for the confusion.

Link to comment

I understand, but I was comparing the speed of not having it on the unRAID server. If you don't do a sql on unRAID all of that metadata and other data gets stored locally on the XBMC. I was just trying to make a speed comparison between the two options. Sorry for the confusion.

 

Gotcha.  To be honest, I've done it once and have no issues.  The database in installed to disk 1 currently on my server.  It's nearly instantaneous on the XBMC home PC and is very quick, just not as quick, on my Raspberry Pi.

Link to comment

I understand, but I was comparing the speed of not having it on the unRAID server. If you don't do a sql on unRAID all of that metadata and other data gets stored locally on the XBMC. I was just trying to make a speed comparison between the two options. Sorry for the confusion.

 

Gotcha.  To be honest, I've done it once and have no issues.  The database in installed to disk 1 currently on my server.  It's nearly instantaneous on the XBMC home PC and is very quick, just not as quick, on my Raspberry Pi.

 

 

Can you please confirm which version of XBMC you are running and if you installed via unMENU (i want to know which mysql version)?

Link to comment

When I asked I was just wondering if you meant with the xbmc database and the media on the same box.

 

To answer your questions. I did not use UnMenu to install my MySQL db. I modified the PLG that is available from one of the threads here and grabbed a package on my own. Then I allowed the system to install on boot. I am running Frodo 12.0 on all my machines. My MySQL is 5.5.29-i486.

 

I highly recommend doing the speed tweaks. They can make a difference if you have not. As for the cache drive, yours should be plenty sufficient. My first cache drive was a 20gb Xbox 360 hard drive and it worked just fine. How much time does it take for a video to start playing, etc?

 

Link to comment

When I asked I was just wondering if you meant with the xbmc database and the media on the same box.

 

To answer your questions. I did not use UnMenu to install my MySQL db. I modified the PLG that is available from one of the threads here and grabbed a package on my own. Then I allowed the system to install on boot. I am running Frodo 12.0 on all my machines. My MySQL is 5.5.29-i486.

 

I highly recommend doing the speed tweaks. They can make a difference if you have not. As for the cache drive, yours should be plenty sufficient. My first cache drive was a 20gb Xbox 360 hard drive and it worked just fine. How much time does it take for a video to start playing, etc?

 

I wonder if I missed some of the tweaks do you mind linking me to where you found them please?

Link to comment

http://wiki.xbmc.org/index.php?title=HOW-TO:Share_libraries_using_MySQL/Advanced_notes

 

There is the skip name resolve tweak and the IP address in the advanced settings.xml. Other than that you may need to look at you internal network. What kind of times are you seeing? We can't tell you if your times are abnormal if we don't know what they are. Using MySQL is not as fast as using the standard db. I have never looked at the queries they are using to call to the db so I have no idea how efficient they are.

Link to comment

http://wiki.xbmc.org/index.php?title=HOW-TO:Share_libraries_using_MySQL/Advanced_notes

 

There is the skip name resolve tweak and the IP address in the advanced settings.xml. Other than that you may need to look at you internal network. What kind of times are you seeing? We can't tell you if your times are abnormal if we don't know what they are. Using MySQL is not as fast as using the standard db. I have never looked at the queries they are using to call to the db so I have no idea how efficient they are.

 

I just tried it. After the disk is spun up it takes 30 seconds to start a movie, and 30 seconds to get back to the menu after I hit pause. Two questions. Under that link you posted I don't know where the my.ini file is located. Secondly if I wanted to start with a clean mysql install would it be just deleting the plg and removed anything that has mysql in it. Or is it more involved?

Link to comment

So on my system it generally takes ~10 seconds sometimes a bit longer to start up a movie. Perfectly acceptable to me. With UnRaid we use my.cnf instead of my.ini. The my.cnf file actually exists in ram because the MySQL plugin runs in ram. What you need to do is to copy the my.cnf file to the flash drive and then edit the file on another computer in a text editor.

 

From the console or telnet session run this command.

cp -p /etc/my.cnf /boot/config/plugins/mysql

 

Now you need to add skip-name-resolve to the mysqld section and save the file. Then reboot your MySQL server.

 

After this any time you reboot your server the my.cnf should be loaded from the flash drive. If your server is running through name resolve this can take some additional time.

 

As for your question about removing MySQL. I have not installed using UnMenu so I don't know if there is an uninstall package button or something like that. But it should be as simple as stopping the service and removing the associated directories from your cache drive and the flash drive if there are any. If you remove the package and plugin you will need to put them back on the flash drive to reinstall.

Link to comment

So on my system it generally takes ~10 seconds sometimes a bit longer to start up a movie. Perfectly acceptable to me. With UnRaid we use my.cnf instead of my.ini. The my.cnf file actually exists in ram because the MySQL plugin runs in ram. What you need to do is to copy the my.cnf file to the flash drive and then edit the file on another computer in a text editor.

 

From the console or telnet session run this command.

cp -p /etc/my.cnf /boot/config/plugins/mysql

 

Now you need to add skip-name-resolve to the mysqld section and save the file. Then reboot your MySQL server.

 

After this any time you reboot your server the my.cnf should be loaded from the flash drive. If your server is running through name resolve this can take some additional time.

 

As for your question about removing MySQL. I have not installed using UnMenu so I don't know if there is an uninstall package button or something like that. But it should be as simple as stopping the service and removing the associated directories from your cache drive and the flash drive if there are any. If you remove the package and plugin you will need to put them back on the flash drive to reinstall.

 

This is great info thanks for all of your help thus far.

 

The file cnf already is on my flash drive and contains:

 

root@Namek:/boot/config/plugins/mysql# cat mysql.cfg
#mysql configuration
SERVICE="enable"
DATADIR="/mnt/cache/.mysql"
root@Namek:/boot/config/plugins/mysql#

 

I added

 

[mysqld] 
skip-name-resolve

 

Didn't know how to just restart sql so I restarted the whole server which got rid of my changes.

 

How do I just restart mysql? Also should I have more in my cnf file?

 

I didn't install via unmenu I installed the 5.5 plg file from here (http://lime-technology.com/forum/index.php?topic=20603.msg229248#msg229248)

 

Thanks again!

Link to comment

The file you modified was the config file not the my.cnf. Totally different files. You need to run the command I posted to copy the my.cnf file over then add the skip name resolve line. My my.cnf has 147 lines and yours should be roughly the same.

 

The plugin you used should work just fine. Mine is a modified version of that plugin and has always worked well for me.

 

I run Simple Features and can restart the MySQL server from within the WebGUI. Not sure if you can do that from the stock GUI. Restarting the server completely works too though.

Link to comment

The file you modified was the config file not the my.cnf. Totally different files. You need to run the command I posted to copy the my.cnf file over then add the skip name resolve line. My my.cnf has 147 lines and yours should be roughly the same.

 

The plugin you used should work just fine. Mine is a modified version of that plugin and has always worked well for me.

 

I run Simple Features and can restart the MySQL server from within the WebGUI. Not sure if you can do that from the stock GUI. Restarting the server completely works too though.

 

Copied the file, added the line, restarted. Now it takes 24 seconds to start and 21 seconds to stop. Still slow for my tastes. I would be happy with the 8-10 that you are getting. I'm not sure what's wrong. I might try a reinstall of mysql? Not sure what else to try. I followed the guides step by step.

Link to comment

Understandable. That seems excessive to me as well. You could try a reinstall but it does make me wonder if something else is going on. Did you use the most recent version of the plugin? I believe that uses a slightly newer build of MySQL than I am running. That could potentially make a difference. I assume you are on a gigabit network as well? I am also running an ssd now so I don't know if that is also affecting the time difference.

Link to comment

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.