Tutorial TeamSpeak - How to Transfer SQLite to MariaDB

Alligatoras

Administrator
Joined
Mar 31, 2016
Messages
2,044
Points
326
Age
25
Location
Greece
Requirements: Perl, MariaDB
Tested on: Ubuntu 18.04 and Centos 7
Special Thanks: @Arcturus for providing the test machines.

Step by Step Guide
  1. You will need to SSH to the machine to do everything so go login.

  2. Stop your currently running teamspeak server.
    Code:
    service teamspeak stop
  3. 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
  4. Create a new database into MariaDB.
    Code:
    mysql -u root -p
    CREATE DATABASE ts3;
    exit;
  5. Go to teamspeak directory.
    Code:
    cd /home/ts3/teamspeak3-server_linux_amd64
    * put your own path

  6. Create a dump file of the current SQLite file.
    Code:
    sqlite3 ts3server.sqlitedb .dump > ts3dump.sql
  7. 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;
        }
    }
  8. Run the Perl file.
    Code:
    perl sql.pl ts3dumb.sql > ts3.sql
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. Reload systemctl to verify the changes on the service start.
    Code:
    systemctl daemon-reload
  16. You are ready to start the server again!
    Code:
    service teamspeak start
  17. 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

New Member
Joined
May 17, 2017
Messages
1
Points
15
Location
Austria
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

Honk Honk
VIP
Joined
May 20, 2016
Messages
191
Points
78
Age
24
Location
Russia
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

Discord hater!
Joined
Feb 2, 2016
Messages
264
Points
92
Location
Germany
Requirements: Perl, MariaDB
Tested on: Ubuntu 18.04 and Centos 7
Special Thanks: @Arcturus for providing the test machines.

Step by Step Guide
  1. You will need to SSH to the machine to do everything so go login.

  2. Stop your currently running teamspeak server.
    Code:
    service teamspeak stop
  3. 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
  4. Create a new database into MariaDB.
    Code:
    mysql -u root -p
    CREATE DATABASE ts3;
    exit;
  5. Go to teamspeak directory.
    Code:
    cd /home/ts3/teamspeak3-server_linux_amd64
    * put your own path

  6. Create a dump file of the current SQLite file.
    Code:
    sqlite3 ts3server.sqlitedb .dump > ts3dump.sql
  7. 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;
        }
    }
  8. Run the Perl file.
    Code:
    perl sql.pl ts3dumb.sql > ts3.sql
  9. 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
  10. 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
  11. 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
  12. 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
  13. 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
  14. 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
  15. Reload systemctl to verify the changes on the service start.
    Code:
    systemctl daemon-reload
  16. You are ready to start the server again!
    Code:
    service teamspeak start
  17. 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?
 

Top