MySQL

MySQL IntroductionComment MySQLInstall MySQL container with Docker-ComposeMySQL AdminMySQL ALTER TABLEMySQL ArithmeticMySQL BackticksMySQL Backup using mysqldumpMySQL Change PasswordMySQL Character Sets and CollationsMySQL clientMySQL ClusteringMySQL Configuration and tuningMySQL Connecting with UTF-8 Using Various Programming language.MySQL Converting from MyISAM to InnoDBMySQL Create New UserMySQL Creating databasesMySQL Customize PS1MySQL Data TypesMySQL Date and Time OperationsMySQL Dealing with sparse or missing dataMySQL DELETEMySQL Drop TableMySQL Dynamic Un-Pivot Table using Prepared StatementMySQL ENUMMySQL Error 1055: ONLY FULL GROUP BY: something is not in GROUP by clause ...MySQL Error codesMySQL EventsMySQL Extract values from JSON typeMySQL Full-Text searchMySQL Group byMySQL Handling Time ZonesMySQL Indexes and KeysMySQL INSERTMySQL JoinsMySQL JOINS: Join 3 table with the same name of id.MySQL JSONMySQL Limit and OffsetMySQL LOAD DATA INFILEMySQL LOCK TABLEMySQL Log filesMySQL Many-to-many Mapping tableMySQL MyISAM EngineMySQL mysqlimportMySQL NULLMySQL One to ManyMySQL ORDER byMySQL PartitioningMySQL Performance TipsMySQL Performance TuningMySQL Pivot queriesMySQL PREPARE StatementsMySQL Recover from lost root passwordMySQL Regular ExpressionsMySQL ReplicationMySQL Reserved WordsMySQL Security via GRANTsMySQL SELECTMySQL Server InformationMySQL SSL Connection SetupMySQL Stored routines (procedures and functions)MySQL String operationsMySQL Table CreationMySQL Temporary TablesMySQL Time with subsecond precisionMySQL TransactionMySQL TRIGGERSMySQL UNIONMySQL UnionsMySQL UPDATEMySQL Using VariablesMySQL VIEWRecover and reset the default root password for MySQL 5.7+



MySQL Change Password

From WikiOD

Change MySQL root password in Linux[edit | edit source]

To change MySQL's root user password:

Step 1: Stop the MySQL server.

  • in Ubuntu or Debian:

sudo /etc/init.d/mysql stop

  • in CentOS, Fedora or Red Hat Enterprise Linux:

sudo /etc/init.d/mysqld stop

Step 2: Start the MySQL server without the privilege system.

sudo mysqld_safe --skip-grant-tables &

or, if mysqld_safe is unavailable,

sudo mysqld --skip-grant-tables &

Step 3: Connect to the MySQL server.

mysql -u root

Step 4: Set a new password for root user.

5.7

FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
FLUSH PRIVILEGES;
exit;

5.7

FLUSH PRIVILEGES;
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new_password');
FLUSH PRIVILEGES;
exit;

Note: The ALTER USER syntax was introduced in MySQL 5.7.6.

Step 5: Restart the MySQL server.

  • in Ubuntu or Debian:

sudo /etc/init.d/mysql stop

sudo /etc/init.d/mysql start

  • in CentOS, Fedora or Red Hat Enterprise Linux:

sudo /etc/init.d/mysqld stop

sudo /etc/init.d/mysqld start

Change MySQL root password in Windows[edit | edit source]

When we want to change root password in windows, We need to follow following steps :

Step 1 : Start your Command Prompt by using any of below method :

Perss Crtl+R or Goto Start Menu > Run and then type cmd and hit enter

Step 2 : Change your directory to where MYSQL is installed, In my case it's

C:\> cd C:\mysql\bin

Step 3 : Now we need to start mysql command prompt

C:\mysql\bin> mysql -u root mysql

Step 4 : Fire query to change root password

mysql> SET PASSWORD FOR root@localhost=PASSWORD('my_new_password');

Process[edit | edit source]

  1. Stop the MySQL (mysqld) server/daemon process.
  2. Start the MySQL server process the --skip-grant-tables option so that it will not prompt for a password: mysqld_safe --skip-grant-tables &
  3. Connect to the MySQL server as the root user: mysql -u root
  4. Change password:
  • (5.7.6 and newer): ALTER USER 'root'@'localhost' IDENTIFIED BY 'new-password';
  • (5.7.5 and older, or MariaDB): SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new-password); flush privileges; quit;
  • Restart the MySQL server.

Note: this will work only if you are physically on the same server.

Online Doc: http://dev.mysql.com/doc/refman/5.7/en/resetting-permissions.html

Credit:Stack_Overflow_Documentation