Search code examples
mysqllinuxcentos

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.

Solution

  • 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, root@127.0.0.1 ) 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