Softpanorama
(slightly skeptical) Open Source Software Educational Society

May the source be with you, but remember the KISS principle ;-)

Softpanorama Search

Setting initial root password and resetting root password on MySQL

News

See also

Recommeded Links

Setting initial root password

Resetting Root Password

 Securing the Initial MySQL Accou

FAQs

Reference
Apache MS Access to mySql conversion PHP integration ACID Installation
(includes MySQL)		 Comparison with other databases Resetting root password on MySQL Humor Etc

Setting initial root password

MySQL MySQL 5.1 Reference Manual 2.11.3 Securing the Initial MySQL Accounts

Part of the MySQL installation process is to set up the mysql database that contains the grant tables:

The grant tables define the initial MySQL user accounts and their access privileges. These accounts are set up as follows:

  • Accounts with the user name root are created. These are superuser accounts that can do anything. The initial root account passwords are empty, so anyone can connect to the MySQL server as root — without a password — and be granted all privileges.
    • On Unix, both root accounts are for connections from the local host. Connections must be made from the local host by specifying a host name of localhost for one of the accounts, or the actual host name or IP number for the other.
  • Two anonymous-user accounts are created, each with an empty user name. The anonymous accounts have no password, so anyone can use them to connect to the MySQL server.
    • On Unix, both anonymous accounts are for connections from the local host. Connections must be made from the local host by specifying a host name of localhost for one of the accounts, or the actual host name or IP number for the other. These accounts have all privileges for the test database and for other databases with names that start with test_.

As noted, none of the initial accounts have passwords. This means that your MySQL installation is unprotected until you do something about it:

  • If you want to prevent clients from connecting as anonymous users without a password, you should either assign a password to each anonymous account or else remove the accounts.
  • You should assign a password to each MySQL root account.

The following instructions describe how to set up passwords for the initial MySQL accounts, first for the anonymous accounts and then for the root accounts. Replace “newpwd” in the examples with the actual password that you want to use. The instructions also cover how to remove the anonymous accounts, should you prefer not to allow anonymous access at all.

You might want to defer setting the passwords until later, so that you don't need to specify them while you perform additional setup or testing. However, be sure to set them before using your installation for production purposes.

Resetting Root Password

MySQL 5.0 Reference Manual A.4.1 How to Reset the Root Password

In a Unix environment, the procedure for resetting the root password is as follows:
  1. Log on to your system as either the Unix root user or as the same user that the mysqld server runs as.
  2. Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, hostname, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the filename has the extension of .pid and begins with either mysqld or your system's hostname.

    You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the pathname of the .pid file in the following command: 

    shell> kill `cat /mysql-data-directory/host_name.pid`

    Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

  3. Create a text file and place the following command within it on a single line: 
    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('MyNewPassword');

    Save the file with any name. For this example the file will be ~/mysql-init.
     

  4. Restart the MySQL server with the special --init-file=~/mysql-init option: 
    shell> mysqld_safe --init-file=~/mysql-init &

    The contents of the init-file are executed at server startup, changing the root password. After the server has started successfully you should delete ~/mysql-init.

  5. You should be able to connect using the new password.

Alternatively, on any platform, you can set the new password using the mysql client(but this approach is less secure):

  1. Stop mysqld and restart it with the --skip-grant-tables --user=root options (Windows users omit the --user=root portion).
  2. Connect to the mysqld server with this command: 
    shell> mysql -u root
  3. Issue the following statements in the mysql client: 
    mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd')
    ->                   WHERE User='root';
mysql> FLUSH PRIVILEGES;

    Replace “newpwd” with the actual root password that you want to use.

  4. You should be able to connect using the new password.

Changing the root password on MySQL

Many distributions of Linux have an option to install MySQL. In this case, or even if you compile MySQL, the default password is blank. MySQL can also run on Windows boxen. When you install MySQL, make sure that you set the root password. You can do this: 

root@u-1:/home/u-1# mysql -u root mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.47

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql> SET PASSWORD FOR root@localhost=PASSWORD('rubberchicken');
Query OK, 0 rows affected (0.06 sec)

mysql>
In this example, we set the root password to rubberchicken. Note from the above that the password wasn't set. If we try this again, we need to use the -p option to enter the password: 

root@u-1:/home/u-1# mysql -u root mysql
ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)
root@u-1:/home/u-1# mysql -u root -p mysql
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3 to server version: 3.23.47

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>
To reset a root password that you forgot (using paths on our system): 

[root@host root]#killall mysqld
[root@host root]#/usr/libexec/mysqld -Sg --user=root &

You may have better luck with: 
mysqld --skip-grant-tables --user=root
Go back into MySQL with the client: 
[root@host root]# mysql
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.41

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

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("newpassword")
    -> WHERE user="root";
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

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

mysql> exit;
[root@host root]#killall mysqld

What Is Mysql's Default Root Password - Just installed in on my Linux box

I installed SuSE Linux 9.1 Professional on an old computer recently. I installed MySQL server (version 4.0.18), Webmin (version 1.250) and Usermin (version 1.180). If I try to connect to the MySQL server with the username root and no password, I get an invalid login error from Webmin, Usermin and mysql-cc. What is the default password, and how can I change it? I am very excited to be learning Linux (after a long journey through Windows-land), but get frustrated on loopholes like these.
Also, an a different note, why would the system stop loading after a certain point (towards the end, don't remeber where) if I have my USB drive plugged in before booting? Dec 29 2005, 02:47 AM

The initial Mysql password is blank according to this info for mysql ver 5.0. YOU WILL HAVE TO READ THE MANUAL FOR YOUR SPECIFIC VERSION. One of the first things they reccomend is to set a new root password and run the Grant table which controls access to the Database.

QUOTE

After a fresh installation, you should connect to the server and set up your users and their access permissions:

shell> mysql -u root mysql
The server should let you connect because the MySQL root user has no password initially. That is also a security risk, so setting the password for the root accounts is something you should do while you're setting up your other MySQL users. For instructions on setting the initial passwords, see Section 2.9.3, “Securing the Initial MySQL Accounts”.



The manuals are available at mysql.com either for online reading or by downloading. If you have installed it locally, probably best to download it.

Hope this helps.

OpaQue

Dec 29 2005, 10:14 AM

Try "su" as username and password is "" (blank).

Inspiron

Dec 29 2005, 02:43 PM

I'm not sure about MySQL but I guess you can get some references here..
http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
http://www.experts-exchange.com/Databases/...Q_20678779.html
 

leiaah

Dec 29 2005, 06:25 PM

The default user is root and the password is, as they say blank for default.

You can set the root password by typing this:

CODE

# mysqladmin -u root password 'new-password'



You can then login by typing this:

CODE

# mysql -u root –p


Then you'll be prompted to provide the password you specified earlier.

You also might want to delete the anonymous user in the User's table. The default configuration of MySQL allows any user access to the system without
providing a username or password.

Delete the user by typing this:

CODE

# mysql -u root –p
mysql> use mysql
mysql> delete from user where User='';
mysql> quit

 

 

Copyright © 1996-2009 by Dr. Nikolai Bezroukov. www.softpanorama.org was created as a service to the UN Sustainable Development Networking Programme (SDNP) in the author free time. Submit comments This document is an industrial compilation designed and created exclusively for educational use and is placed under the copyright of the Open Content License(OPL). Site uses AdSense so you need to be aware of Google privacy policy. Original materials copyright belong to respective owners. Quotes are made for educational purposes only in compliance with the fair use doctrine.

Disclaimer:

Last modified: August 15, 2009