Convert TS3 from sqlite to mysql database

I run a teamspeak server and it uses teamspeak3.  However when I set it up, I didn’t bother getting any further than getting it running.  Now I find out that its using sqlite for a database and that database is taking up a lot of data for useless logs.

First step was to figure out how to convert the database.  After some thankless google searches I found something that worked (after my own edits to it):

sqlite3 ts3server.sqlitedb .dump | egrep -vi ‘^(BEGIN TRANSACTION|PRAGMA|COMMIT|INSERT INTO “devices”|INSERT INTO “sqlite_sequence”|DELETE FROM “sqlite_sequence”)’ | perl -pe ‘s/INSERT INTO \”(.*)\” VALUES/INSERT INTO \1 VALUES/’ | perl -pe ‘s/AUTOINCREMENT/auto_increment/’ | perl -pe ‘s/varchar\)/varchar\(255\)\)/’ > tsdb.sql

Basically it dumps the database, then we remove the things that mysql doesn’t understand or are useless for mysql, and finally it fixes some stuff up so its a proper database script acceptable by mysql.  Then I just went to importing it.  I setup a teamspeak database and user before I did this.

mysql -u teampseak -p teamspeak < tsdb.sql

For the next part, I was just testing.  First I created a ts3server.ini file, then added the agrument into it:

dbplugin=ts3db_mysql

I tried to start up the server but failed.  It seems from google searches others are getting this error as well:

|CRITICAL|DatabaseQuery |   | unable to load database plugin library “libts3db_mysql.so”, halting

It turns out that it needs a library file on the server.  You can find this out with the ldd command:

$ ldd libts3db_mysql.so
linux-vdso.so.1 =>  (0x00007fffa27ff000)
libmysqlclient.so.15 => not found
libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0x00007f272c3f8000)
libm.so.6 => /lib/libm.so.6 (0x00007f272c174000)
libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x00007f272bf5d000)
libc.so.6 => /lib/libc.so.6 (0x00007f272bbda000)
/lib64/ld-linux-x86-64.so.2 (0x00007f272c919000)

So I had hopped that I had some sort of file:

/usr/lib/libmysqlclient.so.16
/usr/lib/libmysqlclient.so.16.0.0

I had located those two files, but I couldn’t get them to work.  Suggestions from searching showed people symlinking the .15 version to their teamspeak home directory.  I tried to just use the .16, but no go.  Back to google to find out how to get that file for my version of ubuntu.  I tried to do a apt-get on “libmysqlclient15off” as suggested name elsewhere, but no luck for my ubuntu version.  I found out I could just pull it right from the package server directly.  That works out for me 🙂  I use 64 bit, so I got the 64 bit version:

$ wget http://mirrors.kernel.org/ubuntu/pool/universe/m/mysql-dfsg-5.0/libmysqlclient15off_5.1.30really5.0.83-0ubuntu3_amd64.deb

$ dpkg -i libmysqlclient15off_5.1.30really5.0.83-0ubuntu3_amd64.deb

Tried to restart teamspeak, still no luck.  So I tried the symlink suggestion (while working in my teamspeak install location):

$ ln -s /usr/lib/libmysqlclient.so.15 libmysqlclient.so.15

Finally it worked, but gave errors because I never setup the ini file that contained the mysql user details (ts3db_mysql.ini).  So I created that and restarted teamspeak again.  The format of the file is as follows:

[config]
host=localhost
port=3306
username=mysql_user_name
password=Your_cool_password
database=mysql_database_name
socket=

Finally, things where working :).  After that I also used the “createinifile=1” attribute when I started the server so it would dump all current contents of my configuration into a ini file.

I setup my log folder for teamspeak via a symlink (as you can’t move it to /var/log directly since it was running as a unprivileged user) to a folder in /var/log (I called mine ts3).  I wanted to setup autorotation of the log files (since the server almost never goes down and I don’t want a 100 mb log file :P).  Alas, it seems to of gotten the best of me so far.  I haven’t had time to figure out how to get it to auto rotate the log files out.

The only other issue is teamspeak also seems to log files into the database (two places!).  I just ran this manually, but I may have to setup a cron script to auto do this for me later on:

DELETE from log WHERE log_timestamp > unix_timestamp() – 2592000

That little command will delete all logs older than 30 days.  Which is more than good for me.  I haven’t even read the logs since I set it up.