I’ve been managing Virtualmin servers for many years and am comfortable with most aspects except the deeper webmin modules and command-line stuff. Recently I tried importing a WordPress database from a different server and encountered a problem with the SQL character set being “UTF8MB4” rather than just good old UTF8. Apparently this is because I have an older MySQL installed.
Is there any simple way I can do this with my limited technical ability? (would be great if you could upgrade PHP or mySQL as normal system upgrades which are done via the update packages page)
Very risky as converting to UTF-8 may cause loss of data, as MB4 is coded on 4 bytes while UTF-8 is on 3 bytes, you need backups in case the 4th byte is lost during conversion to mere UTF8.
ALTER DATABASE yourdatabasename CHARACTER SET utf8 COLLATE utf8_unicode_ci
(source http://dba.stackexchange.com/questions/94789/how-to-convert-a-mysql-database-from-utf8mb4-to-utf8 )
(and http://stackoverflow.com/questions/30074492/what-is-the-difference-between-utf8mb4-and-utf8-charsets-in-mysql )
Can’t you rather force-add compatibility to MB4 in your new server?
Hi Oliver, I did solve the conversion problem with an excellent plugin (WP-MIGRATE-DB); my older SQL did accept the new output. I have to wait for DNS to propagate before I can see if the new DB works. It’s not too risky a case because it’s a very small WP install that I could rebuild manually if I needed to.
However would still appreciate any help or pointers as to how to upgrade MySQL to the most recent version. (also does PHP or PhpMyAdmin also have to be upgraded as well?)
Oliver is right, upgrading MySQL is risky and you didnt say why you need never version. With PHP is relatively easy to upgrade if you know what are you doing but MySQL is a whole different story.
...a problem with the SQL character set being "UTF8MB4" rather than just good old UTF8. Apparently this is because I have an older MySQL installed.
UTF8MB4 was introduced with MySQL 5.5 and that was before 7 years. If this is true you have much bigger problem than upgrading MySQL as your OS is way too old to keep it active and probably have more holes than swiss cheese.
I suspect you didnt change default settings with old DB and you keep the defaults what comes with UTF8.