Issue: How can I migrate a Teamspeak 3 server on Sqlite to a Docker container featuring MariaDB as a database?
Although this seems to be rather straightforward as there is an official Image of Teamspeak 3 server on Dockerhub, there are still a few hoops you got to jump through, especially regarding the MariaDB database.
I used the docker-compose file from the Dockerhub image as a starting point and modified it a little bit to include volume mounts and container-names:
version: '3.1' services: teamspeak: image: teamspeak:3.9.1 container_name: teamspeak restart: always volumes: - "/var/docker-volumes/teamspeak:/var/ts3server/" ports: - 9987:9987/udp - 10011:10011 - 30033:30033 environment: TS3SERVER_DB_PLUGIN: ts3db_mariadb TS3SERVER_DB_SQLCREATEPATH: create_mariadb TS3SERVER_DB_HOST: db TS3SERVER_DB_USER: root TS3SERVER_DB_PASSWORD: example TS3SERVER_DB_NAME: teamspeak TS3SERVER_DB_WAITUNTILREADY: 30 TS3SERVER_LICENSE: accept db: image: mariadb:10.4.8 container_name: teamspeak-db volumes: - "/var/docker-volumes/teamspeak-db:/var/lib/mysql" restart: always environment: MYSQL_ROOT_PASSWORD: example MYSQL_DATABASE: teamspeak
Create the directories used as docker volumes in the previous docker-compose.yaml file:
mkdir -p /var/docker-volumes/teamspeak mkdir -p /var/docker-volumes/teamspeak-db
Start the docker-compose file and thus generate a new Teamspeak 3 server. I would advise testing if you can connect to the server running in the Docker container to mitigate issues in following migration.
After verifying you can connect, shut the running containers down with docker-compose down. Next we’re going to dump the Sqlite database to migrate it. For this install sqlite3 if it’s not already present.
apt-get install sqlite3
Shut down your running Teamspeak 3 server by using the stop command:
After the server stopped locate your sqlite database file called ts3server.sqlitedb – for me the file was located in /usr/local/teamspeak3/teamspeak3-server_linux_amd64/ts3server.sqlitedb
Dump the database using sqlite3 to a file:
sqlite3 ts3server.sqlitedb .dump > db_dump
As the SQL syntax is a little different for Sqlite, Mysql and MariaDB we will have to convert the database dump to match the MariaDB syntax. There is quite a lot of information on this found on the internet, although some manipulation tutorials did not work for me. In the end I have used the following script found on Github: https://github.com/NotoriousPyro/SQLite-to-MariaDB-MySQL-perl-script
Just follow the instructions mentioned there to convert the database dump. For some reason the script missed a few quote characters in my dump which I think shouldn’t be there, as the lines before and after running the same operations do not have them either. So just remove the ” characters, if there are any:
CREATE INDEX index_custom_fields_serverid ON "custom_fields_old" (server_id); CREATE INDEX index_custom_fields_client_id ON "custom_fields_old" (client_id); CREATE INDEX index_custom_fields_ident ON "custom_fields_old" (ident);
For further preparing the database content for MariaDB after importing the converted database dump we’re going to need 2 more files which are located in your old Teamspeak server installation. You’ll find further information in the doc directory contained there: doc/update_mysql_to_mariadb.txt
The 2 files we will need later are located in the sql directory:
Copy the converted database dump and the previously copied migration files into the volume of the MariaDB container:
cp db_dump_mariasql /var/docker-volumes/teamspeak-db/ cp convert_mysql_to_mariadb.sql /var/docker-volumes/teamspeak-db/ cp mariadb_fix_latin_utf8.sql /var/docker-volumes/teamspeak-db/
Start only the MariaDB container up, do not start Teamspeak. We do not want anything to interfere with our database operations. Then connect into the container.
docker exec -it teamspeak-db /bin/bash
Change directory to the database dump and connect to the database.
cd /var/lib/mysql mysql -u root -p
Delete the generated teamspeak database, create it new, exit the connection and reconnect with importing the database dump. The last command should complete without any output.
DROP DATABASE teamspeak; CREATE DATABASE teamspeak; exit mysql -u root -p teamspeak < db_dump_mariasql
To prepare the database for the new MariaDB format execute both previously copied scripts. Disregard log statements mentioning that the table does not support repair or optimize and exit the container shell.
mysql -u root -p teamspeak < convert_mysql_to_mariadb.sql mysql -u root -p teamspeak < mariadb_fix_latin_utf8.sql exit
If there are any avatars, files or IP black- and whitelists you will have to copy those from the Teamspeak directory to the Teamspeak container volume as those are not stored in the database. Further information of the files and their contents can be found here.
Now start the Teamspeak container and you should be able to connect to a fully migrated server.