Solved Set/Reset MySQL Password after the installation.

vince66

Well-Known Member

Reaction score: 13
Messages: 406

How to set MySQL Password after MySQL server after the installation ?

I've just read the threads in forums ... but typing:

mysql -u root -p

I receive the message of Access denied.

Wait for kindly tips.

Thanks in advance
 
Last edited:

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 8,910
Messages: 33,305

Start MySQL with --skip-grant-tables, login, change the password. Stop mysql, remove --skip-grant-tables, start MySQL, login with new password.
 
OP
vince66

vince66

Well-Known Member

Reaction score: 13
Messages: 406

Resetting the Root Password: Generic Instructions
I use the mysql57 so the analog link is:

B.5.3.2.2 Resetting the Root Password: Unix and Unix-Like Systems

It doesn't work.
__________________________________________________________________________
The documentation says:
2. Stop the MySQL server if it is running. Locate the .pid file that contains the server's process ID. The location of the .pid file on my machine is: /var/db/mysql. The fielname is: my_host_name.pid

Stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process.
........
_____________________________________________________________________________


I've stopped MySQL with:

mysql-server stop

but so doing the .pid file disappears in /var/db/mysql . How can I send the kill comand to mysqld ???????
______________________________________________________________________________


Note about my error. After installing mysql57 I started the server as root without using --user=mysql, so the server has probably create root-owned files in the data directory. How can I delete them ?
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 8,910
Messages: 33,305

How can I send the kill comand to mysqld ???????
Instead of blindly following some commands, stop to think what they actually do. Is it really useful to send a kill to a process that's already stopped?
 
OP
vince66

vince66

Well-Known Member

Reaction score: 13
Messages: 406

Is it really useful to send a kill to a process that's already stopped?
Surely no !!!
and I'm agree with you.

I've only reported what is written on the documentation page. Probably it'is wrong the documentation.
Give a look at the link above.
 
OP
vince66

vince66

Well-Known Member

Reaction score: 13
Messages: 406

Ok ... please, let me try the solution ... then I'll post it here.

Thanks very much.
 
OP
vince66

vince66

Well-Known Member

Reaction score: 13
Messages: 406

Resetting the Root Password: Generic Instructions
What is the password I see in the /home/mysql_secret file ?

Is it the temporary password randomly generated after the installation process ?

Why it doesn't work with the comand mysqladmin ?
 

VladiBG

Aspiring Daemon

Reaction score: 331
Messages: 772

there's no standard file /home/mysql_secret file, maybe it's left over from other admin which was installing the server.
There's no temporary password.
You are not following the instructions that i send you at my first post. They will work also for MySQL 5.7.
 
OP
vince66

vince66

Well-Known Member

Reaction score: 13
Messages: 406

You are not following the instructions that i send you at my first post.
1. Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option.

# /usr/local/bin/mysqld_safe --skip-grant-tables -> it runs

2. Connect to the MySQL server using the mysql client

# mysql -> this gives Access denied for the user root@localhost, that is the user I want to use.
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 8,910
Messages: 33,305

Login without a password, the --skip-grant-tables basically disables authentication/authorization. Allowing you to login without a password.

The whole thing:
Code:
service mysql-server stop
sysrc mysql_args="--skip-grant-tables"
service mysql-server start
mysql -u root
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
> exit
service mysql-server stop
sysrc -x mysql_args
service mysql-server start
 
OP
vince66

vince66

Well-Known Member

Reaction score: 13
Messages: 406

The whole thing:
ok.

Before I use your code, Please ...

are the code you wrote the first post-installation thing to do to set the password of the root@localhost ?

My question follows because I've read that MySQL for FreeBSD doesn't provide a initial configuration during the installation process = no password setting, no configuration of the data folder, etc ..........

Thanks in advance.
 
OP
vince66

vince66

Well-Known Member

Reaction score: 13
Messages: 406

> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
Surely I've not been clear.
I've executed the steps you wrote ... and I've also tryed other ways but without results.

The error I receive regards the statement above.

ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement.

If you want I send you a picture of my monitor :):):):):):):):):):):)

There's no temporary password.
At the finish of the installation process is visualized a clear message about the use of a temporary password stored in the directory ./HOME/.mysql_secrete file

o_Oo_Oo_Oo_Oo_Oo_Oo_Oo_Oo_Oo_Oo_Oo_Oo_Oo_O


In any case:
I don' like this way.
Are not clear the steps to follow after the post installation of MySQL to set the password of the root@localhost.

Why I need to set the password using the --skip-grant-tables option, approaching the problem by the client side (that is less secure) ?
What is the server side solution to set the password of the root@localhost (or other network users) ?
Is there an ufficial documentation for the FreeBSD (different by the once of MySQL homepage) ?

It is not possible that in the web are thousand of different solution to approach this setting.

Sorry.:):):):):):):)
 

VladiBG

Aspiring Daemon

Reaction score: 331
Messages: 772

  1. Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, if the server is started with the --skip-grant-tables option, it enables --skip-networking automatically to prevent remote connections.
  2. Connect to the MySQL server using the mysql client; no password is necessary because the server was started with --skip-grant-tables:
    shell> mysql
  3. In the mysql client, tell the server to reload the grant tables so that account-management statements work:
    mysql> FLUSH PRIVILEGES;
    Then change the 'root'@'localhost' account password. Replace the password with the password that you want to use. To change the password for a rootaccount with a different host name part, modify the instructions to use that host name.
    mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass';
 
OP
vince66

vince66

Well-Known Member

Reaction score: 13
Messages: 406

1. Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such as ALTER USER and SET PASSWORD. Because this is insecure, if the server is started with the --skip-grant-tables option, it enables --skip-networking automatically to prevent remote connections.
Ok Vladi ....

I have re-installed the FreeBSD, so the machine is clean !
I'll install again the mysql57-server.

I'll repeat the steps you wrote (I've already follow them this morning).
I'll reply you step by step.

Please, can you write your comand/comands to realize the step 1 ?
I've used:

/usr/local/libexec/mysqld [URL='https://dev.mysql.com/doc/refman/8.0/en/server-options.html#option_mysqld_skip-grant-tables']--skip-grant-tables[/URL]


Preliminary step immediately after the installation.
I declare the statement:
Code:
mysql_enable="YES"
in /etc/rc.conf


Thanks in advance.
 

VladiBG

Aspiring Daemon

Reaction score: 331
Messages: 772

If you have clean install of mysql you don't have password so you can just login as root and set your new password. IF you have a MySQL server which you didn't install and need to reset the password then go trough the steps for reseting the MySQL root password.

SirDice already told you how to modify your rc.conf to pass the additional argument to the MySQL during the start so it runs with --skip-grant-tables option.
 
OP
vince66

vince66

Well-Known Member

Reaction score: 13
Messages: 406

If you have clean install of mysql you don't have password so you can just login as root and set your new password.
Surely I'm an idiot.
However, please, as the computer science has its language, don't speack the natural english language.
It's more easy write instructions.

I'll try to translate:
Code:
If you have clean install of mysql you don't have password so you can just login as root and set your new password
=

Code:
login: root
password: toor

root@maya:# mysql -u root -p
Enter password: toor (obviously it is not visible)

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 25338 to server version: 8.0.13-standard

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

mysql>
Is it enough to establish a connection to the MySQL server (according to 3.1 Connecting to and Disconnecting from the Server)?
This is my aim ... so I can view databases, do database's dumps importing .frm, .MYD, .MYI and db.opt in the data folder and analyze tables.

I do not need to do anything else.

Thanks very much in advance for your time.
 

ljboiler

Well-Known Member

Reaction score: 135
Messages: 410

MySQL "root" user is not the same as FreeBSD "root" user, and have different passwords.

If you do this on a FreeBSD machine where mysql-server was never installed:
Code:
root% pkg install mysql57-server
To start MySQL server:
Code:
root% service mysql-server onestart
It will create /root/.mysql_secret file, which has MySQL "root" password. MUST START MYSQL SERVER TO CREATE THIS FILE.
Login to mysql server:
Code:
root% mysql -u root -p
Do not type any password yet, press ENTER key. You will see:
Code:
Enter password:
Type in password given in /root/.mysql_secret file, and connection to server is complete.

Change MySQL root password with
Code:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
Add mysql_enable="YES" to /etc/rc.conf to make mysql server start when machine is rebooted.
 

udanieli

New Member

Reaction score: 1
Messages: 3

Type in password given in /root/.mysql_secret file, and connection to server is complete.
damn I've been racking my brains for hours. The --skip-grant-tables option does not work and I would never have noticed that auto-generated /root/.mysql_secret file. It's in the port installation message

Code:
Message from mysql57-server-5.7.27:

--
Initial password for first time use of MySQL is saved in $HOME/.mysql_secret
ie. when you want to use "mysql -u root -p" first you should see password
in /root/.mysql_secret

Thank you.
 

sajigol919

New Member

Reaction score: 2
Messages: 9

Login without a password, the --skip-grant-tables basically disables authentication/authorization. Allowing you to login without a password.

The whole thing:
Code:
service mysql-server stop
sysrc mysql_args="--skip-grant-tables"
service mysql-server start
mysql -u root
> ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpassword';
> exit
service mysql-server stop
sysrc -x mysql_args
service mysql-server start
This has saved me. Thanks Sirdice. However, if someone else comes across this, you need to run FLUSH PRIVILEGES; after mysql -u root. Maybe you can update your instructions list SirDice?
 

SirDice

Administrator
Staff member
Administrator
Moderator

Reaction score: 8,910
Messages: 33,305

However, if someone else comes across this, you need to run FLUSH PRIVILEGES; after mysql -u root.
You need to restart MySQL in any case, or else that --skip-grant-tables would still be active. Restarting MySQL will automatically flush the privileges.
 
Top