Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

mysql client configurations.

SOLVED
Go to solution
Steven E. Protter
Exalted Contributor

mysql client configurations.

I have a lab with 4 machines.

2 web, application servers, clustered.
2 back end, servers providing storage.

Currently the two application servers run apache and mysqld. Content replication between the two is simple and involves copying the sql database files. Amazingly this even works when the database is hot.

web/mysql runs active passive with one of the two servers live and the other sitting there doing nothing.

I want to move the mysql database to the back end.

I have successfully modified the my.cnf file and permitted the mysql server to listen, which is not its default. The default is localhost only.

Now I'd like to configure the two application servers to do this:

Instead of connecting to the local mysql database, I want them to connect to the back end one on the network. I've been hunting around in google and can't seem to find the mysql equivalent of tnsnames.ora or sqlnet.ora to tell these servers to look for their data on the network server.

The obvious point of this is the data is dynamic and I want changes to go to a central database which is run in a supported configuration.

Questions:
1) How to set up the mysql client or the my.cnf file to do all read/writes on the remote database?
2) Do I continue to run the mysqld daemon on the app servers, just with a new my.cnf file or other configuration changes?
3) Can I have two application servers connecting on one server.

Further areas come to mind for the back end.

I can use the mysqldump utility to do periodic database dumps, which I can transfer to the passive storage node, which can be imported into the passive node via cron scripting.

Needs/Questions
1)Is there a better way. Can both databases be open and all changes be fed into the standby database like Oracle RAC or datagaurd.
2)Scripts for this stuff?

Specifications:
1)mysql 4.x fully patched.
2)Back end Os can be HP-UX or Linux, I actually have lots of lab opportunities here. Linux would be RH 4 update 4. HP OS would be 11.11 with Serviceguard. I'm only considering HP-UX for the back end not the front.

Lots of bunny ops here. I know nothing about mysql other than a few web pages. I've done a decent job of research as I've managed to configure the database to accept connections from the network and have working replication.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
8 REPLIES
Wouter Jagers
Honored Contributor

Re: mysql client configurations.

Here's my two cents, as I see it and if I understand your situation correctly:

You can have several webservers work on one mysql server. Normally you would configure your application to just use the remote host for its database connection, so no tricky mysql client config or local mysqld should be needed.

It is pretty amazing that you can pull off hot copies of your database files ! Out of curiousity: are there continuous updates on the database, or are we talking luck here ? ;-)

mysql has become pretty good in replication, though.. you can have mysqld on the second backend machine running in master/slave config with the first one, so all updates are queued to the slave database automatically, live, -and- safe :)

( http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html )

If you have total control of your application you could even implement basic load balancing this way, by doing all updates on the master mysqld machine and all queries on the slave.

Cheers,
Wout
an engineer's aim in a discussion is not to persuade, but to clarify.
Steven E. Protter
Exalted Contributor

Re: mysql client configurations.

Shalom,

The data is pretty static. I'd assume there would be real trouble if there were a lot of updates. I will be going to a supported replication method using mysqldump or one of the backup utilities or your link before there is a lot of user update.

I'm trying to move to a model where all the lab machines are doing meaningful work. I have a round robin scheme implemented so the firewalls randomly assign the ip address for web and ftp between the two application boxes and those are floating ip's so it doesn't matter if one machine or the other is offline.

mysql access is the last hurdle to meeting this self imposed goal.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Heironimus
Honored Contributor

Re: mysql client configurations.

MySQL doesn't have anything like tnsnames in Oracle or the catalog in DB2. You'll have to find where you configure the DB in your front-end application and fill in a name/IP for the remote database server.

You can have lots of client machines connecting to the same database. The usual rules about badly-written apps and locking apply, of course.

If you move the database to the back-end servers you don't need to run a MySQL daemon on the front-end servers anymore.
Court Campbell
Honored Contributor
Solution

Re: mysql client configurations.

apps tier wold just needs a code change to connect to mysql on backend servers. Mysqld could be removed from the apps tier. I have a couple of thoughts on the back end servers. You could look into a mysql cluster. Never done it, but looks interesting:

http://dev.mysql.com/tech-resources/articles/mysql-cluster-for-two-servers.html


Other thought is to setup the two backends with linux and xen. add a linux domU and use the xm live migration features for failover. There are quite a few howto's out there on setting that up.
"The difference between me and you? I will read the man page." and "Respect the hat." and "You could just do a search on ITRC, you don't need to start a thread on a topic that's been answered 100 times already." Oh, and "What. no points???"
Steven E. Protter
Exalted Contributor

Re: mysql client configurations.

So the web mysql enabled app will just "know" to look on the network for the mysql database?

I'll read again.

The replication thing looks like a winner, its going to take some time to implement.

Test plan:

1) stop mysqld on the app sever.
2) Test connect to a website that reads its data of mysql and see what happens?

Not even a change to httpd.conf?

I am a bit confused.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Steven E. Protter
Exalted Contributor

Re: mysql client configurations.

Okay, I get it. I have to change the database configuration in the mysql enabled websites and tell it where to point.

No problem.

Light bulb just went off. I might even be able to handle that myself.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com
Wouter Jagers
Honored Contributor

Re: mysql client configurations.

:-)

And yep, you can probably copy over the DB to the backend box (since you seem to be able to pull that off :)) and test right away.

The replication thing does ineded look a bit overwhelming when first checking it out, but as soon as it is set up you'll find it's more straightforward than you'd expect.

G'luck !
an engineer's aim in a discussion is not to persuade, but to clarify.
Steven E. Protter
Exalted Contributor

Re: mysql client configurations.

It always helps to read the code Alex Lavrov wrote for me.

Aside: Alex! read your mail/voicemail.

I'm going to try and implement later this week. I'm almost certain some bunnies are coming.

SEP
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com