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?

• danblack: which mariadb version?
• user1822: Why did you remove the mariadb tag?

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.

• Kondybas: @FreeSoftwareServers It's not a problem to update an user already having grants. But if you want to copy some user you have to copy his grants too. The easiest way to do that is to fetch SHOW CREATE USER and SHOW GRANTS FOR then substitute hostname by desired value and then run that statements. There is no native tool to do that at once.
• Dave: You might find my SQL User Backer tool of use. It will extract all of the users and grants and all you'd have to do is change localhost to % and load the modified files.

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