Setting Up MySQL Master & Slave on 2.1.1

[JustinCarmony]

I've got our new Grid Layer at LT and we're working on migrating our current infrastructure to our Grid. I just had a question on how I would do this:

We want to have a MySQL server that handles our reads and writes. It works great except when we go to do a backup, it hammers our MySQL server 2-5 minutes (due to the load of information we have). We take hourly backups (which have saved us a lot of times before) so we want to continue with hourly backups. However, having our site rendered useless for 2-5 minutes every hour is no good.

I want to set up a MySQL Master and Slave, the master handles all the website requests, and the slave would handle the backups. This would allow the backup to tank the slave, and it would catchup after it was done. However, how do I set this up on the grid?

I can get the master set up, but how to I make a slave instance and set up the network relation between the Master and the slave? I guess that is where I'm getting hung up. Thanks for any help.

Justin Carmony

[PeterNic]

Justin,

Congratulations on your new grid!

Using a slave for backups was one of the design goals for MYSQLR. You can see the example setup in MYSQLR's data sheet (documentation). It is at http://doc.3tera.com/AppLogic23/CatDatabaseAppliancesMySQLR.html, specifically look at the "N-tier application with master-slave replication (suitable for doing backups)" case in Typical Usage. (MYSQLR for 2.1.1 is the same as in 2.3.9, so the data sheet in 2.3.9 is OK to use)

Variations: The admin and sw appliances are not required but they can be helpful; you can use sw attached to aux of the main INSSL; in AppLogic 2.3.9, you won't need them as you can use the AppLogic GUI to login to the MySQL's web management UI (that feature is not available in 2.1.1). The advantage of having them is that you can access the MYSQL web control panels of the master and the slave without logging into the grid GUI. Also, keeping a separate gateway (admin) will allow you to constrain who can log in (using the allowed_ip property of INSSL); you can also use IN for admin.

If you have further questions, you can check out the GridU class on MYSQLR or post here again. Let me know whether it worked.

Best regards,
-- Peter

[JustinCarmony]

Thanks for pointing me in the right direction. I thought MySQLR was introduced in 2.3.9, because I'm not seeing it in my Catalog. If there is an MySQLR Appliance for 2.1.1 where can I get it and have it placed in my grid catalogs? Thanks!

Justin

[JosephD]

Justin,

I am going to send you a PM with information on how I can get you the beta of MySQLr into your catalog.

Joseph
3tera

[JustinCarmony]

k, here is an update on our progress and some road blocks I've ran into. Last night my co-worker and we were testing migration from our old servers to our new Grid server.

First of all, the MySQLR replication works really well and is pretty sweet. No problems with that directly, our Master-Slave config works really, really well.

Here is a list of problems I'm having:

1) Unable to change root - This might not seem like a huge problem for most applications, seeing how the only access to the database is via a direct ethernet connection, so the outside world cannot access it. *However*, our current system has a java client based program that must connect to the MySQL database from the outside.

We have a "IN" gateway pointing to the MySQLR Master's "in." We can get connections through just fine. However, anyone can connect through root with no password. So I changed root to have passwords, but when I restarted the entire application the MySQLR servers would fail saying "Insufficient permissions in the mysql database." I saw the error in the Docs, but no information on what it means and how to fix it. I was able to start the app in debug mode, re-change the root passwords, and it fixed it. However, every time we change the root password the web GUI breaks. This makes total sense as I'm sure the GUI needs to log in to have it work.

So here is my question: How can I change the password for root and set that password among the different places it needs it? Our replication is still working, but when ever we change root the web gui breaks.

2) - All IPs appear to be coming from the gateway's IP address - We do security based on IP addresses for different users to help secure our MySQL box. However, in all our testing when connections coming in through our IN gateway to the MySQLR server, it would show an internal "10.8.*" IP address. Is there a way to have the external IP address come across? I would really appreciate any help in pointing me in the right direction on how to do this.

Thanks,

Justin.

[PeterNic]

Justin,

I am glad things are shaping up and that you like the replicated MYSQL appliance.

On your questions:

1) Unable to change root
The right solution is to set up a VPN gateway in front of the MYSQL and have your Java client go in through it -- either OpenVPN or ssh tunnel (the latter can be done transparently to the Java app). As a quick - and not quite safe - alternative, you can constrain the IP addresses (allowed_ip) in the IN gateway.

We can, of course, look up the place where the GUI takes the password and you can branch the appliance; the issue is that the MYSQLR appliance is not made to be accessible from the outside world - I don't know if the MySQL network protocol is safe enough to be exposed on the 'net; which is why VPN/ssh tunnel seems like the best approach.

2) - All IPs appear to be coming from the gateway's IP address

Yes, this is the case; that's how AppLogic's terminal mechanism works. For http/https, we have special provisions in the gateway (similar to what hardware load balancers do), so that the server can get the real IP address of the client. It does not work for the MySQL protocol, though. Hence, again, VPN tunnel is your best choice -- and you may do your IP check there, not in the server.

In addition to terminal interfaces, AppLogic also supports the notion of "raw" interfaces. The extrernal interface, used both in VPS appliances and in all gateways, is not constrained like the terminals.

If you use raw interfaces, you can do everything that you can do on a physical server and with a regular network interface, including having a public IP address and seeing all IP addresses correctly. To do that, branch your appliance (either the server or a custom gateway you build), enable the "external" interface (Edit Class | Interfaces | External Interface); make sure that the appliance configures the interface correctly (it is usually eth0, and in Redhat-like distros, you need the eth0_config file).

Let me know if this helps.

Best regards,
-- Peter

[Karl]

mySQL allows SSL connections, so that should be more than safe to be allowed over the net.

There really should be a root password set, whilst I appreciate that the appliances are isolated by design, so they can only be accessed by other appliances connected to them, that doesn't rule out on a web appliance, someone finding a security hole, uploading a script and having a free root login to your mySQL appliance - unless I'm missing something here. Not only that, just because they are isolated by design, that doesn't mean there won't at some stage be bugs that break this, IMHO security should be a layered approach, making use of all available mechanisms.

[PeterNic]

Karl,

I agree. We'll review the possibilities - we will be looking for a solution that keeps things secure without adding complexity and dependencies.

The simple scenario that I can see is to have the MySQL database have a root password; then have the application not use the root user but use a different-named user. The latter may not need a password (since if someone breaks into the web appliance, he can see the password, as that appliance needs it). Ideally, this will be an option, as it would still complicate the configuration.

Any suggestions are welcome.

Best regards,
-- Peter

[earthgecko]

Hi Peter

I agree here, there should be a way to set the root password. Regardless of whether the web server uses another database user, the fact remains that should a web server be compromised, there would then be an attack vector. It makes little difference what user the web server uses, an attack just has to then try root <nopassword>.

imho

[PeterNic]

Earthgecko -- good point. I guess if the app uses a non-root user for accessing the database, then root will be still open. There is a post by Pavel elsewhere here that describes a minimal modification to the MYSQL appliance -- I believe a single change -- to add root password.

Thanks for the input to all -- I'll work with the appliance developers to get this in in one of the subsequent releases.

Regards,
- Peter

[PeterNic]

Justin - is everything resolved for you with MYSQLR / replication?

[yturturice]

We are experiencing issues with our 2 MYSQLR master-slave/master-slave components not starting after importing 2 databases. After import replication is running and the databases appear fine. However, on Application Restart these components do not start. Any assistance or documentation beyond the data sheet would be very helpful. Thank you!

Yancey

[PavelGeorgiev]

Yancey,

Most probably the cause for your problem is modification of user permissions in the mysql.user table. When importing data, make sure that the current records in mysql.user are preserved. If you deleted the content of this table (overwriting it with the import), you need to restore the users from a vanilly MYSQLR appliance.

If you did not overwrite the mysql.user table, please give us the more info about the error (you can see this with 'list log' after the application fails to start).

Regards,
Pavel