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

  • 7

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

Category : Ambari , Bigdata

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.


7 Comments

dilip

April 27, 2017 at 11:03 am

mysql> source source /root/ambari.sql;
ERROR:
Failed to open file ‘source /root/ambari.sql’, error: 2

Is the syntax correct?

    admin

    April 28, 2017 at 2:16 pm

    Hello Dilip,

    Thanks for pointing out syntax error. Sorry,it was typo, source keyword repeated two times.
    correct syntax is source ambari.sql;
    You are getting this error “Failed to open file ‘source /root/ambari.sql’, error: 2” means “no such file or directory”. That is it is trying to provide an absolute path, as it’s not clear what the current working directory will be in the context of your MySQL server. You may be assuming that it’s the working directory of your shell, but it’s not obvious that we should expect this to be true.

    Note : So I would suggest you to use following syntax to import data from .sql into your db.
    mysql -u -p database_name < file_name.sql

Derek

March 13, 2018 at 2:38 am

How did replace the “copy from stdin” part from postgres to mysql?

    admin

    April 28, 2018 at 3:26 am

    You can do via some market made tools.

Sivakumar TS

December 4, 2019 at 6:03 am

Need a help to migrate Hive meta store from postgresql to mysql.

Hive Metastore to use mysql database as backend – migrate it to postgresql, and vise versa.

Kindly provide guide to proceed the tasks.

Note:-Both database is running in same hadoop cluster and i have tested the connection from hive successfully connecting. I have problem to migrate.

Leave a Reply to dilip Cancel reply