How to automate mysql_secure_installation in Chef

Question

Chef mysql recipe- in-order to setup a permanent password for root user in mysql, I did find a process which uses "bash" resource in recipe for running a bash script which automates all the steps which pop-up in the process. But after running the convergence it errors out

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

I understand it is because initially the temporary password is generated in mysqld.log files and I need to provide this temp passwd for running the mysql_secure_installation. But I couldn't find a way to include a step in the script where it can bring this temp passwd from the logs and use it in the script. Below is the script I'm currently running in the recipe.

root_password = node.set['mysql_user']['root']['password']
bash "mysql_secure_installation" do
  code <<-EOH
    mysql -u root -e "DELETE FROM mysql.user WHERE User='';"
    mysql -u root -e "DROP DATABASE test;"
    mysql -u root -e "DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';"
    mysql -u root -e "DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');"
    mysql -u root -e "SET PASSWORD FOR 'root'@'localhost' = PASSWORD('#{root_password}');" -D mysql
    mysql -u root -p#{root_password} -e "SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('#{root_password}');" -D mysql
    mysql -u root -p#{root_password} -e "SET PASSWORD FOR 'root'@'::1' = PASSWORD('#{root_password}');" -D mysql
    mysql -u root -p#{root_password} -e "FLUSH PRIVILEGES;"
  EOH
end

What `mysql` cookbook version and `mysql_server` resource are you using to install the MySQL server? AFAIK in the latest version of the cookbook you should use `initial_root_password` to set the root password. — zuazo, Mar 29 '16 at 17:04
I'm not using mysql community cookbook. I developed my custom cookbook based on the regular installation process on centos 6. — RahulK, Apr 01 '16 at 16:16

score 2 · Answer 1 · edited May 23 '17 at 12:14

First of all, your script will only work on the first convergence.

Second, your bash resource will only return the error of the last command (FLUSH PRIVILEGES). The other command errors will be ignored by the resource and the chef execution will continue. You need to use set -e or add a && between mysql commands to avoid this.

Anyway, I very much recommend you to try to use the official mysql cookbook for your task. It will make it simpler. You can use something like the following to install and set the root password:

mysql_service 'default' do
  port '3306'
  initial_root_password root_password
  action [:create, :start]
end

Keep in mind that this cookbook already takes many of your security measures out of the box:

UPDATE mysql.user SET #{password_column_name}=PASSWORD('#{root_password}')#{password_expired} WHERE user = 'root';
DELETE FROM mysql.user WHERE USER LIKE '';
DELETE FROM mysql.user WHERE user = 'root' and host NOT IN ('127.0.0.1', 'localhost');
FLUSH PRIVILEGES;
DELETE FROM mysql.db WHERE db LIKE 'test%';
DROP DATABASE IF EXISTS test ;

After that, you can also run your own SQL scripts using database and mysql2_chef_gem cookbooks if you want:

# Required by `database` cookbook MySQL resources:
mysql2_chef_gem 'default'

connection_info = {
  :host     => '127.0.0.1',
  :username => 'root',
  :password => root_password
}

mysql_database 'mysql_secure_installation' do
  connection connection_info
  database_name 'mysql'
  sql <<-EOH
    -- MY MYSQL SCRIPT HERE;
  EOH
  action :query
end

How to automate mysql_secure_installation in Chef

1 Answers1