Why I need to set mysql root password MANUAL when using mysql recipes(opscode) in my cookbook?


#1

I created a redmine cookbook, that setups redmine.

First of all my cookbook installing mysql and creating a apporative
database redmine:

See my first recipe in a runlist of redmine server names database.rb:

Setup mysql client packages - need for mysql::server recipe

include_recipe “mysql::client”

If mysql server is not installed.

if ! ::File.exists?("/usr/sbin/mysqld") then

 # Fix bug in recipe mainline. When we use credmine::purgeall and 

the rerun chef-client error occurs: "No such file or directory"
directory “/etc/mysql” do
owner "mysql"
group "mysql"
mode 0700
action :create
end

 # Setup security
 node.set['mysql']['remove_test_database'] = true

 # Normally, root should only be allowed to connect from 

‘localhost’. This ensures that someone cannot guess at the root password
from the network.
node.set[‘mysql’][‘remove_anonymous_users’] = true

 # If true Sets root access from '%'. If false deletes any 

non-localhost root users.
node.set[‘mysql’][‘allow_remote_root’] = true

 # Setup mysql server by security parameters seted in 

credmine::_loaddatabags
include_recipe “mysql::server”

 # Prepare chef ruby environment(install some gems) for using LWRP 

at this machine
include_recipe “database::mysql”

 # Restart mysql server with new parameters.
 service "mysql" do
   action [ :enable, :restart ]
 end

end

Using database cookbook idemponent LWRP’s

mysql_connection_info = {:host => “localhost”, :username => “root”,
:password => node[‘mysql’][‘server_root_password’]}

Using database cookbook ::: to create redmine database

mysql_database node.run_state[‘redmine-mysql-name’] do
connection mysql_connection_info
action :create
end

At the last action mysql_database node.run_state[‘redmine-mysql-name’]
do recipe stops with error:

[2013-10-21T13:38:34+00:00] INFO: Processing mysql_database[redmine]
action create (credmine::database line 42)

================================================================================
Error executing action create on resource ‘mysql_database[redmine]’

Mysql::Error

Access denied for user ‘root’@‘localhost’ (using password: YES)

Cookbook Trace:

/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:83:in
new' /var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:83:indb’
/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:78:in
exists?' /var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:36:inaction_create’

Resource Declaration:

In /var/chef/cache/cookbooks/credmine/recipes/database.rb

42: mysql_database node.run_state[‘redmine-mysql-name’] do
43: connection mysql_connection_info
44: action :create
45: end
46:

Compiled Resource:

Declared in

/var/chef/cache/cookbooks/credmine/recipes/database.rb:42:in `from_file’

mysql_database(“redmine”) do
provider Chef::Provider::Database::Mysql
action [:create]
retries 0
retry_delay 2
database_name "redmine"
cookbook_name "credmine"
recipe_name "database"
connection {:host=>“localhost”, :username=>“root”,
:password=>“ahgdfyasrbiywerfybweufy”}
end

[2013-10-21T13:38:35+00:00] ERROR: Running exception handlers
[2013-10-21T13:38:35+00:00] FATAL: Saving node information to
/var/chef/cache/failed-run-data.json
[2013-10-21T13:38:35+00:00] ERROR: Exception handlers complete
[2013-10-21T13:38:35+00:00] FATAL: Stacktrace dumped to
/var/chef/cache/chef-stacktrace.out
[2013-10-21T13:38:35+00:00] FATAL: Mysql::Error: mysql_database[redmine]
(credmine::database line 42) had an error: Mysql::Error: Access denied
for user ‘root’@‘localhost’ (using password: YES)

I changed password in log to (ahgdfyasrbiywerfybweufy) of course instead
of true pass.

And I need to insert my own reset password code before mysql_database
node.run_state[‘redmine-mysql-name’] do
Like this:

TODO may be off this recipe

bash “change_mysql_root_password” do
cwd "/tmp"
code <<-EOH
mysql -u root
–password=#{node[‘mysql’][‘server_root_password’]} mysql -e ‘show
databases;’> /dev/null
if ! [ ? -eq 0 ]; then service mysql stop; if ! [ ? -eq 0 ]; then exit
1000; fi;
sleep 3
mysqld_safe --skip-grant-tables
–socket=/tmp/mysqld_safe.socket --pid-file=/tmp/mysqld_safe.pid

/dev/null &
echo “use mysql;” > /tmp/mysql_flush_root.sql
echo "update user set
password=PASSWORD(’#{node[‘mysql’][‘server_root_password’]}’) where
User=‘root’; " >> /tmp/mysql_flush_root.sql
# Update privileges in memmory, because if we don’t
do this old password be valied instead of new.
echo “flush privileges;” >> /tmp/mysql_flush_root.sql
sleep 5
mysql --socket=/tmp/mysqld_safe.socket <
/tmp/mysql_flush_root.sql
rm /tmp/mysql_flush_root.sql
kill -TERM $(cat /tmp/mysqld_safe.pid);
sleep 2
service mysql start
else exit 0;
fi
EOH
end


This recipe verifies the password that I set into a variable
node[‘mysql’][‘server_root_password’] earlier in _loaddatabags recipe.
Which is running before this database creation of course.

Well I am not understand how I can check why lwrp don’t setup a mysql
with needed password and don’t use this smart hack ))) ?


Best regards,

CVision Lab System Administrator
Vladmir Skubriev


#2

Not sure what’s causing this error for you, but here’s how I install MySQL
in my recipe. I’m using Chef server, not Chef solo.

In my cookbook’s metadata.rb:

depends "database"
depends “mysql”

In my cookbook’s recipe (note that I didn’t put these under any "if"
statement):

Install MySQL

include_recipe "mysql::server"
include_recipe "mysql::client"
include_recipe “database::mysql”

Create database

mysql_connection_info = {:host => ‘localhost’, :username => ‘root’,
:password => node[:mysql][:server_root_password]}
mysql_database ‘mydb’ do
connection mysql_connection_info
action :create
end

Create tables

mysql_database ‘mydb’ do
connection mysql_connection_info
sql { ::File.open("#{$mydir}/mytables.sql").read }
action :query
end

Create user

mysql_database_user ‘myuser’ do
connection mysql_connection_info
password 'mypass’
action :create
end

Grant privileges to user

mysql_database_user ‘myuser’ do
connection mysql_connection_info
password 'mypass’
database_name 'mydb’
host '%'
privileges [:all]
action :grant
end

Hope this helps.

On Mon, Oct 21, 2013 at 6:43 AM, Vladimir Skubriev
skubriev@cvisionlab.comwrote:

I created a redmine cookbook, that setups redmine.

First of all my cookbook installing mysql and creating a apporative
database redmine:

See my first recipe in a runlist of redmine server names database.rb:

Setup mysql client packages - need for mysql::server recipe

include_recipe “mysql::client”

If mysql server is not installed.

if ! ::File.exists?("/usr/sbin/**mysqld") then

# Fix bug in recipe mainline. When we use credmine::purgeall and the

rerun chef-client error occurs: "No such file or directory"
directory “/etc/mysql” do
owner "mysql"
group "mysql"
mode 0700
action :create
end

# Setup security
node.set['mysql']['remove_**test_database'] = true

# Normally, root should only be allowed to connect from 'localhost'.

This ensures that someone cannot guess at the root password from the
network.
node.set[‘mysql’][‘remove_**anonymous_users’] = true

# If true Sets root access from '%'. If false deletes any

non-localhost root users.
node.set[‘mysql’][‘allow_**remote_root’] = true

# Setup mysql server by security parameters seted in

credmine::_loaddatabags
include_recipe “mysql::server”

# Prepare chef ruby environment(install some gems) for using LWRP at

this machine
include_recipe “database::mysql”

# Restart mysql server with new parameters.
service "mysql" do
  action [ :enable, :restart ]
end

end

Using database cookbook idemponent LWRP’s

mysql_connection_info = {:host => “localhost”, :username => “root”,
:password => node[‘mysql’][‘server_root_**password’]}

Using database cookbook ::: to create redmine database

mysql_database node.run_state[‘redmine-mysql-**name’] do
connection mysql_connection_info
action :create
end

At the last action mysql_database node.run_state[‘redmine-mysql-**name’]
do recipe stops with error:

[2013-10-21T13:38:34+00:00] INFO: Processing mysql_database[redmine]
action create (credmine::database line 42)

============================================================

Error executing action create on resource ‘mysql_database[redmine]’
============================================================

Mysql::Error

Access denied for user ‘root’@‘localhost’ (using password: YES)

Cookbook Trace:

/var/chef/cache/cookbooks/**database/libraries/provider_**database_mysql.rb:83:in
new' /var/chef/cache/cookbooks/**database/libraries/provider_**database_mysql.rb:83:indb’
/var/chef/cache/cookbooks/**database/libraries/provider_**database_mysql.rb:78:in
exists?' /var/chef/cache/cookbooks/**database/libraries/provider_**database_mysql.rb:36:inaction_create’

Resource Declaration:

In /var/chef/cache/cookbooks/**credmine/recipes/database.rb

42: mysql_database node.run_state[‘redmine-mysql-**name’] do
43: connection mysql_connection_info
44: action :create
45: end
46:

Compiled Resource:

Declared in /var/chef/cache/cookbooks/**credmine/recipes/database.rb:**42:in

`from_file’

mysql_database(“redmine”) do
provider Chef::Provider::Database::Mysql
action [:create]
retries 0
retry_delay 2
database_name "redmine"
cookbook_name "credmine"
recipe_name "database"
connection {:host=>“localhost”, :username=>“root”, :password=>"

ahgdfyasrbiywerfybweufy"}
end

[2013-10-21T13:38:35+00:00] ERROR: Running exception handlers
[2013-10-21T13:38:35+00:00] FATAL: Saving node information to
/var/chef/cache/failed-run-**data.json
[2013-10-21T13:38:35+00:00] ERROR: Exception handlers complete
[2013-10-21T13:38:35+00:00] FATAL: Stacktrace dumped to
/var/chef/cache/chef-**stacktrace.out
[2013-10-21T13:38:35+00:00] FATAL: Mysql::Error: mysql_database[redmine]
(credmine::database line 42) had an error: Mysql::Error: Access denied for
user ‘root’@‘localhost’ (using password: YES)

I changed password in log to (ahgdfyasrbiywerfybweufy) of course instead
of true pass.

And I need to insert my own reset password code before mysql_database
node.run_state[‘redmine-mysql-**name’] do
Like this:

TODO may be off this recipe

bash “change_mysql_root_password” do
cwd "/tmp"
code <<-EOH
mysql -u root --password=#{node[‘mysql’][’**server_root_password’]}
mysql -e ‘show databases;’> /dev/null
if ! [ ? -eq 0 ]; then service mysql stop; if ! [ ? -eq 0 ]; then exit 1000;
fi;
sleep 3
mysqld_safe --skip-grant-tables
–socket=/tmp/mysqld_safe.**socket --pid-file=/tmp/mysqld_safe.**pid

/dev/null &
echo “use mysql;” > /tmp/mysql_flush_root.sql
echo "update user set password=PASSWORD(’#{node[’**
mysql’][‘server_root_password’**]}’) where User=‘root’; " >>
/tmp/mysql_flush_root.sql
# Update privileges in memmory, because if we don’t do
this old password be valied instead of new.
echo “flush privileges;” >> /tmp/mysql_flush_root.sql
sleep 5
mysql --socket=/tmp/mysqld_safe.**socket <
/tmp/mysql_flush_root.sql
rm /tmp/mysql_flush_root.sql
kill -TERM $(cat /tmp/mysqld_safe.pid);
sleep 2
service mysql start
else exit 0;
fi
EOH
end


This recipe verifies the password that I set into a variable
node[‘mysql’][‘server_root_**password’] earlier in _loaddatabags recipe.
Which is running before this database creation of course.

Well I am not understand how I can check why lwrp don’t setup a mysql with
needed password and don’t use this smart hack ))) ?


Best regards,

CVision Lab System Administrator
Vladmir Skubriev


Best regards, Dmitriy V.


#3

On 10/21/2013 08:56 PM, DV wrote:

Not sure what’s causing this error for you, but here’s how I install
MySQL in my recipe. I’m using Chef server, not Chef solo.

In my cookbook’s metadata.rb:

depends "database"
depends “mysql”

In my cookbook’s recipe (note that I didn’t put these under any "if"
statement):

Install MySQL

include_recipe "mysql::server"
include_recipe "mysql::client"
include_recipe “database::mysql”

Create database

mysql_connection_info = {:host => ‘localhost’, :username => ‘root’,
:password => node[:mysql][:server_root_password]}
mysql_database ‘mydb’ do
connection mysql_connection_info
action :create
end

Create tables

mysql_database ‘mydb’ do
connection mysql_connection_info
sql { ::File.open("#{$mydir}/mytables.sql").read }
action :query
end

Create user

mysql_database_user ‘myuser’ do
connection mysql_connection_info
password 'mypass’
action :create
end

Grant privileges to user

mysql_database_user ‘myuser’ do
connection mysql_connection_info
password 'mypass’
database_name 'mydb’
host '%'
privileges [:all]
action :grant
end

Hope this helps.

On Mon, Oct 21, 2013 at 6:43 AM, Vladimir Skubriev
<skubriev@cvisionlab.com mailto:skubriev@cvisionlab.com> wrote:

I created a redmine cookbook, that setups redmine.

First of all my cookbook installing mysql and creating a
apporative database redmine:

See my first recipe in a runlist of redmine server names database.rb:

# Setup mysql client packages - need for mysql::server recipe
include_recipe "mysql::client"

# If mysql server is not installed.
if ! ::File.exists?("/usr/sbin/mysqld") then

    # Fix bug in recipe mainline. When we use credmine::purgeall
and the rerun chef-client error occurs: "No such file or directory"
    directory "/etc/mysql" do
        owner "mysql"
        group "mysql"
        mode 0700
        action :create
    end

    # Setup security
    node.set['mysql']['remove_test_database'] = true

    # Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at the root
password from the network.
    node.set['mysql']['remove_anonymous_users'] = true

    # If true Sets root access from '%'. If false deletes any
non-localhost root users.
    node.set['mysql']['allow_remote_root'] = true

    # Setup mysql server by security parameters seted in
credmine::_loaddatabags
    include_recipe "mysql::server"

    # Prepare chef ruby environment(install some gems) for using
LWRP at this machine
    include_recipe "database::mysql"

    # Restart mysql server with new parameters.
    service "mysql" do
      action [ :enable, :restart ]
    end

end

# Using database cookbook idemponent LWRP's
mysql_connection_info = {:host => "localhost", :username =>
"root", :password => node['mysql']['server_root_password']}

# Using database cookbook ::: to create redmine database
mysql_database node.run_state['redmine-mysql-name'] do
  connection mysql_connection_info
  action :create
end
------------------

At the last action mysql_database
node.run_state['redmine-mysql-name'] do recipe stops with error:

[2013-10-21T13:38:34+00:00] INFO: Processing
mysql_database[redmine] action create (credmine::database line 42)

================================================================================
Error executing action `create` on resource 'mysql_database[redmine]'
================================================================================

Mysql::Error
------------
Access denied for user 'root'@'localhost' (using password: YES)

Cookbook Trace:
---------------
/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:83:in
`new'
/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:83:in
`db'
/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:78:in
`exists?'
/var/chef/cache/cookbooks/database/libraries/provider_database_mysql.rb:36:in
`action_create'

Resource Declaration:
---------------------
# In /var/chef/cache/cookbooks/credmine/recipes/database.rb

 42: mysql_database node.run_state['redmine-mysql-name'] do
 43:   connection mysql_connection_info
 44:   action :create
 45: end
 46:

Compiled Resource:
------------------
# Declared in
/var/chef/cache/cookbooks/credmine/recipes/database.rb:42:in
`from_file'

mysql_database("redmine") do
  provider Chef::Provider::Database::Mysql
  action [:create]
  retries 0
  retry_delay 2
  database_name "redmine"
  cookbook_name "credmine"
  recipe_name "database"
  connection {:host=>"localhost", :username=>"root",
:password=>"ahgdfyasrbiywerfybweufy"}
end

[2013-10-21T13:38:35+00:00] ERROR: Running exception handlers
[2013-10-21T13:38:35+00:00] FATAL: Saving node information to
/var/chef/cache/failed-run-data.json
[2013-10-21T13:38:35+00:00] ERROR: Exception handlers complete
[2013-10-21T13:38:35+00:00] FATAL: Stacktrace dumped to
/var/chef/cache/chef-stacktrace.out
[2013-10-21T13:38:35+00:00] FATAL: Mysql::Error:
mysql_database[redmine] (credmine::database line 42) had an error:
Mysql::Error: Access denied for user 'root'@'localhost' (using
password: YES)

I changed password in log to (ahgdfyasrbiywerfybweufy) of course
instead of true pass.

And I need to insert my own reset password code before
mysql_database node.run_state['redmine-mysql-name'] do
Like this:

# TODO may be off this recipe
bash "change_mysql_root_password" do
  cwd "/tmp"
  code <<-EOH
                mysql -u root
--password=#{node['mysql']['server_root_password']} mysql -e 'show
databases;'> /dev/null
                if ! [ $? -eq 0 ]; then
                    service mysql stop; if ! [ $? -eq 0 ]; then
exit 1000; fi;
                    sleep 3
                    mysqld_safe --skip-grant-tables
--socket=/tmp/mysqld_safe.socket --pid-file=/tmp/mysqld_safe.pid
>/dev/null &
                    echo "use mysql;" > /tmp/mysql_flush_root.sql
                    echo "update user set
password=PASSWORD('#{node['mysql']['server_root_password']}')
where User='root'; " >> /tmp/mysql_flush_root.sql
                    # Update privileges in memmory, because if we
don't do this old password be valied instead of new.
                    echo "flush privileges;" >>
/tmp/mysql_flush_root.sql
                    sleep 5
                    mysql --socket=/tmp/mysqld_safe.socket <
/tmp/mysql_flush_root.sql
                    rm /tmp/mysql_flush_root.sql
                    kill -TERM $(cat /tmp/mysqld_safe.pid);
                    sleep 2
                    service mysql start
                else exit 0;
                fi
  EOH
end
------
This recipe verifies the password that I set into a variable
node['mysql']['server_root_password'] earlier in _loaddatabags
recipe. Which is running before this database creation of course.

Well I am not understand how I can check why lwrp don't setup a
mysql with needed password and don't use this smart hack ))) ?




-- 
Best regards,

CVision Lab System Administrator
Vladmir Skubriev


Best regards, Dmitriy V.
Thanks. I try this in separate branch. May be it helps me.


Best regards,

CVision Lab System Administrator
Vladmir Skubriev


#4

On 10/21/2013 08:56 PM, DV wrote:

Not sure what’s causing this error for you, but here’s how I install
MySQL in my recipe. I’m using Chef server, not Chef solo.

In my cookbook’s metadata.rb:

depends "database"
depends “mysql”

In my cookbook’s recipe (note that I didn’t put these under any "if"
statement):

Install MySQL

include_recipe "mysql::server"
include_recipe "mysql::client"
include_recipe “database::mysql”

Create database

mysql_connection_info = {:host => ‘localhost’, :username => ‘root’,
:password => node[:mysql][:server_root_password]}
mysql_database ‘mydb’ do
connection mysql_connection_info
action :create
end

Create tables

mysql_database ‘mydb’ do
connection mysql_connection_info
sql { ::File.open("#{$mydir}/mytables.sql").read }
action :query
end

Create user

mysql_database_user ‘myuser’ do
connection mysql_connection_info
password 'mypass’
action :create
end

Grant privileges to user

mysql_database_user ‘myuser’ do
connection mysql_connection_info
password 'mypass’
database_name 'mydb’
host '%'
privileges [:all]
action :grant
end

Hope this helps.

I found the reason why not work.

Thak you this some helps to me )

See my last letters )


Best regards,

CVision Lab System Administrator
Vladmir Skubriev