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)
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:
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.