Chef-client change MySQL root password using python and template

After installing Mysql you need to ALTER root password as part of mysql_secure_installation process. |
Let’s automate it with python (because official mysql cookbook seems to have some problems with new software versions).

Notice: chef is running in local mode.

Software list

mysql Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)
Python 3.6.5
CentOS Linux release 7.4.1708 (Core)V
mysql Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)

Create chef recipe to install Python 3.6 with needed dependencies

remote_file '/tmp/ius-release.rpm' do
source 'https://centos7.iuscommunity.org/ius-release.rpm'
owner 'root'
group 'root'
mode '0755'
action :create
end

execute 'ius-release-rpm' do
command '/usr/bin/rpm -i /tmp/ius-release.rpm'
ignore_failure true
end

execute 'yum_update' do
command 'yum update'
ignore_failure true
end

['python36u', 'python36u-libs', 'python36u-devel', 'python36u-pip'].each do |package|
yum_package package do
action :install
end
end

execute 'install_pymysql' do
command '/usr/bin/pip3.6 install PyMySQL'
ignore_failure true
end

Create chef template file

Name it mysql_querry.erb and place it in folder /templates of your cookbook

#!/usr/bin/python3.6
import pymysql.cursors

def con_mysql():
connection = pymysql.connect(host='localhost',
user='root',
password='<%= @password %>',
db='mysql',
charset='utf8mb4',
cursorclass=pymysql.cursors.DictCursor)
return connection

def change_root_pass():

connection = con_mysql()
cursor = connection.cursor()
sql = "ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPassword493';"
cursor.execute(sql)
connection.commit()
connection.close()

def main():
change_root_pass()

Automate chef to change MySQL default passwords after first installation

Chef recipe that reads temporary password created after MySQL installation and pass it to python script via chef’s template

if File.exist?('/root/temp_pass_mysql.txt')
password = ::File.read('/root/temp_pass_mysql.txt').chomp
template '/root/mysql_querry.py' do
source 'mysql_querry.erb'
owner 'root'
group 'root'
mode '0755'
variables(
:password => password,
)
end
end

execute 'change_mysql_root' do
command '/usr/bin/python3.6 ~/mysql_querry.py'
ignore_failure true
end