Teachnique
      CourseRoadmaps
      Login

      HomeIntroductionFeaturesVersionsVariablesInstallationAdministrationPHP SyntaxNode.js SyntaxJava SyntaxPython SyntaxConnectionWorkbench

      Create DatabaseDrop DatabaseSelect DatabaseShow DatabaseCopy DatabaseDatabase ExportDatabase ImportDatabase Info

      Create UsersDrop UsersShow UsersChange PasswordGrant PrivilegesShow PrivilegesRevoke PrivilegesLock User AccountUnlock User Account

      Create TablesShow TablesAlter TablesRename TablesClone TablesTruncate TablesTemporary TablesRepair TablesDescribe TablesAdd/Delete ColumnsShow ColumnsRename ColumnsTable LockingDrop TablesDerived Tables

      QueriesConstraintsInsert QuerySelect QueryUpdate QueryDelete QueryReplace QueryInsert IgnoreInsert on Duplicate Key UpdateInsert Into Select

      Create ViewsUpdate ViewsDrop ViewsRename Views

      IndexesCreate IndexDrop IndexShow IndexesUnique IndexClustered IndexNon-Clustered Index

      Where ClauseLimit ClauseDistinct ClauseOrder By ClauseGroup By ClauseHaving ClauseAND OperatorOR OperatorLike OperatorIN OperatorANY OperatorEXISTS OperatorNOT OperatorNOT EQUAL OperatorIS NULL OperatorIS NOT NULL OperatorBetween OperatorUNION OperatorUNION vs UNION ALLMINUS OperatorINTERSECT OperatorINTERVAL Operator

      Using JoinsInner JoinLeft JoinRight JoinCross JoinFull JoinSelf JoinDelete JoinUpdate JoinUnion vs Join

      Unique KeyPrimary KeyForeign KeyComposite KeyAlternate Key

      TriggersCreate TriggerShow TriggerDrop TriggerBefore Insert TriggerAfter Insert TriggerBefore Update TriggerAfter Update TriggerBefore Delete TriggerAfter Delete Trigger

      Data TypesVARCHARBOOLEANENUMDECIMALINTFLOATBITTINYINTBLOBSET

      Regular ExpressionsRLIKE OperatorNOT LIKE OperatorNOT REGEXP Operatorregexp_instr() Functionregexp_like() Functionregexp_replace() Functionregexp_substr() Function

      Fulltext SearchNatural Language Fulltext SearchBoolean Fulltext SearchQuery Expansion Fulltext Searchngram Fulltext Parser

      Date and Time FunctionsArithmetic OperatorsNumeric FunctionsString FunctionsAggregate Functions

      NULL ValuesTransactionsUsing SequencesHandling DuplicatesSQL InjectionSubQueryCommentsCheck ConstraintsStorage EnginesExport Table into CSV FileImport CSV File into DatabaseUUIDCommon Table ExpressionsOn Delete CascadeUpsertHorizontal PartitioningVertical PartitioningCursorStored FunctionsSignalResignalCharacter SetCollationWildcardsAliasROLLUPToday DateLiteralsStored ProcedureExplainJSONStandard DeviationFind Duplicate RecordsDelete Duplicate RecordsSelect Random RecordsShow ProcesslistChange Column TypeReset Auto-IncrementCoalesce() Function

      Useful FunctionsStatements ReferenceQuick GuideUseful ResourcesDiscussion

      Feedback

      Submit request if you have any questions.

      Course
      Change Password

      MySQL Tutorial

      This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

      Change Password

      MySQL provides an account to each user which is authenticated with a username and a password. The default account in MySQL is a root with no password (One can however set a password to the root using a statement). Similarly, other user-defined accounts can have passwords set explicitly using an SQL statement or can have it system generated by MySQL.

      MySQL Change User Password

      Just like any other authenticated accounts, MySQL has a provision to change the user password. But one must make sure that there is currently no application being used by the user. If the password is reset without disconnecting the application, the application cannot connect to the server through this user again.
      We can change the password for a MySQL user account using the following three SQL statements −
      • UPDATE statement
      • SET PASSWORD statement
      • ALTER USER statement

      The UPDATE Statement

      The most basic way to change a user's password in MySQL is by using the UPDATE statement. This statement is used to update account details, including the account password, from the 'root' account. But, once the modifications are done using this statement, you must use the FLUSH PRIVILEGES statement to reload privileges from the grant table of the MySQL database.

      Syntax

      Following is the syntax to change password using the UPDATE statement 
      UPDATE mysql.user 
      SET authentication_string = PASSWORD(password_string)
      WHERE User = user_name AND 
            Host = host_name
      FLUSH PRIVILEGES;

      Example

      Following example demonstrates how to change the password of a user account using the UPDATE statement. Firstly, we are creating a user account "sample" with a password '123456' 
      CREATE USER 'sample'@'localhost' IDENTIFIED BY '123456';
      Following is the output obtained 
      Query OK, 0 rows affected (0.02 sec)
      Now, you can verify the list of users using the following query 
      SELECT User FROM mysql.user;
      The table will be displayed as shown below
      User
      mysql.infoschema
      mysql.session
      mysql.sys
      root
      sample
      If you have the MySQL version 5.7.6 and later, you can directly modify the mysql.user table with the following query 
      UPDATE user
      SET authentication_string = PASSWORD('xxxxxx')
      WHERE User = 'sample' AND Host = 'localhost';
      After executing the above code, we get the following output 
      Query OK, 1 row affected (0.02 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
      After making changes to user accounts, you need to use the FLUSH PRIVILEGES statement to apply these changes immediately 
      FLUSH PRIVILEGES;
      The output obtained is as shown below 
      Query OK, 0 rows affected (0.01 sec)

      The SET PASSWORD statement

      The SET PASSWORD statement is used to set a password for a MySQL account. It contains a "password-verification" clause which lets the system know that the current user password needs to be replaced by another.

      Syntax

      Following is the syntax for the SET PASSWORD statement 
      SET PASSWORD FOR username@localhost = password_string;
      You can also change the password using SET PASSWORD without using the FOR clause. To use this syntax however, you must already be logged in on the user account you wish to change the password of 
      SET PASSWORD = password_string;

      Example

      Now, using the SET PASSWORD statement, we are changing the password to 'hello' 
      SET PASSWORD = 'hello';

      Output

      Following is the output of the above code 
      Query OK, 0 rows affected (0.01 sec)

      The ALTER USER Statement

      To alter anything regarding a user account in MySQL, including changing passwords, ALTER USER statement is more preferable than SET PASSWORD statement. This statement is not used alone, instead is followed by the IDENTIFIED BY clause to authenticate the new password.
      Note that the user must be connected to the MySQL server for this statement to work.

      Syntax

      Following is the syntax to change the password using the ALTER USER statement 
      ALTER USER username IDENTIFIED BY 'password';

      Example

      Here, we are changing the password of the sample@localhost account to '000000' using the ALTER USER query given below 
      ALTER USER sample@localhost IDENTIFIED BY '000000';

      Output

      Output of the above code is shown below 
      Query OK, 0 rows affected (0.01 sec)
      The password is now changed. To verify, log in to the sample account again using the new password 
      C:\Windows\System32> mysql -u sample -p
      Enter password: ******
      
      mysql>

      Changing User password Using a Client Program

      Besides using MySQL queries to change the user password in MySQL, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

      Syntax

      Following are the syntaxes
      PHPNodeJSJavaPython
      To change the user's password MySQL database, we need to execute the ALTER USER statement using this function as 
      $sql = "ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password'";
      $mysqli->query($sql);

      Example

      Following are the client programs to change the user password in MySQL
      PHPNodeJSJavaPython
      $dbhost = 'localhost';
      $dbuser = 'root';
      $dbpass = 'password';
      $mysqli = new mysqli($dbhost, $dbuser, $dbpass);
         if($mysqli->connect_errno ) {
           printf("Connect failed: %s", $mysqli->connect_error);
           exit();
      }
      //printf('Connected successfully.');
      $sql = "ALTER USER 'root'@'localhost' IDENTIFIED BY 'password1'";
      if($mysqli->query($sql)){
         printf("User password has been changed successfully...!");
      }
      if($mysqli->error){
         printf("Failed..!" , $mysqli->error);
      }
      $mysqli->close();

      Output

      The output obtained is as follows 
      Your password has been changed successfully...!