Accessing the MySQL-Database

From TrackmaniaServer Wiki

You can directly access the Database of your server via your Admin-Panel

Contents 1 Important !! 2 How to login to your database 3 Importing a Database (SQL-Dump) 4 Exporting a Database (SQL-Dump) 5 Frequently used commands 6 Cleaning up the Database 7 Frequently asked questions 7.1 How do I remove a challenge from the database correctly? 7.2 How do I connect to the database from my own webserver?

Important !!

Create an emergency dump (Reset-Tab -> Create Emergeny Dump) before making any changes on the database, so that we can restore it in case of a crash. Make sure to wait until the Dump-Job is complete before starting the database edit.

How to login to your database

1. Visit the Admin-Panel of your server here: http://trackmaniaserver.org/administration
2. Select your server
3. Scroll down. On the left side you find the login credentials for the database.
4. Click the link to the database interface and enter the login details you obtained in step 3 (If you can not login, restart the server!)
5. Select the desired database at the left
   1. TM-Forever Servers: Select aseco_rentXXX
   2. TM2/Canyon Servers: Select xaseco2_rentXXX

Importing a Database (SQL-Dump)

If you have a dump of an old Xaseco(2) Database, you may import it. Note: The dump MUST be compatible to the current Xaseco(2)-Version we are running! If it is not compatible, your server may crash. The maximum filesize for import is 50Mb. If your file is bigger, split it manually to 2 or 3 files.

1. Follow the steps above and make sure you selected the correct database (Step 5 above)
2. Click Import at the top
3. Select the SQL-Dump from your local Harddrive (.gz encoded file is good)
4. Click OK at the bottom and wait

Exporting a Database (SQL-Dump)

It is highly recommended to export the database from time to time to prevent any dataloss.

1. Select the desired database (See How to login to your database above)
2. Click Export at the top of the interface
3. Select SQL at the format box
4. Click OK and save the file to your local harddisk

Frequently used commands

Here you can find some common SQL-Commands. Execute these commands by clicking on SQL at the top of the database interface and paste the command into the textarea.

• Clear TOP-Donators: UPDATE players_extra SET Donations=0
• Clear all Records: (TM2) TRUNCATE TABLE records; TRUNCATE TABLE maps;
• Clear all Records: (TMF) TRUNCATE TABLE records; TRUNCATE TABLE challenges;
• Clear all local Karma-Votes: TRUNCATE TABLE rs_karma;

Cleaning up the Database

Attention: We assume no liability, responsibility or warranty for this process. You do it on your own risk.

To remove all tracks, records, votes from the XAseco/XAseco2-Database, that are no more on the server, perform the following steps:

1. Kick all players from your server and set a player- and spectator password
2. Restart the servertool to get the latest updates for this feature and wait until the restart is complete (important!)
3. Backup your database as described above (See Exporting a Database) (important!)
4. Connect to your server as Master-Admin
5. Press <SPACE> to open the chat
6. Enter the following command: /rent cleanupdb
7. Wait until the cleanup is complete
8. Restart the servertool
9. Remove the passwords from the server

However, if something went wrong, just import the file you previously exported (Step 3) and restart the servertool again.

Frequently asked questions

Here we collect some frequently asked questions concerning MySQL.

How do I remove a challenge from the database correctly?

The easiest way is to activate the plugin "Cheated Records Killer" and type "/rmcheat" ingame to delete records. Anyway, if you want to use the MySQL-Interface, go to the table challenges (on TM2 it is called maps) and find the track you want to delete. Note the Id of the challenge. Now click SQL at the top and enter the following into the textarea. Replace XX by the Id of the challenge you got before:

TM-Nations/United-Forever-Servers with Xaseco

DELETE FROM records WHERE ChallengeId = XX;
DELETE FROM challenges WHERE Id=XX;

TM2-Canyon-Servers with XAseco2

DELETE FROM records WHERE MapId = XX;
DELETE FROM maps WHERE Id=XX;

How do I connect to the database from my own webserver?

All information can be found at the Admin-Panel of your gameserver.

• IP: The IP-Address of your gameserver
• Port: 3306
• Username: Your Server-ID lowercase. Example rent1285
• Password: The XMLRPC-Password.
• Make sure that
  • ...your host is using MySQL-Client Version MySQL 5.5.21 QLV, otherwise the connection is not possible
  • ...your host allows connecting to external DB-Servers.