Getting started with MySQL

MySQL is an open source Relational Database Management System (RDBMS) and it runs on virtually all platforms, including Linux, UNIX and Windows. 

It is used for a wide range of purposes, including data warehousing, e-commerce, and logging applications but most commonly it is used for the purpose of a web database.

Databases:

A database is a structured collection of data. Computers are very good at handling large amounts of data, hence database management systems play a central role in computing.

To add, access, and process data stored in a computer database we use a database management system such as MySQL Server.

Types of Databases:

  1. Relational Database : It stores data in separate tables rather than putting all the data in one big storeroom. The database structures are organized into physical files optimized for speed. The SQL in “MySQL” stands for “Structured Query Language”. SQL is the most common standardized language used to access databases.
  2. Non Relational Database : It stores data in key-value format, in documents or by some other method without using tables like a relational database. It is also called NoSQL . Examples of non relational databases are MongoDB, Redis, DocumentDB.

Database Queries :

Query languages are used to make queries in a database. A database query extracts data from a database and formats it in a readable form.

Connecting to MySQL Server

Update softwares on your instance using command :

$ sudo apt-get update

Install MySQL-Server

$ sudo apt-get install mysql-server

Securing MySQL-server

$ sudo mysql_secure_installation

To start MySQL at every boot :

$ sudo systemctl enable mysql

Connect to MySQL-Server

$ sudo mysql -u root -p

Creating new users

To create new users, use command :

mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';

To list all users, use command :

mysql> SELECT USER, HOST FROM mysql.user ;

Granting Permissions on Databases :

Granting all permissions to a user:

mysql> GRANT ALL ON DATABASE_name.* TO 'User'@'localhost' ;

These privileges are for database_name and it applies to all tables of that database, which is indicated by the .* that follows and it is assigned to user when that user is connected through locally, as specified by @’localhost’.

After updating users privileges, save the changes by using command:

mysql> FLUSH PRIVILEGES ;

Rather than providing all privileges to the entire database to a user, you can also give specific privileges to read from a table in a database.

mysql> GRANT ALL PRIVILEGES ON Table_name TO 'USER'@'localhost';
mysql> FLUSH PRIVILEGES ;

Creating a Super USER

Super user have same privileges as a default root account, to create a super user, use command:

mysql> GRANT ALL PRIVILEGES ON *.* To 'user'@'%' ;
mysql> FLUSH PRIVILEGES ;

User has ALL privileges across ALL databases on the server. Here we replace the database_name with the wildcard asterisk(*) and localhost can be written as “%”.

To log out :

To log out Mysql, use command :

mysql> exit ;

Get to know more about using MySQL commands in the next blog.