formula does not set root password

When using the root_password option in the pillar.example to set the root password, I am unable to login as root using the root password:

sudo salt-call pillar.get mysql
local:
    ----------
    server:
        ----------
        root_password:
            xxx
        root_user:
            root

The login attempt:

mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

The output from debconf:

----------
          ID: mysql_debconf
    Function: debconf.set
        Name: mysql-server
      Result: True
     Comment:
     Started: 14:59:59.839264
    Duration: 1488.64 ms
     Changes:
              ----------
              mysql-server/root_password:
                  (password hidden)
              mysql-server/root_password_again:
                  (password hidden)
              mysql-server/start_on_boot:
                  true

[Sylvain303]

at installation of the mysql.server state?
Please note, that you currently cant change root password that way, if I understand well the formula. I need to test it today.

Did you try to log without password?
Which operating system?
MariaDB or mysql?

Could you post lookup part of the pillar?

[lizard1990]

when i install the mysql.5.7.4 use rpm package on CentOS7.2,I encounted the same error,i search a lot.found a random password was genarated in /var/lib/mysql/.mysql_secret,so how can i get that in sls File and log in?

[Sylvain303]

@lizard1990

in server.sls the state or setting up root password is checking OS family, does it match for CentOS?

{% elif os_family == 'RedHat' or 'Suse' %}
mysql_root_password:
  cmd.run:
    - name: mysqladmin --user {{ mysql_root_user }} password '{{ mysql_root_password|replace("'", "'\"'\"'") }}'
    - unless: mysql --user {{ mysql_root_user }} --password='{{ mysql_root_password|replace("'", "'\"'\"'") }}' --execute="SELECT 1;"
    - require:
      - service: mysqld

os_family computed that way:

{% set os_family = salt['grains.get']('os_family', None) %}

what do you get if you execute this on the minion?

salt-call grains.get os_family

[lizard1990]

hi,
thanks for the reply,
i use the CentOS 7.2.1511,
the infomation i get is this:
[salt.state ][ERROR ][15501] {'pid': 15755, 'retcode': 1, 'stderr': "\x07mysqladmin: connect to server at 'localhost' failed\nerror: 'Access denied for user 'root'@'localhost' (using password: NO)'", 'stdout': ''}
,i found the mysql after 5.7 gen a random pass when installed use the offical cite's RPM package ,
so the root's password is not null or empty ,when the sls statement below use the mysqladmin function without a pass is failed,am i right?

lizardchao@126.com

From: Sylvain303
Date: 2016-08-03 15:27
To: saltstack-formulas/mysql-formula
CC: Andy; Mention
Subject: Re: [saltstack-formulas/mysql-formula] formula does not set root password (#124)
@lizard1990
in server.sls the state or setting up root password is checking OS family, does it match for CentOS?
{% elif os_family == 'RedHat' or 'Suse' %}
mysql_root_password:
cmd.run:
- name: mysqladmin --user {{ mysql_root_user }} password '{{ mysql_root_password|replace("'", "'"'"'") }}'
- unless: mysql --user {{ mysql_root_user }} --password='{{ mysql_root_password|replace("'", "'"'"'") }}' --execute="SELECT 1;"
- require:
- service: mysqld
os_family computed that way:
{% set os_family = salt['grains.get']('os_family', None) %}

what do you get if you execute this on the minion?
salt-call grains.get os_family
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[lizard1990]

hi,
when i execute the 'salt-call grains.get os_family on the minion'
the result is
[root@stack3 ~]# salt-call grains.get os_family
local:
RedHat

lizardchao@126.com

From: Sylvain303
Date: 2016-08-03 15:27
To: saltstack-formulas/mysql-formula
CC: Andy; Mention
Subject: Re: [saltstack-formulas/mysql-formula] formula does not set root password (#124)
@lizard1990
in server.sls the state or setting up root password is checking OS family, does it match for CentOS?
{% elif os_family == 'RedHat' or 'Suse' %}
mysql_root_password:
cmd.run:
- name: mysqladmin --user {{ mysql_root_user }} password '{{ mysql_root_password|replace("'", "'"'"'") }}'
- unless: mysql --user {{ mysql_root_user }} --password='{{ mysql_root_password|replace("'", "'"'"'") }}' --execute="SELECT 1;"
- require:
- service: mysqld
os_family computed that way:
{% set os_family = salt['grains.get']('os_family', None) %}

what do you get if you execute this on the minion?
salt-call grains.get os_family
—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread.

[Sylvain303]

@lizard1990, so it means that your OS is detected as RedHat, I only have Debian nearby, I can't test the formula. But we can still diagnose. The call to mysqladmin in your error message is conform to your OS detection.

Try to catch part of the output in the following command on the minion. We are looking for the command which install the mysql package, it depends of the lookup section in your pillar, and the default you've kept from default.yaml and also the full mysqladmin command line used by salt.

salt-call -ldebug state.apply mysql.server

Also you should check your pillar values, you can check (on the minion) Remove or alter private data, passwords, etc. if you want to share.

salt-call pillar.get mysql --out=yaml

i found the mysql after 5.7 gen a random pass when installed use the offical cite's RPM package , so the root's password is not null or empty ,when the sls statement below use the mysqladmin function without a pass is failed,am i right?

Yes, did you manage to connect you mysql on the minion from command line?

See example from Debian in my ticket #120 you will need to adapt to your OS. That may helps.

[lizard1990]

yes,i connect mysql on the minion from command line,it got the same error,the mysql need a password,
i try the same Formulas on CentOS6.7 use the mysql5.1.73 version,it works totally fine,so i think it's the Version problem,your help is appreciated,thanks Sylvain303,Then I'll use this version.:)

[Sylvain303]

@lizard1990, so to sum it up, it doesn't works with mysql.5.7.4 on CentOS 7.2.1511, but works with mysql5.1.73 on CentOS6.7, so you downgraded your OS version, right?

If I understand it well, you could use, on mysql.5.7.4 on CentOS 7.2.1511, the pass generated in /var/lib/mysql/.mysql_secret to connect to the server, right?

This command may allow you to connect with root privileges to the freshly installed mysql:

mysql --defaults-file=/var/lib/mysql/.mysql_secret mysql

If the file is in the same format as my.cnf.

This file seems to come from mysql_install_db — Initialize MySQL Data Directory

[Routhinator]

Confirming this bug on CentOS 7.2

This formula cannot configure MySQL on 7.2 it seems.

[Routhinator]

Yep. The new RPM for CentOS 7.2 generates a random password and logs it.

We'll have to get the random pass from the logs and use it to change the pass in order for this formula to work with this version again:

http://www.if-not-true-then-false.com/2010/install-mysql-on-fedora-centos-red-hat-rhel/

[Routhinator]

So I hit a snag with this, where the generated root password does not get logged.

However, I found a workaround that will fix this formula:

Regardless of platform, use --initialize for “secure by default” installation (that is, including generation of a random initial root password). In this case, the password is marked as expired and you will need to choose a new one. With the --initialize-insecure option, no root password is generated; it is assumed that you will assign a password to the account in timely fashion before putting the server into production use.

We just need to pass --initialize-insecure to the mysql process to allow salt to configure the initial password afterwards.

[Routhinator]

Ahh, in MySQL 5.7, database should be initialized with mysqld --initialize or mysqld --initilize-insecure instead of mysql_install_db

They've replaced the command, for the better.

But after having fully analyzed exactly what mysql_install_db was trying to do, it became obvious that there’s no way this would work reliably cross platform without another large command line tool.

It just was not the right approach.

What do you do in such cases?

You cut out the middle man.

mysql_install_db‘s primary job was to invoke the mysqld (MySQL server) binary in a special mode and pipe in a bunch of SQL scripts to it.

What if mysqld could instead do the job all on its own? Without the need for all of these extra SQL files? So you don’t have to carry them around and worry if they match the binary? Why not have mysqld bootstrap itself and eliminate all of this complexity?

This is how the new --initialize MySQL server option was born.

http://mysqlserverteam.com/initialize-your-mysql-5-7-instances-with-ease/

[Routhinator]

Ok, so I've nailed down the issue with this formula, it's the solution I'm having issues with. I've almost got it, and if someone can tell me how to make a state require another state, we'll be good.

The problem:

With Mysql 5.7, if the service detects that the database is not initialized on startup, it initializes it. As of MySQL 5.7, that is done with a random password generated for root. This password should be logged to the MySQL log, however on multiple tries with a Vagrant box, it was not logged anywhere.

This isn't an issue on Debian, as the password is set with debconf before the service is started.

On Arch, this formula is calling mysql_install_db - which will cause the same problem. To fix Arch, the formula needs to call mysqld --initialize-insecure instead.

On Redhat systems, the service absolutely cannot be started until the database is configured with this command, or we're hosed. There's no guaranteed way to retrieve the password afterwards.

To fix this I created a state that initialises the DB and added it to server.sls:

# Initialize mysql database with --initialize-insecure option before starting service so we don't get locked out.
mysql_initialize:
  cmd.run:
    - name: mysqld --initialize-insecure --user=mysql --basedir=/usr --datadir=/var/lib/mysql
    - user: root
    - creates: /var/lib/mysql/mysql/
    - require:
      - pkg: {{ mysql.server }}

So long as this gets called before the mysqld: state, it works flawlessly. However I cannot get the mysqld state to require this one. I've tried simply moving the state above the mysqld state and they ended up running together. If I try to define a require, it causes the mysqld state to fail, which at least allows the state to run first.

Does anyone know how to require this?

# Start Service
mysqld:
  service.running:
    - name: {{ mysql.service }}
    - enable: True
    - require:
      - cmd: mysql_initialize
    - watch:
      - pkg: {{ mysql.server }}
      - file: mysql_config
{% if "config_directory" in mysql and "server_config" in mysql %}
      - file: mysql_server_config
{% endif %}