【AWSへデプロイ】Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)の解決方法

前提・実現したいこと

ruby on railsのアプリをAWSのEC2にデプロイをしています。
下記のエラーの解消方法を教えてください

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

環境

ruby 2.7.1p83 (2020-03-31 revision a0c7c23c9c) [x86_64-linux]
Rails 5.2.6
Mysql 5.7

エラー

EC2
1[ec2-user@ip-xxx-xx-xx-xxx app]$ bundle exec rails db:migrate RAILS_ENV=production
2/home/ec2-user/app/vendor/bundle/ruby/2.7.0/gems/actionpack-5.2.6/lib/action_dispatch/middleware/stack.rb:37: warning: Using the last argument as keyword parameters is deprecated; maybe ** should be added to the call
3/home/ec2-user/app/vendor/bundle/ruby/2.7.0/gems/actionpack-5.2.6/lib/action_dispatch/middleware/ssl.rb:59: warning: The called method `initialize' is defined here
4rails aborted!
5Mysql2::Error::ConnectionError: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111)
6/home/ec2-user/app/vendor/bundle/ruby/2.7.0/gems/mysql2-0.5.3/lib/mysql2/client.rb:90:in `connect'
7・
8・
9・

おそらく下記が原因のエラーだと思います。
(2)と (13)は解消してきましたがこれだけどれれ調べてもうまくいきません

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

試したこと

①MySQLの再起動
②/etc/my.cnfの変更

my.cnf
1[mysqld]
2datadir=/var/lib/mysql
3socket=/tmp/mysql.sock
4innodb_force_recovery=1
5# Disabling symbolic-links is recommended to prevent assorted security risks
6symbolic-links=0
7# Settings user and group are ignored when systemd is used.
8# If you need to run mysqld under a different user or group,
9# customize your systemd unit file for mariadb according to the
10# instructions in http://fedoraproject.org/wiki/Systemd
11
12[mysqld_safe]
13log-error=/var/log/mariadb/mariadb.log
14pid-file=/var/run/mariadb/mariadb.pid
15
16#
17# include all files from the config directory
18#
19!includedir /etc/my.cnf.d

③mariadbのmariadb-server.x86_64 追加

git:ターミナル
1mariadb.x86_64                                              1:5.5.68-1.amzn2                                       @amzn2-core
2mariadb-devel.x86_64                                        1:5.5.68-1.amzn2                                       @amzn2-core
3mariadb-libs.x86_64                                         1:5.5.68-1.amzn2                                       installed  
4mariadb-server.x86_64                                       1:5.5.68-1.amzn2                                       @amzn2-core

④database.ymlの確認

databaseyml
1# SQLite version 3.x
2#   gem install sqlite3
3#
4#   Ensure the SQLite 3 gem is defined in your Gemfile
5#   gem 'sqlite3'
6#
7default: &default
8  adapter: sqlite3
9  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
10  timeout: 5000
11  socket: /tmp/mysql.sock
12
13development:
14  <<: *default
15  database: db/development.sqlite3
16
17# Warning: The database defined as "test" will be erased and
18# re-generated from your development database when you run "rake".
19# Do not set this db to the same as development or production.
20test:
21  <<: *default
22  database: db/test.sqlite3
23
24production:
25  <<: *default
26  database: <%= ENV['DB_DATABASE'] %>
27  adapter: mysql2
28  encoding: utf8mb4
29  charset: utf8mb4
30  collation: utf8mb4_general_ci
31  host: <%= ENV['DB_HOST'] %>
32  username: <%= ENV['DB_USERNAME'] %>
33  password: <%= ENV['DB_PASSWORD'] %>
34~

⑤/tmp/mysql.sockを/tmp/mysql2.sockに変更

[ec2-user@ip-xxx-xxx-xx-xxx app]$ mv mysql.sock mysql2.sock
mv: `mysql.sock' を stat できません: No such file or directory

⑥ローカル環境のmysql.socketの位置

local@my-mac app % mysql_config --socket 
/tmp/mysql.sock

⑦本番環境のmysql.socketの位置

[ec2-user@ip-xxx-xxx-xx-xxx app]$ mysql_config --socket 
/var/lib/mysql/mysql.sock

他にできることあれば、どなたか解決方法わかる方お願いします。

行動規範の内容に同意します

回答1件

まず database.yml を整理しましょう
default が adapter: sqlite3 なら、ここにsocket は無いほうが混乱しません
production: の host: <%= ENV['DB_HOST'] %> ですが、別サーバーで動いてます？だと socket はおかしいかも。同じサーバーならこの記述は無い方が混乱しません。

で、
mysql_config --socket の結果と database.ymlの記述が合っているか確認してください

投稿2021/10/19 22:54