how to change MySQL user password with console(MySQL console)

login to the MySQL  server with the console provided.

mysql  -uroot -p

then  change the database as mysql

use mysql;

then use the following command to change the root password


//the new password of the user whose password should be changed
update user set password=PASSWORD("abc123") where User='root';


Hope this will helpful for you!!!

Thanks and Regards
Chathuranga Tenakoon
chathuranga.t@gmail.com

Encrypt MySQL data using AES techniques

the data will be encrypted and stored in the database for providing an additional level of security for the data. In MySQL AES(Advanced Encryption Standard) algorithm can be used to encrypt and decrypt the data being inserted and retrieved.

•  AES_ENCRYPT(data,encryption_key) - the method that can be used to encrypt the data being inserted. 

        e.g:- AES_ENCRYPT('chathuranga','abc123');

• AES_DECRYPT(encrypted_data,encryptyed_key) 

suppose you need to encrypt and store the username and email of the every user in the database. these encrypted values are stored in the database as binary strings. therefore you must give suitable data types for the columns in the table to accept and hold binary string inputs. therefore we must use varbinary instead of varchar. create the following table in the database.

create table user_table(

user_id int auto_increment primary key,

username varbinary(100),

email varbinary(100));

then insert following data into the table created. you can see that  am using AES_ENCRYPT function with cha123 as the key.

insert into user_table values('',AES_ENCRYPT('chathuranga','cha123'),AES_ENCRYPT('chathuranga.t@gmail.com','cha123'));



insert into user_table values('',AES_ENCRYPT('darshana','cha123'),AES_ENCRYPT('chathurangat@lankacom.net','cha123'));

you can see that the data has been stored in a encrypted format. see below screen dump.

 the result of the select query is displayed in the encrypted format. if you need to see the original values (decrypted values) you have to use the AES_DECRYPT function to decrypt the values stored.  the command is as follows.

select AES_DECRYPT(username,'cha123') As username_origianal , AES_DECRYPT(email,'cha123') As email_origianal from user_table;

refer the below screen shot.

 finally try out  the following select statement to understand how to use where clause with an encrypted columns.

select AES_DECRYPT(username,'cha123') As username_origianal , 

AES_DECRYPT(email,'cha123') As email_origianal 

from user_table 

where username = AES_ENCRYPT('chathuranga','cha123');

Hope this will helpful for you!!!

Thanks and Regards,
Chathuranga Tennakoon
chathuranga.t@gmail.com

Indexing in Database

indexes are used to speed up the data retrieving queries known as select queries. Indexes are created on a per column basis. If you have a table with the columns: name, age, birthday and employeeID and want to create an index to speed up how long it takes to find employeeID values in your queries, then you would need to create an index for employeeID. When you create this index, MySQL will build a lookup index where employeeID specific queries can be run quickly. However, the where clause based on the  name, age and birthday queries would not be any faster.

Indexes are something extra that you can enable on your MySQL tables to increase performance,but they do have some downsides. When you create a new index MySQL builds a separate block of information that needs to be updated every time there are changes made to the table. This means that if you are constantly updating, inserting and removing entries in your table this could have a negative impact on performance.

Advantages 

• Indexes speed up the data retrieving based on where and order by clauses

Disadavantages

•  Indexes slow down inserts , update and deleted, so you want to use them carefully on columns that are FREQUENTLY changed.
•  Creating an index requires an additional storage space . this space is occupied by the look-up index and it keep a track of modification(insert/delete/update) records those were done to the related column/field. therefore too many indexes can increase the database size and the file size unnecessarily.

create mysql index on new table

 If you are creating a new MySQL table you can specify a column to index by using the INDEX term as we have below. We have created two fields: name and employeeID (index).

CREATE TABLE employee (
 name VARCHAR(50), 
 employeeID INT, INDEX (employeeID)
)

create mysql index on existing table

CREATE INDEX id_index ON employee(employeeID)

Hope this will helpful for you!

regards

Chathuranga Tennakoon

chathuranga.t@mail.com