Creating DB and managing account with MySQL stored procedures
  • PDF

Creating DB and managing account with MySQL stored procedures

  • PDF

Available in VPC

MySQL stored procedure (SP) 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 (SP), 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 actual access address and user account number to execute in mysql.local and user account, and enter the account password when Enter password: is displayed.
    $ 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

cloudhadoop-sql-procedure1_en

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.

cloudhadoop-sql-procedure2_en

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();
    

cloudhadoop-sql-procedure3_en

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.

cloudhadoop-sql-procedure4_en


Was this article helpful?

What's Next