Seperate MySQL server

[jinxedworld]

Hi,

I'd like to know if it is possible to use a seperate database server, which is completely detached from the Cluster Manager. Or does the CP doesn't allow this? We can always setup databases manually and remove the MySQL references from the Siteworx templates, but that's not really what we're going for :)


Any tips would be welcome!

[Fr3d]

I think it's possible, by editing iworx.ini file, but I couldn't say what to put in it.

[IWorx-Tim]

Hi,

I'd like to know if it is possible to use a seperate database server, which is completely detached from the Cluster Manager. Or does the CP doesn't allow this? We can always setup databases manually and remove the MySQL references from the Siteworx templates, but that's not really what we're going for :)


Any tips would be welcome!

I've never heard of anyone do this before. Theoretically you could do that if you did those things and put the IP of the database server in any script config files in replace of 'localhost' but it's not designes to work that way and would be an UNSUPPORTED configuration.

The database server would NOT be load balanced, which kind of defeats the purpose of the cluster as the database server is often a large part of the load.

[peterz]

The database server would NOT be load balanced, which kind of defeats the purpose of the cluster as the database server is often a large part of the load.

So, the databases are balanced among the different nodes and not just run from the cluster manager? From looking at my cluster management setup, it says that the role of the cluster manager is to act as a database server and the role of the clustered server is servicing requests. Am I wrong and the databases are balanced in the cluster?

[jinxedworld]

The database server would NOT be load balanced, which kind of defeats the purpose of the cluster as the database server is often a large part of the load.

I was always under the assumption that the only MySQL server that was running in the cluster was running on the cluster manager. Am i wrong, and ar the nodes also running MySQL for all users, hence loadbalanced?

[IWorx-Tim]

All web data, email, and databases are stored on the cluster manager. The other nodes share the load by distributing the processor power and provide dns redundancy. How this works is that the other servers mount the /chroot/home directory on the CM.

[jinxedworld]

All databases are stored under /var/lib/mysql, and not under the nfs mount located at /chroot/home, so that's not the way MySQL data is synced. Could you please confirm this for me Tim, this is really a big issue for me actually.

[IWorx-Socheat]

Actually, MySQL is *not* one of the available load-balanced services in an InterWorx cluster. Normally, sites in a cluster must reference the IP of the CM when using databases. The CM is the single database server for all nodes in the cluster. Therefore, you could just as easily have the sites reference another database server, outside of the CM. It's not a supported setup, but it should work. You'd lose the ability to control MySQL from InterWorx though.

[IWorx-Tim]

Actually, MySQL is *not* one of the available load-balanced services in an InterWorx cluster.

Whoops, my mistake ;-) Sorry 'bout that.

[Fr3d]

You'd lose the ability to control MySQL from InterWorx though.
Couldn't you edit the iworx.ini as I said in my first reply? As long as the port is open on the other server, it shouldn't notice the change... I'll probably be proven wrong, but it *looks* like it might work :p

[IWorx-Socheat]

That would work for a few things, like changing the runtime variables. But we actually call "service mysqld start|stop|restart" to well... start, stop, and restart MySQL. :) Also, to determine if it's running, we simply check to see if there's something listening on 3306 on localhost. So some things would work, some things won't.

Socheat

[hubbadubba]

Also interested in this- though it seems the cluster manager achieves the goal of having my beefiest server handle MySQL, it also is apparent that the CM ends up handling web requests and other hosting tasks like mail too, which is not ideal for my setup.

Looking at the iworx.ini file, I see full paths to mysql data and binary locations- eg:
[root@srv ~]# cat /usr/local/interworx/iworx.ini | grep mysql
mysql="/usr/bin/mysql"
mysql_base="/var/lib/mysql"
dsn="mysql://iworx:SM9RGpofdEPK@unix(/home/interworx/var/run/mysql.sock)/iworx_horde"
dsn="mysql://iworx:SM9RGpofdEPK@unix(/home/interworx/var/run/mysql.sock)/iworx"
dsn.orig="mysql://iworx:SM9RGpofdEPK@unix(/home/interworx/var/run/mysql.sock)/iworx"
mysqlcheck="/home/interworx/bin/mysql/mysqlcheck"
mysqldump="/home/interworx/bin/mysql/mysqldump"
mysql="/home/interworx/cron/mysql.pex"
mysql_home="/home/mysql"
mysqldump="mysqldump"
[mysql]
dsn="mysql://iworx:SM9RGpofdEPK@unix(/home/interworx/var/run/mysql.sock)/iworx_mysql"
home="/var/lib/mysql"
rootdsn="mysql://iworx:sKf7ywH8uCd9@unix(/var/lib/mysql/mysql.sock)/mysql"
socket="/home/interworx/var/run/mysql.sock"
user="mysql"
[mysql.bin]
mysql="/home/interworx/bin/mysql.pex"
mysqladmin="/usr/bin/mysqladmin"
mysqlcheck="/usr/bin/mysqlcheck"
mysqld="/usr/sbin/mysqld"
mysqld.sysv="/etc/init.d/mysqld"
mysqldump="/usr/bin/mysqldump"
[mysql.file]
dsn="mysql://iworx:SM9RGpofdEPK@unix(/home/interworx/var/run/mysql.sock)/iworx_ftp"
dsn="mysql://iworx:SM9RGpofdEPK@unix(/home/interworx/var/run/mysql.sock)/iworx_vpopmail"
sql="/home/vpopmail/etc/vpopmail.mysql"
dsn="mysql://iworx:SM9RGpofdEPK@unix(/home/interworx/var/run/mysql.sock)/iworx_spam"Am I correct in assuming:
anything referenced by /home/interworx/... should be left alone
can configure symlinks using nfs automount to for the oth locations, such as "/usr/sbin/mysqld" and "/home/mysql"

[hubbadubba]

Maybe there is a simple way to address this- can I use the cluster manager direct web and mail traffic to the node and only use the cluster manager machine for mysql?

[IWorx-Socheat]

Yes, when setting up the load-balancing policy, you can choose to not have the cluster manager as part of the policy. This will then send all traffic to only the nodes.

[hubbadubba]

Thanks Socheat- seems like the way to go, then, as long as the CPU overhead created by forwarding those requests isn't too great. Curious if anyone has tested this.

[pascal]

Thanks Socheat- seems like the way to go, then, as long as the CPU overhead created by forwarding those requests isn't too great. Curious if anyone has tested this.

Today we have a cluster and indeed we've set it like you said :
- CM = Mysql only + Nfs
- Nodes = all others services

With this our CM has 2.5 load average (bi-xeon 3.0 )
We have a 1Gb switch between the CM and all nodes, but even with this it looks like MySQL requests and NFS never use more than 10Mb (strange part of thing)

It will come the time we'll need indeed a MySQL cluster (part of interworx or outside it).
We have done some tests with the new mysql-cluster and we know that at least you must have 3 boxes to have it working (2 mysql node and 1 mysql manager)

I really hope the MySQL cluster option will become a full part of the Interworx cluster. This will allow to really use the cluster as a main and alone architecure

Pascal

[pascal]

That would work for a few things, like changing the runtime variables. But we actually call "service mysqld start|stop|restart" to well... start, stop, and restart MySQL. :) Also, to determine if it's running, we simply check to see if there's something listening on 3306 on localhost. So some things would work, some things won't.

Socheat

Socheat,

Could you please confirm me that the only problem to create a MySQL server on a separate server would only be :

- Start/stop/restart the MySQL server
- SIM (check the service)
- All MySQL services setup in nodeworx (as it is configured for localhost by default, at least for phpmyadmin)

What about the MySQL functionalities in Siteworx ?

Today all MySQL services in siteworx panel on a node doesn't work !

- phpmyadmin :
- Create a user
- Create a database
- Manage authority

It looks like they look for localhost ?

I don't understand why it isn't possible. As far as I know iworx use a DSN to access the MySQL database, so all these programs could access a MySQL server anywhere ?

Do you think Interworx will allow this in a short time ? I don't think it is too much complicated but I maybe false ?

Thanks a ton for an iworx team answer :)

Pascal

ps: There is some others thread talking about this
Could it be possible to regroup all these thread in only one to not have cross posting ?
http://www.interworx.com/forums/showthread.php?p=12928
http://www.interworx.com/forums/showthread.php?t=2004

[IWorx-Socheat]

Socheat,

Could you please confirm me that the only problem to create a MySQL server on a separate server would only be :

- Start/stop/restart the MySQL server
- SIM (check the service)
- All MySQL services setup in nodeworx (as it is configured for localhost by default, at least for phpmyadmin)

Yes, that is correct.

What about the MySQL functionalities in Siteworx ?

Today all MySQL services in siteworx panel on a node doesn't work !

- phpmyadmin :
- Create a user
- Create a database
- Manage authority

I don't understand why it isn't possible. As far as I know iworx use a DSN to access the MySQL database, so all these programs could access a MySQL server anywhere ?

This should already be possible. On one of our test clusters, running RC4, I logged into SiteWorx on node1, added a database, added a database user, and added database permissions. Everything worked without problems. If it's not working for you, open a ticket, and we'll check it out. If this isn't what you meant, be a bit more descriptive about the problem you're experiencing.

Could it be possible to regroup all these thread in only one to not have cross posting ?
http://www.interworx.com/forums/showthread.php?p=12928
http://www.interworx.com/forums/showthread.php?t=2004

Yes, don't cross-post and we won't have to re-group the threads. ;)

[pascal]

In fact I confirm you this :

- On node1, for every siteworx accounts the databases/users are not listed
- On node1, I can create a database, user account, but in this case they are not listed in the CM (so where are they created ?)

Pascal

[IWorx-Socheat]

In fact I confirm you this :

- On node1, for every siteworx accounts the databases/users are not listed
- On node1, I can create a database, user account, but in this case they are not listed in the CM (so where are they created ?)

Pascal

Something is definitely wrong then. Open a ticket.

[pascal]

ok support ticket open.

If all the siteworx feature works, then it means that it is completly possible to separate the mysql server :)

Do you see others impacts, than the one about the service management (start/stop). Because this problem is an admin problem and not a final user one, and in this case, for us it is not so important :)

Pascal

[pascal]

Thinking about others impact :

Today I can run a backup from command line from the node or the Cm so could I conclude the backup and mysqldump sholdn't be a problem ? but for a backup from siteworx accounts ?

Pascal

[pascal]

In fact I confirm you this :

- On node1, for every siteworx accounts the databases/users are not listed
- On node1, I can create a database, user account, but in this case they are not listed in the CM (so where are they created ?)

Pascal
Something is definitely wrong then. Open a ticket.

for the prosperity, this problem exists only with interworx 2.1.3, not with 3.0 and it is a authority pbm (the database is well created on the CM)

Thanks Socheat

Pascal