Automatically Create a MySQL Database

Preparing the server

• Install the MySQL server on your server.
• Install phpMyAdmin if you want to provide a way to manage the database.

Configure the game

Create variables

• Configure variables for the MySQL user and password. Go to Settings > Games > Select the game > Variables. Create 2 new variables:
  • Name: MySQLUser
  • Preserve value: Checked
  • Name: MySQLPassword
  • Preserve value: Checked

Create the scripts

• Go back to the game's main settings. Click on the Custom Scripts icon. Create the following scripts. Make sure you update the value of mysql_password in all of the scripts.

Operating System: Any
Description: Generate MySQL user and password
Script Engine: IronPython
Event: Before created
Ignore execution errors Unchecked
Script:

import clr;
import System;

clr.AddReference("TCAdmin.SDK");
from TCAdmin.SDK.Misc import Random;
from System import String;

ThisService.Variables["MySQLUser"] = String.Format("db{0}", ThisService.ServiceId);
ThisService.Variables["MySQLPassword"] = Random.RandomString(10,True,True);
ThisService.Save();

Operating System: Any
Description: Create MySQL database and user
Script Engine: IronPython
Event: After created
Ignore execution errors Unchecked
Script:

import clr;
import System;

clr.AddReference("TCAdmin.DatabaseProviders.MySql");
clr.AddReference("TCAdmin.SDK");
from TCAdmin.DatabaseProviders.MySql import MySqlManager;
from System import String;

mysql_server="127.0.0.1";
mysql_root="root";
mysql_password="";

with MySqlManager() as mysql:
 escapeduser=mysql.PrepareSqlValue(ThisService.Variables["MySQLUser"]);
 escapedpass=mysql.PrepareSqlValue(ThisService.Variables["MySQLPassword"]);
 mysql.Connect(String.Format("Data Source={0};User Id={1};Password={2};Pooling=False;", mysql_server, mysql_root, mysql_password));

 mysql.ExecuteNonQuery(String.Format("DROP DATABASE IF EXISTS {0};", escapeduser));
 if mysql.Execute(String.Format("SELECT COUNT(*) as count FROM mysql.user WHERE user='{0}' AND host='127.0.0.1';", escapeduser)).Rows[0].Item["count"] == 1 :
  mysql.ExecuteNonQuery(String.Format("DROP USER {0}@127.0.0.1;", escapeduser));

 mysql.ExecuteNonQuery(String.Format("CREATE DATABASE {0};", escapeduser));
 mysql.ExecuteNonQuery(String.Format("GRANT ALL PRIVILEGES ON {0}.* TO '{0}'@'127.0.0.1' IDENTIFIED BY '{1}';", escapeduser, escapedpass));