How to Assign a Password to the Root Account on MySQL

In yesterday's article I told you how to secure the anonymous accounts on MySQL. If you want to setup a password for root account, you have three possibilities.

The first method is identical with the one used to assign passwords for anonymous accounts. For that we will use the SET PASSWORD method. The steps are: connect to the MySQL server as root and use the SET PASSWORD statements. To encrypt the passwords use PASSWORD function. These are the steps for Windows: shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('softpedia'); mysql> SET PASSWORD FOR 'root'@'%' = PASSWORD('softpedia');

For Unix, use these steps: shell> mysql -u root mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('softpedia'); mysql> SET PASSWORD FOR 'root'@'host_name' = PASSWORD('softpedia');

In Unix environment, in the second statement replace 'host_name' with the name of the server host.

Another way to set up a password for the root account is to use mysqladmin. From there use the following commands: shell> mysqladmin -u root password "softpedia" shell> mysqladmin -u root -h host_name password "softpedia"

Use the same commands for Windows and Unix environment. Again, replace host_name with the name of the server host. You can use the double quotes only if your password has spaces or special character interpretable by the compiler.

The third method you may use to set up the passwords is to use UPDATE statement to alter users table directly from the database. Again, the UPDATE statement is the same on Windows and Unix environments. shell> mysql -u root mysql> UPDATE mysql.user SET Password = PASSWORD('softpedia') -> WHERE User = 'root'; mysql> FLUSH PRIVILEGES;

After you assign the password for root, if you want to connect to the MySQL server you will have to supply the password. shell> mysqladmin -u root -p shutdown Enter password: (enter root password here)