CODEWITHSUNDEEP
mysqllinuxcentos
CodeMed
CodeMed

Reputation: 9205

Setting MySQL root password in CentOS Linux

I installed MySQL on CentOS Linux and am trying to set the root password. Towards this end, I took the following steps:

  1. I opened the terminal and typed in su - to run as root.
  2. I then ran mysql - u root, which resulted in a lot of output and another prompt.
  3. I then typed in UPDATE mysql.user SET Password=PASSWORD('NewPassHere') WHERE User='root';

But step 3 produced the following error:

-bash: syntax error near unexpected token `('

When I change step 3 to UPDATE mysql.user SET Password='NewPassHere' WHERE User='root';, I get the following error:

bash: UPDATE: command not found...

How can I resolve this error so that I can set the root password in MySQL successfully?

Update

Based on Chuck's suggestion, I tried the following, but got the following results:

[root@localhost ~]# /usr/bin/mysqladmin -u root password 'newpwd'
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)'
Check that mysqld is running and that the socket: '/var/lib/mysql/mysql.sock' exists!

The following two commands also had the following two results:

[root@localhost ~]# sudo service mysqld status
Redirecting to /bin/systemctl status  mysqld.service
mysqld.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)

[root@localhost ~]# sudo service mysqld start
Redirecting to /bin/systemctl start  mysqld.service
Failed to issue method call: Unit mysqld.service failed to load: No such file or directory.

Answer

The solution to this problem was to delete MySQL using yum remove and then to follow the steps in this tutorial explicitly. However, I am marking Chuck's answer below as accepted because he spent so much time looking into this.

Upvotes: 2

Views: 5740

Answers (3)

Walterwhites
Walterwhites

Reputation: 1477

With mysql you can:

1) connect you to mysql

 mysql -u root -p

2) run mysql command:

use mysql

3) run mysql command:

update user set authentication_string=password('NEWPASSWORD') where user='root';

4) run mysql command:

flush privileges;

​5) run mysql command:

quit

6) Now you can connect without password with

mysql -u root

Upvotes: 0

andy1062
andy1062

Reputation: 36

I see the OP has already reinstalled mysql but I'd like to mention on CentOS you can use a great command which secures your installation for production and sets the mysql root password in the process. The command is: mysql_secure_installation Here's how it works

first install mysql using yum

[root@mail ~]# yum install mysql-server

then set mysql to run at start up and start mysql

[root@mail ~]# chkconfig mysqld on
[root@mail ~]# service mysqld start

then run mysql secure installation here is sample output: (when first run the mysql root password is blank so just hit enter for the first question)

[root@mail ~]# mysql_secure_installation
Enter current password for root (enter for none):
OK, successfully used password, moving on...
Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Remove anonymous users? [Y/n] y
... Success!
Disallow root login remotely? [Y/n] n
... skipping.
Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
Reload privilege tables now? [Y/n] y
... Success!
Cleaning up...
All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

Upvotes: 1

Chuck
Chuck

Reputation: 206

I usually use mysqladmin to set root password. On CENTOS 6, try:

/usr/bin/mysqladmin -u root password 'new-password'

This assumes you're setting the root password for a brand new install and the root password currently doesn't exist. If you do have a root password already, you'll need to append -p to the end of that command and enter in the current mysql root password.

Note that this will only set the password for user root@localhost. Once you can log into mysql, you should run a query to see how many root users actually exist. You should probably see at least two ( root@localhost, [email protected] ) depending on the version and platform. You need to set the root password for each host individually. From the mysql command line, type: 

SELECT user, host FROM mysql.user WHERE user = 'root';

Then you can log out of mysql again and use the mysqladmin command to set all your passwords, this time with the -h flag to specify hosts:

/usr/bin/mysqladmin -u root password 'new-password' -h127.0.0.1

Upvotes: 3

Related Questions