Un-manage mysql

[#1]

Hello

we have an Ambari 2.3 installation with Hive using a local mysql installation as database. Now, we have implemented an HA solution for MySQL, MasterHA for the mater, which is a bunch of scripts+daemon that monitor if mysql is alive and move its floating IP to another slave (a slave promotion) in case of master failure.

Doing the changes (when I changed the MySQL IP in Ambari), Ambari restarted the mysqld instance, triggering the master failover, which by the way worked well 🙂

So my question is: to avoid interference between Ambari and MasterHA, how can I tell Ambari that it shouldn't manage the mysql server, in a running Ambari installation?

Thanks!

[#2]

@Davide Ferrari

If you are referring to the database ambari uses to to store it's configuration info, then you'll need to re-setup Ambari for the new MySQL address:

[root@sandbox nifi_demo]# ambari-server setup
Using python  /usr/bin/python2
Setup ambari-server
Checking SELinux...
SELinux status is 'disabled'
Customize user account for ambari-server daemon [y/n] (n)? n
Adjusting ambari-server permissions and ownership...
Checking firewall status...
Checking JDK...
Do you want to change Oracle JDK [y/n] (n)? n
Completing setup...
Configuring database...
Enter advanced database configuration [y/n] (n)? y
Configuring database...
==============================================================================
Choose one of the following options:
[1] - PostgreSQL (Embedded)
[2] - Oracle
[3] - MySQL
[4] - PostgreSQL
[5] - Microsoft SQL Server (Tech Preview)
[6] - SQL Anywhere
==============================================================================
Enter choice (1): 3
Hostname (localhost): 

The MySQL database that is restarted by Ambari is most likely the Hive Metastore database. You will need to change the configuration for the Hive database to an "Existing MySQL" database. Shutdown Hive and repoint the database to the new HA configuration.

[#3 · Accepted solution]

ambari-server setup controls the database used by Ambari, which I don't think you want to change. You can see the DB name, username, and jdbc string in /etc//etc/ambari-server/conf/ambari.properties Ambari doesn't actually perform any mysql restarts on that database.

Instead, you may have to delete the MySQL component from the HIVE service in Ambari since you're managing it yourself.

http://server:8080/api/v1/clusters/$NAME/services/HIVE/components/MYSQL_SERVER will show which host contains that component. (note: you may have to stop the DB server before running this).

If I understood your question correctly, this is what you want to do to manage MySQL server on your own. You should also ask on the Hive mailing list.

curl -u admin:admin -X DELETE -H 'X-Requested-By:admin' http://server:8080/api/v1/clusters/$NAME/hosts/$FQDN/host_components/MYSQL_SERVER

curl -u admin:admin -X DELETE -H 'X-Requested-By:admin' http://server:8080/api/v1/clusters/$NAME/services/HIVE/components/MYSQL_SERVER

[#4]

If you are asking about Ambari: To change the Ambari DB: you can re-run "ambari-server setup", enter the "advanced database configuration" section and setup for the DB you want to use. You are responsible for migrating the data from the embedded PostgreSQL to the SQL DB you plan to use.

If you are asking about Hive: To change the Hive DB (after installing your cluster and leaving Ambari to install a default MySQL instance), you can modify the database settings in Services > Hive > Configs and choose "Existing MySQL" (or other option) that you can point to your own MySQL instance. Refer to this section of the docs for how to setup Hive to use an existing MySQL database (i.e. not the MySQL that Ambari has installed by default to use with Hive).

http://docs.hortonworks.com/HDPDocuments/Ambari-2.2.2.0/bk_ambari_reference_guide/content/ch_amb_ref...

[#5]

Thank you for your answers. To clarify: I don't want to touch Ambari's DB (where Ambari store its configs), I want to change the DB where Hive stores its metadata. Actually, it's what I did: as @jeff and @emaxwell said I changed the "Existing MySQL" option in Hive configuration and I pointed it to my own DB, but since Ambari was already managing the myql server, it restarted the mysqld daemon. So, probably I guess that @Alejandro Fernandez answer is right, with the `curl -X DELETE` operation.

So to sum it up: we were using the Hive-MySQL DB instance installed by default by Ambari, installed alongside other HDP services in one of our masters. Now I wanted to make that MySQL installation high available so I installed another mysql on another master, made it a slave of the original instance and put a virtual, floating IP dedicated to the MySQL service. Then I changed the Hive MySQL address in Hive configuration to use the new VIP (that was at that moment pointing to the original mysql instance) and applied the new Hive config. It's then when Ambari decided to restart my original MySQL instance (and the VIP consequently moved to the MySQL slave). Hope it's more clear now 🙂

[#6]

After stopping the Hive mysql server from Ambari UI and issuing

curl -u admin:admin -X DELETE -H 'X-Requested-By:admin' http://server:8080/api/v1/clusters/$NAME/hosts/$FQDN/host_components/MYSQL_SERVER

I've successfully removed Hive Mysql from ambari management and I guess that next Hive restarts will not touch anymore Mysql. Thank you @Alejandro Fernandez and all the others too