The MySQL client program, also known as the MySQL monitor, is an interface that allows the user to connect to the MySQL server, create and modify databases, and execute queries and view their results. This program is started by executing the command mysql at the shell prompt. In general, the syntax for this command is:
%>mysql [options] [database]
Where [options] can be one or a series of options used in conjunction with the mysql program, and [database] is the name of the database to use. Since it is assumed to be the reader’s first time using the MySQL monitor, take a moment to review all offered options by executing the following command:
%>mysql –help
This produces a long list of options that can be used in conjunction with the mysql program. For the moment, however, the main goal is to simply connect to the database server. Therefore, execute the following command:
%>mysql -u root
The following should appear:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 3.23.28-gamma-log
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer
mysql>
Congratulations, you are now connected to the MySQL monitor as the almighty root user. Your first official action as this supreme leader of the MySQL database server should be to ensure that nobody else can declare this position. Therefore, make it possible to only connect as root in the future by supplying a password. Change the password from its current blank (or null) value, to something difficult to guess using the following command:
mysql>SET PASSWORD FOR ‘root’@’localhost’ = PASSWORD(‘secret_password’);
The ‘root’, which is the username, and ‘localhost’, which is the hostname, constitute a unique user in MySQL. For those readers perhaps unfamiliar with networking terminology, ‘localhost’ is a name used to specify the local server; that is, the server upon which MySQL resides. Therefore, by stating ‘root’@’localhost’, this command is telling the MySQL server to set the password for a user named ‘root’ that will connect specifically from the local server (thus ‘localhost’). More specifically, this command will change the password by updating what are commonly known as the MySQL privilege tables. These tables, collectively located in the mysql database, contain information regarding the connection and usage capabilities of all users intended to use the MySQL database server. More specifically, this command will update the user table, updating the password field of the row in which the user field’s value is ‘root’. The password field will be updated with the encrypted value of the string enclosed within the Password() function.
Of course, do not forget this password. Since it is stored in encrypted text on the database server, it cannot simply be looked up if forgotten.
There is also an alternative method for updating a password:
%>mysqladmin -u root password ‘secret_password’
This command will accomplish the same results as the one previously introduced.
Exiting and Reconnecting to the MySQL Monitor
In order to test the new password, exit the MySQL database using the following command:
mysql>\q
This will return you to the system shell. Now log back into the monitor, this time using the following command:
%>mysql -u root -p
Doing so will result in a prompt for the root user password, as follows:
Enter password:
Go ahead and enter the password supplied within the update command that was used to set the root password. Assuming it is entered correctly, the standard MySQL greeting will appear, and root will be connected to the MySQL server once again.
Careful With That Password!
Many readers may be tempted to instead try to include the password on the same line as the mysql connection command, as follows:
%>mysql -u root -psecret_password
Do not do this! Not only is it a highly insecure method for entering the password, but it will not produce the expected results! It is insecure not only because it will allow any onlookers the possibility of seeing the password in its plaintext format, but also because any user can use the Unix ‘ps’ command to look at what commands you are executing and view the password in its plaintext format from there.
