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;
[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 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'
[root@wte ~]# 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
Comments
If you've forgotten your root
If you've forgotten your root password, you'll need Linux Root login to reset your MySQL root password. Here is the steps for resetting forgotten MySQL root password.
// First login to your Linux box as a root user, and stop mysqld daemon. # service mysqld stop // Now, start the mysqld daemon without the grant table bypassing // authentication # mysqld_safe --skip-grant-tables & // The mysqld daemon should have started. Now login as a root user // without the password. # mysql -u root mysql> use mysql // Reset the root user password. mysql> update user set Password=PASSWORD('new-password') where user='root'; mysql> flush privileges; mysql> exit; // Restart the mysqld daemon, so that the new password will take // into effect. # service mysqld restartAdd new comment