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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
ASKER
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.
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.
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.
ASKER
am i supposed to run these commands in the Query window from my workbench?
if the answer is yes they are not working