Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

upgrading mysql database to 5.5 the root user has disappeared.

I'm trying to create it again using this commands:

javier@javier-mbp:~$ sudo pkill mysqld
[sudo] password for javier: 
javier@javier-mbp:~$ sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables&
[1] 1927
javier@javier-mbp:~$ 111028 16:37:44 mysqld_safe Logging to '/var/log/mysql/error.log'.
111028 16:37:44 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

Then I do:

javier@javier-mbp:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT CREATE, DROP ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Any help?

EDIT: solved, well.. I just should do a backup of the user 'root' since I'm removing the database before upgrading to 5.5.

share|improve this question
add comment

1 Answer 1

When you start mysql with --skip-grant-tables, you cannot use GRANT/REVOKE commands. You can INSERT directly into the mysql.user table.

Whereas this command cannot work using --skip-grant-tables:

GRANT CREATE, DROP ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;

This command should be run instead:

REPLACE INTO mysql.user SET
user='root',
host='localhost',
password=PASSWORD('123456'),
create_priv='Y',
drop_priv='Y',
grant_priv='Y';

and restart mysql.

You may also want to backup your grants from the previous mysql installation as SQL commands.

share|improve this answer
    
Roland, this is a terrific answer! Although the step of adding the user to the user table is clear, I was uncertain if their is additional business logic that may affect other tables. Seeing your answer, coming from you, assures me that there is not! –  dotancohen Oct 28 '13 at 12:29
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.