How to install MySQL 5.7 Server on Ubuntu 16.04
(P) Codever is an open source bookmarks and snippets manager for developers & co. See our How To guides to help you get started. Public bookmarks repos on Github ⭐🙏
This is not the first time I need to install and configure a MySql Community Server on a fresh system with Ubuntu (16.04) running on it. So instead next time to have to google for the individual steps again, I decided to write a post. Find out also how to tweak the Server configuration.
Install MySQL Server
This guide is based on the official MySQL documentation - A Quick Guide to Using the MySQL APT Repository, where my own data is used instead.
Add the MySQL APT Repository
Go to the download page for the MySQL APT repository at https://dev.mysql.com/downloads/repo/apt/ and select the package for my Linux distribution. As said mine is
Ubuntu 16.04, which is covered by the mysql-apt-config_0.8.1-1_all.deb package at the time of this writing:
shell> wget https://dev.mysql.com/get/mysql-apt-config_0.8.1-1_all.deb
Next install the release package with the following command:
shell> sudo dpkg -i mysql-apt-config_0.8.1-1_all.deb
Selecting previously unselected package mysql-apt-config.
(Reading database ... 27837 files and directories currently installed.)
Preparing to unpack mysql-apt-config_0.8.1-1_all.deb ...
Unpacking mysql-apt-config (0.8.1-1) ...
Setting up mysql-apt-config (0.8.1-1) ...
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_ALL to default locale: No such file or directory
/usr/bin/locale: Cannot set LC_CTYPE to default locale: No such file or directory
/usr/bin/locale: Cannot set LC_ALL to default locale: No such file or directory
OK
During installation I chose the MySQL 5.7 Server version
The next mandatory step is to update the package information from the MySQL APT repository
shell> sudo apt-get update
Hit:1 https://mirrors.linode.com/ubuntu xenial InRelease
Get:2 https://mirrors.linode.com/ubuntu xenial-updates InRelease [102 kB]
Get:3 https://mirrors.linode.com/ubuntu xenial-backports InRelease [102 kB]
Get:4 https://repo.mysql.com/apt/ubuntu xenial InRelease [14.2 kB]
Hit:5 https://ppa.launchpad.net/webupd8team/java/ubuntu xenial InRelease
Get:6 https://security.ubuntu.com/ubuntu xenial-security InRelease [102 kB]
Get:7 https://repo.mysql.com/apt/ubuntu xenial/mysql-5.7 Sources [886 B]
Get:8 https://repo.mysql.com/apt/ubuntu xenial/mysql-apt-config amd64 Packages [567 B]
Get:9 https://repo.mysql.com/apt/ubuntu xenial/mysql-apt-config i386 Packages [567 B]
Get:10 https://repo.mysql.com/apt/ubuntu xenial/mysql-5.7 amd64 Packages [2709 B]
Get:11 https://repo.mysql.com/apt/ubuntu xenial/mysql-5.7 i386 Packages [2712 B]
Get:12 https://repo.mysql.com/apt/ubuntu xenial/mysql-tools amd64 Packages [2608 B]
Get:13 https://repo.mysql.com/apt/ubuntu xenial/mysql-tools i386 Packages [1928 B]
Fetched 333 kB in 0s (603 kB/s)
Reading package lists... Done
Installing MySQL with APT
Now to install MySQL execute the following command:
shell> sudo apt-get install mysql-server
You will be asked for the root password during the installation. Make a note of it if you want to spare the time needed to reset it later1
The server is started automatically after installation. You can check the version of the server by issuing the following command:
shell> mysql --version
mysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrapper
Starting and stopping the MySQL Server
You can check the status:
shell> sudo service mysql status
stop it:
shell> sudo service mysql stop
and start it again:
shell> sudo service mysql start
Because I am lazy and I will probably repeat these commands several times, at least in the beginning, I defined some aliases for them - snippet from my .bash_aliases file:
# MySql
alias mysql-start="sudo service mysql start"
alias mysql-stop="sudo service mysql stop"
alias mysql-restart="sudo service mysql restart"
alias mysql-status="sudo service mysql status"
alias mysql-connect-root="mysql -uroot -p"
alias mysql-vim-mysql.conf.d.my.cnf="sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf"
See my post A developer’s guide to using aliases to learn how I’ve become best buddies with the Bash alias. O, and I’ve also created a video with it.
Tune MySQL configuration
Most of the default configuration values are fine, but you can tweak it if you don’t have enough memory 2 (like my server running on a 2GB RAM machine) or want to improve performance 3.
I have personally written about optimizing MySQL Server Settings, but this remains a science for itself to me. Anyway before you are ready to override a default value in
my.cnf, have a look in the official documentation about Server System Variables and try to reason what each really means…
Now which configuration file should I use, because there are bunch of them under the /etc/mysql directory:
/etc/mysql/
|-- conf.d
| -- mysql.cnf
|-- my.cnf -> /etc/alternatives/my.cnf
|-- my.cnf.fallback
|-- mysql.cnf
|-- mysql.cnf.2017-01-13-original
|-- mysql.conf.d
-- mysqld.cnf
Well, because these are server settings, I want to override I need to put them below the [mysld] tag, which is to be found in mysqld.conf. So edit this file:
shell> sudo vim /etc/mysql/mysql.conf.d/mysqld.cnf
which looks like the following:
#
# The MySQL Server configuration file.
#
# For explanations see
# https://dev.mysql.com/doc/mysql/en/server-system-variables.html
[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log
# By default we only accept connections from localhost
bind-address = 127.0.0.1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# my overrides
max_allowed_packet = 1M
max_connections = 75
table_open_cache = 32M
key_buffer_size = 32M
Remember these are values for a small 2GB RAM server. It is supposed to run one MySQL Database among a bunch of other servers… Also very important to notice is that you can now change the configuration values at runtime, but they won’t be persisted when the server is restarted.
