Create DB and manage accounts through MySQL stored procedure

The latest service changes have not yet been reflected in this content. We will update the content as soon as possible. Please refer to the Korean version for information on the latest updates.

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, follow these steps:

1. Access the cluster server through SSH directly.

• For more information, see Access a cluster node through SSH.

2. Run the following commands to access MySQL DB.

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

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

How to use Stored procedure

Check the procedure creation

To view the created procedure, follow these steps:

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, follow these steps:

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, follow these steps:

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, follow these steps:

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.