What is Mysql?

MySQL is a widely accessible open source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL).

SQL is the most well-known language for accessing, editing and managing content in a database. It is most noted for its fast handling, demonstrated dependability, straightforwardness, and adaptability of utilization. MySQL is a primary piece of practically every open source PHP application. Great examples of PHP/MySQL-based projects are phpBB, osCommerce and Joomla.

How to Install MySQL on Ubuntu

In this tutorial, we will install MySQL on Ubuntu 16.04.2, the procedures demonstrated here can work for other versions of Ubuntu as well. MySQL is installed using the apt package manager. Before you begin the installation, ensure that all your packages are up to date. You may run the following command in the terminal to update the packages.

sudo apt-get update

Next, we will install a package which downloads all the necessary files, does an initial setup configuration of the database and manages the running of MySQL as a system service. Run the following command on your terminal to get going, when prompted whether you’d like to install the new package, enter ‘y’ and press enter.

sudo apt-get install mysql

An admin screen prompting for a root password will pop up amid the package installation process. Input a password of your choice twice, and installation will proceed. In a moment, the installation process will be complete.

Securing MySQL

For security reasons, you do not want applications connecting to MySQL using the root user that you created. It is recommended to have applications connect to the MySQL database using non-root users, in this step we show you how to do this. The default MySQL installation is meant for development and testing objectives. However, this kind of environment is deemed insecure for production environments, therefore is shipped with a utility to crank the underlying security. Run the following command on the terminal and answer the prompt questions according to your environment requirements.

sudo mysql_secure_installation

Once you are done with the prompts, you will have setup a MySQL instance with proper security in place. Now you need to create non-root users with which applications will use to interact with the MySQL database.

How to create MySQL users

To get started, connect to the MySQL instance using the MySQL command line client. Run the following command in your terminal to log in to the MySQL cmd.

mysql -u root -p

To create a new user, use the MySQL CREATE USER command. Run the following command on your terminal, remember to replace “nameofuser” and “strongpass” with your desired new user’s name and password respectively.

CREATE USER 'nameofuser'@'localhost' IDENTIFIED BY 'strongpass';

This command has no output on successful execution. MySQL allows application of privileges to users created so that they can handle basic database operations. Once again replace “nameofuser” with the name of the user you created.

GRANT ALL PRIVILEGES ON * . * TO 'nameofuser'@'localhost';

It is recommended to reload the privileges to ensure that the new granted permissions are in place. Run this command on your terminal to achieve this.

FLUSH PRIVILEGES;

How to connect to MySQL

If you have reached this point, you are all set to connect and play around with MySQL. Exit the command line client with “ctrl-d”. Reconnect to the MySQL instance with our new non-root user by running the following command in your terminal.

mysql -u nameofuser -p

Basic MySQL commands

Create a database

Syntax:

CREATE DATABASE [name of database];

Example:

CREATE DATABASE mydatabase;

Connect to the database

Syntax:

use [name of database];

Example:

use mydatabase;

Create a table

Syntax:

CREATE TABLE table (
       column1 type [[NOT] NULL]
               [AUTO_INCREMENT],
       column2 type [[NOT] NULL]
               [AUTO_INCREMENT],
       …
       other options,
       PRIMARY KEY (column(s))   );

Example:

CREATE TABLE Students (           LastName varchar(30) NOT NULL,         FirstName varchar(30) NOT NULL,         StudentID int NOT NULL,         Major varchar(20),         Dorm varchar(20),         PRIMARY KEY (StudentID)     );

MySQL best practices

Optimize Your Queries for the Query Cache

Most MySQL servers are query caching empowered. It’s amongst the best strategies for enhancing performance, that is unobtrusively taken care of by the database engine. Whenever the same query is run multiple times, the result comes from the cache, hence much quicker.

EXPLAIN Your SELECT Queries

To get an insight on what MySQL is doing to run your query, use the EXPLAIN keyword. This helps in identifying bottlenecks and other issues that may be troubling your query or other database objects.

LIMIT 1 When Getting a Unique Row

When querying your tables for just one row, or the existence of records that match a given WHERE clause, it is recommended adding LIMIT 1 to your SELECT query to increase performance. This means the database engine will return results after it finds just one record instead of scanning through the whole given database object.

Index the search fields

If there are columns in your tables that you will use for “search by” queries, it is recommended that you always index them.

Index and use same column names for joins

It’s also best practice to always index the columns used in a JOIN. This significantly improves how MySQL optimizes the JOIN operation. Also, ensure that the columns being joined are of the same data type. If they are of different kinds, MySQL may be unable to use one of the indexes.

Avoid selecting all (SELECT *)

The amount of data you read from tables significantly affects the speed of the query. It affects the time it takes for disk operations. If the database server is accessed over a network, it will influence the time required to transfer data over the network. It is one of MySQL’s best practices always to specify which columns you need when you are doing your SELECT’s.

Choose the right storage engine

MySQL has two main storage engines; MyISAM and InnoDB. Each of these has their advantages and disadvantages.

MyISAM is recommended for read-heavy apps, though it performs poorly in cases where there are a lot of writes. Database objects get locked when an operation is done on them regardless of how simple they are. MyISAM would come in handy when doing numerous SELECT COUNT(*) queries.

InnoDB tends to be a more sophisticated storage engine. However, it can be a bit laggier than MyISAM for many small applications. But it supports row-based locking, which scales better. It can also handle some more advanced features such as transactions.