Creating DB and managing account with MySQL stored procedures

Available in Classic

MySQL stored procedure is a collection of SQL statements for performing a series of procedures organized so that it can be executed as CALL function();.
It provides four features: creating a user account, creating a new DB, changing a password, and checking the created account list.

MySQL DB access

In order to run MySQL Stored Procedure, you have to connect to the cluster server where the DB is installed, and then access the MySQL DB.

The following describes how to access a MySQL DB.

1. Connect to the cluster server directly. (Refer to Connecting to cluster nodes through SSH)

2. Run the following command to access the MySQL DB.

   • • Enter the user account in <User Account> and run it. When Enter password: is displayed, enter the account password.

   $ mysql -h mysql.local -u <user account> -p
   Enter password: Password
   

How to use stored procedure

Check procedure creation

The following describes how to view created procedures.

1. Run the show databases; command, and use the following command to access the Ambari DB among the printed DB list.

   • Enter the name of the database you want to actually access in the location of ambari_{unique number}.

   use ambari_{unique number};
   

2. Run the show procedure status; command to view the 4 procedures created in the Ambari DB.

• Currently available procedures: CREATE_DB, CREATE_USER, CHANGE_PW, CHECK_USER
  

Create database

The following describes how to create a database.

1. Enter a name for the database to be created with the CREATE_DB procedure.

   • Database name example: new_database

   call CREATE_DB('new_database');
   

2. Run the show databases; command to check the newly created database.
   

Create user account and view user account list

The following describes how you can create a user account and view the list of user accounts.

1. Enter the user account and password to create with the CREATE_USER procedure.

   • User account example: new_user
   • Password example: password!

   call CREATE_USER('new_user', 'password1!');
   

2. Use the CHECK_USER procedure to check the account list to see if the user account has been created.

   call CHECK_USER();
   

Change password

The following describes how to change a password.

1. Use the CHANGE_PW procedure to enter the account name of which you wish to change the password, and a new password.

   • Target user account example: new_user
   • Changed password example: change_pw2@

   call CHANGE_PW('new_user', 'change_pw2@');
   

2. Check if the Query OK message has been printed. If the message is successfully printed, then the user can now log in with the new password.