How do I change an existing Ambari DB Postgres to MySQL?

How do I change an existing Ambari DB Postgres to MySQL?

By default when you configure your ambari server then it runs on postgres database. And if after sometime we need to change it to our comfortable and your org lovable db(like mysql) then you need to use following steps.

Step 1: Please stop your ambari server and then take back of postgres  ambari db(the default password is ‘bigdata’):

$ ambari-server stop

$ pg_dump -U ambari ambari > /temp/ambari.sql

Step 2: Now you need to setup mysql on any of the node with the help of following command :

$ yum install mysql-connector-java

Step 2: Now confirm that .jar is in the Java share directory and Make sure the .jar file has the appropriate permissions – 644

$ ls /usr/share/java/mysql-connector-java.jar

Step 3: Create a user for Ambari and grant it permissions.

For example, using the MySQL database admin utility:

# mysql -u root -p

CREATE USER ‘ambari’@’%’ IDENTIFIED BY ‘bigdata’;

GRANT ALL PRIVILEGES ON *.* TO ‘ambari’@’%’;

CREATE USER ‘ambari’@’localhost’ IDENTIFIED BY ‘bigdata’;

GRANT ALL PRIVILEGES ON *.* TO ‘ambari’@’localhost’;

CREATE USER ‘ambari’@’hdpm1.com>’ IDENTIFIED BY ‘bigdata’;

GRANT ALL PRIVILEGES ON *.* TO ‘ambari’@’hdpm1.com’;

FLUSH PRIVILEGES;

Step 4: Now you need load/restore the Ambari Server database schema.

mysql -u ambari -p

CREATE DATABASE ambaridb;

USE ambaridb;

SOURCE temp/ambari.sql; (the backup from postgres);

Step 5: Now finally update the ambari-server configuration to reference the MySQL instance:

• On the ambari-server node you need to run ambari setup:

​            $ ambari-server setup

Enter advanced database configuration [y/n] (n)?

choose “y”, and follow the steps for setting it up as MySQL (option 3) using the guide mentioned above. Once that is done, don’t change any other settings after the db change.

Once setup is complete for the MySQL instance then you can start ambari:

$ ​ambari-server start

So now you have successfully migrated ambari postgres db to mysql.