Mysql Commands

Configuration file

/etc/my.cnf     /root/.my.cnf


>mysqladmin varialbles

>mysql

>mysql -h <hostname/IP> -u user -p<password> dbname

logs
datadir     cPannel-/var/lib/mysql       server_name.err


file formats - storage engine format


Create Database

>mysqladmin create dbname;
>mysql> use dbname;
>SELECT User FROM mysql.user;
>mysql> show databases;
>mysql> show tables;
>mysql>CREATE USER 'monty'@'localhost' IDENTIFIED BY 'some_pass';
>select user();




How to set Privilages

>mysql>GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost';
>mysql> SHOW GRANTS FOR 'admin'@'localhost';
>REVOKE privileges ON database.* FROM 'username'@'host'
>FLUSH PRIVILEGES;
>select * from mysql.user where User='username';



DB Backup and restore

> mysqldump dbname > dumpfilename
>mysqldump dbname table > table.sql

Restore
>mysql dbname < dump
or
mysql > use dbname
mysql > source /full/path/of/db_dump.sql
>mysql dbname < table.sql



Deletion of db

>mysqladmin drop dbname;
>mysql > drop table table_prefix;
>mysql > truncate tablename;[To remove the entries in table]



MySQL Storage Engines

MyISAM and Innodb.
>mysql> show engines;



InnoDB Limitations

No full text indexing
Cannot be compressed for fast, read-only


MyISAM limitations

No Foriegn keys and cascading deletes and updates
No rollback abilities
No transactional integrity (ACID compliance)
Row limit of 4,284,867,296 rows
Maximum of 64 indexes per row