Knowledgebase
How can I convert a MySQL database to an SQLite database (or the other way around)

You cannot convert the database from one type to another without issues. While there are tools around that offer to convert MySQL to SQLite and vice versa, in our experience the conversion never goes without issues and you will most certainly end up messing up the database causing errors in the long run, which will ultimately corrupt your database forcing you to start from scratch.

What you could do however is use snapshots to move the servers between instances and thus indirectly "converting" your database.
You will need to schedule a maintenance window during which all of your virtual servers on the instance that you want to "convert" will be unavailable.

Then you will need to set up a new instance with the default SQLite database backend. Keep in mind to use the defaults.sql recommended for ATHPs (available here: http://media.teamspeak.com/literature/defaults.sql ) before you start the new instance for the first time.
In addition to that, if you want all the icons, files and avatars to be preserved you should copy the files folder from the SQLite instance over to the MySQL instance folder before you start the new instance with the MySQL backend. If you skip this step the icons, files and avatars will have to be set/uploaded again afterwards.

Then login to the query interface of the new instance and delete the virtual server that has been created by default.

Now you connect to the query interface of your running TeamSpeak instance that is using MySQL.

Now that you are connected to the query interface of both the new instance (running SQLite) and the old instance (running MySQL) you need to do the following for each and every virtual server that is running on the server instance using MySQL:

On the MySQL server query:

serverstop sid=VIRTUALSERVER_ID
use sid=VIRTUALSERVER_ID
serversnapshotcreate

Now copy the resulting string (excluding the newline at the end and the error id=0 msg=ok) and do the following on the SQLite server query connection:

serversnapshotdeploy LONG_STRING_YOU_JUST_COPIED
serverstart sid=VIRTUALSERVER_ID

Back to the query interface of the MySQL server instance do the following:
serveredit virtualserver_autostart=0


Repeat the above for every virtual server on the MySQL instance.

As the output of "serversnapshotcreate" is very large, it is advisable to use a telnet client that can log output from the connection to a file or increase the amount of text that is being kept in the window so you can be sure to get the entire output to copy. You have to be absolutely certain to copy the exact output as given by the query interface for serversnapshotcreate, any new line characters or control characters that are copied which were not part of the original output will cause the snapshot to be invalid and thus fail to import on the SQLite instance.



After all of the virtual servers on the MySQL instance have been deployed in the SQLite Instance without errors, you should restart the SQLite instance. You should NOT shut the MySQL instance down just yet!!! (IMPORTANT)

All virtual servers that were running before should now be running again and all users should be able to connect without issues (provided you run both instances on the same machine, otherwise the IP would've changed) and should still have all their channels, permissions etc. The only thing that will be missing are any uploaded files which includes icons and avatars. Those will have to be uploaded again.

After you have confirmed that all virtual servers are running without problems and everything is ok you can now shutdown the TeamSpeak instance that was running on MySQL.

(15 vote(s))
Helpful
Not helpful

Comments (0)