12

I have logged in to MySQL with the --skip-grant-tables option. But I don't know how to get all privileges back to the root user.

I tried:

GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost';

MySQL said:

# 1290 - The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Trying:

GRANT ALL PRIVILEGES ON * . * TO 'root'@'localhost'
WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0
MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

MySQL said:

# 1290 - The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
Tony
  • 9,672
  • 3
  • 47
  • 75
Steven
  • 24,410
  • 42
  • 108
  • 130
  • 1
    There seems to be a solution in this question/answer http://stackoverflow.com/questions/1709078/how-can-i-restore-the-mysql-root-users-full-privileges – Jeff Paquette Nov 11 '09 at 03:27

6 Answers6

21

For mysql 8.0.12+
I've tried the documentation (not working) I've tried various other options, all failed.

PASSWORD() is deprecated, SET PASSWORD is disabled, ALTER USER is disabled as well.

  1. stop mysql service
  2. Start it this way: /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
  3. Login (mysql) and exec: use mysql; update user set authentication_string='' where User='root';
  4. killall mysqld
  5. Start service again

Now you can use set password or alter user after logging in without a password

John
  • 7,507
  • 3
  • 52
  • 52
  • 2
    THIS IS THE ANSWER! Why in the world is PASSWORD() deprecated? So strange. – mbrinson Jun 25 '20 at 23:04
  • 2
    @mbrinson the number of strange things I've noticed with MySQL is huge. The truth is that it's not maintained professionally, it's a combination of hundreds of ugly hacks. INNODB is slow in counting because mysql is using only one thread with huge latency in processing cycles, what's the solution ? They invent a complex result cache. With Mysql 8 they remove the cache (breaking ten thousands of applications) and invent multithreading, however only multithreading for queries without WHERE so only for counts.. I could give countless examples like these, it's maintained by 'code hackers' – John Jun 30 '20 at 00:43
  • 3
    I'm getting a No Database selected error. – Overload119 Jul 10 '21 at 18:51
  • 1
    @Overload119: Execute `USE mysql;` before `UPDATE user...` – Daniel Rikowski Dec 02 '21 at 10:47
  • I get `Column 'authentication_string' is not updatable` – Burrito Feb 12 '23 at 16:59
8

If you cant acces to mysql server as a root you should delete or cannot restore all root records in mysql.user table, delete all of them and add a new record.

You should use mysql PASSWORD() function to hash your cleartext password. check for more information http://dev.mysql.com/doc/refman/5.1/en/password-hashing.html

First stop mysql server and launch mysqld with --skip-grant-tables.

[root@mysql ~]# service mysqld stop; mysqld_safe --skip-grant-tables &

Hash your cleartext password.

mysql> select PASSWORD('CLEARTEXT_PASSWORD');
+-------------------------------------------+
| PASSWORD('CLEARTEXT_PASSWORD')            |
+-------------------------------------------+
| *1EADAEB11872E413816FE51216C9134766DF39F9 |
+-------------------------------------------+
1 row in set (0.00 sec)

Use mysql database to apply changes.

mysql> use mysql;

Delete all root records

mysql> delete from user where User='root';

Add new record that root user can access with all privileges from your ip adress.

mysql> insert into `user` VALUES('YOUR_IP_ADDRESS','root','*1EADAEB11872E413816FE51216C9134766DF39F9','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','','',0,0,0,0,'','');

Flush changes.

mysql> FLUSH PRIVILEGES;

Exit from mysql command line .

mysql> exit;

Restart mysqld service.

[root@mysql ~]# /etc/init.d/mysqld restart

if you run this mysql commands/ queries you will get a new access to mysql server.

cyb0k
  • 2,478
  • 23
  • 19
  • If the Insert has a hard time matching the required privilege columns, see answer below. Copy an existing line, if you only need a new Grant with e.g. another allowed host column. – user18099 Nov 04 '16 at 09:49
  • 3
    select PASSWORD('CLEARTEXT_PASSWORD'); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('CLEARTEXT_PASSWORD')' at line 1 – Draif Kroneg Jun 25 '20 at 09:25
7

First, stop the MySQL server and then start it with the --skip-grant-tables option.

[root@backups1 mysql5.7]# /etc/init.d/mysqld stop
[root@backups1 mysql5.7]# /usr/local/mysql/bin/mysqld_safe --skip-grant-tables --user=mysql

Then, connect to your instance without a password:

[root@backups1 mysql5.7]# mysql -u root

Then, reset the password for the root user.

mysql> flush privileges;
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '';
mysql> flush privileges;

Switch to normal mode of MySQL then connect without password.

a2f0
  • 1,615
  • 2
  • 19
  • 28
Mansur Ul Hasan
  • 2,898
  • 27
  • 24
  • If I login as root with the skip-grant-tables option then I can't execute any statements. The error is : "The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement" – Vincent Aug 27 '21 at 16:37
  • 1
    in that case you need to run flush privileges – Mansur Ul Hasan Aug 27 '21 at 17:37
  • I tried lots of things without success until this. I think the key is running `flush privileges` before any `alter user` command. Could you explain why that suppresses the error `"The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement"`? – Burrito Feb 12 '23 at 17:12
5

When you run mysql using --skip-grant-tables mysql won't check any permissions. So basically you can do anything.

To get back the root privileges you would need to run a query in mysql DB like this

select * from user where user = 'root'

just to check if the root user is still there if ok:

UPDATE user SET Grant_priv = 1, Super_priv = 1 WHERE user = 'root'

after you can restart mysql without the --skip-grant-tables and the root user should be able to do some grant so your query should work

RageZ
  • 26,800
  • 12
  • 67
  • 76
  • 2
    In MariaDb the correct statement is `UPDATE user SET Grant_priv = 'Y', Super_priv = 'Y' WHERE user = 'root'`, because it uses ENUM ('Y', 'N'). Your original version has been executed, but the values in the table 'Y'/'N' not updated, but this small modification did the trick. – Dankó Dávid Jun 08 '20 at 08:51
1

If the goal of your --skip-grant-tables is insert a new Grant, you can do so by inserting a line in mysql.user (see other answers)

If your challenge is in giving this new Grant all the required privileges (many Y/N colums), then you can copy an existing root grant. And adjust only what you require.

CREATE TEMPORARY TABLE mysql.tmpUser SELECT * FROM mysql.user WHERE host="old" and user="root";
UPDATE mysql.tmpUser SET host="new" WHERE user = "root";
INSERT INTO mysql.user SELECT * FROM mysql.tmpUser;
user18099
  • 663
  • 1
  • 6
  • 13
-10

To run a GRANT query, you just don't run mysql with skip-grant-tables -- what's complicated about that...?

Alex Martelli
  • 854,459
  • 170
  • 1,222
  • 1,395
  • 6
    he needs to get root privileges. – RageZ Nov 11 '09 at 03:38
  • ...so he shouldn't be using `skip-grant-tables` -- why ever would he want that _and_ `GRANT`? Though I admit your approach works too. – Alex Martelli Nov 11 '09 at 04:05
  • 3
    It seems he forgot password for the root user. – bluszcz Feb 12 '14 at 12:57
  • The question seems to be about getting privilege flags back. You can use skip-grant to log in. But you can not grant. You can insert a line in mysql.user for a new root grant line, but the query needs to match many privilege columns. One Y/N for each privilege. – user18099 Nov 04 '16 at 09:45