How to change MySQL user and root password?

We have been using DirectAdmin control panel for a number of years, and each time we setup a server it is necessary to change the root password. By default, Direct Admin creates a MySql superuser called "da_admin" with a preassigned password, but we do not know what the root password is. Since we use both DirectAdmin and command-line to manage our server, it is essential that we have proper MySQL root password assigned.

1. Since we do have the MySQL superuser called "da_admin" which we can use to change "root" user password, we'll just login to MySQL client with "da_admin" user credential. If you are changing "root" password of an existing "root" account, you'll login to MySQL as a "root" user. We'll directly modify MySQL user table to accomplish this.

$ mysql -u da_admin -p
mysql> use mysql;
mysql> update user set password=password("new-password") where user = "root";
mysql> flush privileges;

[[email protected] ~]# mysql -u da_admin -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.5.31 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, 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> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> update user set password=PASSWORD("new-password") where user = "root";
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

As noted above, you'll login as MySQL ad a superuser, and modify the mysql.user table directly. The last "flush privileges" MySQL command instructs MySQL to reload the privileges defined in the MySQL user table.

2. You may accomplish the same task with mysqladmin MySQL command.

By default, fresh MySQL installation do not have "root" password, so if you have never set the "root" password for the server you'll simply assign a new password with the following mysqladmin command.

$ mysqladmin -u root password 'new-password'

If you wish to change existing root password, you'll use the following command instead. When entered, you'll be prompted for current root password.

$ mysqladmin -u root -p password 'new-password'

[[email protected] ~]# mysqladmin -u root -p password 'new-password' 
Enter password: 

Note that you do not have to run "flush privileges" MySQL command to reload the privileges when you use "mysqladmin" command.

3. If you wish to setup "root" or any user login without having to supply password on command-prompt, you may create .my.cnf file in your home directory. MySQL reads this file each time it opens the MySQL client. Create the ~/.my.cnf file. [Note: ~ represents your HOME directory path in Linux]

[client]
user=root
password=root-password
host=127.0.0.1

[mysql]
database = database-name

Be sure to modify the permission of the file to limit access to yourself, so that other users on the system cannot view this file.

% chmod 600 ~/.my.cnf