Rebuild database without reinstalling everything

@techbill, problems apparently start when you (try to) convert the database from MYISAM to InnoDB or/and change collation - utf8_unicode_ci or utf8mb4_sumthing_?

I think I’ve solved my original issue related to that, but my database is now a mix of InnoDB and MYISAM - some MYISAM tables won’t convert, not sure if I’d only cause more problems if I forced it somehow. Also not sure what recommended collation etc. is.

I am still curious to see an answer to the original question of this thread, how to do a clean surgical rebuild of the database without losing data and without having to reinstall everything.

We recently converted our database from Latin to utf8 as well too.

What we did was to set up in my.cnf that it defaults to urf8 and InnoDB then export all the database from Latin. (my.cnf is only accessible by root / server admin)

We deleted and cleaned up all old database then we imported it back into which get stored as utf8 in InnoDB.

1 Like

Thanks. Good info. I had set up those defaults in my.cnf, haven’t tried exporting and importing the whole database yet - no data loss?

Wasn’t utf8mb4 recommended for Mautic?

And I think converting MYISAM to InnoDB is what causes most problems.

Would simply exporting, importing a mixed MYISAM/InnoDB database into default InnoDB still work?

No data loss as far we could see but we do have a back-up and a back-up of back-up before attempting this.

If I recalled correctly, I exported into SQL then delete the whole database completely.

I edited the my.cfg setting the default to utf8 and InnoDB then import the “SQL” back into the newly created database.

That was a typo in my earlier post. Sorry about that.

Yes, utf8mb4 is what I went with.

Here a partial screenshot of my parital DB after importing it back in.

1 Like