MySQL Reference

Useful mysqladmin commands

1. Get MySQL version

[root@host]# mysqladmin --version

2. Post MySQL installation, the root user password is blank. To set the password, use the below command:

[root@host]# mysqladmin -u root password "new_password";

3. Create database

[root@host]# mysqladmin -u root -p create TUTORIALS
Enter password:******

4. Drop database

[root@host]# mysqladmin -u root -p drop TUTORIALS
Enter password:******

Adding new user

root@host# mysql -u root -p
Enter password:*******
mysql> use mysql;
Database changed

mysql> INSERT INTO user 
          (host, user, password, 
           select_priv, insert_priv, update_priv) 
           VALUES ('localhost', 'guest', 
           PASSWORD('guest123'), 'Y', 'Y', 'Y');
Query OK, 1 row affected (0.20 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT host, user, password FROM user WHERE user = 'guest';
+-----------+---------+------------------+
| host      | user    | password         |
+-----------+---------+------------------+
| localhost | guest | 6f8c114b58f2ce9e |
+-----------+---------+------------------+
1 row in set (0.00 sec)

or

root@host# mysql -u root -p password;
Enter password:*******
mysql> use mysql;
Database changed

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP
    -> ON TUTORIALS.*
    -> TO 'zara'@'localhost'
    -> IDENTIFIED BY 'zara123';

Remember to FLUSH PRIVILEGES after doing the above!!

Administrative commands

# USE <database>;

# SHOW DATABASES;

# SHOW TABLES;

# SHOW INDEX FROM <tablename>;

Storage Engines

mysql> SHOW ENGINES\G
*************************** 1. row ***************************
 Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
 Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
 Engine: InnoDB
Support: YES
Comment: Supports transactions, row-level locking, and foreign keys
*************************** 4. row ***************************
 Engine: BerkeleyDB
Support: NO
Comment: Supports transactions and page-level locking
*************************** 5. row ***************************
 Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
...

CREATE TABLE t (i INT) ENGINE = INNODB;
CREATE TABLE t (i INT) TYPE = MEMORY;

The older term TYPE is supported as a synonym for ENGINE for backward compatibility, but ENGINE is the preferred term and TYPE is deprecated.

SET storage_engine=MYISAM;
SET table_type=BDB;

Miscellaneous

1. Get table status such as type of storage engine etc

# SHOW TABLE STATUS;

2. Get server meta-data

Command	Description
SELECT VERSION( )	Server version string
SELECT DATABASE( )	Current database name (empty if none)
SELECT USER( )	Current username
SHOW STATUS	Server status indicators
SHOW VARIABLES	Server configuration variables