MySQL is the 2nd most popular database management system commonly used by web applications as backend. It integrates into Web development frameworks like LAMP (Linux, Apache, MySQL, PHP) to enable database and SQL (Structured Query Language) support. Here we are outlining the steps to install MySQL on Ubuntu 16.04 OS.
MySQL support on Ubuntu is unparallel. It is fast and reliable. Also, Ubuntu 16.04 is an LTS version which means it would get regular updates until five years from the date of release. And that’s why we recommend an LTS version for installing and managing servers like MySQL and Apache TomCat.
Debian users who want to install MySQL on Debian OS can also follow this tutorial as the commands and syntax are almost the same. However, do let us know for any variation and we’ll address it.
Prior installing MySQL on Ubuntu, you’ll have to decide the version of MySQL package for the installation. Its latest version is v5.7, but the older versions like v5.5 and v5.6 are also quite popular. In this post, we’ll use all these versions to install MySQL on Ubuntu.
Table of Index.
- How to install MySQL – Ubuntu 16.04?
- How to configure MySQL on Ubuntu?
- Managing MySQL database and users.
- Performance Tuning Your MySQL Server.
- A Virtual machine or physical system running Ubuntu 16.04 OS
- Sudo or Root access to install MySQL
- Fast and stable internet connection
Did you check if the system which you are using to install MySQL has the hostname set? It is a best practice to use a name for accessing the database servers. Make sure the hostname length should not exceed the maximum of 15 characters. You can check the hostname with any of the below commands.
# Display the short host name. $ hostname # Display the fully qualified domain name (FQDN). $ hostname -f
With the commands given here, you can install all versions of MySQL supported by Ubuntu 16.04.
To install MySQL v5.7, you can use the version included in Ubuntu’s APT repository. For Ubuntu 16.04, it currently points to the latest MySQL v5.7.
At the onset, we’ll first update the APT repo and upgrade the software already installed on our Ubuntu system.
$ sudo apt-get update $ sudo apt-get upgrade
Next, run the following commands to install both MySQL server and client packages.
$ sudo apt-get install mysql-server mysql-client
During the installation, the system will prompt you to set the database root user password. Make sure the password is hard to guess for others but the one which you can easily recall when needed.
To install an older version of MySQL on Ubuntu, the process is somewhat similar. You just need to suffix the version identifier in the package names. Check out the below commands to carry out the installation.
First of all, update the package index and the system.
$ sudo apt-get update $ sudo apt-get upgrade
$ sudo apt-get install mysql-server-5.5 mysql-client-5.5
$ sudo apt-get install mysql-server-5.6 mysql-client-5.6
The installation comes with a security script to control the default settings for root logins and sample users. Run this script with <sudo> privileges.
$ sudo mysql_secure_installation
Upon execution, the script will prompt you to enter the database root password you set during MySQL installation. Further, it’ll ask some questions which you need to respond with <ENTER> key to accept the default setting. Remember to ignore one question which asks you modify the root password.
Now, you are all set to start working with MySQL. However, if you like to access the database instance remotely via any client software, then you might run the following commands to allow remote access.
sudo iptables -I INPUT -p tcp --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT sudo iptables -I OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT
To initialize the MySQL version earlier than 5.7, run the following command.
For versions, 5.7 and above, you should be using the following command.
In case the, database directory is pre-initialized, the command will issue the following error message.
[ERROR] --initialize specified but the data directory has files in it. Aborting.
It is easy to track if the MySQL software is successfully installed or not.
mysql --version mysql Ver 14.14 Distrib 5.7.16, for Linux (x86_64) using EditLine wrapper
As the name suggests, the command will give you the version installed.
Now, to check if the MySQL service daemon is running or not, issue the following command.
$ service mysql status
It will return the following status. Please note that the value of process-id will change.
mysql start/running, process 3215
However, if the MySQL server is not running, then you can spawn it by using the below commands.
$ sudo service mysql start
In addition to the above commands, you can test it further by logging into the MySQL database.
$ mysqladmin -p -u root version mysqladmin Ver 8.42 Distrib 5.7.16, for Linux on x86_64 Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Server version 5.7.16-0ubuntu0.16.04.1 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/run/mysqld/mysqld.sock Uptime: 18 min 25 sec
The above command will verify the successful MySQL installation on Ubuntu.
The system permits you to alter the default MySQL configuration. If you want to change the default port, IP address or any other simple setting, then edit the “/etc/mysql/my.cnf” file.
However, modify the “/etc/mysql/mysql.conf.d/mysqld.cnf” file to update any advance database server option.
$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
Once you make these changes, you’ll need to restart the MySQL services. You can run any of the below commands.
# Run the following command $ systemctl restart mysql.service # Or you can also run the below command. $ service mysql restart
For connecting to the installed MySQL instance, you need to run the below command.
$ mysql -u root -p
After entering the database root user password, the above command will open the MySQL command prompt.
At the MySQL shell, you can execute SQL queries.
For viewing the existing database users, run the below query into the MySQL shell.
mysql> SELECT User, Host FROM mysql.user; +------------------+-----------+ | User | Host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.sys | localhost | | root | localhost | +------------------+-----------+ 3 rows in set (0.00 sec)
You can create standard database users who can connect to the MySQL server and perform database related tasks.
mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'test123'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT User, Host FROM mysql.user; +------------------+-----------+ | User | Host | +------------------+-----------+ | debian-sys-maint | localhost | | mysql.sys | localhost | | root | localhost | | testuser | localhost | +------------------+-----------+ 4 rows in set (0.00 sec)
You can also login with the newly created user.
$ mysql -u testuser -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.7.16-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
With everything in place, you might now want to create a new database as well. Use the below command to create a fresh MySQL DB.
mysql> CREATE DATABASE techbeamers; Query OK, 1 row affected (0.00 sec)
You can verify if the new database is available for use. Run the below query.
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | techbeamers | +--------------------+ 5 rows in set (0.00 sec)
Next, to update the database, you’ll first of all need to use the database.
mysql> use techbeamers; Database changed
Let’s now create a dummy table in the newly created database.
mysql> CREATE TABLE state (name VARCHAR(50), capital VARCHAR(256)); Query OK, 0 rows affected (0.01 sec)
You can check if the table gets created successfully or not.
mysql> show tables; +-----------------------+ | Tables_in_techbeamers | +-----------------------+ | state | +-----------------------+ 1 row in set (0.00 sec)
You can install MySQL tuner package which comes with a Perl script to optimize the performance and tighten the security of a running MySQL instance.
Run the following commands to install and setup MySQL tuner.
$ sudo apt-get install mysqltuner
Once you get the MySQL tuner installed, issue the below command to start the performance tuner. But, please make sure that the MySQL server has run at least 24 hours or longer before starting the performance tuner.
$ mysqltuner --host <Host name> --user <User Name> --pass <Password>
By default, the tuner will apply the default recommendations which you can change if needed.
Summary – How to Install MySQL on Ubuntu 16.04 OS
We tried to keep all the instructions simple and directed to install MySQL on Ubuntu. And we wish that you would now be able to use them on your systems.
If you still have any open question about MySQL, then please let us know through your comments.