Hi,

As this issue is open for a year now and still a big problem to all first-time Galera users, increasing priority.

As far as I know there is no work-around, except not uing mysql_secure_installation. Instead, you could use the corresponding GRANT statements.

Thanks,
Michaël

Hi Michaël,
We have been able to run mysql_secure_installation in non-production with Galera - simply by running it on each Galera node (and we did not run this script on the first node in the cluster before adding more nodes, we did it after..) So far everything seems fine..but maybe not according to your comment in this JIRA ticket?

Based on what you are saying and what is presented here: https://mariadb.com/kb/en/library/mysql_secure_installation/
Is this not a recommended approach in production simply because someone may neglect to run it on all nodes (said first-time user incorrectly assuming mysql.user changes would not be transported to the other nodes), or is there some other issue? I'm a little confused since I would think it is preferable to run the types of commands in the script, as opposed to not using mysql_secure_installation e.g. do_query "DELETE FROM mysql.db WHERE Db='test' OR Db='test
_%'"

Thanks!

mysql_secure_installation.cc from mysql has most of thse fixed.

Otherwise/alternately we can do queries like:

set_root_password

SELECT CONCAT('ALTER USER IF EXISTS ',

                GROUP_CONCAT(

                    CONCAT('\'', User, '\'@\'', Host, '\'', ' IDENTIFIED BY '$esc_pass')

                )

            ) INTO @str

            FROM mysql.user

            WHERE User='root'

        PREPARE stmt FROM @str;

        EXECUTE stmt;

        DROP PREPARE stmt;

remove_remote_root

        SELECT CONCAT('DROP USER IF EXISTS ',

                GROUP_CONCAT(

                    CONCAT('\'', User, '\'@\'', Host, '\'')

                )

            ) INTO @str

            FROM mysql.user

            WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

        PREPARE stmt FROM @str;

        EXECUTE stmt;

        DROP PREPARE stmt;

Suggested SQL fragments https://github.com/vitessio/vitess/pull/7318/files