Automatically Create a MySQL Database
Revision as of 14:33, 17 July 2015 by TCAWiki (talk | contribs) (→Backup the database when the service is moved and restore it on the new server)
Contents
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 * FROM mysql.user WHERE user='{0}' AND host='localhost';", escapeduser)).Rows.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 * as count FROM mysql.user WHERE user='{0}' AND host='localhost';", escapeduser)).Rows[0].Count == 1 : mysql.ExecuteNonQuery(String.Format("DROP USER {0}@localhost;", escapeduser));
Create the custom link to phpMyadmin
- If you installed phpMyAdmin you can create a custom. Go back to the game's main settings. Click on the Custom Links icon. Add a new link with these values:
- Name: phpMyAdmin
- Description: Manage your database with phpMyAdmin
- Url: http://$[Service.IpHostname]/phpMyAdmin/index.php?pma_username=![MySQLUser]&pma_password=![MySQLPassword]
- Icon URL: ~/App_Themes/Default/Images/ControlPanel/MenuIcons/Base/Database.png
- Open in a new window: Checked
Using the MySQL variables in templates
You can use the following variables in your mail and config file templates:
- ![MySQLUser]
- ![MySQLPassword]
Optional scripts
Backup the database when the service is moved and restore it on the new server
Operating System: Any Description: Backup MySQL database before moving Script Engine: IronPython Event: Before move 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; from System.IO import FileStream, Path, FileMode, FileAccess; mysql_server="localhost"; mysql_root="root"; mysql_password=""; if not ThisService.Variables.HasValue("MySQLUser") : Script.Exit(); backupfile=Path.Combine(ThisService.RootDirectory, "_backup.sql"); with MySqlManager() as mysql: escapeduser=mysql.PrepareSqlValue(ThisService.Variables["MySQLUser"]); escapedpass=mysql.PrepareSqlValue(ThisService.Variables["MySQLPassword"]); mysql.AutoDisconnect = False; mysql.Connect(String.Format("Data Source={0};User Id={1};Password={2};Pooling=False;", mysql_server, mysql_root, mysql_password)); #only make a backup if database exists and has tables if mysql.Execute(String.Format("SHOW DATABASES LIKE '{0}';", escapeduser)).Rows.Count == 1 : mysql.ExecuteNonQuery(String.Format("USE {0};", escapeduser)); if mysql.Execute("SHOW TABLES;").Rows.Count > 0 : with FileStream(backupfile, FileMode.Create, FileAccess.Write) as file: mysql.DumpDatabase(file); mysql.ExecuteNonQuery(String.Format("DROP DATABASE IF EXISTS {0};", escapeduser)); if mysql.Execute(String.Format("SELECT * FROM mysql.user WHERE user='{0}' AND host='localhost';", escapeduser)).Rows.Count == 1 : mysql.ExecuteNonQuery(String.Format("DROP USER {0}@localhost;", escapeduser));
Operating System: Any Description: Restore MySQL database after move Script Engine: IronPython Event: After move 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; from System.IO import File, Path; mysql_server="localhost"; mysql_root="root"; mysql_password=""; if not ThisService.Variables.HasValue("MySQLUser") : Script.Exit(); backupfile=Path.Combine(ThisService.RootDirectory, "_backup.sql"); 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 * FROM mysql.user WHERE user='{0}' AND host='localhost';", escapeduser)).Rows.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)); if File.Exists(backupfile) : with MySqlManager() as mysql2: mysql2.Connect(String.Format("Data Source={0};Database={1};User Id={1};Password={2};Pooling=False;", mysql_server, ThisService.Variables["MySQLUser"], ThisService.Variables["MySQLPassword"])); mysql2.ImportDatabase(backupfile); File.Delete(backupfile);