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.

4 comments on “Convert TS3 from sqlite to mysql database”

  1. BarryBlack

    I cannot get this to work for the love of me 😐

    I am getting the same error, and i’ve done exactly as instructed here and other places, but to no prevail 😐
    |CRITICAL|DatabaseQuery | | unable to load database plugin library “libts3db_mysql.so”, halting

    I am using 64bit ubuntu 10.24 server version, teamspeak version is 3.0.0.rc1 or whatever it’s called.
    It finds my libmysqlclient.so.15, but linking, re-downloading/installing or whatever, nothing works 😐

    Any idea’s?

    • SleePy

      BarryBlack,

      Are you using the startup script provided in the linux download?

      I receive that error when manually starting TS3 without having the proper environment variable set and/or not being the directory where the ts3 binary is.

      Also note by default that the startup script doesn’t call the ini file I mentioned here. You have to modify it to do that:
      COMMANDLINE_PARAMETERS=”inifile=ts3server.ini”

  2. BarryBlack

    ./ts3server_minimal_runscript.sh inifile=ts3server.ini
    worked right away …. -.-

    Gotta love user stupidity, right? 🙂
    Ty anyway, your post was to big help!

Leave a Reply

Highslide for Wordpress Plugin