Getting started with MySQL

This will cover the basic usage of using MySQL. If you should be interested in setting up MySQL, please refer to

MySQL Documentation

SSH Client:

We will need a SSH Client, in order to connect to the server running MySQL:
http://the.earth.li/~sgtatham/putty/latest/x86/putty.exe

Once the download has been completed, open putty.exe and provide the following details:

Host Name: [email protected]_ip_of_the_server

Then select Open.

After entering your password you will be logged on to the server.

Accessing MySQL:

$ mysql -u your_username -p

Create Database:

mysql> create database test_db;

List Databases:

mysql> show databases;

Select Database to action:

mysql> use test_db;

Create a Table:

We will create a table named 'cars' in the database that was created. The table will look like this:

| Model  | Color  | Max Speed  |  
|--------|--------|------------|  
| Polo   | blue   |    180     |   
| Fiesta | white  |    210     |   
| Yaris  | white  |    120     |  

Let's create the table:

mysql> create table cars (model VARCHAR(20), color VARCHAR(20), maxspeed VARCHAR(20));

Insert data into the table

mysql> insert into cars VALUES("polo", "blue", "180");
mysql> insert into cars VALUES("fiesta", "white", "210");
mysql> insert into cars VALUES("yaris", "blue", "120");

List tables:

mysql> show tables;

Describe Tables:

mysql> describe cars;

Queries: Show all info of the table

mysql> select * from cars;

Queries: Show all the white cars

mysql> select model from cars where color = 'white';

Queries: Show model and speed having a max speed more than 120

mysql> select model,maxspeed from cars where maxspeed > '120';

Create New User:

Create a new database, then create a user and grant him all privileges to his db.

mysql> CREATE DATBASE testdb;
mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON  testdb. * TO 'newuser'@'localhost';
mysql> FLUSH PRIVILEGES;

Grant Access from a Remote Location:

mysql> GRANT ALL PRIVILEGES on testdb.* to 'newuser'@'172.10.20.30' IDENTIFIED by 'password';

Grant Access to Prefixed Database Names:

Grant access to prefixed db names like db_test1 , db_test2 for a specific address:

mysql> GRANT ALL PRIVILEGES ON `db_%`.* TO 'newuser'@'localhost' IDENTIFIED BY 'password';

Grant Access to a Database from Everywhere:

mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'newuser'@'%' IDENTIFIED BY 'password' ;

Getting Some Insights:

mysql> show full processlist;
+--------+------+-----------------+---------------------+---------+------+-------+-----------------------+----------+
| Id     | User | Host            | db                  | Command | Time | State | Info                  | Progress |
+--------+------+-----------------+---------------------+---------+------+-------+-----------------------+----------+
| 250067 | root | localhost:56640 | NULL                | Sleep   |    9 |       | NULL                  |    0.000 |
+--------+------+-----------------+---------------------+---------+------+-------+-----------------------+----------+

Getting Status Variables:

mysql> SHOW GLOBAL STATUS LIKE 'Connections';
mysql> SHOW GLOBAL STATUS LIKE 'Max_used_connections';
mysql> SHOW GLOBAL STATUS LIKE 'Threads_created';
mysql> SHOW GLOBAL STATUS LIKE 'Threads_cached';

Deleting your database:

mysql> drop database test_db;

Exit MySQL:

mysql> exit;