How to grant all privileges to root user in MySQL 8 0

Question

Tried  mysql gt  GRANT ALL PRIVILEGES ON     TO  root      IDENTIFIED BY  root  WITH GRANT OPTION    Getting     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  IDENTIFIED BY  root  WITH GRANT OPTION  at line 1    Note  The same is working when tried in previous versions   Also tried  mysql gt  GRANT ALL PRIVILEGES ON     TO  root      WITH GRANT OPTION    Getting     ERROR 1410  42000   You are not allowed to create a user with GRANT   MySQL  8 0 11 0  username password is root root

User · Answer

My Specs   mysql --version mysql  Ver 8 0 19 for Linux on x86 64  MySQL Community Server - GPL    What worked for me    mysql gt  USE mysql  mysql gt  UPDATE User SET Host     WHERE User  root  AND Host  localhost

User · Answer

Just my 2 cents on the subject  I was having the exact same issue with trying to connect from MySQL Workbench   I m running a bitnami-mysql virtual machine to set up a local sandbox for development   Bitnami s tutorial said to run the  Grant All Privileges  command    opt bitnami mysql bin mysql -u root -p -e  grant all privileges on     to  root      identified by  PASSWORD  with grant option     This was clearly not working  I finally got it to work using Mike Lischke s answer   What I think happened was that the root   user had the wrong credentials associated to it  So if you ve tried to modify the user s privileges and with no luck try    Dropping the user  Create the user again  Make sure you have the correct binding on your my cnf config file  In my case I ve commented the line out since it s just for a sandbox environment    From Mysql Console   List Users  helpful to see all your users    select user  host from mysql user    Drop Desired User   drop user     username            Create User and Grant Permissions   CREATE USER     username         IDENTIFIED BY     password      GRANT ALL PRIVILEGES ON     TO     username         WITH GRANT OPTION    Run this command   FLUSH PRIVILEGES    Locate your mysql config file  my cnf  and look for a line that looks like this   bind-address 127 0 0 1   and comment it using a        bind-address 127 0 0 1   Then restart your mysql service   Hope this helps someone having the same issue

User · Answer

For those who ve been confused by CREATE USER  root   localhost  when you already have a root account on the server machine  keep in mind that your  root   localhost  and  root   your remote ip  are two different users  same user name  yet different scope  in mysql server  Hence  creating a new user with your remote ip postfix will actually create a new valid root user that you can use to access the mysql server from a remote machine  For example  if you re using root to connect to your mysql server from a remote machine whose IP is 10 154 10 241 and you want to set a password for the remote root account which is  Abcdef123      here are steps you would want to follow   On your mysql server machine  do mysql -u root -p  then enter your password for root to login   Once in mysql gt  session  do this to create root user for the remote scope  mysql gt  CREATE USER  root   10 154 10 241  IDENTIFIED BY  Abcdef123        After the Query OK message  do this to grant the newly created root user all privileges  mysql gt  GRANT ALL ON     TO  root   10 154 10 241     And then  FLUSH PRIVILEGES    Restart the mysqld service  sudo service mysqld restart   Confirm that the server has successfully restarted  sudo service mysqld status    If the steps above were executed without any error  you can now access to the mysql server from a remote machine using root

User · Answer

1  This worked for me  First  create a new user  Example  User foo with password bar   gt  mysql gt  CREATE USER  foo   localhost  IDENTIFIED WITH mysql native password BY  bar     2  Replace the below code with a username with  foo     gt  mysql gt  GRANT ALL PRIVILEGES ON database name   TO foo   localhost        Note  database name is the database that you want to have privileges    means all on all   3  Login as user foo  mysql gt  mysql -u foo -p   Password  bar  4  Make sure your initial connection from Sequelize is set to foo with pw bar

User · Answer

You will get this error  ERROR 1410  42000   You are not allowed to create a user with GRANT  If you are trying to run a GRANT on a user that doesn t exist  Therefore  first run this to make sure the user you use in your GRANT matches exactly to what you have  select User  Host from user  In particular pay attention whether the user you created is at localhost but the one you are trying to grant to is

User · Answer

Starting with MySQL 8 you no longer can  implicitly  create a user using the GRANT command  Use CREATE USER instead  followed by the GRANT statement   mysql gt  CREATE USER  root      IDENTIFIED BY  root   mysql gt  GRANT ALL PRIVILEGES ON     TO  root      WITH GRANT OPTION    Caution about the security risks about WITH GRANT OPTION  see    Grant all privileges on database

User · Answer

My Specs   mysql --version mysql  Ver 8 0 16 for Linux on x86 64  MySQL Community Server - GPL    What worked for me    mysql gt  CREATE USER  username   localhost  IDENTIFIED BY  desired password   mysql gt  GRANT ALL PRIVILEGES ON db name   TO  username   localhost  WITH GRANT OPTION    Response in both queries   Query OK  O rows affected  0 10 sec     N B  I created a database  db name  earlier and was creating a user credential with all privileges granted to all tables in the DB in place of using the default root user which I read somewhere is a best practice

User · Answer

This worked for me   mysql gt  FLUSH PRIVILEGES  mysql gt  GRANT ALL PRIVILEGES ON     TO  root     WITH GRANT OPTION  mysql gt  FLUSH PRIVILEGES

User · Answer

This may work   grant all on dbtest   to  dbuser      identified by  mysql password

User · Answer

The specified user just doesn t exist on your MySQL  so  MySQL is trying to create it with GRANT as it did before version 8  but fails with the limitations  introduced in this version    MySQL s pretty dumb at this point  so if you have  root   localhost  and trying to grant privileges to  root      it treats them as different users  rather than generalized notion for root user on any host  including localhost   The error message is also misleading   So  if you re getting the error message  check your existing users with something like this  SELECT CONCAT      user         host       FROM mysql user    and then create missing user  as Mike advised  or adjust your GRANT command to the actual exisiting user specificaion

User · Answer

I had the same problem on CentOS and this worked for me  version  8 0 11    mysql gt  GRANT ALL PRIVILEGES ON     TO  root

User · Answer

I had this same issue  which led me here  In particular  for local development  I wanted to be able to do mysql -u root -p without sudo  I don t want to create a new user  I want to use root from a local PHP web app    The error message is misleading  as there was nothing wrong with the default  root      user privileges    Instead  as several people mentioned in the other answers  the solution was simply to set bind-address 0 0 0 0 instead of bind-address 127 0 0 1 in my  etc mysql mysql conf d mysqld cnf config  No changes were otherwise required

User · Answer

I see a lot of  wrong  answers  it is just as simple as this  USE mysql  CREATE USER  user   localhost  IDENTIFIED BY  P ssW0rd   GRANT ALL ON     TO  user   localhost   FLUSH PRIVILEGES   Note  instead of a self-created user you can use root to connect to the database  However  using the default root account to let an application connect to the database is not the preferred way  Alternative privileges  be careful and remember the least-privilege principle   -- Grant user permissions to all tables in my database from localhost -- GRANT ALL ON my database   TO  user   localhost    -- Grant user permissions to my table in my database from localhost -- GRANT ALL ON my database my table TO  user   localhost    -- Grant user permissions to all tables and databases from all hosts -- GRANT ALL ON     TO  user        If you would somehow run into the following error   ERROR 1130  HY000   Host    1 2 3 4    is not allowed to connect to this MySQL server  You need add change the following two lines in  etc mysql my cnf and restart mysql  bind-address             0 0 0 0 skip-networking

User · Answer

1  grant privileges  mysql  GRANT ALL PRIVILEGES ON   TO  root     WITH GRANT OPTION   mysql  FLUSH PRIVILEGES  2  check user table   mysql  use mysql  mysql  select host user from user   3 Modify the configuration file  mysql default bind ip 127 0 0 1  if we want to remote visit services just delete config   Modify the configuration file vi  usr local etc my cnf   Comment out the ip-address option  mysqld    Only allow connections from localhost  bind-address   127 0 0 1   4 finally restart the services  brew services restart mysql

User · Answer

Well  I just had the same problem  Even if route had     could not connect remotely  Now  having a look at my ini file  config file in windows  the bind-address statement was missed   So    I putted this bind-address     after  mysqld  and restarted the service  Now it works

User · Answer

Check out your username and domain is the same as created before  Mysql select account by the two colums in user table If it is different  mysql may think you want to create a new account by grant which is not supported after 8 0 version

[mysql] How to grant all privileges to root user in MySQL 8.0

Examples related to mysql

Examples related to mysql-error-1064

Examples related to mysql-8.0