Copy MySQL User but change HostName

Question

I am migrating to a docker MariaDB setup and all my users were created w/ "Localhost". From what I read and tested ironically '%' excludes localhost. I want to copy my existing users adding a second entry utilizing '%' as the hostname. Any suggestions?

Answer 1

You have to call SHOW CREATE USER 'username'@'localhost' and SHOW GRANTS FOR 'username'@'localhost' for all the users fetched by

SELECT user, host 
  FROM mysql.user
 WHERE host = 'localhost'

and to replace all 'localhost' entries by '%' in the returned statements.

Answer 2

You can use mariadb-dump (previously called mysqldump) to dump all the users and their grants to an SQL file:

mariadb-dump --system=users > /tmp/users.sql

If you really want to copy all localhost users to % users, then use the search-and-replace feature in your favourite GUI editor. Or, in a Unix-like environment you can do something like:

 sed -i 's/localhost/%/g' /tmp/users.sql