Forum Stats

  • 3,733,034 Users
  • 2,246,685 Discussions
  • 7,856,484 Comments

Discussions

Cannot start mysql with --skip-grant-tables argument

3720642
3720642 Member Posts: 10
edited January 2019 in MySQL Community Space

I've recently had to reinstall mysql server and, for some reason, there was no prompt to set a root password. Also, running dpkg-reconfigure mysql-community-server (and client) doesn't bring any prompts. The old password I used doesn't work, nor does a variety of different passwords, nor leaving the password blank.

So, I've looked around for a way to reset the root password and found that you need to stop the service, then start it with the arguments --skip-grant-tables --skip-networking, or --initialize-insecure (https://dev.mysql.com/doc/refman/8.0/en/data-directory-initialization-mysqld.html)

The problem is that, whenever I try either, it fails with the following error message:

2018-12-27T13:57:43.343621Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 67033

2018-12-27T13:57:43.346792Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

2018-12-27T13:57:43.346863Z 0 [ERROR] [MY-010119] [Server] Aborting

2018-12-27T13:57:43.347038Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13)  MySQL Community Server - GPL.

[email protected]:/# mysql

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Of course, simply pointing to the Security section doesn't help at all. What, exactly, do I have to do to make the command (mysqld --skip-grant-tables --skip-networking OR --initialize-insecure) run with those arguments and not fail?

Running on Debian 9

Tagged:

Best Answer

  • 3720642
    3720642 Member Posts: 10
    edited January 2019 Accepted Answer

    Turns out the problem was using the mysqld command. I tried all those options mentioned, but none worked, it was always "mysql is not running". Even using it by itself returns the following error message:

    [email protected]:/usr/sbin# mysqld

    2019-01-02T15:20:47.976625Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 32421

    2019-01-02T15:20:47.979711Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

    2019-01-02T15:20:47.979787Z 0 [ERROR] [MY-010119] [Server] Aborting

    2019-01-02T15:20:47.980008Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13)  MySQL Community Server - GPL.

    However, service mysql start always worked. So, instead of mysqld, i simply did service mysql start --initialize-insecure, which then allowed me to connect to the mysql command line without asking me for the root password. I feel dumb for not trying this earlier. Still, thanks for the help and sorry for the trouble.

    --Running on Debian 9

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited December 2018

    It is not clear that you actually used any of the commands in the document you link to.

    You have not shown the command-line and arguments you ran that generated that error, or who you were logged in as.

    It would benefit you, I believe, if you read this part of the documentation too, which goes to explain the difference between an upgrade to 8 and an install of 8 WRT root security.

    caching_sha2_password and the root Administrative Account

  • 3720642
    3720642 Member Posts: 10
    edited December 2018

    [email protected]:/var/lib/mysql# mysqld --skip-grant-tables --skip-networking

    2018-12-31T14:48:39.557080Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 129788

    2018-12-31T14:48:39.560182Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

    2018-12-31T14:48:39.560250Z 0 [ERROR] [MY-010119] [Server] Aborting

    2018-12-31T14:48:39.560412Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13)  MySQL Community Server - GPL.

    [email protected]:/var/lib/mysql#

    Also, this:

    [email protected]:/var/lib/mysql# mysqld --initialize-insecure

    [email protected]:/var/lib/mysql# mysqld --initialize-insecure &

    [1] 130100

    [email protected]:/var/lib/mysql# mysql -uroot

    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

    [1]+  Fim da execução com status 1      mysqld --initialize-insecure

    [email protected]:/var/lib/mysql# mysqld --initialize-insecure --user root

    [email protected]:/var/lib/mysql# mysqld --initialize-insecure --user root &

    [1] 130127

    [email protected]:/var/lib/mysql# mysql

    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

    [1]+  Fim da execução com status 1      mysqld --initialize-insecure --user root

    [email protected]:/var/lib/mysql#

    I don't see how that link you gave is supposed to help me? I cannot connect with root while mysql is running, I cannot change the password through the mysql console, I need to reset it.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited December 2018

    The link I posted explains the difference between upgrade to 8.0 and install WRT security.

    The link you posted states:

    pastedImage_0.png

    ...so try that, specifying the CORRECT user "--user=mysql"

    The documentation goes on to say:

    pastedImage_0.png

    so you may need something similar to:

    <span class="prompt token">shell></span><span class="command token"> bin/mysqld</span> <span class="property token">--initialize</span> <span class="token constant">--user</span><span class="attr-value token"><span class="punctuation token">=</span>mysql</span><br/>   <span class="token constant">--basedir</span><span class="attr-value token"><span class="punctuation token">=</span>/opt/mysql/mysql</span><br/>   <span class="token constant">--datadir</span><span class="attr-value token"><span class="punctuation token">=</span>/opt/mysql/mysql/data</span>

    Note "--user=mysql" again.

  • 3720642
    3720642 Member Posts: 10
    edited January 2019 Accepted Answer

    Turns out the problem was using the mysqld command. I tried all those options mentioned, but none worked, it was always "mysql is not running". Even using it by itself returns the following error message:

    [email protected]:/usr/sbin# mysqld

    2019-01-02T15:20:47.976625Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 32421

    2019-01-02T15:20:47.979711Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!

    2019-01-02T15:20:47.979787Z 0 [ERROR] [MY-010119] [Server] Aborting

    2019-01-02T15:20:47.980008Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13)  MySQL Community Server - GPL.

    However, service mysql start always worked. So, instead of mysqld, i simply did service mysql start --initialize-insecure, which then allowed me to connect to the mysql command line without asking me for the root password. I feel dumb for not trying this earlier. Still, thanks for the help and sorry for the trouble.

    --Running on Debian 9

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,776 Bronze Crown
    edited January 2019

    No problem, happy to hear you got it sorted.

Sign In or Register to comment.