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));
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="127.0.0.1"; 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='127.0.0.1';", escapeduser)).Rows[0].Item["count"] == 1 : mysql.ExecuteNonQuery(String.Format("DROP USER {0}@127.0.0.1;", escapeduser));