mysql replication and standby apps + what routine mysql test to run

Scriptcase 9 General
#1

Hi everyone,
Not specifically an SC question, but has anyone implemented mysql replication with a project? What I want to do is have a complete standby web app and DB, on a second server, in case the first has issues. I was thinking of doing an hourly sql dump and restore to the second one, but then started thinking that there was a mysql solution???

The second web app (file system on second webserver) should be fine as-is right? I mean, the SC connections used will not have to be altered, as the second web app wold see its own DB on the localhost machine, assuming I name it the same with teh same DB password?

At first, daily or hourly is fine, but eventually scaling up with parallel apps and servers, with replicated DBs would seem good.

What I want to avoid is , “the site is down” , and then have to do an restoring thing under pressure. Seems like a standby setup would only need me to repoint my domains inside my hosting service control panel. The I could go in and figure out what happened.

Second question: What types of routine test/repairing processes should I run on mysql, assuming my DB becomes quite large with lots of paying users :slight_smile: ? I see things inside phpmyadmin, but are tose good to run, and are there other things I can run automatically?

Any tips are appreciated,
Jamie

#2

Hi Jamie. This is an interesting question I’ve been puzzling with. We have been developing application servers in Delphi for a (very) long time. Replication is done by sending the messages to both servers, but we have them both running. We divide our users over both engines and if one fails then all go over one. Now we are building applications in scriptcase we share the same issue. Problem is that there are more issues then MySQL itself. Your server itself can run into trouble, the webserver etc. But keeping it to the database, mySQL has mechanisms for replicating. See http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html for that. I must admit that I haven’t read this in full. It might be that you need a paid licence to be able to use this. (In fact you need to if you run a commercial application on MySQL anyway).
I’m not sure if repointing as you describe it would work. If it is on DNS base than it can take some time to get it done. What you need is a server in the front to redirect you to the right server. That would work. But then your frontserver must be alive all the time…
Considering MySQL: we have a optimizing script in the cron. Nothing special things here. It’s just a bunch of statements as you can find in the MySQL manual. You can use phpMyAdmin, but then it will be a manual action. Why not write a small php script to do so?
I don’t know how it is done on Unix, but on Windows you just can copy the mySQL files from a to b. If you have created an empty database on b then it will work.
But I’m interested in better ideas. Like to keep in touch.

#3

Investigating further

Thanks Albert. I read your reply with interest and will be doing some more research. I fired off a support request to my hosting service, webfaction, who I have been with for many many years. They are very responsive and very good for programmer types. I will let you know what they recommend as well.

My new SC app (actually it is 3 separate SC projects on 3 subdomains) is a second generation of my web service. My earlier system kind of evolved, and I have many many installations of it running clients, but each one is its own self-contained DB and php file system. Super in terms of not having anything that can take out the whole system, but a royal pain for me to maintain all those different systems. My new one is one single DB , that the 3 SC systems all share - they are an admin backend, a membership ordering side, and a public pages side.

I have still kept to SC6 for my projects. I want to launch them all on that version, finishing all the development work. Then I will upgrade to SC7.

Anyway, I may send you a private message so we can keep in touch on this more.
Peace,
Jamie

[QUOTE=aducom;15515]Hi Jamie. This is an interesting question I’ve been puzzling with. We have been developing application servers in Delphi for a (very) long time. Replication is done by sending the messages to both servers, but we have them both running. We divide our users over both engines and if one fails then all go over one. Now we are building applications in scriptcase we share the same issue. Problem is that there are more issues then MySQL itself. Your server itself can run into trouble, the webserver etc. But keeping it to the database, mySQL has mechanisms for replicating. See http://dev.mysql.com/doc/refman/5.7/en/replication-howto.html for that. I must admit that I haven’t read this in full. It might be that you need a paid licence to be able to use this. (In fact you need to if you run a commercial application on MySQL anyway).
I’m not sure if repointing as you describe it would work. If it is on DNS base than it can take some time to get it done. What you need is a server in the front to redirect you to the right server. That would work. But then your frontserver must be alive all the time…
Considering MySQL: we have a optimizing script in the cron. Nothing special things here. It’s just a bunch of statements as you can find in the MySQL manual. You can use phpMyAdmin, but then it will be a manual action. Why not write a small php script to do so?
I don’t know how it is done on Unix, but on Windows you just can copy the mySQL files from a to b. If you have created an empty database on b then it will work.
But I’m interested in better ideas. Like to keep in touch.[/QUOTE]