How to properly setup MySQL 5.7 for production on Ubuntu 16.04

Step 1: Install MySQL

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

Step 2: Secure MySQL

sudo mysql_secure_installation
sudo service mysql status
mysql -u root -p

Step 3: Use utf8mb4 to fully support Unicode

[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
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 |
+--------------------------+--------------------+
mysqlcheck -u root -p --auto-repair --optimize --all-databases

Step 4: Create new user and database

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

Step 5: Improve performance and stability with MySQLTuner

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)

--

--

Software Maker, Founder & CEO of DayOneTeams.com

Love podcasts or audiobooks? Learn on the go with our new app.

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