Difference between revisions of "Automatically Create a MySQL Database"
Line 31: | Line 31: | ||
ThisService.Variables["MySQLPassword"] = Random.RandomString(10,True,True); | ThisService.Variables["MySQLPassword"] = Random.RandomString(10,True,True); | ||
ThisService.Save(); | 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)); |
Revision as of 14:05, 27 February 2015
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.
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));