Getting Started with MySQL

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: your_user@the_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;