Use Skip-grant-tables to troubleshoot MYSQL root password loss

b.5.3.2 How to Reset the Root Password

If you had never assigned a root password for MySQL, the server does not require a password at all for connecting as . However, this is insecure. For instructions in assigning passwords, see section 2.18.4, "Securing the Initial MySQL Accounts".

If you know the root password and want to change it, see section 13.7.1.6, "SET password Syntax".

If you assigned a root password previously but has forgotten it, you can assign a new password. The following sections provide instructions for Windows and Unix and unix-like systems, as well as generic instructions th At the apply to any system.

b.5.3.2.1 resetting the Root password:windows Systems

On Windows, with the following procedure to reset the password for the MySQL account ‘root‘@‘localhost‘ . The password for a, a root different host name part, and modify the instructions to use that host name.

2. Log on to your system as Administrator.

4. Stop the MySQL server if it is running. For a server so is running as a Windows service, go to the Services manager:from the Start menu, selectControl Panel, then administrative Tools, then Services. Find the MySQL service in the list and stop it.

   If your server is not running as a service, you may be need to use the Task Manager to force it to stop.

6. Create a text file containing the following statement on a single line. Replace the password with the password, that's want to use.

   SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' Mynewpass ');

8. Save the file. This example assumes the name of the file C:\mysql-init.txt .

10. Open a console window to get to the command Prompt:from the Start menu, select Run, then enter cmd A s the command to be run.

11. Start the MySQL server with the Special --init-file option (notice, the backslash in the option value is doubled):

    cd "C:\Program Files\MySQL\MySQL Server 5.0\bin"mysqld-nt --init-file=C:\\mysql-init.txt

    If you installed MySQL to a different location, adjust the CD command accordingly.

    The server executes --init-file the contents of the file named by the option at startup, changing the account ‘root‘@‘localhost‘ password.

    To has server output to appear in the console window rather than in a log file, add the --console option to the mysqld command.

    If you installed MySQL using the MySQL installation Wizard, you may be need to specify a --defaults-file option. For example:

    mysqld-nt         --defaults-file="C:\\Program Files\\MySQL\\MySQL Server 5.0\\my.ini"         --init-file=C:\\mysql-init.txt

    The appropriate --defaults-file setting can be found using the Services manager:from the Start menu, select Control Panel, Thenadministrative Tools, then Services. Find the MySQL service in the list, right-click it, and choose the Properties option. The Path to executable field contains the --defaults-file setting.

13. After the server has started successfully, delete C:\mysql-init.txt .

You should now is able to connect to the MySQL server as root using the new password. Stop the MySQL server and restart it normally. If you run the server as a service, start it from the Windows Services window. If you start with the server manually, use whatever command normally.

b.5.3.2.2 resetting the Root Password:unix and Unix-like Systems

On Unix, use the following procedure-to-reset the password for the MySQL account ‘root‘@‘localhost‘ . The password for a, a root different host name part, and modify the instructions to use that host name.

The instructions assume that's you'll start the MySQL server from the Unix login account so you normally use for running It. For example, if you run the server using themysqlLogin account, your should log in asmysqlBefore using the instructions. Alternatively, can log in asroot, but the This case must start mysqld with the --user=mysql option. If you start the server as root without using --user=mysql , the server could create root -owned files in the data directory, such As log files, and these may cause permission-related problems for the future server startups. If that happens, you'll need to either the ownership of the files to mysql or remove them.

2. Log on to your system as the Unix user and the MySQL server runs as (for example, mysql ).

3. Stop the MySQL server if it is running. Locate the .pid file that contains the server ' s process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations is /var/lib/mysql/ , /var/run/mysqld/ , and /usr/local/mysql/data/ . Generally, the file name has a extension of and .pid begins with either mysqld or your system ' s host name.

   Stop the MySQL server by sending a normal kill (not kill -9 ) to the mysqld process. Use the actual path name of the .pid file in the following command:

   kill `cat /mysql-data-directory/host_name.pid`

   Use Backticks (not forward quotation marks) with the cat command. These cause the output of to is cat substituted into the kill command.

5. Create a text file containing the following statement on a single line. Replace the password with the password, that's want to use.

   SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' Mynewpass ');

7. Save the file. This example assumes the name of the file /home/me/mysql-init . The file contains the password, so does not save it where it can is read by other users.  If You is not logged mysql in as (the user of the server runs as), make sure that the file have permissions that permit mysql To read it.

9. Start the MySQL server with the special --init-file option:

   mysqld_safe --init-file=/home/me/mysql-init &

   The server executes --init-file the contents of the file named by the option at startup, changing the account ‘root‘@‘localhost‘ password.

11. After the server has started successfully, delete /home/me/mysql-init .

You should now is able to connect to the MySQL server as root using the new password. Stop the server and restart it normally.

b.5.3.2.3 Resetting the Root password:generic instructions

The preceding sections provide password-resetting instructions specifically for Windows and Unix and unix-like systems. Alternatively, on any platform, you can reset the password using the MySQL client (but this approach is less SECU RE):

2. Stop the MySQL server if necessary, then restart it with the --skip-grant-tables option. This enables anyone to connect without a password and with all privileges, and disables account-management statements such As SET PASSWORD . Because This was insecure, you might want to use the conjunction with to --skip-grant-tables --skip-networking prevent remote clients from connecting.

4. Connect to the MySQL server using the  MySQL  client; No password is necessary because the server was started With --skip-grant-tables :

   Pre class= "programlisting" >shell> MySQL

6. in The mysql  client, tell the server to reload the Grant tables So, account-management statements work:

   mysql>   FLUSH privileges;   

   Then change The   account password. Replace the password with the password, that's want to use. The password for a root  account with a different host name part, modify T He instructions to use that host name.

   mysql>   SET PASSWORD for ' root ' @ ' localhost ' = PASSWORD (' Mynewpass ');   

You should now is able to connect to the MySQL server as root using the new password. Stop the server and restart it normally (without the --skip-grant-tables and --skip-networking options).

Http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Introduces a very useful mysql boot parameter----skip-grant-tables。 As the name implies, when you start MySQL, do not start the grant-tables, authorization table. What's the use of it? Of course it is useful to forget the administrator password.
Operation Method:
1. Kill the original MySQL:
Rcmysqld stop
Or:
Service Mysqld Stop
Or:
Kill-term mysqld
2. Start MySQL with command line parameters:
/usr/bin/mysqld_safe--skip-grant-tables &
3. Modify the Administrator password:
Use MySQL;
Update user set Password=password (' Yournewpasswordhere ') where user= ' root ';
Flush privileges;
Exit
4. Kill MySQL and restart MySQL

You can also configure this parameter in the configuration file/etc/my.cnf

Forget MySQL (and PHP collocation of the best combination) root password is in MySQL (and PHP collocation of the best combination) very common problem, but a lot of friends do not reset the root password, that complain Ah, I have deep feelings, close-up this article with you to communicate:

1, edit MySQL (and PHP collocation of the best combination) configuration file:

Windows environment:%mysql (and PHP collocation of the best combination) _installdir%\my.ini//general in MySQL (and the best combination of PHP) installation directory has my.ini that MySQL (and PHP collocation of the best combination) configuration file.

Linux Environment:/ETC/MY.CNF

Add the following line to the [MySQL (and PHP collocation best combination) d] Configuration segment:

Skip-grant-tables

Save to exit Edit.

2, then restart MySQL (and PHP collocation of the best combination) service

In the Windows environment:

net stop MySQL (and the best combination of PHP collocation)

net start MySQL (and the best combination of PHP collocation)

In a Linux environment:

/etc/init.d/mysql (the best mix with PHP) d restart

3. Set a new root password

And then execute it at the command line:

MySQL (best combination with PHP)-uroot-p MySQL (the best combination of PHP collocation)

Directly enter the database without a password.

Now we execute the following statement to update the root password to 7758521:

Update user set Password=password ("123456") where user= ' root ';

Quit MySQL (and the best combination of PHP collocation).

4. Restore the configuration file and restart the service

Then modify the MySQL (and PHP collocation best combination) configuration file to delete the line you just added.

Re-start the MySQL (and PHP collocation of the best combination) service, password modification completed.

The modification is complete.

With the new password 123456 try it, and can log in MySQL (and the best combination of PHP) feel is different?

Summarize:

1. Kill the MYSQLD process first:

Tip: Don't be afraid to let you forget your MySQL password

2. Use the Skip-grant-tables option to start MySQL:

Tip: Don't be afraid to let you forget your MySQL password

3. Login Change Password:

Tip: Don't be afraid to let you forget your MySQL password

4. Turn off MySQL

Tip: Don't be afraid to let you forget your MySQL password

5. Start MySQL

Tip: Don't be afraid to let you forget your MySQL password

The above is the article to introduce the use of skip-grant-tables to solve the MYSQL root password lost, I hope you like.

Http://www.bkjia.com/Mysql/1058276.html

Use Skip-grant-tables to resolve MYSQL root password loss (go)