- Print
- PDF
Creating DB and managing account with MySQL stored procedures
- Print
- PDF
Available in VPC
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.
Connect to the cluster server directly. (Refer to Connecting to cluster nodes through SSH)
Run the following command to access the MySQL DB.
- Enter the user account in
<User Account>
and run it. WhenEnter password:
is displayed, enter the account password.
$ mysql -h mysql.local -u <user account> -p Enter password: Password
- Enter the user account in
How to use stored procedure
Check procedure creation
The following describes how to view created procedures.
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};
- Enter the name of the database you want to actually access in the location of
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.
Enter a name for the database to be created with the
CREATE_DB
procedure.- Database name example:
new_database
call CREATE_DB('new_database');
- Database name example:
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.
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!');
- User account example:
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.
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@');
- Target user account example:
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.