Correct MySQL configuration for Ruby on Rails Database.yml file

Question

I have this configuration:

development:
  adapter: mysql2
  encoding: utf8
  database: my_db_name
  username: root
  password: my_password
  host: mysql://127.0.0.1:3306

And I am getting this error:

Unknown MySQL server host 'mysql://127.0.0.1:3306' (1)

Is there something obvious that I am doing incorrectly?

Amokrane Chentir · Accepted Answer · 2011-05-03T16:05:32.180

208

You should separate the host from the port number. You could have something, like:

development:
  adapter: mysql2
  encoding: utf8
  database: my_db_name
  username: root
  password: my_password
  host: 127.0.0.1
  port: 3306

edited May 03 '11 at 16:05

answered May 03 '11 at 15:59

Amokrane Chentir

29,907
37
114
158

4

and host can't be `host: localhost` – Adrian C. Nov 27 '12 at 14:59
For security reasons, it's generally considered bad practice to use `root` as the production database user. To set up a dedicated user for your rails app, see the [MySQL docs about user creation](https://dev.mysql.com/doc/refman/5.7/en/adding-users.html). – Feliks Montez May 29 '17 at 21:13
2

Also for security reasons (if you're using version control) you should not store your database password in `database.yml`. Instead, do what user3118220 did and get it from your environment: `password: ENV['MY_RAILS_APP_DB_PASSWORD']`. – Feliks Montez May 29 '17 at 21:19

pangpang · Answer 2 · 2016-01-08T07:29:04.503

23

You also can do like this:

default: &default
  adapter: mysql2
  encoding: utf8
  username: root
  password:
  host: 127.0.0.1
  port: 3306

development:
  <<: *default
  database: development_db_name

test:
  <<: *default
  database: test_db_name

production:
  <<: *default
  database: production_db_name

edited Jan 08 '16 at 07:29

answered Jan 08 '16 at 06:25

pangpang

8,581
11
60
96

score 4 · Answer 3 · edited Mar 28 '18 at 01:00

4

Use 'utf8mb4' as encoding to cover all unicode (including emojis)

default: &default
  adapter: mysql2
  encoding: utf8mb4
  collation: utf8mb4_bin
  username: <%= ENV.fetch("MYSQL_USERNAME") %>
  password: <%= ENV.fetch("MYSQL_PASSWORD") %>
  host:     <%= ENV.fetch("MYSQL_HOST") %>

(Reference1) (Reference2)

edited Mar 28 '18 at 01:00

Stephen Rauch

47,830
31
106
135

answered Mar 28 '18 at 00:40

botibs

81
3

score 2 · Answer 4 · answered Mar 24 '15 at 04:30

If you can have an empty config/database.yml file then define ENV['DATABASE_URL'] variable, then It will work

$ cat config/database.yml
 
$ echo $DATABASE_URL
mysql://root:my_password@127.0.0.1:3306/my_db_name

for Heroku: heroku config:set DATABASE_URL='mysql://root:my_password@host.com/my_db_name'

Rick · Answer 5 · 2015-08-26T23:47:25.890

0

If you have multiple databases for testing and development this might help

development:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: DBNAME
  pool: 5
  username: usr
  password: paswd
  shost: localhost
test:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: DBNAME
  pool: 5
  username: usr
  password: paswd
  shost: localhost
production:
  adapter: mysql2
  encoding: utf8
  reconnect: false
  database: DBNAME
  pool: 5
  username: usr
  password: paswd
  shost: localhost

edited Aug 26 '15 at 23:47

answered Oct 30 '14 at 06:33

Rick

12,606
2
43
41

1

how is `shost` different than `host`? – konyak Oct 16 '15 at 18:04

score 0 · Answer 6 · edited May 23 '17 at 12:18

0

None of these anwers worked for me, I found Werner Bihl's answer that fixed the problem.

Getting "Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'" error when setting up mysql database for Ruby on Rails app

edited May 23 '17 at 12:18

Community

1
1

answered Nov 02 '16 at 17:26

t q

4,593
8
56
91

Correct MySQL configuration for Ruby on Rails Database.yml file

6 Answers6

Linked