How to properly setup MySQL 5.7 for production on Ubuntu 16.04

Image for post
Image for post

MySQL is a popular open-source database management system for web and server applications. Many companies rely on it to power their websites and business systems.

This guideline will explain how to properly install, configure, tune up MySQL 5.7 on Ubuntu 16.04.

Update package index on your server and install the package.

sudo apt-get update
sudo apt-get install mysql-server

When you’re prompted to create a root password, choose a secure one (or generate it). Make sure you remember it.

Improve the security of your MySQL installation with mysql_secure_installation.

sudo mysql_secure_installation

Press ENTER if you want to use defaults settings. Then test the server status and root login.

sudo service mysql status
mysql -u root -p

By default, MySQL use utf8 charset — which only supports 3-bytes characters. The real UTF-8 supports 4-bytes characters.

That means the default MySQL does not support all Unicode characters. This can cause huge issue on your system in the future.

And trust me, you don’t want to make the migration once your DB already has a tons of data. Better to do it right at beginning.

First, update /etc/my.cnf to use utf8mb4 — MySQL’s REAL UTF-8

[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

Restart your server and confirm the change

sudo service mysql restartmysql -u root -p
mysql> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| Variable_name | Value |
+--------------------------+--------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+

Repair and optimize all tables to ensure there is no side effect.

mysqlcheck -u root -p --auto-repair --optimize --all-databases

You will need to create your first database and user account to start using MySQL.

mysql> create user 'firstuser'@'%' IDENTIFIED BY 'yourpassword';
mysql> create database firstdb;
mysql> use firstdb;
mysql> grant all privileges on firstdb.* to 'firstuser'@'%' with grant option;
# Test new DB and new user account.
mysql -ufirstuser -p firstdb

Congratulation! Now you have a well-configured MySQL to develop or deploy your applications. However, we can do more.

MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability.

You should only use this tuner after your database has been running at least 1 day so it can give you better advice.

On Ubuntu, install the tool with apt-get then execute the command. Provide your root login account when being asked. The tool will provide you several recommendations and variables to adjust (like below).

sudo apt-get install mysqltuner
mysqltuner
-------- Recommendations --------
General recommendations:
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
query_cache_limit (> 1M, or use smaller result sets)
tmp_table_size (> 16M)
max_heap_table_size (> 16M)

Make sure you consider their WARNING before making any changes. This guideline covers many important aspects which will be helpful for your operations.

Good luck!

Software Maker, Founder & CEO of DayOneTeams.com

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store