How to change MySQL user and root password?

We have been using the DirectAdmin control panel for a number of years, and each time we set up 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 a proper MySQL root password assigned.

1. Since we do have the MySQL superuser called "da_admin" which we can use to change the "root" user password, we'll just log in to the MySQL client with the "da_admin" user credential. If you are changing the "root" password of an existing "root" account, you'll log in to MySQL as a "root" user. We'll directly modify the 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;

[root@wte ~]# 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 log in to MySQL as 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 the MySQL command.

By default, fresh MySQL installation do not have a "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 the existing root password, you'll use the following command instead. When entered, you'll be prompted for the current root password.

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

[root@wte ~]# mysqladmin -u root -p password 'new-password' 
Enter password: 

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

3. If you wish to set up "root" or any user login without having to supply a 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