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. Add the following scripts. They will create the database and user when the service is created and delete them when the service is deleted. 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="localhost";
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='localhost';", escapeduser)).Rows[0].Item["count"] == 1 :
  mysql.ExecuteNonQuery(String.Format("DROP USER {0}@localhost;", escapeduser));

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

Operating System: Any
Description: Delete MySQL database and user
Script Engine: IronPython
Event: Before deleted
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="localhost";
mysql_root="root";
mysql_password="";

if not ThisService.Variables.HasValue("MySQLUser") :
 Script.Exit();
 
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='localhost';", escapeduser)).Rows[0].Item["count"] == 1 :
  mysql.ExecuteNonQuery(String.Format("DROP USER {0}@localhost;", escapeduser));