Link to home
Create AccountLog in
Avatar of arghosrho
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?
ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of arghosrho
arghosrho

ASKER

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
can anybody help me please? the situation is becoming dire
After searching and asking arround looks like MySQL Workbench includes mysqluserclone utility
https://fossies.org/windows/misc/mysql-utilities-1.6.5.zip/mysql-utilities-1.6.5/docs/man/mysqluserclone.1
<<
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.
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.