Mysql – Recovering MySQL 5.7 tables in MariaDB 10

mariadbMySQLrestore

I did a very foolish thing… I did not realize installing mariaDB would uninstall MYSQL 5.7 using apt-get. (Using Ubuntu 16.04).

So now I am trying to recover my data.

So far, I have read many guides and tried several things. I think this has gotten me into bad position, because the advice seems to be very version specific.

My most successful attempt is 1) Fresh install of MaraiDB. 2) copy * files from old sql directory to new. 3) Copy database directory myDatabase/ from old sql directory to new. 3) Run mysql_secure_installation 4) Configure my users, etc. etc.

This works except that I cannot run sql queries.

SELECT * FROM myDatabase.tableOne;

I get either:

SQL Error [1932] [42S02]: (conn:56) Table 'myDatabase.tableOne' doesn't exist in engine

OR

SQL Error [1146] [42S02]: (conn:56) Table 'myDatabase.tableTwos'

It seems that all tables give one of these two errors.

So far, I have read that mysqlcheck can fix this.

If I run

/var/lib/mysql$ mysqlcheck myDatabase
myDatabase.tableOne
Error    : Table 'myDatabase.tableOne' doesn't exist in engine
status   : Operation failed

I can't find any suggested solutions for fixing this. Although perhaps I went the wrong way from the beginning. Thanks for your help.

Best Answer

Before doing anything else I would recommend that you backup the old mysql data directory and the configuration file on /etc. So should something go wrong you don't loose your data.

Then I would check what specific MariaDB version of you have installed. As per MariaDB compatibility chart, you need MariaDB 10.2 to migrate from MySQL 5.7. Once you are sure you have the right version installed, you should refer to the migration doc:

https://mariadb.com/kb/en/library/migrating-to-mariadb/

Alternatively you could uninstall MariaDB and install MySQL again, just check that you are installing the latest 5.7 version available. For this you can download a deb package from the mysql downloads and install it manually or set up the mysql repository: https://dev.mysql.com/downloads/repo/apt/

Related Question