Create DB and manage accounts through MySQL stored procedure

Available in VPC

MySQL Stored Procedure organizes SQL syntaxes that perform a series of procedures so they can be run in CALL function(); format.
MySQL Stored Procedure provides 4 features: creating user accounts, creating new databases, changing the password, and viewing the list of accounts created.

Access MySQL DB

To run MySQL Stored Procedure, you must access MySQL DB after accessing the cluster server where the database is installed.

To access MySQL DB:

1. Access the cluster server through SSH directly.

   • For more information, see Access a cluster node with SSH.
   2. Run the following commands to access MySQL DB.
      • Connect to the master node via SSH and execute the command.
      • Enter the cluster administrator account information in [관리자 계정] and execute the command. When Enter password: appears, enter the account password.

   $ mysql -h mysql.local -u [admin account] -p
   Enter password: Password
   

How to use Stored procedure

Check the procedure creation

To view the created procedure:

1. Among the DB lists that are output after you run the show databases; commands, access Ambari DB with the following commands:
   • Enter the name of the database you want to access in the ambari_{unique number} location.

   use ambari_{unique number};
   

2. Run the SHOW PROCEDURE STATUS WHERE DB = 'ambari_{unique number}'; commands and check the 4 procedures created in Ambari DB.

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

Create the database

To create a database:

1. Enter the name of the database you want to create through the CREATE_DB procedure.

   • Example of the database name: new_database

   call CREATE_DB('new_database');
   

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

Create the user account and view the list of user accounts

To create a user account and view the list of user accounts:

1. Enter the user account to create and the password through the CREATE_USER procedure.
   • Example of the user account: new_user
   • Example of the password: password!

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

2. View the list of accounts and check if the user account is created through the CHECK_USER procedure.

   call CHECK_USER();
   

Change password

To change the password:

1. Through the CHANGE_PW procedure, enter the name of the account whose password you want to change and the new password.
   • Example of the target user account: new_user
   • Example of the new password: change_pw2@

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

2. Check if the Query OK message is output. If the message is output properly, you can access it with the changed password after that.