Ubuntu 16.04 up/down grade Mysql 5.7 to Mariadb 10
Posted: Thu Feb 09, 2017 10:51 am
OK, so I was chatting about this in another thread which was dealing with a different mysql version, so I thought I'd move the discussion to another one with a more informative title.
The issue is this. Ubuntu 16.04 installs mysql 5.7 by default when you install mysql. There are some rather large changes in between 5.6 and 5.7, particularly in the mysql.user table, which mean that if you want to install mariadb 10.x, which is based on mysql 5.6, then the normal 'drop-in replacement' doesn't work.
During installation it will detect mysql 5.7 and then move the data directory from /var/lib/mysql to /var/lib/mysql5.7 and then install itself in a clean /var/lib/mysql directory. The root password is preserved. However any other data is lost.
You can dump data from mysql before you upgrade and try to import into mariadb, but when you do that, any apps which depend on the database will be unable to authenticate. eg roundcube, any wordpress installations you have etc, etc.
I think I may have found a solution. On the mysql website there are a number of commands you can run on the database to roll back these changes before upgrading. I ran these on a test server and it seemed to work. I'll do some more detailed testing and then try to upgrade my main server. Wish me luck.
Here's what I have:
Now make manual changes as necessary from my.cnf to /etc/maria.cnf
The contents of rollback.sql:
From https://dev.mysql.com/doc/refman/5.7/en ... eries.html
So that worked on my test server.
The issue is this. Ubuntu 16.04 installs mysql 5.7 by default when you install mysql. There are some rather large changes in between 5.6 and 5.7, particularly in the mysql.user table, which mean that if you want to install mariadb 10.x, which is based on mysql 5.6, then the normal 'drop-in replacement' doesn't work.
During installation it will detect mysql 5.7 and then move the data directory from /var/lib/mysql to /var/lib/mysql5.7 and then install itself in a clean /var/lib/mysql directory. The root password is preserved. However any other data is lost.
You can dump data from mysql before you upgrade and try to import into mariadb, but when you do that, any apps which depend on the database will be unable to authenticate. eg roundcube, any wordpress installations you have etc, etc.
I think I may have found a solution. On the mysql website there are a number of commands you can run on the database to roll back these changes before upgrading. I ran these on a test server and it seemed to work. I'll do some more detailed testing and then try to upgrade my main server. Wish me luck.
Here's what I have:
Code: Select all
mkdir migrate
cp rollback.sql migrate
cd migrate
cp /etc/mysql/my.cnf ./
# dump db before changes, to be safe
mysqldump --all-databases > all.sql
mysql < rollback.sql
mysqldump --all-databases > all-rolledback.sql
service mysql stop
# another backup!
tar -zcvf mysql.bak.tar.gz /var/lib/mysql
apt-get remove mysql-server mysql-client
apt-get install mariadb-server mariadb-client
mysql < all-rolledback.sql
service mysql restart
mysql --version
The contents of rollback.sql:
From https://dev.mysql.com/doc/refman/5.7/en ... eries.html
Code: Select all
ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.procs_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.columns_priv MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.user MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.db MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.procs_priv MODIFY User char(16) binary DEFAULT '' NOT NULL;
ALTER TABLE mysql.user ADD Password char(41) character set latin1 collate latin1_bin NOT NULL default '' AFTER user;
UPDATE mysql.user SET password = authentication_string WHERE LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
UPDATE mysql.user SET authentication_string = '' WHERE LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
ALTER TABLE mysql.help_category ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_keyword ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_relation ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_topic ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_leap_second ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_name ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition_type ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.plugin ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.servers ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE utf8_bin DEFAULT 'mysql_native_password';
ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE utf8_bin DEFAULT '';
DROP DATABASE sys;