Connect to database from host client

Hi, this should be possible. You'll need to enable your mysql root account to login from any host, as this is securely restricted to localhost by default.

Windows or Mac>$ vagrant ssh
Vagrantbox>$ mysql
mysql> grant all privileges on *.* to root@'%' identified by 'root'; flush privileges;
SSH Password: vagrant
MySQL Password: root

Also, see attached using MySQL Workbench. Enjoy

Eric,

I'm not really sure, but as the VDDs are meant to be used for development on the developers' local machines - can we make these root account permissions (to enable connection from the host) the default ones?

Thanks
AndyB

Yeah, that's a good idea. I'll look into that.

When I run this command:

grant all privileges on *.* to root@'%' identified by 'root'; flush privileges;

within the mysql cli from the vagrant box, I get this message:

ERROR 1227 (42000): Access denied; you need (at least one of) the RELOAD privilege(s) for this operation

If I try to access the mysql cli using the password (i.e. running mysql -proot instead of just mysql), I get:

ERROR 1045 (28000): Access denied for user 'vagrant'@'localhost' (using password: YES)

I'd also like to see this set up by default, as I think this is a pretty common workflow for local development. But at the moment I can't seem to get the instructions above to work.

Ah, got it. In #1 above, instead of just typing mysql, you should type mysql -u root -proot.

I forgot to specify the user above which is why it didn't work for me at first. But in any case granting the privileges won't work unless you use the user/pass combination when you access mysql.

Sorry to generate so many issue replies, but I wanted to let you know that I've added documentation of the above to the getting started page.

The only change needed for this to work is to edit vdd/chef/cookbooks/berks/mysql/attributes/default.rb and change line 10 from false to true:

#
default['mysql']['service_name'] = 'default'

# passwords
default['mysql']['server_root_password'] = 'ilikerandompasswords'
default['mysql']['server_debian_password'] = nil
default['mysql']['server_repl_password'] = nil

# used in grants.sql
default['mysql']['allow_remote_root'] = false
default['mysql']['remove_anonymous_users'] = true
default['mysql']['root_network_acl'] = nil

case node['platform']
when 'smartos'
  default['mysql']['data_dir'] = '/opt/local/lib/mysql'
else
  default['mysql']['data_dir'] = '/var/lib/mysql'
end

# port
default['mysql']['port'] = '3306'

change to:

#
default['mysql']['service_name'] = 'default'

# passwords
default['mysql']['server_root_password'] = 'ilikerandompasswords'
default['mysql']['server_debian_password'] = nil
default['mysql']['server_repl_password'] = nil

# used in grants.sql
default['mysql']['allow_remote_root'] = true
default['mysql']['remove_anonymous_users'] = true
default['mysql']['root_network_acl'] = nil

case node['platform']
when 'smartos'
  default['mysql']['data_dir'] = '/opt/local/lib/mysql'
else
  default['mysql']['data_dir'] = '/var/lib/mysql'
end

# port
default['mysql']['port'] = '3306'

Here's a patch to accomplish #7. My only question is whether we want to make changes to stuff in the berks folder, or if that's code that needs to remain as it is.

@bobojo I'm not too excited about modifying the berks files and we can declare this in the mysql attributes easily enough instead.

Host-based connections now work with Sequel Pro after implementing the attached patch for me. Anyone verify? Thanks

Using the patch from #9 worked fine for me using Sequel Pro. :-)

Connection settings in Sequel Pro I used:

Connected and examining the phpMyAdmin db:

@siliconmeadow Thanks for confirming. Cheers