Solved Set/Reset MySQL Password after the installation.

[vince66]

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

 

[SirDice]

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

 

[VladiBG]

https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

scroll down to:
B.5.3.2.3 Resetting the Root Password: Generic Instructions

 

[vince66]

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]

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?

 

[vince66]

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.

 

[SirDice]

Probably it'is wrong the documentation.

No, it's not wrong. You just used a different way of stopping the MySQL process.

 

[vince66]

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

Thanks very much.

 

[vince66]

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]

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.

 

[vince66]

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]

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

The whole thing:

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

 

[vince66]

Login without a password

What means login without a password.

Please write the comand. I've not used password.

 

[vince66]

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.

 

[vince66]

> 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




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]

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';

 

[vince66]

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:

mysql_enable="YES"

in /etc/rc.conf


Thanks in advance.

 

[VladiBG]

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.

 

[vince66]

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:

If you have clean install of mysql you don't have password so you can just login as root and set your new password

=

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]

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:

root% pkg install mysql57-server

To start MySQL server:

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:

root% mysql -u root -p

Do not type any password yet, press ENTER key. You will see:

Enter password:

Type in password given in /root/.mysql_secret file, and connection to server is complete.

Change MySQL root password with

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]

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

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]

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

The whole thing:

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]

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.

 

[jetoblaster]

I haven't done a mysql server install for a while, I too did a search. I do not doubt the methods above in gaining access however I do believe the correct way to get your installation of mysql server ready after the initial install is to simply start mysql using the normal command such as;

/usr/local/etc/rc.d/mysql-server onestart or /usr/local/etc/rc.d/mysql-server start

Once the server is started, secure the installation by running;

/usr/local/bin/mysql_secure_installation

This will allow you to not only set a root password, you can remove the test database etc..

You will then need to reset the root password with the following

ALTER USER 'root'@'localhost' IDENTIFIED BY 'NEWPASSWORDGOESHERE';

 

[VladiBG]

mysql_secure_installation can be run only if the root password is known or empty after initial installation so you can't use it to reset the forgotten password.