How to move the Ranger DB to a new database host

[#1]

Note: This is for information and testing purposes only. Please exercise caution prior to using it on business critical environments.

Steps to migrate the ranger and ranger_audit database to the new MySQL host without re-installing Ranger Service via Ambari UI -

1. Stop all the services on the cluster via Ambari UI.

2. Make sure Ranger Admin and Ranger usersync is down.

3. Take dump of ranger and ranger_audit databases.

$ mysqldump ranger > /path/to/ranger_dump.sql
$ mysqldump ranger_audit > /path/to/ranger_audit_dump.sql

4. Login to new database host and to the database.

$ mysql -u root -p

5. Create user rangerdba and provide them all privileges.

CREATE USER 'rangerdba'@'localhost' IDENTIFIED BY '<rangerdba_password>'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangerdba'@'localhost'; 
CREATE USER 'rangerdba'@'%' IDENTIFIED BY '<rangerdba_password>'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangerdba'@'%'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangerdba'@'<new_database_fqdn>';
GRANT ALL PRIVILEGES ON *.* TO 'rangerdba'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'rangerdba'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

6. Create user rangeradmin and provide them all privileges.

CREATE USER 'rangeradmin'@'localhost' IDENTIFIED BY '<rangeradmin_password>'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangeradmin'@'localhost'; 
CREATE USER 'rangeradmin'@'%' IDENTIFIED BY '<rangeradmin_password>'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangeradmin'@'%'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangeradmin'@'<new_database_fqdn>'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangeradmin'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'rangeradmin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

7. Create user rangerlogger and provide them all privileges.

CREATE USER 'rangerlogger'@'localhost' IDENTIFIED BY '<rangerlogger_password>'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangerlogger'@'localhost'; 
CREATE USER 'rangerlogger'@'%' IDENTIFIED BY '<rangerlogger_password>'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangerlogger'@'%'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangerlogger'@'<new_database_fqdn>'; 
GRANT ALL PRIVILEGES ON *.* TO 'rangerlogger'@'localhost' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'rangerlogger'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

8. Create database ranger and ranger_audit in the new database host.

CREATE DATABASE ranger;
CREATE DATABASE ranger_audit;

9. Restore the backup from old database host to the new one.

$ mysql ranger < /path/to/ranger_dump.sql
$ mysql ranger_audit < /path/to/ranger_audit_dump.sql

10. Navigate to Ranger Config in Ambari UI.

11. Change the Ranger DB host to new database hostname. This should update the JDBC connect string for a Ranger database and JDBC connect string for root user with new database hostname.

12. Click on Test Connection. It should be successful.

13. Restart the Ranger service via Ambari and then all other services and validate Ranger Admin UI.