How to get the database size of the MySQL DB ?

mysql -e "SELECT table_schema AS 'Database Name', SUM((data_length+index_length)/1024/1024) AS 'Database Size in MB' 
FROM information_schema.TABLES where table_schema='dbname';"

EXAMPLE
mysql -e "SELECT table_schema AS 'Database Name', SUM((data_length+index_length)/1024/1024) AS 'Database Size in MB' 
FROM information_schema.TABLES where table_schema='employee';"


ANSWER

+---------------+---------------------+

| Database Name | Database Size in MB |

+---------------+---------------------+

| employee      |       7224.01562500 |

+---------------+---------------------+

How to give permission to any host for root user of MySQL ?

Setting permission to any host on Windows

Connect to server and run following commands : 
C:\Program Files\MySQL\MySQL Server 5.0\bin>mysql -u root -p
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 5.0.51a-community-nt MySQL Community Edition (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use mysql
Database changed

mysql> GRANT ALL ON *.* to root@'10.239.199.57' IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

How to import a large SQL dump file to MySQL ?

1. Open command prompt.

2. [Windows only] Set char set to unicode

chcp 65001

3. Connect to MySQL instance (remote or localhost MySQL instance)

mysql -h 192.165.1.1 --port=3306 -u root -p

mysql -u root -p

4. Set Max allowed packet size to a large number

set global max_allowed_packet=1000000000;

5. Set network buffer length to a large number

set global net_buffer_length=1000000; 

6. Disable checks for foreign key to avoid errors, delays and unusual behaviour 

SET foreign_key_checks = 0;
SET UNIQUE_CHECKS = 0;
SET AUTOCOMMIT = 0;

7. Import your SQL dump file
source C:\shaan\dbdump020320016.sql

8. Enable foreign key checks when procedure is complete

SET foreign_key_checks = 1;
SET UNIQUE_CHECKS = 1;
SET AUTOCOMMIT = 1;

How to write comments in MySQL ?

Use any of the following comment format :

Example

SELECT ..... ;   # This comment continues to the end of line

SELECT ..... ;   -- This comment continues to the end of line

SELECT code_user  /* ! USER temporary code */  FROM user

What is SERIAL data type ?

SERIAL is an alias for below declaration :

BIGINT  UNSIGNED  NOT  NULL  AUTO_INCREMENT  UNIQUE

How to connect remotely to MySQL with the standard "root" user ?

$ mysql -u root -p

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'xxx.xxx.xxx.xxx' IDENTIFIED BY 'xxxxxxx' WITH GRANT OPTION;

Remember you need to create an account with your IP (xxx.xxx.xxx.xxx) as host,

this tells MySQL that this user with this IP has permission to remotely access the Database.

How to create, display, delete and change table and columns ?

Create table

CREATE TABLE post

( id INTEGER PRIMARY KEY,

  subject VARCHAR(80) NOT NULL,

  description VARCHAR(256) NOT NULL,

  create_date DATE

);

Create new table by selecting rows from another table

Create a backup copy of this table

CREATE TABLE postBackup

  SELECT * FROM post;

Display table

Display list of all tables in a database

SHOW TABLES;

Display schema of a tables

DESCRIBE TABLES;

DESC TABLES;

Display the "CREATE TABLE" statement of a table

SHOW CREATE TABLE post;

Display list of columns of a table

SHOW COLUMNS FROM post;

Change table

Rename a table

ALTER TABLE post

  RENAME TO posts;

Add a new column to a table

ALTER TABLE post

  ADD COLUMN author VARCHAR(40);

Delete an existing column from a table

ALTER TABLE post

  DROP COLUMN create_date;

Rename column from a table

ALTER TABLE post

  CHANGE COLUMN subject title VARCHAR(60);

Delete table 

Drop a table

DROP TABLE post;

What is the default port and password for MySQL ?

Default port of MySQL : 3306

Default username / password :  root / <No password>  or  admin

What are MySQL advantages over Oracle ?

MySQL advantages over Oracle

 1. Open source

 2. No cost for development purpose

 3. Good for small application

 4. Easy to learn and to become master

How to reset password of root user ?

mysqladmin -u root password 'new-password'

How to use variables in the MySQL query statements ?

Use @ symbol to denote the variables.

CONCAT function can be used to concatenate variables with strings.

Example

SET @district = 'DELHI';

SET @query_condition = CONCAT(' WHERE district=', @district);

SELECT id FROM user @query_condition;