Before you begin using MySQL, it is important to password protect the root user. User root in MySQL is the most powerful user, similar to QSECOFR on IBM i, admin on Windows, or root on Linux/Unix.
Prerequisites: Before you can set the root user password, you must first install Zend DBi and start the MySQL server daemon. These articles can help with that:
Install MySQL using the Zend DBi installer with Zend Server
Start the Zend DBi MySQL Daemon job and verify it is running
After verifying that the Zend DBi MySQL server daemon is running, please sign on to a 5250 session as QSECOFR or a *SECOFR class user. Please enter this command:
call qp2term
This will bring up a PASE terminal shell. Please enter this command:
cd /usr/local/mysql/bin
This sets your current directory to the MySQL executables directory. Next, please enter this command to set your root password (substitute your own password instead of ‘bobbysox’):
mysqladmin -u root password 'bobbysox'
Your password for the MySQL root user is now ‘bobbysox’ (or whatever you put in there). The command can only be run with this syntax once. Now that you have a root password, you must specify the password when using the root profile. To use the password, append ‘-p’ to the start of the password. For example, to change the password to belladonna, do this:
mysqladmin -u root -pbobbysox password 'belladonna'
Your root password is now ‘belladonna’ (or whatever you put in there).
Test your password by starting MySQL using the password. Again, to use the password, append ‘-p’ to the start of the password:
mysql -u root -pbelladonna
This should give you a mysql prompt. You are logged in to MySQL as the root user. Now, please run this command:
SELECT User, Host, Password FROM mysql.user;
Notice that in MySQL, as is common in many languages, statements are terminated with a semicolon. The output will be similar to this:
+------+------------------+-------------------------------------------+
| User | Host | Password |
+------+------------------+-------------------------------------------+
| root | localhost | *2198373A8F20B74720DF9155B88C65981F214C19 |
| root | I61SUP1.ZEND.COM | |
| root | 127.0.0.1 | |
| | localhost | |
| | I61SUP1.ZEND.COM | |
+------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)
Root password is set for localhost, but notice there are additional rows for user root from other hosts, and that their passwords are blank. Run the following command for each host to password protect user root from other hosts. In our example, we need to protect I61SUP1.ZEND.COM and 127.0.0.1. For this demonstration, we will just use the same password, but it can be different for each of them:
SET PASSWORD FOR 'root'@'I61SUP1.ZEND.COM' = PASSWORD('belladonna');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('belladonna');After each command runs, you will see a message like this one:
Query OK, 0 rows affected (0.01 sec)
Don’t worry about the “0 rows affected”. To verify that passwords have been entered for user root from all hosts, please run this command again:
SELECT User, Host, Password FROM mysql.user;
The output should look like this:
+------+------------------+-------------------------------------------+
| User | Host | Password |
+------+------------------+-------------------------------------------+
| root | localhost | *2198373A8F20B74720DF9155B88C65981F214C19 |
| root | I61SUP1.ZEND.COM | *2198373A8F20B74720DF9155B88C65981F214C19 |
| root | 127.0.0.1 | *2198373A8F20B74720DF9155B88C65981F214C19 |
| | localhost | |
| | I61SUP1.ZEND.COM | |
+------+------------------+-------------------------------------------+
Notice that all rows for user root now have passwords.
Type ‘exit’ and press enter to quit mysql.
You should now be able to connect to your MySQL data base at the ip address of your IBM i, port 3306 (or another port, if it has been changed), user root, and password ‘belladonna’ (or whatever you put in there).
You can learn more about securing MySQL from the online MySQL documentation. Here are some links to get you started:
Securing the Initial MySQL Accounts
User Names and Passwords
How to Reset the Root Password
For reference, here is the entire PASE shell session from this demo:
> cd /usr/local/mysql/bin
$
> mysqladmin -u root password 'bobbysox'
$
> mysqladmin -u root -pbobbysox password 'belladonna'
$
> mysql -u root -pbelladonna
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.59 MySQL Community Server (GPL)
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement
.
mysql>
> SELECT User, Host, Password FROM mysql.user;
+------+------------------+-------------------------------------------+
| User | Host | Password |
+------+------------------+-------------------------------------------+
| root | localhost | *2198373A8F20B74720DF9155B88C65981F214C19 |
| root | I61SUP1.ZEND.COM | |
| root | 127.0.0.1 | |
| | localhost | |
| | I61SUP1.ZEND.COM | |
+------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql>
> SET PASSWORD FOR 'root'@'I61SUP1.ZEND.COM' = PASSWORD('belladonna');
Query OK, 0 rows affected (0.01 sec)
mysql>
> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('belladonna');
Query OK, 0 rows affected (0.00 sec)
mysql>
> SELECT User, Host, Password FROM mysql.user;
+------+------------------+-------------------------------------------+
| User | Host | Password |
+------+------------------+-------------------------------------------+
| root | localhost | *2198373A8F20B74720DF9155B88C65981F214C19 |
| root | I61SUP1.ZEND.COM | *2198373A8F20B74720DF9155B88C65981F214C19 |
| root | 127.0.0.1 | *2198373A8F20B74720DF9155B88C65981F214C19 |
| | localhost | |
| | I61SUP1.ZEND.COM | |
+------+------------------+-------------------------------------------+
5 rows in set (0.00 sec)
mysql>
> exit
Bye
$Like this:
Like Loading...
