Avatar of arghosrho
 asked on

Migrating users from mysql 5.6 to MYSQL 8

Dear experts.
i have a MYSQL server 5.6.41 running on windows server 2012.
im a the point of migrating it to another windows system with the new MYSQL 8.
i managed to export all the databases which is not an issue via workbench.
but how to i export all the usernames and privalages? its going to be an issue to recreate them all manually.
can anyone help please?
MySQL ServerWindows OSWindows Server 2012SQL

Avatar of undefined
Last Comment
David Favor

8/22/2022 - Mon

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

im sorry im quite a noob in MYSQL.
am i supposed to run these commands in the Query window from my workbench?
if the answer is yes they are not working

as you also know from the problem description im running on windows so the tools are going to work

My bad - I missed that being so used to run MySQL on  Linux...those commands are for Linux/UNIX world...let me check if Percona pt-show-grants works in Windows too
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy

can anybody help me please? the situation is becoming dire

After searching and asking arround looks like MySQL Workbench includes mysqluserclone utility
The following command shows all users on the local server in the most verbose output in CSV format:
shell> mysqluserclone −−source=root@localhost −−list −−format=csv −vvv

And you should be able to start the "shell" on windows via the "Plugins" as described here http://drcharlesbell.blogspot.com/2011/04/mysql-workbench-utilities.html

<<You can also launch the MySQL Utilities command window by clicking on the Plugins menu item and selecting Start Shell for MySQL Utilities.>>

Then you just run the mysqlclone command above.
David Favor

Icohan's way is most correct.

And there's a redneck way, if the correct way breaks for you.

1) stop both 5.6 + 8.0 instances

2) rsync /var/lib/mysql from your 5.6 instance to your 8.0 instance.

Also rsync over the root access file (which has database root user/pass, if one exists) + locate this file the correct place in you 8.0 config files.

3) Restart you 8.0 instance.

I use MariaDB, so this works every time, as MariaDB will simply look at current data format + convert all files to whatever version of MariaDB is running.

With MySQL... sigh... There's a reason why most Linux Distros have switched or will switch shortly to installing MariaDB instead of MySQL... because MariaDB works better, faster, etc...

Try Icohan's method + if you have problems try the brute force rsync method.

Suggestion: Start with latest MariaDB-10.3.9 (as of today), rather than MySQL 8.0 + you'll likely have far less problems over the life of your project.
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.