Moving DB from Pencora to MariaDB

[Andy.N]

I used @Slavik excellent guide to set set up a server that run Litespeed and Percona. I'm using that server as a stand-alone database server for my site.

I'm getting a new server that has MariaDB and would like to move my DB to that new server.
Is there any known problem with moving database and tables from Percona to MariaDB? If not, what is the procedure to doing so?

Thanks a lot.

 

[Adam Howard]

I used @Slavik excellent guide to set set up a server that run Litespeed and Percona. I'm using that server as a stand-alone database server for my site.

I'm getting a new server that has MariaDB and would like to move my DB to that new server.
Is there any known problem with moving database and tables from Percona to MariaDB? If not, what is the procedure to doing so?

Thanks a lot.

If you're asking if there are any special steps you need to take to export / import a database that was using Percona and now you're moving it to MariaDB .... No.

It's very easy to do.

Both Percona and MariaDB are basically tweaked versions of MySQL (without being technical about it). The same commands that working on MySQL to export and import, work also on both Percona and MariaDB.

My only caution is that MariaDB for all the prase it gets, was originally designed for improved myisam, while Percona was designed more with innodb in mind. They'll both run either or (obviously), but I think Percona does a better job with innodb

 

[MattW]

MariaDB also includes the InnoDB improvements of Percona, including the xtradb innodb replacement

https://mariadb.com/kb/en/about-xtradb/

I used @Slavik excellent guide to set set up a server that run Litespeed and Percona. I'm using that server as a stand-alone database server for my site.

I'm getting a new server that has MariaDB and would like to move my DB to that new server.
Is there any known problem with moving database and tables from Percona to MariaDB? If not, what is the procedure to doing so?

Thanks a lot.

I've gone from MySQL 5.5 --> Percona 5.5 --> Percona 5.6 --> Percona 5.5 --> MariaDB 5.5 and had no issues with the databases

 

[Slavik]

I used @Slavik excellent guide to set set up a server that run Litespeed and Percona. I'm using that server as a stand-alone database server for my site.

I'm getting a new server that has MariaDB and would like to move my DB to that new server.
Is there any known problem with moving database and tables from Percona to MariaDB? If not, what is the procedure to doing so?

Thanks a lot.

Its as simple as stopping Percona, uninstalling, Installing MariaDB, starting. Run a check on the tables.

Job Done

 

[Andy.N]

Its as simple as stopping Percona, uninstalling, Installing MariaDB, starting. Run a check on the tables.

Job Done

Slavik,
Thanks. I wish it's that simple. The MariaDB is on a different server so I'm guessing the following steps
1. close the forum
2. mysqldump to backup the db on Percona server
3. mysqldump to restore the db on MariaDB server
4. run the check
5. Profit????

 

[MattW]

Step 4 isn't needed if you are importing into another MySQL server.

 

[Adam Howard]

MariaDB also includes the InnoDB improvements of Percona, including the xtradb innodb replacement

https://mariadb.com/kb/en/about-xtradb/


I've gone from MySQL 5.5 --> Percona 5.5 --> Percona 5.6 --> Percona 5.5 --> MariaDB 5.5 and had no issues with the databases

That's good to know. I've not been following the MariaDB project for sometime (years). I've installed when people requested it and they were happy, which was good enough for me. But I personally always found Percona to run better. And I believe this is because they started out with innodb in mind from the start.

But guess it really is just a personal preference.

 

[Floren]

What if you move from different MySQL versions? Is not as simple as you think.
This is what I would do to make sure the data integrity is OK (only if versions match):
Percona Server
- create a database dump
MariaDB Server
- create a specific forum user with restricted permissions
- create a new database
- import the Percona dump
- assign forum user to the new database
- force a MySQL upgrade, to ensure data integrity

 

[Adam Howard]

What if you move from different MySQL versions? Is not as simple as you think.
This is what I would do to make sure the data integrity is OK (only if versions match):
Percona Server
- create a database dump
MariaDB Server
- create a specific forum user with restricted permissions
- create a new database
- import the Percona dump
- assign forum user to the new database
- force a MySQL upgrade, to ensure data integrity

Technically speaking, the need for matching SQL versions was long ago resolved. When you import it should upgrade your version to the new location.

(Or so says MySQL)

But that is debatable. Some will argue the integrity (as you did), others will claim security, and others will side with it migrating as you import.

 

[MattW]

That is what I found. I upgraded my old cpanel server to Percona 5.6 and then went back down to Percona 5.5 and it had no ill effect on the databases on the server.

 

[Adam Howard]

That is what I found. I upgraded my old cpanel server to Percona 5.6 and then went back down to Percona 5.5 and it had no ill effect on the databases on the server.

There are some old school people, like @Floren who still feel the need to migrate it as he described. I use to do it that way too, because long ago it was needed.

It is not needed any more, but some people will argue it (recall this being debated once).

Like yourself @MattW, I've upgraded and even downgraded without issue. And I've exported and imported into older & newer version without issue.

 

[MattW]

It was a bit of a brown trouser moment when I realised I had to go back down to 5.5 as Cpanel wouldn't compile PHP on the new 5.6 release.

 

[Adam Howard]

It was a bit of a brown trouser moment when I realised I had to go back down to 5.5 as Cpanel wouldn't compile PHP on the new 5.6 release.

cPanel always makes things "interesting"

 

[MattW]

Yep, that's why I no longer use it for my main site. I've still got it for my shared hosting VPS where I host friends and family, as they are familiar with it, but I just leave it as standard as possible and don't mess about with it.

 

[p4guru]

What if you move from different MySQL versions? Is not as simple as you think.
This is what I would do to make sure the data integrity is OK (only if versions match):
Percona Server
- create a database dump
MariaDB Server
- create a specific forum user with restricted permissions
- create a new database
- import the Percona dump
- assign forum user to the new database
- force a MySQL upgrade, to ensure data integrity

that's how i do it for major version upgrades as well..

Do it right one time. That's all that is need

 

[Floren]

Technically speaking, the need for matching SQL versions was long ago resolved. When you import it should upgrade your version to the new location.

(Or so says MySQL)

But that is debatable. Some will argue the integrity (as you did), others will claim security, and others will side with it migrating as you import.

Exactly. While discussing it, see this post. (1020 views and 3 replies, he probably tweeted the link)
Could it be because the dump was bad... or just is not compatible with the new MySQL version?

Also... mysql_upgrade is a tool that checks and updates your tables to the latest version.
In other words, the need for matching versions was not 100% resolved.

 

[Andy.N]

Just an update: the move was complete last night. I dump the database from the old Percona db server, import them into the new MariaDB and that was it.
Instead of putting them on separate web and DB server, I use one single server (centminmod). The difference is this new server has SSD, twice the RAM.
I haven't done anything with my.cnf yet besides increase the innodb buffer so the db is in ram. It really flies.

 

[Adam Howard]

Exactly. While discussing it, see this post. (1020 views and 3 replies, he probably tweeted the link)
Could it be because the dump was bad... or just is not compatible with the new MySQL version?

Also... mysql_upgrade is a tool that checks and updates your tables to the latest version.
In other words, the need for matching versions was not 100% resolved.

From that post, it would appear he had a bad export.

MySQL has had that upgraded / downgrade function for almost 10 years now (think it's 7yrs off memory). It was first introduced in MySQL 5.1.x

Somewhere in 5.5 they made it part of the import process... You don't even need to run the mysql_upgrade command, you simply just import & it converts it.

Every once in a while, I come across someone who still prefers to do it old school though. There is nothing wrong with doing it the old school way... It after all works. But so does doing it the current way (it works).

I only time I think of doing it that way (the old school way), if it doesn't take the first time (having exported it means you have a local copy to try again) or if the database is very large (I will concede that on a larger database more than 10 GB, it maybe best not to depend on the automated process).