Apr 19, 2015

5 Steps to Migrate from MySQL Community Edition to MariaDB

Why Should You Consider the Migration?
There are many reasons for it, but in bottom line in open source you should look for the community. 

MySQL CE, MariaDB or Percona DB?
Well every one should make their decisions. However, you should decide if you are looking for commercial support or community support. If you are looking for commercial support, choose the company that you most trust (and gives you the best deal).
If you are looking for community support take a look where community is, check if the forums are active and if bugs that are being reported by the community are being taken care of. Finally search on Linkedin. It's a great way to sense where the wind blows.

What Should You Expect?
Faster releases, better response to community, some performance boost and in the bottom line: no change is need from your client side.

How to Migrate?
Migration currently is very simple, just like upgrading to a new major MySQL release:

  1. Backup:
    sudo service mysql stop
    sudo cp -R /var/lib/mysql /var/lib/mysql.old
    sudo cp -R /etc/mysql/my.cnf /etc/mysql/my.cnf.old
  2. Uninstall MySQL
    dpkg --get-selections | grep -v deinstall | grep -i mysql
    sudo apt-get purge -y percona-toolkit
    sudo apt-get purge -y php5-mysql
    sudo apt-get purge -y libmysqlclient18 mysql-client  mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5
  3. Install MariaDBsudo apt-get install software-properties-common
    sudo apt-key adv --recv-keys --keyserver hkp://keyserver.ubuntu.com:80 0xcbcb082a1bb943db
    sudo add-apt-repository 'deb http://sfo1.mirrors.digitalocean.com/mariadb/repo/10.0/ubuntu trusty main'
    sudo apt-get -y update
    sudo apt-get -y dist-upgrade
    sudo apt-get install mariadb-server mariadb-client percona-toolkit
  4. Select your root password
  5. Upgrade
    sudo mysql_upgrade -uroot -p
That's all. You will not need to change any client or recompile them.

Bottom Line
Migration is easier then you may expect, now you can test and verify if it fits your needs.

Keep Performing
Moshe Kaplan

Apr 4, 2015

Modifying Your MySQL Structure w/o Downtime

Did you try to add a new index to your MySQL huge table and suffered from a downtime?
If the answer is positive, you should introduce yourself PT-ONLINE-SCHEMA-CHANGE.

How the Magic is Done?
Actually Percona imitates MySQL behavior with a little tweak.
When modifying a table structure MySQL copies the original table structure, modifies it, copies the data and finally renames the table.
The only problem w/ this behavior that it locks the original table...

Percona is doing the same, but instead of locking the original table, it reviews the latest changes and implements them on the new table. That way the original table still serves the users, and changes replacement is done in a single atomic process.

Percona Toolkit Installation
Download the Percona toolkit and install it (the following is relevant for Ubuntu):
> wget http://www.percona.com/downloads/percona-toolkit/2.2.13/deb/percona-toolkit_2.2.13_all.deb
> sudo dpkg -i percona-toolkit_2.2.13_all.deb

Making a Change
Just call the tool with permissions, database name (D flag), table name (t flag), command to execute (--alter flag) and finally use the execute flag to implement the changes.
pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor -uuser -p"password" --execute

Things to Notice
  1. You must have a primary key on the table
  2. If you want only to verify the process before replacing the tables themselves, use --dry-run instead of execute (or just drop this parameter).

Bottom Line
Modifying your database will cause performance degradation, but it should not result in a downtime.

Keep Performing,
Moshe Kaplan


Intense Debate Comments

Ratings and Recommendations