Moving from mysql 5.5 to mariadb 10

drdrake

Hi,

I am trying to move from mysql 5.5 to mariadb 10. What's the process? Is it enough as adding the repo and installing mariadb (which will remove mysql)? Is it risky this way?

Also does automysqlbackup work with mariadb?

PrincessOfCats

For maximum compatability, use mysqldump to dump the databases; you can then remove /var/lib/mysql, remove mysql, and install mariadb. You can then import the dumps into mariadb.

drdrake

@ALinuxNinja said:
For maximum compatability, use mysqldump to dump the databases; you can then remove /var/lib/mysql, remove mysql, and install mariadb. You can then import the dumps into mariadb.

Does automysqlbackup support mariadb 10?

PrincessOfCats

@drdrake said:

@ALinuxNinja said:
For maximum compatability, use mysqldump to dump the databases; you can then remove /var/lib/mysql, remove mysql, and install mariadb. You can then import the dumps into mariadb.

Does automysqlbackup support mariadb 10?

Seems to, except for https://bugs.launchpad.net/ubuntu/+source/automysqlbackup/+bug/1483942 which can be fixed by enabling proposed in trusty

sidewinder

Is it worth making the switch?

sirpumpkin

@ALinuxNinja said:
For maximum compatability, use mysqldump to dump the databases; you can then remove /var/lib/mysql, remove mysql, and install mariadb. You can then import the dumps into mariadb.

I'm also thinking about making the switch, but is that even necessary? As far as I know, you can simply uninstall MySQL and install Mariadb and it should automatically work as before. Obviously you should backup your databases before (but you should always do that )

drdrake

@sidewinder said:
Is it worth making the switch?

Yeah, mysql hasn't yet fixed the 0day vulnerability leaked before a few days.

rds100

From what i understand for the mysql vulnerability to be exploited, the mysql configuration file must be owned (or just writable) by the user under which mysqld is run.
Why on earth would anyone have their mysqld configuration file (or any other important configuration file) owned and writable by anything but root???

steny

...

sirpumpkin

At least on ubuntu 14.04 it seems as if the 0day vulnerability is not fixed yet in the mariadb version you can get from the repos. So I guess one would have to either wait or use the repos provided directly by mariadb?

PrincessOfCats

@sirpumpkin said:
At least on ubuntu 14.04 it seems as if the 0day vulnerability is not fixed yet in the mariadb version you can get from the repos. So I guess one would have to either wait or use the repos provided directly by mariadb?

Status: https://people.canonical.com/~ubuntu-security/cve/2016/CVE-2016-6662.html

sirpumpkin

So I just migrated from MySQL to MariaDB 5.5 from their repositories on Ubuntu 14.04.

Wordpress worked fine afterwards, however I had massive problems with owncloud (which would give me internal server errors on login) and JIRA, which thought it was being updated (and also gave an error message).

The OwnCloud error was:

[...] General error: 1665 Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.

I was able to fix both by adding

binlog_format = MIXED

to the my.cnf file, although I'm surprised I had to do this? Maybe somebody can shed some light on it, as I didn't really understand why there is a difference in binlog format between the current MySQL version from ubuntu 14.04 repos and MariaDB 5.5? My old my.cnf didn't have this line and everything worked just fine...

eva2000

sirpumpkin said: Maybe somebody can shed some light on it,

https://doc.owncloud.org/server/8.0/admin_manual/configuration_database/linux_database_configuration.html#db-binlog-label

ownCloud is currently using a TRANSACTION_READ_COMMITTED transaction isolation to avoid data loss under high load scenarios (e.g. by using the sync client with many clients/users and many parallel operations). This requires a disabled or correctly configured binary logging when using MySQL or MariaDB

sirpumpkin

@eva2000 Yeah, I know about this, but Ubuntu 14.04 ships with mysql-server version 5.5.52-0ubuntu0.14.04.1 currently.

Prior to MySQL 5.7.7, statement-based logging format was the default.

Source: http://dev.mysql.com/doc/refman/5.7/en/binary-log-formats.html

The default format, statement-based logging [...]

Source: https://mariadb.com/kb/en/mariadb/binary-log-formats/

So if in both cases, statement based logging was used by default, I don't really know why I had to manually change it to 'Mixed' when I migrated to Mariadb.

eva2000

quick search of owncloud github code shows they are setting tx_isolation level on a session basis not global so doesn't matter what you set globally ? https://github.com/owncloud/core/blob/5a058f6c0ef64ff0272fcdb58478f9c6611d6819/lib/private/DB/Connection.php#L141

parent::setTransactionIsolation(parent::TRANSACTION_READ_COMMITTED);

mehargags

MariaDB /PerconaDB are "Drop in" replacements for MySQL which means you can just install MariaDB on top of Mysql and it will replace your MySQL without a problem. While installing you will get prompts for Resetting your root password... just press enter to skip it (2-3 times I guess). Also preserve your my.cnf (default) and you should be good.