MySQL Cheatsheet

INTRODUCTION

In this post I will show you some of the most useful MySQL commands.

For how to install MySQL please, check my previous tutorial  https://insidegnulinux.blogspot.in/2016/10/mysql-configuration.html 

Cheatsheet

1)  MySQL server default

     MySQL Default Databases:

     i)   mysql

     ii)  information_schema 

     iii) test

     MySQL Default Accounts:

     i) root

     ii) anonymous

2) Administering MySQL Database commands:

    A) Login into Mysql:

       i) Login into MySQL with user having no password:

       ~]# mysql -u username

       ii) Login into mysql with password

       ~]# mysql -u username -ppassword

    B) Working with database after login into MySQL.

        i) To check  the all databases present.

  

        mysql> show databases;

        ii) To select database on which you have to work:

        mysql> use databasename

        iii) creating new database:

  

        mysql> create database linux;

        

        iv) Removing database.

        mysql> drop database databasename:

     

        v) After selecting database if you have to see particular table structure:

   

        mysql> desc tablename;

    C) Controlling user access on MySQL.

          

        i) In mysql database user table is the  table which controls the login  of the user. To check current available users.

        mysql> use myql;

        mysql> select host,user from user;

       ii) To enabled remote login of user into mysql.

       mysql> update user set host='%' where user='username';

       Note: Specify localhost for disabling remote login at the place of '%' sign.

       iii) To check currently login user;

       mysql> select user();

   

       iv) To set encrypted password for any user:

       mysql> set password for 'mayur'@'%' = password ('mypass');

       mysql> select user,host,password from user;

       We can verify the hash algorithm  use in password function as follow:

       mysql> select password('mypass');

       It will show you the same encrypted password which you can see in  user table.

       v) For security purpose if you want to disabled remote root login:

    

       mysql> update user set host='localhost' where user='root';

       vi) Deleting user from MySQL database:

  

       mysql> delete from mysql.user where user='username';

       mysql> flush privileges;

D) Granting and revoking user permissions.

     i) Granting permission on any database;

     mysql> grant all on db1.* to 'username'@'localhost';

     For giving read only access.

     mysql> grant select on db1.* to 'username'@'localhost';

     To give full access on all databases with grant option.

     mysql> grant all on *.* to 'username'@'localhost' with grant option;

     Giving limited access on queries for user due to performance reasons.

     mysql> alter user 'username'@'localhost' with max_queries_per_hour 50;

     ii) Revoking permission on any database;

   

     mysql> revoke all privileges with  grant option from user 'username'@'localhost';

     To remove some  specific options from any database.

     mysql> revoke update,insert on db1.* from 'username'@'localhost';

E) Backup and restore mysql database.

     i) Backup MySQL Database.

     

     ~]# mysqldump -u username -ppassword databasename > dumpfile.sql

     ~]# mysqldump -u root -pmypass db1 > db1.sql

     ii) Backup MySQL Database with structure only.

     ~]# mysqldump -u username -ppassword -no-data databasename > dumpfile.sql

     iii) Backup MySQL database with Data without structure.

     ~]# mysqldump -u username -ppassword -no-create-info databasename > dumpfile.sql

     iv) Restoring MySQL Database.

     ~]# mysql -u username -ppassword < dumpfile.sql 

       

3) MySQL Performance commands:

    i) If your database is huge and continuously updating there might be situation where your query will return bad data.

       To avoid this use analyze command.

       

       mysql> analyze table table_name;

   ii) To Avoid defragmentation problem in  MySQL;

      

      mysql> optimize table table_name;

  iii) Detecting problems in database (Integraty of data)

      mysql>  check table table_name;

  iv) Repairing error in table found detect using check command.

     mysql> repair table table_name;

4) MySQL Table commands:

    i)  List all tables in current database;

        mysql> show tables;

    ii) Create New table

  

        mysql> create table table_name( column1 int(10) not null primary key auto_increment,

                     column2 varchar(50) not null,

                     column3 varchar(50) null,

                     .........);

   iii) Alter Table

        mysql> alter table table_name add[column];

        mysql> alter table table_name drop[column];

        iv) Adding primary key in existing table:

       

        mysql> alter table table_name add primary key (column,..)

       v) Removing primary key from existing table:

       mysql> alter table table_name drop primary key;

       vi) Deleting table from database:

       mysql> drop table table_name;

       vii) Inserting data into table.

       mysql> insert into table ( column1,column2,..)

                    values (value1, value2...);

  

       viii) Updating data in table.

       mysql> update table_name set column1=value1 where condition;

       ix) Delete from rows from table;

       mysql> delete from table_name where condition;

       x) Searching data in table.

       mysql> select * from table_name where condition;