MYSQLR Appliance

[BeckyHester]

This thread is dedicated to questions and comments related to the MYSQLR and MYSQLR64 - MYSQL Database Appliance Suitable for Replication.
The MYSQLR appliance data sheet can be found at:
AppLogic 2.4.x: http://doc.3tera.net/AppLogic24/CatDatabaseAppliancesMySQLR.html
AppLogic 2.7.x: http://doc.3tera.net/AppLogic27/CatDatabaseAppliancesMySQLR.html (page will be published concurrently with 2.7 release)

[renato.fernandes]

Hello!

Recently i created an Application with MySQLr appliance. All work fine, except when i try to add a password to root user of MySQL. When restart the Application, the MySQLr appliance fail to start.

How to add a password to root user of MySQL database without cause this issue?

Thanks in advance,



Renato Fernandes
Sysadm
WEBTraffic

[PeterNic]

Renato,

Please consider whether you really need a password for the root user -- the only appliance(s) that can talk to your MYSQLR are those you connect to it. In regular physical server-based installation, where the network is shared between many servers, the password is a necessary security practice (which shouldn't be taken into an isolated appliance model without re-thinking).We have found that in the majority of cases there is no need for such password -- if you want an appliance to have access, you connect it to the database server; if you don't want it to have access, don't connect it.

There are indeed some cases when the password is needed. If you decide you really need the password, you can branch the MYSQL(R) appliance, make some changes to its scripts, test it and put it back in the catalog (e.g., in the "user" catalog), and then use your version in your apps. I have asked one of our appliance engineers to post here the exact steps needed.

Best regards,
-- Peter

[renato.fernandes]

Hello Peter,

I need a password for root because my developers use a LINUX5 appliance to connect to DB too, not only my systems... and there developers only need to see 1 database. If root no have password, there guys access all databases simple using "mysql -uroot".

I apparently solved the problem changing some scripts how you said... now my application run normally, i don't tested the replication functionality but when i do this i let you know.

If possible, i interesting in a reply from appliance engineers, so i confirm if do all right.

Sorry, my english is poor... i am from brazil.

I appreciate your attention.


Renato Fernandes
Sysadm
WEBTraffic

[PavelGeorgiev]

Renato,

By default MYSQLR has 4 accounts for user root:
- root@localhost
- root@MYSQLR
- root@127.0.0.1
- root@%


The first 3 are local accounts and it does not make much sense to set password for them.

If you just need to limit remote access (connections comming on the 'in' terminal), you can do this in two steps:

1) set a password for the root@% account:

set password for 'root'@'%' = PASSWORD('some_pass');

2) Branch the appliance and apply this patch to /appliance/db_permck.sh

--- db_permck.sh 2009-01-15 08:30:27.000000000 -0800
+++ db_permck.sh.orig 2008-01-08 10:06:38.000000000 -0800
@@ -23,7 +23,7 @@
F=`mysql -D mysql -e "show grants for 'root'@'%'"`
RET=$?

-if [ $RET -eq 1 ] || [ echo "${F:18}" | grep -vEq "^GRANT ALL PRIVILEGES ON \*\.\* TO 'root'@'%' (IDENTIFIED BY PASSWORD '.*' )?WITH GRANT OPTION$" ] ; then
+if [ $RET -eq 1 ] || [ "${F:18}" != "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION" ] ; then
exit 1
fi



The patch (2) will most likely be included in the standard MYSQLR appliance in the next applogic release, so you`ll be able to change the root password without branching the appliance.

[renato.fernandes]

Hello PavelGeorgiev,

I do all changes you said plus a change into file /appliance/create_db.sh
Changed the line 44 from:
mysql -D mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION"
From:
mysql -D mysql -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'MYPASSWORD' WITH GRANT OPTION"

So, if you change there 2 files(create_db.sh and db_permck.sh) you got an appliance with password for root@% and this create the database normally when you start first time the application.

Doubts, let me know.

Thank you! I appreciate your attention!


Renato Fernandes
Sysadm
WEBTraffic

[Jsmart]

There is a bug in the current MYSQLR implementation that causes it to fail to start mysql after a vol resize. This is due to the way in which MYSQLR identifies the correct partition that the database lives on.

When MYSQLR first creates the ext3-snapshot partition to write its files, it creates a UID for that partition. Due to the way in which we create a new volume and copy the data during the vol resize this UID changes.

For now if you need to resize this is the best process:

create a new (raw) volume of the size you want
attach it to the MYSQLR appliance.
start the app letting the MYSQLR appliance boot FULLY and create the EXT3-SNAPSHOT partition
stop the app
"vol manage <OLD> <NEW>"
"cp -ax /mnt/vol/* /mnt/vol2/"
exit the vol manager
reboot and you DB should be intact on the new volume.
delete the old volume or move it out of the APP as a backup.


We are working on fixing this in the 2.7 release

--Jessie

[igord]

Dear 3tera Support,

we are DNSEurope and we are experiencing difficulties with creating application which will use external master mysql server (non-Applogic) and Mysql64R appliance.

The situation is following - our customer have master mysql server at his office network, and we need to setup Applogic application with potentially 2 web + 2 mysql servers with LB + NAS + PS, to create load balanced environment.

We have examined documentation on:

http://doc.3tera.com/AppLogic27/CatDatabaseAppliancesMYSQLR.html

but there are explained scenarios where only two or more Applogic-based applications are used, but without usage of external master mysql server.

So problem which we have is that we can`t connect WebR64 appliance(s) with external mysql server.

Please, is there any starting point or even better how-to for this setup?

Thanks in advance!

[PavelGeorgiev]

There is no official document as part of the AppLogic documentation, here are the steps that you would need to make a consistent copy of the master database and transfer to the slave (s). There is more than one way to do this and which way you go depends on how much downtime you can afford on the master. Here is how we do it in the MYSQLR* appliances, it does not stop the master but it does create a write lock for steps (3) - (7) so your master will be in read only mode for the time you transfer your database.

1) Stop the mysql daemon on the slave(s)
/etc/init.d/mysqld stop

2) Enable replication on the master - you need to enable binary logs in mysqld.conf and also create a replication user. For this example we are using replication_user@% with no password, you might use a different user:

mysql -e "GRANT REPLICATION SLAVE,SUPER ON *.* TO 'replication_user'@'%'"


3) Lock tables on the master. After this step no write queries will be done on the master, read queries will work. Open a mysql console on the master and execute the following, without closing the mysql console:

FLUSH TABLES;
FLUSH TABLES WITH READ LOCK;


4) Verify all databases are locked - open a new mysql console on the master and try to do some write operation, for example:

CREATE DATABASE DELME;

It should freeze and it should not complete as long as the first mysql console where the lock was made is open. If the operation completes then you need to go back to the previous step.

5) Get log position - run the following on the master:

SHOW MASTER STATUS;

Save the values for File and Position.

6) Transfer the data to the slave - if you are using the same architecture on the master and slave(s) you can use copy the data dir to /mnt/data on the slave (remove all files in the dir before that and dont copy any binary logs, error logs, and *info files) or you can dump the database on the master and import it on the slave.

7) Remove the write lock on the master. After this the master will be fully operational. In the console that is open on the master, execute:

UNLOCK TABLES


8) Start the slave(s)

/bin/rm -f /mnt/binlogs/relay-log* /mnt/binlogs/mysql-bin*
/etc/init.d/mysqld start


9) Start replication on the slave(s)

mysql -e 'CHANGE master TO master_host="rout", master_user="replication_user", master_password="", master_log_file="$master_log_file", master_log_pos="$master_log_pos"';

$master_log_file and $master_log_pos are the values that you saved in step (5)

10) Verify that replication is running
- Create a new database on the master, verify it is present on the slave(s)
- Delete the database on the master, verify it is deleted on the slaves(s)
- Login the slave(s) web console and verify replication is reported as running


Notes:
- You will not be able to use the 'Fix/Initiate' functionality of the slave(s) as it only works for AppLogic appliances. If your master was an AppLogic appliance, this whole operation is a single click and happens with virtually zero downtime on the master (there is a write lock for a second or so but that will not fail any queries, it will just delay them).


Hope that helps,
Pavel

[igord]

Dear Pavel,

thank you for valuable information about mysql replication setup!

Question which we still have is - how to connect mysql64R appliance and it`s interfaces so it can connect to external mysql server?

For example, you are saying:

mysql -e 'CHANGE master TO master_host="rout", master_user="replication_user", master_password="", master_log_file="$master_log_file", master_log_pos="$master_log_pos"';

so it seems that you have connected somehow "rout" with external mysql server, how?

through NET ?

I have created setup which looks like this (and it is working):

http://support.dnseurope.net/mysqlrsetup.png

but as you can see I`ve added one OUT interface to mysql64R so it can communicate with external mysql server. Is that way proper?

With this scenario (manually added OUT interface to mysqlR appliance, I see no way in connecting another mysqlR with existing mysqlR)

Thanks!

[PavelGeorgiev]

Question which we still have is - how to connect mysql64R appliance and it`s interfaces so it can connect to external mysql server?


Sorry for skipping that part, it seemed kind of obvious in my head (which it really isn't).

You don't need to branch the MYSQLR appliance. You can connect the 'rout' terminal to an OUT gateway (note: in this case the mysql traffic will go over the internet unencrypted) or use a VPN appliance which will tunnel the traffic to your master. Just configure the gateway (OUT or VPN) to connect to your master.


With this scenario (manually added OUT interface to mysqlR appliance, I see no way in connecting another mysqlR with existing mysqlR)


I'm not sure what you are trying to do here. If you need to add another mysqlr that will connect to your non-AppLogic master, just connect its 'rout' terminal to the OUT appliance.

If you want to chain the appliances so the replication is 'external_master' -> 'applogic_mysqlr_1' -> 'applogic_mysqlr_2', then configure 'applogic_mysqlr_1' to run with rpl_mode set to 'master_and_slave', connect its 'rout' terminal to your external master (via an OUT or VPN gateway), configure your 'applogic_mysqlr_2' to run as slave and connect 'rout' terminal on 'applogic_mysqlr_2' to 'rin' terminal on 'applogic_mysqlr_1'.

Hope that makes sense,
Pavel

[PeterNic]

igord,

The simplest way to connect rout to your external MySQL server is to connect an OUT gateway appliance to the rout output of the MYSQLR appliance, and set the remote_host property of the OUT gateway to the IP and/or hostname of your external server. This way, when master_host referring to "rout" will cause the connection to be established with whatever host is set in remote_host (for more info on how that works, see the OUT appliance datasheet (http://doc.3tera.com/AppLogic29/CatGatewayOut.html)).

Alternatively, you can use the VPN gateway appliance -- see the very last diagram in the MYSQLR's datasheet (http://doc.3tera.com/AppLogic29/CatDatabaseAppliancesMySQLR.html)

Does this help?

Best regards,
- Peter

PS: there is no need to branch the MYSQLR appliance -- it can be used for this purpose as-is from the catalog.

[igord]

Hello,

thank you very much for the help, it was really useful !