setting mysql root password

dotKer

Member
(1) install xubuntu
(2) install Apache
(3) install mysql

I did the above one by one.

I have to set mysql root password.
but I failed in setting mysql root password.

if I enter the following, expecting "enter password?"
mysql> msql -u root -p
It says the following intead of "enter password?"
mysql> msql -u root -p
>
I don't know what I did worng.
I don't know what to do next.
I like to set mysql password of root with your help.

How can I set mysql root password?
 
Last edited:


alkion

Member
Setting a root password (after installation on Linux the password is blank by default). Root is the administrator of the MySQL database server. We should absolutely follow these steps immediately after installation.

Method 1:
Code:
mysql -u root
Code:
mysql>use mysql;
Code:
mysql>update user set password=PASSWORD('password') where user='root';
Code:
mysql>flush privileges;
Code:
mysql>quit
Method 2:
Code:
/usr/bin/mysqladmin –u root password ‘password’

MySQL - root password reset

Code:
 /etc/init.d/mysql stop
Code:
mysqld_safe --skip-grant-tables --skip-networking &
Code:
mysql -uroot mysql
Code:
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
We resume the process from the background with the fg command and after the message:
Code:
mysqld_safe --skip-grant-tables --skip-networking
we will use the CTRL + C combination, it should appear:
Code:
^C130504 21:32:04 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
We start the MySQL server in normal mode:
Code:
 /etc/init.d/mysql start
Finally, you can check the reset password:
Code:
mysql -uroot -p
 

dotKer

Member
Method 1:
mysql -u root
I presume the above is the below.
Method 1:
mysql> -u root
As I do the above and enter, it says the below
Code:
mysql> -u root
    ->
it is different from the below what I expected.\
Code:
mysql> -u root
mysql>
I am afraid that I don't know what is wrong.

For typing the following, it needs "mysql"
Code:
mysql> use mysql;
 
Last edited:

dotKer

Member
My presume was, I think again, wrong, so I did it again in slightly different way.

The following is the copy of the whole shell.
Code:
[email protected]:~$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
[email protected]:~$ sudo mysql -u root
[sudo] dotker의 암호:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)

Copyright (c) 2000, 2019, 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('password') where user='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql>
Why it says the following in the last line?
Code:
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
The following shows it has database named "mysql" correctly.
Code:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)
 
Last edited:

alkion

Member
You execute commands as root
and here I think I see $ or not root?
Code:
:~$ mysql -u root
Code:
mysql> update user set Password=PASSWORD('here is your password') WHERE User='root';
 

dotKer

Member
Code:
mysql> update user set Password=PASSWORD('******') WHERE User='root'; 
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

Code:
mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv  | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv        | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv     | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type               | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher             | blob                              | NO   |     | NULL                  |       |
| x509_issuer            | blob                              | NO   |     | NULL                  |       |
| x509_subject           | blob                              | NO   |     | NULL                  |       |
| max_questions          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections        | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections   | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                 | char(64)                          | NO   |     | mysql_native_password |       |
| authentication_string  | text                              | YES  |     | NULL                  |       |
| password_expired       | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed  | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked         | enum('N','Y')                     | NO   |     | N                     |       |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)
I see that there is no "Password" column in the table "user" in the above.

Why is there no "password" column?
(possiblity1) Something wrong in the installation of mysql possibly.
(possiblity2) Since I did play to type "password" in the shell so many time in various way, Maybe I did accidentally remove the column "Password".


What should I do?
(option1) Should I add the new column "Password" in the table "use"?
(option2) Should I remove mysql and reinstall it?
 

alkion

Member
reinstall
and

 
Last edited:

Members online


Latest posts

Top