How To Change MySql Root Password

In this example, I am going to change mysql root account's password from 1234 to 123456 using SQL. The queries are so self-explanatory. So, there is no need to describe them.

D:\dev\xampp\mysql\bin>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.51a Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql;
Database changed
mysql> update user set password=PASSWORD("123456") where User="root";
Query OK, 2 rows affected (0.03 sec)
Rows matched: 2 Changed: 2 Warnings: 0

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

D:\dev\xampp\mysql\bin>mysql -u root -p
Enter password: ******
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.51a Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Some Mysql-Specific Commands

Following is a list of some of mysql specific commands that I frequently use. You should be connected to mysql server through the command-line to execute the commands.

>show databases;
Display the list of databases hosted by the mysql server

>use db;
Selects the database to be working on.

>desc table;
Describes the metadata of the table;

>show tables;
Display tables of the selected database

>source test.sql;
execute an SQL script file