How to install MySQL server on Ubuntu

MySQL with disk background

MySQL is one of the most popular Open Source Relational DataBase Management System, RDBMS for short. MySQL is widely spread across the world, it is employed in enterprise environments as well as small hobbyist sites. Surprisingly, it is dead-simple to set it up and start using it on Ubuntu, let’s see how.

Requirements (before you start)

Important
I take absolutely NO responsibility of what you do with your machine; use this tutorial as a guide and remember you can possibly cause data loss if you touch things carelessly.

Databases workloads are among the most demanding ones. But that’s not true when you have a handful of small databases, and you do not perform many queries (operations). As a matter of fact you can install and get MySQL working on even the smallest 1GB RAM cloud instances without any issues.

Although mechanical hard drives have been used for databases for a long time, having a Solid State Drive holding your database will significantly improve your database performance.

You will need a running instance of Ubuntu to follow this tutorial, if you’re going to use MySQL server as a stable database (for a long time) I suggest you use a LTS version of Ubuntu such as Xenial Xerus or Bionic Beaver.

If everything is in order, let’s start.

Installing

MySQL has been available by default in Ubuntu repositories for a while so it’s quite easy to install, fire up your terminal and follow the steps:

Ubuntu > 16.04Ubuntu <= 16.04
$ sudo apt-get update
$ sudo apt-get install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-6 libfcgi-perl libhtml-parser-perl
  libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl
  libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server-5.7 mysql-server-core-5.7
Suggested packages:
  libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx tinyca
The following NEW packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-6 libfcgi-perl libhtml-parser-perl
  libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl
  libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.7
  mysql-server-core-5.7
0 upgraded, 21 newly installed, 0 to remove and 3 not upgraded.
Need to get 21.0 MB of archives.
After this operation, 162 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y

You won’t be asked to set up a root password, the default one will be blank. That is very insecure! To set one up go to Securing MySQL.

$ sudo apt-get update
$ sudo apt-get install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-6 libfcgi-perl libhtml-parser-perl
  libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl
  libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server-5.7 mysql-server-core-5.7
Suggested packages:
  libdata-dump-perl libipc-sharedcache-perl libwww-perl mailx tinyca
The following NEW packages will be installed:
  libaio1 libcgi-fast-perl libcgi-pm-perl libencode-locale-perl libevent-core-2.1-6 libfcgi-perl libhtml-parser-perl
  libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl
  libtimedate-perl liburi-perl mysql-client-5.7 mysql-client-core-5.7 mysql-common mysql-server mysql-server-5.7
  mysql-server-core-5.7
0 upgraded, 21 newly installed, 0 to remove and 3 not upgraded.
Need to get 21.0 MB of archives.
After this operation, 162 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y

During the installation you will be asked to set up a root password, pick a strong one.

That’s pretty much it, your database management system is already up and running, you can learn how to manage it, secure it as well as common operations in the following paragraphs.

Managing MySQL

Starting MySQL

Ubuntu > 16.04Ubuntu <= 16.04
$ sudo systemctl start mysql
$ sudo service mysql start

Stopping MySQL

Ubuntu >= 16.04Ubuntu < 16.04
$ sudo systemctl stop mysql
$ sudo service mysql stop

Starting MySQL at boot

Ubuntu > 16.04Ubuntu <= 16.04
$ sudo systemctl enable mysql

The service is enabled by default.

Securing MySQL

Ubuntu (any version)
$ sudo mysql_secure_installation
Please set the password for root here.

New password:
Re-enter new password:

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

Disallow root login remotely? (Press y|Y for Yes, any other key for No) :

Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

Reload privilege tables now? (Press y|Y for Yes, any other key for No) :

Please notice the output in the command is heavily edited, but it will ask you the same questions. In this way you can set up your root password and secure your MySQL instance.

Accessing MySQL shell

Ubuntu (any version)
$ mysql -u USER -p

Change USER according to the user name of the database user. The prompt will ask for a password.

You can get a list of commands by issuing:

mysql> \h
For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/

List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
resetconnection(\x) Clean session context.

For server side help, type 'help contents'

You can exit using the exit command:

mysql> exit
Bye

Common operations

Create a Database

MySQL Shell
mysql> CREATE DATABASE database_name;
Query OK, 1 row affected (0.00 sec)

Drop a Database (delete)

MySQL Shell
mysql> DROP DATABASE database_name;
Query OK, 1 row affected (0.00 sec)

Create a User

MySQL Shell
mysql> CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';
Query OK, 1 row affected (0.00 sec)

Change the command according to your needs.

Drop a User (delete)

MySQL Shell
mysql> DROP USER username;
Query OK, 1 row affected (0.00 sec)

Granting privileges

MySQL Shell
mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
Query OK, 1 row affected (0.00 sec)

Of course you may not want to grant ALL PRIVILEGES to a particular user, instead you may want to let the user create a table, or insert data. You can replace ALL PRIVILEGES with the desired list of privileges, you can find a complete list here. After any privilege modification you should always reload privileges to make sure they are enforced immediately.

Revoking privileges

MySQL Shell
mysql> REVOKE ALL ON database_name.* FROM 'username'@'localhost';
Query OK, 1 row affected (0.00 sec)

You can replace ALL with the desired list of privileges you want to revoke, you can find a complete list here. After any privilege modification you should always reload privileges to make sure they are enforced immediately.

Reloading privileges

After any privilege modification you should always reload the privileges to make sure they are enforced immediately.

MySQL Shell
mysql> FLUSH PRIVILEGES;
Query OK, 0 row affected (0.00 sec)
Image courtesy of mark | marksei
mark

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.