How to change a password in MySQL

Overview

Often, people are wondering how to change a password in MySQL.

How to change the root password

Root Password

• After first installation

 After the first installation and the first startup the root password should be changed using

mysqladmin -u root password 'new_root_password'

if the password already exists

mysqladmin -u root -p'oldpassword' password newpassword

Once the root password changed you should change it for all root accounts using the mysql_secure_installation

• Change password using mysqladmin

mysqladmin -u user -p'oldpassword' password 'newpassword'

• Here how to change a password in MySQL

Make sure you are connected to a database
use mysql;

• Change the password for all user where user = 'username'

update user set password=PASSWORD('newpassword') where user='username';
flush privileges;

• Change the password for a specific user

update user set password=PASSWORD('newpassword') where user='username' and host='hostname';
EX:  update user set password=PASSWORD('changeme') where user='user1' and host='%';
update user set password=PASSWORD('changeme') where user='user1' and host='192.168.0.10';

or

set password for useryouwanttochange = PASSWORD ( 'new_password' );

How to upgrade the engine of your databases in MySQL

Overview

Upgrading your MySQL on linux is fairly easy as long as you follow the proper steps
This exemple will take the change to InnoDB as exemple

Backup your database

The first step is to backup Your environment including the my.cnf usually located in /etc

cp /etc/my.cnf /etc/my.cnf.yyymmdd
service mysql stop
cd /backup
cp -Rp /var/lib/mysql .
service mysql start

To backup your databases you can also use my ksh script from one of my posts from my blog

Upgrade each database

the second step is to run the following command for each instance except mysql and the other system databases.

echo 'SHOW TABLES;' \
| mysql -uroot --password=your_password -D your_database \
| awk '!/^Tables_in_/ {print "ALTER TABLE `"$0"` ENGINE = InnoDB;"}' \
| column -t \
| mysql -uroot --password=your_password -D your_database 

Check all the tables in the database

The last step is to validate that all your tables for each database are now InnoDB.
I prefer the second option that is much easier to run

mysql -uroot --password=your_password -D your_database

show table engine;

or

You can use mysqlcheck to check all tables on the database.

mysqlcheck -c your_database  -uroot  -pyour_password

Set InnoDB as the default engine

If you want to set innodb as the default engine you can do it by adding the following parameter in you my.cnf in the [mysqld] section

default-storage-engine=innodb