How to properly setup MySQL 5.7 for production on Ubuntu 16.04
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.
Step 1: Install MySQL
Update package index on your server and install the package.
sudo apt-get update
sudo apt-get install mysql-serverWhen you’re prompted to create a root password, choose a secure one (or generate it). Make sure you remember it.
Step 2: Secure MySQL
Improve the security of your MySQL installation with mysql_secure_installation.
sudo mysql_secure_installationPress ENTER if you want to use defaults settings. Then test the server status and root login.
sudo service mysql status
mysql -u root -pStep 3: Use utf8mb4 to fully support Unicode
By default, MySQL use
utf8charset — which only supports 3-bytes characters. The realUTF-8supports 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-databasesStep 4: Create new user and database
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.
Step 5: Improve performance and stability with MySQLTuner
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!
More Information:
