MySQL forgot root password solution under Linux

If you use the MySQL database to forget the root account password, you can adjust the configuration file, skip the password way to log the database,

In the database to modify the account password, the general default account is root

1. Edit MySQL configuration file my.cnf

Note: subject to the actual MY.CNF profile path

Vim/etc/my.cnf

[Mysqld]
Spik-grant-tables #增加

2. Restart MySQL Service

Note: subject to the actual MySQL startup script path

/etc/init.d/mysqld restart

If an error is noted, observe the contents of the MY.CNF configuration file.

3. Login Database

/usr/bin/mysql Enter the following command:

Note: subject to actual MySQL execution file path

mysql> use MySQL;
mysql> UPDATE user SET Password = Password (' new password ') WHERE user = ' root ';
mysql> flush Privileges;
Mysql> quit

4. Delete or comment The spik-grant-tables added in the first step

5. Restart MySQL Service

/etc/init.d/mysqld restart

6. Test with new password

Special cases:

I this is the use of the source installation of MySQL, the default profile my.cnf (/USR/LOCAL/MYSQL/MY.CNF) is not configured, are in the boot file mysqld(/etc/rc.d/init.d/mysqld) is configured in the

Datadir=/data/mysql

Basedir=/usr/local/mysql

If you follow the above method, add "spik-grant-tables" to the MY.CNF, then restart the MySQL times error

The right solution:

Depending on the datadir and basedir parameters in the startup file mysqld , the options in the MY.CNF are enabled, and then add "spik-grant-tables", MySQL restart will not error, but also normal to follow the following steps

MY.CNF prior to modification

# for advice The change settings
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[Mysqld]

# Remove Leading # and set to the amount of RAM for the most important data
# Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Spik-grant-tables
# Remove Leading # to turn on a very important data integrity option:logging
# Changes to the binary log between backups.
# Log_bin

# These is commonly set, remove the # and set as required.
# Basedir =.....
# DataDir =.....
# port =.....
# server_id = ...
# socket = .....

# Remove Leading # To set options mainly useful for reporting servers.
# The server defaults is faster for transactions and fast selects.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

Sql_mode=no_engine_substitution,strict_trans_tables

Modified MY.CNF

# for advice The change settings
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[Mysqld]

# Remove Leading # and set to the amount of RAM for the most important data
# Cache in MySQL. Start at 70% of all RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
# Spik-grant-tables
# Remove Leading # to turn on a very important data integrity option:logging
# Changes to the binary log between backups.
# Log_bin

# These is commonly set, remove the # and set as required.
Basedir =/usr/local/mysql
DataDir =/data/mysql
Port = 3306
# server_id = ...
# socket = .....
Skip-grant-tables

# Remove Leading # To set options mainly useful for reporting servers.
# The server defaults is faster for transactions and fast selects.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

Sql_mode=no_engine_substitution,strict_trans_tables

Then restart MySQL, take the following steps, reset the password, then the my.cnf in the changes of the four items all commented out, restart MySQL again.

MySQL forgot root password solution under Linux