Go Back   HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials > ISPConfig 3 > Installation/Configuration

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Reply
 
Thread Tools Display Modes
  #1  
Old 10th October 2012, 20:53
SuperJC SuperJC is offline
Junior Member
 
Join Date: Oct 2012
Posts: 9
Thanks: 2
Thanked 3 Times in 2 Posts
Question ISPConfig 3 - Automated MultiServer Setup - Problems with System Server Services Mods

Hi All,

Been using ISPConfig for awhile, mostly with single server setups, but a few test runs of multiserver setups. Really great software! We appreciate all the hard work that goes into it's development and support!

Based off some existing scripts (referenced elsewhere in these forums), we've successfully been able to create an automated script with a combination of bash and expect that can setup up everything unattended (after the basic OS install). We are having trouble with one last step.

This step involves modifying the services provided by each server. In the Web Interface this is under System > Server Services > <server_name>. We can successfully modify the sql to change the services, but it seems to miss some setting, and the monitoring thinks the FTPServer should be running on all the systems.

Here's the sql (run from bash):

Code:
mysql -u${LCL_MYSQL_ROOT_USERNAME} -p${LCL_MYSQL_ROOT_PASSWORD} -D${LCL_ISPCONFIG_MYSQL_DB} << SQL2
UPDATE server SET mail_server=0, web_server=1, dns_server=0, file_server=1, db_server=0, vserver_server=0, proxy_server=0, firewall_server=1, updated=1 WHERE server_name='${LCL_SERVER_FQDN_WEB}';
UPDATE server SET mail_server=1, web_server=0, dns_server=0, file_server=0, db_server=0, vserver_server=0, proxy_server=0, firewall_server=1, updated=2 WHERE server_name='${LCL_SERVER_FQDN_MAIL}';
UPDATE server SET mail_server=0, web_server=0, dns_server=0, file_server=0, db_server=1, vserver_server=0, proxy_server=0, firewall_server=1, updated=3 WHERE server_name='${LCL_SERVER_FQDN_DB}';
UPDATE server SET mail_server=0, web_server=0, dns_server=1, file_server=0, db_server=0, vserver_server=0, proxy_server=0, firewall_server=1, updated=4 WHERE server_name='${LCL_SERVER_FQDN_DNS1}';
UPDATE server SET mail_server=0, web_server=0, dns_server=1, file_server=0, db_server=0, vserver_server=0, proxy_server=0, firewall_server=1, updated=5 WHERE server_name='${LCL_SERVER_FQDN_DNS2}';
SQL2
(Obviously the variables get replaced with the necessary information.) What else do we need to do? I notice that when the changes are made from the Web Interface, the only difference (that I could find anyway) is that the changes are logged into the sys_datalog table as well. Do we need to emulate that from our script? If so, what would we insert? The information in there seems somewhat cryptic.

Any help, or leads to finding what we need are appreciated! Thanks in advance!

Of course, we can always resort to using the Web Interface to make the changes, however, we would like to fully automate the install end to end!

Thanks and regards,
SuperJC
Reply With Quote
Sponsored Links
  #2  
Old 11th October 2012, 08:44
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,977
Thanks: 825
Thanked 5,369 Times in 4,216 Posts
Default

All cahnges that you do in the mysql database of the amster server have to be done with the ispconfig remote api or at least the ispconfig mysql api which has special functions to write the datalog. If you do changes in the database manually in the database and thes echanges dont have a valid transaction record in sys_datalog, the changes will be ignored and not transferred to the slave server.

The sys datalog is a transaction log which contains serialized objects of changed record with the state of the record before and after the change to allow the servers to identify which columns and values have been changed in a databse record.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
The Following 2 Users Say Thank You to till For This Useful Post:
adamassasda291 (30th August 2013), SuperJC (11th October 2012)
  #3  
Old 11th October 2012, 16:03
SuperJC SuperJC is offline
Junior Member
 
Join Date: Oct 2012
Posts: 9
Thanks: 2
Thanked 3 Times in 2 Posts
Default

Quote:
Originally Posted by till View Post
All cahnges that you do in the mysql database of the amster server have to be done with the ispconfig remote api or at least the ispconfig mysql api which has special functions to write the datalog. If you do changes in the database manually in the database and thes echanges dont have a valid transaction record in sys_datalog, the changes will be ignored and not transferred to the slave server.

The sys datalog is a transaction log which contains serialized objects of changed record with the state of the record before and after the change to allow the servers to identify which columns and values have been changed in a databse record.
Hi Till,

Thanks for your reply and the timely information. So now I will have to try to figure out the API. My first scan of the remote APIs doesn't seem to turn up one that could be used for this purpose. I do see a function in db_mysql.inc.php that I could possibly leverage. Not quite sure how to do so. However, I do see some info here that might help me: http://www.howtoforge.com/how-to-cre...or-ispconfig-3

If anyone has some helpful hints on using these APIs and functions it would be greatly appreciated!

Thanks,
SuperJC
Reply With Quote
  #4  
Old 11th October 2012, 16:06
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 35,977
Thanks: 825
Thanked 5,369 Times in 4,216 Posts
Default

You can do this with the datalogUpdate function of the db_mysql.inc.php script. It executes the sql query and creates the nescessary information for the datalog.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
The Following User Says Thank You to till For This Useful Post:
SuperJC (11th October 2012)
  #5  
Old 11th October 2012, 16:09
SuperJC SuperJC is offline
Junior Member
 
Join Date: Oct 2012
Posts: 9
Thanks: 2
Thanked 3 Times in 2 Posts
Default

Quote:
Originally Posted by till View Post
You can do this with the datalogUpdate function of the db_mysql.inc.php script. It executes the sql query and creates the nescessary information for the datalog.
Thanks again. That was the function I had my eye on! Now I just have to figure out how to use it. I think I have some ideas, so we'll see how it goes!

Thanks for your help!

Take care,
SuperJC
Reply With Quote
  #6  
Old 11th October 2012, 23:31
SuperJC SuperJC is offline
Junior Member
 
Join Date: Oct 2012
Posts: 9
Thanks: 2
Thanked 3 Times in 2 Posts
Lightbulb

Quote:
Originally Posted by till View Post
You can do this with the datalogUpdate function of the db_mysql.inc.php script. It executes the sql query and creates the nescessary information for the datalog.
Quote:
Originally Posted by SuperJC View Post
...That was the function I had my eye on! Now I just have to figure out how to use it. I think I have some ideas, so we'll see how it goes!...
Piecing together information I found in various posts on this forum, I think I was able to figure it out. Here's the script I worked up. It probably could be refined, and I'm open to suggestions & improvements!

Code:
#!/bin/bash
[ $# -eq 1 ] || { echo -e "\nError:\tConfiguration file needed ...\nUsage:\t${0##*/} <configuration_file.conf>\n"; exit 1; }
LCL_CONF_FILE="${1}"
# source in the variables set in the config file
source ${LCL_CONF_FILE}

php <<PHP1
<?php
include_once('/usr/local/ispconfig/interface/lib/config.inc.php');
\$conf['start_session'] = false;
include_once('/usr/local/ispconfig/interface/lib/app.inc.php');
include_once('/usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php');

	global \$app;
	\$tablename = "server";
	\$index_field = "server_name";

	// WEB
	\$index_value = "${LCL_SERVER_FQDN_WEB}";
	\$update_data = array(	"mail_server"		=>	"0",
				"web_server"		=>	"1",
				"dns_server"		=>	"0",
				"file_server"		=>	"1",
				"db_server"		=>	"0",
				"vserver_server"	=>	"0",
				"proxy_server"		=>	"0",
				"firewall_server"	=>	"1",
				"updated"		=>	"1",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);

	// MAIL
	\$index_value = "${LCL_SERVER_FQDN_MAIL}";
	\$update_data = array(	"mail_server"		=>	"1",
				"web_server"		=>	"0",
				"dns_server"		=>	"0",
				"file_server"		=>	"0",
				"db_server"		=>	"0",
				"vserver_server"	=>	"0",
				"proxy_server"		=>	"0",
				"firewall_server"	=>	"1",
				"updated"		=>	"2",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);

	// DB
	\$index_value = "${LCL_SERVER_FQDN_DB}";
	\$update_data = array(	"mail_server"		=>	"0",
				"web_server"		=>	"0",
				"dns_server"		=>	"0",
				"file_server"		=>	"0",
				"db_server"		=>	"1",
				"vserver_server"	=>	"0",
				"proxy_server"		=>	"0",
				"firewall_server"	=>	"1",
				"updated"		=>	"3",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);

	// DNS1
	\$index_value = "${LCL_SERVER_FQDN_DNS1}";
	\$update_data = array(	"mail_server"		=>	"0",
				"web_server"		=>	"0",
				"dns_server"		=>	"1",
				"file_server"		=>	"0",
				"db_server"		=>	"0",
				"vserver_server"	=>	"0",
				"proxy_server"		=>	"0",
				"firewall_server"	=>	"1",
				"updated"		=>	"4",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);

	// DNS2
	\$index_value = "${LCL_SERVER_FQDN_DNS2}";
	\$update_data = array(	"mail_server"		=>	"0",
				"web_server"		=>	"0",
				"dns_server"		=>	"1",
				"file_server"		=>	"0",
				"db_server"		=>	"0",
				"vserver_server"	=>	"0",
				"proxy_server"		=>	"0",
				"firewall_server"	=>	"1",
				"updated"		=>	"5",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);
?>
PHP1
These posts were helpful in constructing this:
Thanks,
SuperJC
Reply With Quote
The Following 2 Users Say Thank You to SuperJC For This Useful Post:
adamassasda291 (22nd August 2013), till (12th October 2012)
  #7  
Old 16th October 2012, 01:46
SuperJC SuperJC is offline
Junior Member
 
Join Date: Oct 2012
Posts: 9
Thanks: 2
Thanked 3 Times in 2 Posts
 
Default

Quote:
Originally Posted by SuperJC View Post
...It probably could be refined...
So, here's some refinements we made to mimic more closely what the WebUI form returns:

Code:
LCL_SERVER_ID_CP="$(mysql -N -u${LCL_MYSQL_ROOT_USERNAME} -p${LCL_MYSQL_ROOT_PASSWORD} -D${LCL_ISPCONFIG_MASTER_SERVER_MYSQL_DB} -e "SELECT server_id FROM server WHERE server_name='${LCL_SERVER_FQDN_CP}';")"
LCL_SERVER_ID_WEB="$(mysql -N -u${LCL_MYSQL_ROOT_USERNAME} -p${LCL_MYSQL_ROOT_PASSWORD} -D${LCL_ISPCONFIG_MASTER_SERVER_MYSQL_DB} -e "SELECT server_id FROM server WHERE server_name='${LCL_SERVER_FQDN_WEB}';")"
LCL_SERVER_ID_MAIL="$(mysql -N -u${LCL_MYSQL_ROOT_USERNAME} -p${LCL_MYSQL_ROOT_PASSWORD} -D${LCL_ISPCONFIG_MASTER_SERVER_MYSQL_DB} -e "SELECT server_id FROM server WHERE server_name='${LCL_SERVER_FQDN_MAIL}';")"
LCL_SERVER_ID_DB="$(mysql -N -u${LCL_MYSQL_ROOT_USERNAME} -p${LCL_MYSQL_ROOT_PASSWORD} -D${LCL_ISPCONFIG_MASTER_SERVER_MYSQL_DB} -e "SELECT server_id FROM server WHERE server_name='${LCL_SERVER_FQDN_DB}';")"
LCL_SERVER_ID_DNS1="$(mysql -N -u${LCL_MYSQL_ROOT_USERNAME} -p${LCL_MYSQL_ROOT_PASSWORD} -D${LCL_ISPCONFIG_MASTER_SERVER_MYSQL_DB} -e "SELECT server_id FROM server WHERE server_name='${LCL_SERVER_FQDN_DNS1}';")"
LCL_SERVER_ID_DNS2="$(mysql -N -u${LCL_MYSQL_ROOT_USERNAME} -p${LCL_MYSQL_ROOT_PASSWORD} -D${LCL_ISPCONFIG_MASTER_SERVER_MYSQL_DB} -e "SELECT server_id FROM server WHERE server_name='${LCL_SERVER_FQDN_DNS2}';")"

php <<PHP1
<?php
include_once('/usr/local/ispconfig/interface/lib/config.inc.php');
\$conf['start_session'] = false;
include_once('/usr/local/ispconfig/interface/lib/app.inc.php');
include_once('/usr/local/ispconfig/interface/lib/classes/db_mysql.inc.php');

	global \$app;
	\$tablename = "server";
	\$index_field = "server_id";

	// CP
	\$index_value = "${LCL_SERVER_ID_CP}";
	\$update_data = array(	"mail_server"		=>	"0",
				"web_server"		=>	"0",
				"dns_server"		=>	"0",
				"file_server"		=>	"0",
				"db_server"		=>	"0",
				"vserver_server"	=>	"0",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);

	// WEB
	\$index_value = "${LCL_SERVER_ID_WEB}";
	\$update_data = array(	"mail_server"		=>	"0",
				"web_server"		=>	"1",
				"dns_server"		=>	"0",
				"file_server"		=>	"1",
				"db_server"		=>	"0",
				"vserver_server"	=>	"0",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);

	// MAIL
	\$index_value = "${LCL_SERVER_ID_MAIL}";
	\$update_data = array(	"mail_server"		=>	"1",
				"web_server"		=>	"0",
				"dns_server"		=>	"0",
				"file_server"		=>	"0",
				"db_server"		=>	"0",
				"vserver_server"	=>	"0",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);

	// DB
	\$index_value = "${LCL_SERVER_ID_DB}";
	\$update_data = array(	"mail_server"		=>	"0",
				"web_server"		=>	"0",
				"dns_server"		=>	"0",
				"file_server"		=>	"0",
				"db_server"		=>	"1",
				"vserver_server"	=>	"0",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);

	// DNS1
	\$index_value = "${LCL_SERVER_ID_DNS1}";
	\$update_data = array(	"mail_server"		=>	"0",
				"web_server"		=>	"0",
				"dns_server"		=>	"1",
				"file_server"		=>	"0",
				"db_server"		=>	"0",
				"vserver_server"	=>	"0",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);

	// DNS2
	\$index_value = "${LCL_SERVER_ID_DNS2}";
	\$update_data = array(	"mail_server"		=>	"0",
				"web_server"		=>	"0",
				"dns_server"		=>	"1",
				"file_server"		=>	"0",
				"db_server"		=>	"0",
				"vserver_server"	=>	"0",
				"mirror_server_id"	=>	"${LCL_SERVER_ID_DNS1}",);
	\$app->db->datalogUpdate(\$tablename, \$update_data, \$index_field, \$index_value, \$force_update = false);
?>
PHP1
Likely those Server_ID variables could be done in SQL too, but with my limited knowledge of it, I decided to use bash variables.

Comments, suggestions, improvement always welcome!

Thanks,

SuperJC
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
ISPConfig 3.0.3.3 fresh instalation - email tab is empty artur_gib Installation/Configuration 14 28th January 2014 19:02
No dns zone files created? sjau Installation/Configuration 7 13th March 2012 13:15
Version 1.3 of the ISPConfig 3 Manual is finally available! falko General 44 2nd December 2011 12:04
Email problem 'Cannot set my user or group id.' (using ISPConfig 3 + OpenSuSE 11.2) urosm Installation/Configuration 5 19th June 2010 22:41
The Perfect Setup - Debian Etch (Debian 4.0) some trouble daniel80 HOWTO-Related Questions 26 1st February 2008 16:30


All times are GMT +2. The time now is 00:15.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.