1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Moving DB from Pencora to MariaDB

Discussion in 'Server Configuration and Hosting' started by Andy.N, Nov 16, 2013.

  1. Andy.N

    Andy.N Well-Known Member

    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.
     
  2. Adam Howard

    Adam Howard Well-Known Member

    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
     
    Last edited: Nov 16, 2013
  3. MattW

    MattW Well-Known Member

    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
     
    Adam Howard and SneakyDave like this.
  4. Slavik

    Slavik XenForo Moderator Staff Member

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

    Job Done :)
     
    Adam Howard, Andy.N and MattW like this.
  5. Andy.N

    Andy.N Well-Known Member

    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????
     
    Adam Howard likes this.
  6. MattW

    MattW Well-Known Member

    Step 4 isn't needed if you are importing into another MySQL server.
     
    Andy.N likes this.
  7. Adam Howard

    Adam Howard Well-Known Member

    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.
     
    Last edited: Nov 16, 2013
  8. Floren

    Floren Well-Known Member

    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
     
    Last edited: Nov 17, 2013
    p4guru likes this.
  9. Adam Howard

    Adam Howard Well-Known Member

    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.
     
  10. MattW

    MattW Well-Known Member

    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 likes this.
  11. Adam Howard

    Adam Howard Well-Known Member

    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 likes this.
  12. MattW

    MattW Well-Known Member

    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 likes this.
  13. Adam Howard

    Adam Howard Well-Known Member

    cPanel always makes things "interesting"
     
    MattW likes this.
  14. MattW

    MattW Well-Known Member

    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.
     
    Adam Howard likes this.
  15. p4guru

    p4guru Well-Known Member

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

    Do it right one time. That's all that is need :)
     
  16. Floren

    Floren Well-Known Member

    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. :)
     
    Last edited: Nov 17, 2013
  17. Andy.N

    Andy.N Well-Known Member

    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 likes this.
  18. Adam Howard

    Adam Howard Well-Known Member

    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).
     
    Last edited: Nov 17, 2013

Share This Page