We are happy to announce that Vesta is back under active development as of 25 February 2024. We are working on v1 candidate and expect to engage more with the community over the coming months. We are committed to open source, and we encourage contributors to help us build the future of Vesta.
Ubuntu 16.04 up/down grade Mysql 5.7 to Mariadb 10
Ubuntu 16.04 up/down grade Mysql 5.7 to Mariadb 10
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;
Re: Ubuntu 16.04 up/down grade Mysql 5.7 to Mariadb 10
I've just completed this on my production server and it worked as expected. Mysql memory usage has dropped to around half, and I'm no longer getting the 'oom' messages and swapping that I experienced with mysql.
A couple of further pointers. Mariadb 10 doesn't work very well with systemd: it seems very confused and neither systemctl restart mysql.service and /etc/init.d/mysql restart work properly (for example). The solution to this was to upgrade to Mariadb 10.1 by adding the mariadb repositories. Make sure to fully stop mysql service first.
Secondly, after running the upgrade, be sure to run 'mysql_upgrade' which fixes a couple of issues with the table format, and will stop you getting errors in your error.log.
A couple of further pointers. Mariadb 10 doesn't work very well with systemd: it seems very confused and neither systemctl restart mysql.service and /etc/init.d/mysql restart work properly (for example). The solution to this was to upgrade to Mariadb 10.1 by adding the mariadb repositories. Make sure to fully stop mysql service first.
Secondly, after running the upgrade, be sure to run 'mysql_upgrade' which fixes a couple of issues with the table format, and will stop you getting errors in your error.log.
Re: Ubuntu 16.04 up/down grade Mysql 5.7 to Mariadb 10
Third ...
Just did my first server reboot since switching to mariadb, and was alarmed that the database didn't come up.
journalctl -xe gave me an error about apparmor, which led me on a search around the internet.
Solution was to disable apparmor for mysql service.
ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
Reloading the apparmor service didn't seem effective, but another reboot after this and all is running again. Seems that Ubuntu 16.04 has a load of changes under the hood.
Just did my first server reboot since switching to mariadb, and was alarmed that the database didn't come up.
journalctl -xe gave me an error about apparmor, which led me on a search around the internet.
Solution was to disable apparmor for mysql service.
ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/
Reloading the apparmor service didn't seem effective, but another reboot after this and all is running again. Seems that Ubuntu 16.04 has a load of changes under the hood.
-
- Posts: 4
- Joined: Sun May 21, 2017 11:07 am
Re: Ubuntu 16.04 up/down grade Mysql 5.7 to Mariadb 10
how to install Mariadb 10.2? (fresh VestaCP with Mysql5.7)
-
- Posts: 4
- Joined: Sun May 21, 2017 11:07 am
Re: Ubuntu 16.04 up/down grade Mysql 5.7 to Mariadb 10
need step by step guide for this :(