Migrate MySQL User from MySQL 5.6 to 8.0.21:
If you are migrating MySQL from 5.6 to 8.0.21 new env. (Not in place upgrade) then this is one of the option to migrate mysql users.
$ mysql -uroot -p -S/mysql/mysql.sock --skip-column-names --execute \
"SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | tee step1
$ mysql -uroot -p -S/mysql/mtsprd/data/mtsprd.sock --skip-column-names < step1 | tee step2
$ sed -e "s/ IDENTIFIED BY PASSWORD '.*'//" -e "s/$/;/" < step2 | tee step3
Note: Interesting use of function CONCAT, QUOTE, Linux utility tee, and sed makes it easy to generate script.
Generate dump file from MySQL 5.6.30:
$ mysqldump -uroot -p --set-gtid-purged=OFF -S /mysql/mysql.sock --databases <db1> \<db2> --events --triggers --routines --force --ignore-table=<table1> \
--ignore-table=<table2> > /mysql/dump.sql
Note:
--force, -f - Ignore all errors; continue dump process
--ignore-table=<table1> - If view reference to invalid table(s) or column(s)
Restore Dump:
mysql -uroot -p -S/mysql/mts/mts.sock < dump.sql
Comments
Post a Comment