Ians Ramblings

I bought a 2TB network storage device the other day a WD MyBook Live but wondered if I could get my old website back up and running on it as well as using it for a media store and for streaming to my PS3. While I was there I got the FTP service working properly so I could access it externally through my router.

These are the steps I took to get MySQL and WordPress installed and running incase it helps anyone else looking to do the same.

Download the config file from your MyBook live under
Settings then Utilities.
Edit the file and set ssh enabled to yes
Re-upload the config using the utilities page again, the box will then reboot
SSH into the box using putty for example

root welc0me

once logged in type

passwd

and press enter
enter new root password
Any changes you want to make to the FTP server can be made in /etc/vsftpd.conf
By the way default passive port range is 5000-5099 which you can tweak.
Update the following to Sources.list in /etc/apt/sources.list
change

deb ftp://ftp.us.debian.org/debian main

to

deb ftp://ftp.us.debian.org/debian main contrib non-free

Run

apt-get update
apt-get install mysql-server
apt-get install wget

Run

wget http://wordpress.org/latest.tar.gz
tar xvfz latest.tar.gz
mv wordpress /var/www/yourownname

Then just go to http://nasboxip/yourownname/ in your browser to begin the configuration of wordpress
Once complete you will want to add a virtual host to your Apache config in /etc/apache2/sites-enabled/

I also then changed the existing Apache config file to only allow access from the LAN that way, as it is the default domain it will stop any external web access to the password protected admin interface just in-case there is a weakness in their authentication code.

Just a little post incase people want an example of using mysqldump to select specific rows from a table.

mysqldump -hdbhostname.com -uremote -pxxxxx --where="type_id=2 AND copyright_id IN (22,2227,3084,424)" dbname tablename > output.sql

Without table creation

mysqldump -hdbhostname.com -uremote -pxxxxx --where="type_id=2 AND copyright_id IN (22,2227,3084,424)" -t dbname tablename > output.sql

I have just been making a new Sphinx index, we needed to index the dates of items in the database as well as the fulltext information and some integer fields for filtering. That all seems very straightforward, there’s even a timestamp type in sphinx, so following the examples you would just use MySQL’s UNIX_TIMESTAMP function to get you seconds since the epoch. The problem is dates before 1970 of course, as MySQL just returns 0 for those as expected, and even if you did some trickery, sphinx won’t support negative timestamp values.

At the moment, I am using TO_DAYS for my date fields, and then doing TIME_TO_SECS for the time field and storing them in unsigned 32 bit integer attributes for filtering. This is fine for us because we actually filter on the days only, but we do use the time in the sorting, so I can just use the Sphinx extended sorting syntax.

Finally on the code side, you would need to use something like Date_to_Days from Date::Calc(for Perl) to convert inputted dates into days.

The other day I was looking into how to bring back some results from a MySQL table but to use my own ordering rather than ordering by one of the columns. This is useful for example, if you use Sphinx or a Semantic search engine to fetch the IDs of documents that match a search and then want to pull back the full row data by the primary key.

I discovered: -

[code]SELECT * FROM table WHERE id IN (7,6,3,4,5,2,1) ORDER BY FIELD(id,1324576);[/code]

where id is your primary key for example and the ID’s then listed in the order you want them returned. Just thought I would share it, incase anyone else had an interest.

If you are struggling to find and generate the wordforms text file for Sphinx using Ispell I as I was perhaps this generated file will help you. Apparently versions of spelldump earlier than 0.9.9rc2 don’t generate the file correctly.

This file basically helps with tokenising the words in your Sphinx index, for example walked walks both become walk. I am hoping it will speed up search speed, even though index speed is going to be slower.

en_dict.tar.gz

This is just something I was wondering about, whether I could back up specific rows I am going to modify during test, so I can re-insert them all after I have finished and deleted my test data.

To selectively dump just specifc rows from your mysql table you can use the following:

mysqldump --where="domain_id='7'" -t -umyueser mydatabase mytable > mydumpfile.sql

Omitting the “-t” option will also place the drop and re-create table SQL into your dumpfile. It can be handy if you just want to backup a specific set of data from your db. Note it does not create REPLACE INTO’s though so you would need to delete the matching rows before restoring from such a dumpfile.

You can globally replace the INSERT INTO statements with REPLACE INTO using the following command

sed -i 's/INSERT INTO `metadata`/REPLACE INTO `metadata`/g' mydumpfile.sql