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
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;
Comments