Install MySQL 5.7 on Ubuntu 18.04

In this article, we will install MySQL 5.7 on Ubuntu 18.04 and see how to use it with a Rails 5.2 application.vInstall MySQL related packages:

sudo apt-get install -y mysql-server mysql-client libmysqlclient-dev

Check if MySQL is running or not:

 /etc/init.d/mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
   Active: active (running) since Thu 2019-02-21 19:24:02 UTC; 1min 38s ago
 Main PID: 11741 (mysqld)
    Tasks: 28 (limit: 1110)
   CGroup: /system.slice/mysql.service
           └─11741 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

Going into the MySQL prompt as the deploy user gives error:

ERROR 1698 (28000): Access denied for user 'root'@'localhost'

You can go into mysql prompt as the root user:

sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)

Copyright (c) 2000, 2019, 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.

Check if you can execute commands:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

On the server, add the production database configuration section for MySQL in shared/config/database.yml:

production:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: sample_production
  pool: 5
  username: your-user-name
  password: your-secret
  socket: /tmp/mysql.sock

Add mysql2 gem to Gemfile and run bundle. This gave the error:

Mysql2::Error::ConnectionError: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

Add the:

host: 127.0.0.1

to the database.yml. Still getting the error:

Mysql2::Error: Access denied for user 'root'@'localhost'

As a deploy user on the server, go the mysql prompt:

sudo mysql -u root
SET PASSWORD FOR 'deploy'@'localhost' = PASSWORD('mypassword');

This gives the error:

Mysql2::Error::ConnectionError: Access denied for user 'deploy'@'localhost' (using password: YES)

Create the deploy user, database and provide the appropriate privileges for the deploy user.

sudo mysql -u root
CREATE DATABASE IF NOT EXISTS sample_production;
CREATE USER IF NOT EXISTS 'deploy'@'localhost' IDENTIFIED BY 'secret-password';
CREATE USER IF NOT EXISTS 'deploy'@'%' IDENTIFIED BY 'secret-password';
GRANT ALL PRIVILEGES ON sample_production.* TO 'deploy'@'localhost';
GRANT ALL PRIVILEGES ON sample_production.* TO 'deploy'@'%';
FLUSH PRIVILEGES;
\q

If you get:

Mysql2::Error::ConnectionError: Access denied for user 'deploy'@'localhost' to database 'your production database'

Make sure you the correct application name when you grant all privileges. The deployed application will now work with MySQL database. If you are switching from Sqlite database, you can extract the record from the sqlite database:

hash = User.first.attributes

This will give you the values as a hash, you can then create the user record in MySQL:

User.create(hash)

You can also use seeds.rb to save the existing records if you only have a few records. Grouping the mysql2 gem in production does not prevent installing the mysql2 gem in development. You have to specifically use certain switches and it's a lot easier to install it on the development machine even if you are not using it.


Related Articles