TeamSpeak - How to Transfer SQLite to MariaDB

Alligatoras · Mar 29, 2019

Requirements: Perl, MariaDB
Tested on: Ubuntu 18.04 and Centos 7
Special Thanks: @Arcturus for providing the test machines.

Step by Step Guide

You will need to SSH to the machine to do everything so go login.
Stop your currently running teamspeak server.
Code:
```
service teamspeak stop
```
Before you do anything you should take a backup of your server just in case you mess up with anything.
Code:
```
tar -cvf ts3.tar /home/ts3/teamspeak3-server_linux_amd64
```
or
Code:
```
zip -r ts3.zip /home/ts3/teamspeak3-server_linux_amd64
```

Create a new database into MariaDB.

Code:

mysql -u root -p
CREATE DATABASE ts3;
exit;

Go to teamspeak directory.
Code:
```
cd /home/ts3/teamspeak3-server_linux_amd64
```
* put your own path
Create a dump file of the current SQLite file.
Code:
```
sqlite3 ts3server.sqlitedb .dump > ts3dump.sql
```

Create the Perl converter file and put the following code.

Code:

nano sql.pl

Perl:

#! /usr/bin/perl
while ($line = <>){
    if (($line !~  /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/) && ($line !~ /PRAGMA foreign_keys=OFF/)){

        if ($line =~ /CREATE TABLE \"(\w*)\"(.*)/){
                $name = $1;
                $sub = $2;
                $sub =~ s/\"//g;
                $line = "DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n";
        }
        elsif ($line =~ /INSERT INTO \"(\w*)\"(.*)/){
                $line = "INSERT INTO $1$2\n";
                $line =~ s/\"/\\\"/g;
                $line =~ s/\"/\'/g;
        }else{
                $line =~ s/\'\'/\\\'/g;
        }
        $line =~ s/([^\\'])\'t\'(.)/$1THIS_IS_TRUE$2/g;
        $line =~ s/THIS_IS_TRUE/1/g;
        $line =~ s/([^\\'])\'f\'(.)/$1THIS_IS_FALSE$2/g;
        $line =~ s/THIS_IS_FALSE/0/g;
        $line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g;
        $line =~ s/(.*)varchar([^\(0-9\)].*)/$1varchar\(255\)$2/g;
        print $line;
    }
}

Run the Perl file.
Code:
```
perl sql.pl ts3dumb.sql > ts3.sql
```
Now that you have a compatible MySQL file you need to import it to the database we made before.
Code:
```
mysql -u root -p ts3 < ts3.sql
```
Convert and Fix to fully work with MariaDB. Under teamspeak's directory go to "sql/updates_and_fixes" and run the following commands.
Code:
```
mysql -u root -p ts3 < convert_mysql_to_mariadb.sql
mysql -u root -p ts3 < mariadb_fix_latin_utf8.sql
```
Go back to teamspeak's root directory and create the "ts3server.ini" file and add the following lines bellow.
Code:
```
nano ts3server.ini
```
If you already have this file just modify the following lines.
Code:
```
dbplugin=ts3db_mariadb
dbpluginparameter=ts3db_mariadb.ini
dbsqlpath=sql/
dbsqlcreatepath=create_mariadb/
dbconnections=10
```
You will also need to create the file "ts3db_mariadb.ini" so you can make the database connection from teamspeak to mysql.
Code:
```
nano ts3db_mariadb.ini
```
Code:
```
[config]
host=127.0.0.1
port=3306
username=root
password=your_password
database=ts3
```

Copy the MariaDB library from teamspeak's "redist" folder into teamspeak's main folder.

Code:

cp /home/ts3/teamspeak3-server_linux_amd64/redist/libmariadb.so.2 /home/ts3/teamspeak3-server_linux_amd64/libmariadb.so.2

Modify the teamspeak server start script to read the ts3server.ini file on ts3 server start or restart.
Find the following lines:

Code:

ExecStart=/home/ts3/teamspeak3-server_linux_amd64/ts3server_startscript.sh start
ExecReload=/home/ts3/teamspeak3-server_linux_amd64/ts3server_startscript.sh restart

And replace them with:

Code:

ExecStart=/home/ts3/teamspeak3-server_linux_amd64/ts3server_startscript.sh start inifile=ts3server.ini
ExecReload=/home/ts3/teamspeak3-server_linux_amd64/ts3server_startscript.sh restart inifile=ts3server.ini

Reload systemctl to verify the changes on the service start.
Code:
```
systemctl daemon-reload
```
You are ready to start the server again!
Code:
```
service teamspeak start
```

After that update your teamspeak's instance log should return this.

Code:

2019-03-28 16:12:08.739862|INFO    |ServerLibPriv |   |TeamSpeak 3 Server 3.7.1 (2019-03-28 07:46:26)
2019-03-28 16:12:08.740933|INFO    |ServerLibPriv |   |SystemInformation: Linux 3.10.0-957.10.1.el7.x86_64 #1 SMP Mon Mar 18 15:06:45 UTC 2019 x86_64 Binary: 64bit
2019-03-28 16:12:08.746528|INFO    |DatabaseQuery |   |dbPlugin name:    MariaDB plugin, version 3, (c)TeamSpeak Systems GmbH
2019-03-28 16:12:08.746601|INFO    |DatabaseQuery |   |dbPlugin version: 2

You are ready!

You can safely remove these files from the teamspeak directory as they are not needed anymore:

ts3dump.sql
sql.pl
ts3.sql
ts3server.sqlitedb (Old database file. You can keep it for backup if you want.)

PaXe · Apr 26, 2019

Is there a way to do this with a remote MariaDB server?
It always only connected to the local Database, it didn't matter what IP i gave it in the "ts3db_mariadb.ini " file, always just used 127.0.0.1
Then again, maybe that's intentional because of latency or something like that?

It would be pretty handy since I have every bot and so on running on that remote Database, only the server itself that's still on sqlite.
I tried like a month ago but couldn't get it to work and now I saw this post and thought that maybe here someone has some kind of information about that.

FromLondon · Apr 26, 2019

PaXe said:
Is there a way to do this with a remote MariaDB server?
It always only connected to the local Database, it didn't matter what IP i gave it in the "ts3db_mariadb.ini " file, always just used 127.0.0.1
Then again, maybe that's intentional because of latency or something like that?

It would be pretty handy since I have every bot and so on running on that remote Database, only the server itself that's still on sqlite.
I tried like a month ago but couldn't get it to work and now I saw this post and thought that maybe here someone has some kind of information about that.

Doesn't matter where from you run bots (local or remote). They are connecting to TeamSpeak ServerQuery interface (not related to sqlite). Server retrieve data for bots by itself.
Remote MariaDB (100+ ping for each request even with cache) is bad solution.

Joxiii · Apr 27, 2019

Alligatoras said:
Requirements: Perl, MariaDB
Tested on: Ubuntu 18.04 and Centos 7
Special Thanks: @Arcturus for providing the test machines.

Step by Step Guide

You will need to SSH to the machine to do everything so go login.

Stop your currently running teamspeak server.

Code:

service teamspeak stop

Before you do anything you should take a backup of your server just in case you mess up with anything.

Code:

tar -cvf ts3.tar /home/ts3/teamspeak3-server_linux_amd64

or

Code:

zip -r ts3.zip /home/ts3/teamspeak3-server_linux_amd64

Create a new database into MariaDB.

Code:

mysql -u root -p CREATE DATABASE ts3; exit;

Go to teamspeak directory.

Code:

cd /home/ts3/teamspeak3-server_linux_amd64

* put your own path

Create a dump file of the current SQLite file.

Code:

sqlite3 ts3server.sqlitedb .dump > ts3dump.sql

Create the Perl converter file and put the following code.

Code:

nano sql.pl

Perl:

#! /usr/bin/perl while ($line = <>){ if (($line !~ /BEGIN TRANSACTION/) && ($line !~ /COMMIT/) && ($line !~ /sqlite_sequence/) && ($line !~ /CREATE UNIQUE INDEX/) && ($line !~ /PRAGMA foreign_keys=OFF/)){ if ($line =~ /CREATE TABLE \"(\w*)\"(.*)/){ $name = $1; $sub = $2; $sub =~ s/\"//g; $line = "DROP TABLE IF EXISTS $name;\nCREATE TABLE IF NOT EXISTS $name$sub\n"; } elsif ($line =~ /INSERT INTO \"(\w*)\"(.*)/){ $line = "INSERT INTO $1$2\n"; $line =~ s/\"/\\\"/g; $line =~ s/\"/\'/g; }else{ $line =~ s/\'\'/\\\'/g; } $line =~ s/([^\\'])\'t\'(.)/$1THIS_IS_TRUE$2/g; $line =~ s/THIS_IS_TRUE/1/g; $line =~ s/([^\\'])\'f\'(.)/$1THIS_IS_FALSE$2/g; $line =~ s/THIS_IS_FALSE/0/g; $line =~ s/AUTOINCREMENT/AUTO_INCREMENT/g; $line =~ s/(.*)varchar([^$0-9$].*)/$1varchar$255$$2/g; print $line; } }

Run the Perl file.

Code:

perl sql.pl ts3dumb.sql > ts3.sql

Now that you have a compatible MySQL file you need to import it to the database we made before.

Code:

mysql -u root -p ts3 < ts3.sql

Convert and Fix to fully work with MariaDB. Under teamspeak's directory go to "sql/updates_and_fixes" and run the following commands.

Code:

mysql -u root -p ts3 < convert_mysql_to_mariadb.sql mysql -u root -p ts3 < mariadb_fix_latin_utf8.sql

Go back to teamspeak's root directory and create the "ts3server.ini" file and add the following lines bellow.

Code:

nano ts3server.ini

If you already have this file just modify the following lines.

Code:

dbplugin=ts3db_mariadb dbpluginparameter=ts3db_mariadb.ini dbsqlpath=sql/ dbsqlcreatepath=create_mariadb/ dbconnections=10

You will also need to create the file "ts3db_mariadb.ini" so you can make the database connection from teamspeak to mysql.

Code:

nano ts3db_mariadb.ini

Code:

[config] host=127.0.0.1 port=3306 username=root password=your_password database=ts3

Copy the MariaDB library from teamspeak's "redist" folder into teamspeak's main folder.

Code:

cp /home/ts3/teamspeak3-server_linux_amd64/redist/libmariadb.so.2 /home/ts3/teamspeak3-server_linux_amd64/libmariadb.so.2

Modify the teamspeak server start script to read the ts3server.ini file on ts3 server start or restart.
Find the following lines:

Code:

ExecStart=/home/ts3/teamspeak3-server_linux_amd64/ts3server_startscript.sh start ExecReload=/home/ts3/teamspeak3-server_linux_amd64/ts3server_startscript.sh restart

And replace them with:

Code:

ExecStart=/home/ts3/teamspeak3-server_linux_amd64/ts3server_startscript.sh start inifile=ts3server.ini ExecReload=/home/ts3/teamspeak3-server_linux_amd64/ts3server_startscript.sh restart inifile=ts3server.ini

Reload systemctl to verify the changes on the service start.

Code:

systemctl daemon-reload

You are ready to start the server again!

Code:

service teamspeak start

After that update your teamspeak's instance log should return this.

Code:

2019-03-28 16:12:08.739862|INFO |ServerLibPriv | |TeamSpeak 3 Server 3.7.1 (2019-03-28 07:46:26) 2019-03-28 16:12:08.740933|INFO |ServerLibPriv | |SystemInformation: Linux 3.10.0-957.10.1.el7.x86_64 #1 SMP Mon Mar 18 15:06:45 UTC 2019 x86_64 Binary: 64bit 2019-03-28 16:12:08.746528|INFO |DatabaseQuery | |dbPlugin name: MariaDB plugin, version 3, (c)TeamSpeak Systems GmbH 2019-03-28 16:12:08.746601|INFO |DatabaseQuery | |dbPlugin version: 2

You are ready!

You can safely remove these files from the teamspeak directory as they are not needed anymore:

ts3dump.sql

sql.pl

ts3.sql

ts3server.sqlitedb (Old database file. You can keep it for backup if you want.)

Why you want to use MariaDB/mysql instead of SQLITEDB?

TeamSpeak - How to Transfer SQLite to MariaDB

Alligatoras

PaXe

Member

FromLondon

Honk Honk

Joxiii

Discord hater!