The mysql_user module requires the .my.cnf file to have the new root credentials stored before it can be used to update the passwords of the other root users. Otherwise the task fails with error msg: Using password: NO
The .my.cnf template task should be before the mysql_user task
From the mysql_user docs
Both login_password and login_user are required when you are passing credentials. If none are present, the module will attempt to read the credentials from ~/.my.cnf
The mysql_user module requires the .my.cnf file to have the new root credentials stored before it can be used to update the passwords of the other root users. Otherwise the task fails with error msg: Using password: NO
The .my.cnf template task should be before the mysql_user task
From the mysql_user docs