1 Database - 2 Servers

Solutions for other advanced phplisters

1 Database - 2 Servers

Postby ArthurJGuy » 12:09am, Mon 10 Sep, 2007

Hey all - heres my setup.

I have two servers, both with their own installation of PHP list.

I have one central database with about 800,000 users that both installations share (via remote MySQL).

I have Server #1 currently sending out email #1 currently at about 3,000 emails per hour (a bit slow compared to prior sends but I can live with that)

I want Server #2 to send out email #2 to the same list however I am unable to process the mail que. It gives an error saying that a process for that page is allready running (Im assuming this is Server 1, Email 1).

I also noticed that no matter which server I log into I am able to see the sending status and que status of both messages including the messages per hour rate.

Any help is advised and this is most urgent, any info that would help solve this for you guys LMK.
ArthurJGuy
phpList newbie
 
Posts: 2
Joined: 12:02am, Mon 10 Sep, 2007

Postby ArthurJGuy » 6:18am, Mon 10 Sep, 2007

Well ladies and gentlemen I ended up solving my own problem here, but I figuired I would document how I did it for anyone else that should need this to help balance a load.

lists/config/config.php

Server 1:
Code: Select all
$database_host = "localhost";

Server 2:
Code: Select all
$database_host = "IP.TO.SERVER1";


Server 1:
Code: Select all
$table_prefix = "phplist_s1_";

Server 2:
Code: Select all
$table_prefix = "phplist_s2_";


Other then that, these two files are identical.

Now what we are going to do is esentially setup some SQL tables that redirect to the existing tables - this way I dont have to try to clone over 900,000 users, and also both lists keep the same config.

So, since the database is allready setup using the prefix phplist_s1_ (yours may be phplist_, this is ok) We are going to export the SQL structure for every table.

Now we are going to run a find and replace to change the existing prefix to phplist_s2 for every table. The other change we are going to make is that instead of

ENGINE=MyISAM


We need to replace that with

Code: Select all
ENGINE=MERGE UNION=(origional_example)


So for the table phplist_s1_admin the insert SQL should read.

Code: Select all
CREATE TABLE `phplist_s2_admin` (
  `id` int(11) NOT NULL auto_increment,
  `loginname` varchar(25) NOT NULL default '',
  `namelc` varchar(255) default NULL,
  `email` varchar(255) NOT NULL default '',
  `created` datetime default NULL,
  `modified` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `modifiedby` varchar(25) default NULL,
  `password` varchar(255) default NULL,
  `passwordchanged` date default NULL,
  `superuser` tinyint(4) default '0',
  `disabled` tinyint(4) default '0',
  PRIMARY KEY  (`id`),
  UNIQUE KEY `loginname` (`loginname`),
  KEY `loginnameidx` (`loginname`)
) ENGINE=MERGE UNION=(phplist_admin) DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;



Now that this is done for every single table in the databse, you have essentially redirected all of the new tables to the old tables.

Now I went back and deleted the following tables.

phplist_s2_message
phplist_s2_usermessage
phplist_s2_listmessage
phplist_s2_linktrack
phplist_s2_sendprocess
phplist_s2_eventlog

Then I exported the origionals of these tables again, and inserted them with new new table names but no MERGE statement.

I hope that makes sence, its kinda late and I had a busy day.
ArthurJGuy
phpList newbie
 
Posts: 2
Joined: 12:02am, Mon 10 Sep, 2007


Return to Advanced Answers, Howtos, Tips & Tricks

Who is online

Users browsing this forum: No registered users and 1 guest

cron