Using MySQL in Ubuntu

     Hi friends, after a long long time. I used to write this blog during my second year of the University life. These blog post contains the things I have done in Linux during my University life. Somehow I was unable to continue that. But now I need to add another post to this. It's about using MySQL and it's basic functions in Ubuntu. This was also an assignment I got during Uni life.

      This post describes simply how can we backup a database and restore it under Linux environment. Relevant Linux commands about how we can create database, tables and then insert, update and delete data from that tables and how to drop a table, backup database and restore are included here.

This was done under following environment.

anuja@anuja-laptop:~$ uname -a
Linux anuja-laptop 2.6.28-18-generic #60-Ubuntu SMP Fri Mar 12 04:40:52 UTC 2010 i686
GNU/Linux

anuja@anuja-laptop:~$ mysql --version
mysql Ver 14.12 Distrib 5.0.75, for debian-linux-gnu (i486) using readline 5.2

 

Login to MySQL prompt

anuja@anuja-laptop:~$ mysql -u root -p -h localhost
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 227
Server version: 5.0.75-0ubuntu10.5 (Ubuntu)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

Create a MySQL database with the name “exam”

mysql> CREATE DATABASE exam;
Query OK, 1 row affected (0.03 sec)

View the created database

mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
...
| exam |
...

Use that database and check is there any table already

mysql> USE exam;
Database changed

mysql> SHOW TABLES;
Empty set (0.00 sec)

Create a table under the “exam” database

mysql> CREATE TABLE `exam`.`student_details` (
-> `stdRegNo` VARCHAR( 255 ) NOT NULL ,
-> `stdName` VARCHAR( 255 ) NOT NULL ,
-> `stdCurrentYear` VARCHAR( 255 ) NOT NULL ,
-> PRIMARY KEY ( `stdRegNo` )
-> );
Query OK, 0 rows affected (0.00 sec)

Describe the created table

mysql> DESCRIBE student_details;
+----------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| stdRegNo | varchar(255) | NO | PRI | NULL | |
| stdName | varchar(255) | NO | | NULL | |
| stdCurrentYear | varchar(255) | NO | | NULL | |
+----------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Insert data into the table

mysql> INSERT INTO `exam`.`student_details` (
-> `stdRegNo`, `stdName`, `stdCurrentYear`)
-> VALUE (
-> "2007ICT052", "Anuja", "3"
-> );
Query OK, 1 row affected (0.00 sec)

Show data in the table

mysql> SELECT * FROM student_details;
+------------+---------+----------------+
| stdRegNo | stdName | stdCurrentYear |
+------------+---------+----------------+
| 2007ICT052 | Anuja | 3 |
+------------+---------+----------------+
1 row in set (0.00 sec)

Update data in a table

mysql> UPDATE `exam`.`subject_marks`
-> SET `subGrade` = "A++"
-> WHERE `stdRegNo` = '2007ICT052';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 0

Logout from the SQL prompt

mysql> quit
Bye

Backup the “exam” database

anuja@anuja-laptop:/media/disk$ mysqldump -u root -p exam > exambackup.dump
Enter password:

Drop a database from MySQL

mysql> DROP DATABASE exam;
Query OK, 2 rows affected (0.00 sec)

Restore the database that previously backup

Here I had to create a database from the same name before importing the backup file

anuja@anuja-laptop:/media/disk$ mysql -u root -p exam < exambackup.dump
Enter password:

 

In my next post I will show a shell script that will perform backup functionality and also how we can add that to Cron jobs. 

Bye for now :-)