Question:
When I try to access my servers MySQL, I get an error like "ERROR 1044: Access denied for user: '@localhost' to database 'mysql'". What's going on?
Answer:
MySQL is a very powerful, fast, and flexible database, with the ability to add multiple users, each of which has configurable permissions.
Software:
MySQL
Detail:
This problem is usually a result of attempting to access your server without specifying a valid username and password.
Solution:
To set passwords for both the root MySQL user and the administrative MySQL user, you will need to update the 'mysql' database, which contains user privileges.
To connect to the 'mysql' database through the MySQL monitor program, follow these steps:
Log into your Freedom server.
Enter the following command at your shell prompt:
> mysql -u root mysql
The mysql> prompt appears, indicating that you are now working within the MySQL monitor:
mysql>
Now that you are connected to the 'mysql' database, you can set the password for both the 'root' user and the administrative user according to the instructions in the following sections.
Once you are connected to the 'mysql' database according to the instructions in the previous section, you can set the MySQL password for the user 'root'. To do so, follow the steps below:
Enter the following command, where new-root-password is the password you wish to set for the root MySQL user:
mysql> UPDATE user
-> SET password=PASSWORD('new-root-password')
-> WHERE user='root';
HINT: MySQL commands do not execute until you type a send statement, such as a semicolon (;). If you press [Enter] before typing the send statement, a continuation prompt (->) appears, as shown in the example above.
Note that the single quotes around the password are required.
Update the 'mysql' database.
You must enter the following command before any changes to the 'mysql' database will take effect:
mysql> FLUSH PRIVILEGES;
Verify the changes.
Use the following command to verify that a password exists for the root MySQL user:
mysql> SELECT * FROM user
-> WHERE User = 'root';
The Password field should display a line of garbled text, which represents the password you supplied in encrypted form.
After setting the password for the user 'root', you must use the -p option to log into the MySQL monitor program as the root user, as shown below:
> mysql -u root -p <databasename>
Replace <databasename> with the database you wish to use. You will then be prompted to use your password.
The process for setting the administrative password is exactly the same as for the root password, except that the username will be your login username instead of root.
Assuming that you are already connected to the 'mysql' database, follow the steps below to set a password for the administrative user:
Create a password for the administrative user.
Enter the following command, where admin_user is the administrative username (which, by default, is the same as your UNIX shell username) and SomePass is the password you want to set for the administrative user:
mysql> UPDATE user
-> SET Password = password('SomePass')
-> WHERE User = 'admin_user';
Update the 'mysql' database.
You must enter the following command before any changes to the 'mysql' database will take effect:
mysql> FLUSH PRIVILEGES;
Verify the changes.
Use the following command to verify that a password exists for the administrative MySQL user:
mysql> SELECT * FROM user
-> WHERE User = 'admin_user';
The Password field should display a line of garbled text, which represents the password you supplied in encrypted form.
Once you've added additional users, you will need to use a slightly different command to change the passwords:
mysql> UPDATE user
-> SET password=PASSWORD('your-new-password')
-> WHERE user='username';
Replace username with the user whose password you are changing.
Again, you must issue the following command before any changes to the 'mysql' database (and, therefore, the user password and privilege settings) will take effect:
mysql> FLUSH PRIVILEGES;
For much more complete information on MySQL access privileges, see www.mysql.com.