MySQL 5.7 – Where is the thesql_secret file on Windows 7

MySQLmysql-5.7Securitywindows

I've just installed a copy of MySQL 5.7 for testing, and I love the fact that it now only creates a single root account, and puts the password into a file called .mysql_secret.

There's just one problem. Where can I find the above mentioned file. On Linux it is apparently found in $HOME, but my test DB is on Windows.

I tried various places including %USERPROFILE% but I just can't find it anywhere!!

Can anyone please tell me where to find this file? As otherwise my testing will be over before it has begun.

Best Answer

My blind guess would be to look in %APPDATA%\MySQL on your system as follows:

cd %APPDATA%
cd MySQL
dir .my_secret

For those who use the MySQL no-install Zip File (such as myself), you will not see one.

If .my_secret is not on your Windows servers, I have an alternative. You could start up mysqld manually with skip-grant-tables, set the password

net stop mysql
cd "C:\Program Files (x86)\MySQL\MySQL 5.7\bin"
start mysqld --skip-grant-tables
mysql -ANe"update mysql.user set authentication_string=PASSWORD('mynewpassword') where user='root'"
mysqladmin shutdown
net start mysql
mysql -uroot -p
Enter password: (enter the password 'mynewpassword' and hit <Enter>)

Give it a Try !!!

Related Solutions

MySQL SSL disabled on Windows

You need to check to make sure the user indeed has the ssl field loaded.

Run this query;

SELECT A.* FROM
(SELECT user,host FROM mysql.user WHERE ssl_type IS NOT NULL) A
INNER JOIN
(SELECT user,host FROM mysql.user WHERE ssl_cipher IS NOT NULL) B
USING (user,host);

If nothing comes back, then look the user using

SELECT * FROM
(SELECT user,host FROM mysql.user WHERE ssl_type IS NOT NULL) A
UNION
(SELECT user,host FROM mysql.user WHERE ssl_cipher IS NOT NULL);

If nothing comes back from this second query, that means no user in mysql.user has ssl_type and ssl_cipher filled in properly.

Another thing you can investigate is this:

Did you add this to my.ini ?

[mysqld]
ssl

If you didn't, please do so and run this

C:\> net stop mysql
C:\> net start mysql

Mysql – Where to put the.ini file for 5.6.21 MySQL Community Server on Windows

The folder C:\Program Files\MySQL\MySQL Server 5.6 is the correct location for my.ini.

There are two possible reasons 1400 shows up as table_definition_cache.

REASON #1

You did not restart mysql.

C:\> net stop mysql
C:\> net start mysql

Value should be in place afterwards.

REASON #2

The default value for table_definition_cache is -1 (autosized). That makes mysqld figure out a fair value for table_definition_cache, given your OS configuration.

CAVEAT

Make sure you place

table_definition_cache = 400

under the [mysqld] group header before you restart mysql

GIVE IT A TRY !!!

I dug a little further and learned why 1400 comes up for table_definition_cache

According to the Documentation for table_definition_cache

The default value is based on the following formula, capped to a limit of 2000:
400 + (table_open_cache / 2)

The default value for table_open_cache is 2000

Applying the formula

400 + (table_open_cache / 2) =
400 + (2000 / 2) =
400 + 1000 =
1400

One of two things is happening

table_definition_cache is still not being picked up from my.ini
table_definition_cache is being picked up from my.ini, but is being rejected due to needed resources