How to start MySQL with --skip-grant-tables

Question

I locked my root user out from our database  I need to get all privileges back to the root user  I have my password and I can log in to MySQL  But the root user has no all privileges

User · Answer

On the Linux system you can do following (Should be similar for other OS)

Check if mysql process is running:

sudo service mysql status

If runnning then stop the process: (Make sure you close all mysql tool)

sudo service mysql stop

If you have issue stopping then do following

Search for process: ps aux | grep mysqld Kill the process: kill -9 process_id

Now start mysql in safe mode with skip grant

sudo mysqld_safe --skip-grant-tables &

User · Answer

I m in windows 10  using WAMP64 server  Searched for my cnf and my ini  Found my ini in C  wamp64 bin mariadb mariadb10 2 14   Following the instructions from the colleagues    Opened the quick start menu from Wampserver  selected  Stop All Services  Opened my ini in a text editor  searched for  mysqld  Added  skip-grant-tables  at the end of the  mysqld  section  but within it  Save the file  leave the editor open In the Wampserver menu  select  Restart Services   There will be a warning about the skip-grant-tables option In the Wampserver menu select MySQL to open the prompt It asked for a password  just press enter Paste the command ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  newpassword   It must report that the operation was successful  no tables affected  In the my ini file  erase the  skip-grant-tables  line  save the file In the WampServer menu  select once more Restart Service   Now you can enter with the new password  Thanks to all answers here

User · Answer

How to re-take control of the root user in MySQL   DANGER  RISKY OPERATTION   Start session ssh  using root if possible   Edit my cnf file using   sudo vi  etc my cnf  Add line to mysqld block    skip-grant-tables  Save and exit  Restart MySQL service   service mysql restart  Check service status   service mysql status  Connect to mysql   mysql  Using main database   use mysql   Redefine user root password   UPDATE user SET  authentication string    PASSWORD  myNuevoPassword   WHERE  User     root     Edit file my cnf   sudo vi  etc my cnf  Erase line   skip-grant-tables  Save and exit  Restart MySQL service   service mysqld restart  Check service status   service mysql status  Connect to database   mysql -u root -p  Type new password when prompted    This action is very dangerous  it allows anyone to connect to all databases with no restriction without a user and password  It must be used carefully and must be reverted quickly to avoid risks

User · Answer

If you use mysql 5 6 server and have problems with C  Program Files MySQL MySQL Server 5 6 my ini   You should go to C  ProgramData MySQL MySQL Server 5 6 my ini   You should add skip-grant-tables and then you do not need a password     SERVER SECTION   ----------------------------------------------------------------------     The following options will be read by the MySQL Server  Make sure that   you have installed the server correctly  see above  so it reads this    file      server type 3  mysqld  skip-grant-tables   Note  after you are done with your work on skip-grant-tables  you should restore your file of C  ProgramData MySQL MySQL Server 5 6 my ini

User · Answer

Edit my ini file and add skip-grant-tables and restart your mysql server     mysqld  port  3306 socket    C  xampp mysql mysql sock  basedir    C  xampp mysql   tmpdir    C  xampp tmp   datadir    C  xampp mysql data  pid file    mysql pid    enable-named-pipe key buffer   16M max allowed packet   1M sort buffer size   512K net buffer length   8K read buffer size   256K read rnd buffer size   512K myisam sort buffer size   8M log error    mysql error log  skip-grant-tables   Change here for bind listening   bind-address  127 0 0 1     bind-address     1

User · Answer

I had the same problem as the title of this question  so incase anyone else googles upon this question and wants to start MySql in  skip-grant-tables  mode on Windows  here is what I did   Stop the MySQL service through Administrator tools  Services   Modify the my ini configuration file  assuming default paths   C  Program Files MySQL MySQL Server 5 5 my ini   or for MySQL version    5 6  C  ProgramData MySQL MySQL Server 5 6 my ini    In the SERVER SECTION  under  mysqld   add the following line   skip-grant-tables   so that you have    SERVER SECTION   ----------------------------------------------------------------------     The following options will be read by the MySQL Server  Make sure that   you have installed the server correctly  see above  so it reads this    file     mysqld   skip-grant-tables   Start the service again and you should be able to log into your database without a password

User · Answer

Use the following command  notice the  d    mysqld --skip-grant-tables

User · Answer

Please run this below command from the console to skip the user table verification while launching mysql database from command prompt  mysqld -skip-grant-tables

User · Answer

if you are running on Apple MacBook OSX then      Stop your MySQL server  if it is already running    Find your MySQL configuration file  my cnf   For me it was placed    Applications XAMPP xamppfiles etc  You can just search if you can t find it   Open my cnf file in any text editor  Add  skip-grant-tables   without quotes  at the end of  mysqld  section and save the file  Now start your MySQL server  It ll start with skip-grant-tables option    Do what you want now    PS  Please remove skip-grant-tables from my cnf file once you are done with whatsoever you want to do ELSE MySQL server will always run without access grants

User · Answer

if this is a windows box  the simplest thing to do is to stop the servers  add skip-grant-tables to the mysql configuration file  and restart the server   once you ve fixed your permission problems  repeat the above but remove the skip-grant-tables option   if you don t know where your configuration file is  then log in to mysql send SHOW VARIABLES LIKE   config   and one of the rows returned will tell you where your configuration file is

User · Answer

After trying lots of things  this is what worked for me   sudo mysql -u root ALTER USER  root   localhost  IDENTIFIED WITH mysql native password BY  newpassword      So first we use sudo to log in mysql as root without needing a password  Then we just update root s password   After that  I restarted mysqld   sudo service mysql restart   And the newpassword logged root in

[mysql] How to start MySQL with --skip-grant-tables?

Examples related to mysql

Examples related to privileges