Migrating from MySQL to Percona Server
Percona Server is just MySQL with a few extra options added in by Percona. It’s backwards compatible and based off the same code base. If you’re not familiar with Percona, they are the world’s leading MySQL consultants. The main reason I switched is because Ubuntu uses an old version of MySQL. Ubuntu is about a year behind in packaging MySQL. Something to do with checking the copyright after Oracle got ahold of it. This seemed to be the easiest way to update. A few other reasons follow.
Everyone and their mom says xtraBackup is the way to go for MySQL backups. Even Facebook uses it. xtraBackup is an open source project made by Percona. mysqldump is fine for small projects, but it’s not real scalable when you have any real amount of data. It’s available in the Percona apt repositories.
By default, older version of MySQL use the MyISAM storage engine, which has fallen out of favor. The default in newer MySQL installs is InnoDB. Percona also makes a storage engine called XtraDB, which is backwards compatible with InnoDB and supposedly a bit more performant. MariaDB (MySQL fork maintained by the MySQL creator) uses it as their default as well. Sounds like most people don’t notice a huge difference between XtraDB and InnoDB, but both are much favored over MyISAM which caused lots of problems for people.
Finally, there’s also HandlerSocket, which is a plugin for MySQL. It allows you to do primary key lookups directly to the storage engine bypassing MySQL’s SQL layer. It’s supposed to be 5-10x faster because it doesn’t have to parse the SQL and do table locking. It turns MySQL into a key/value as good as any of the NoSQL solutions. It’s actually much better because you can still run SQL queries on your data, which you can’t do with most of the NoSQL solutions and you get MySQL’s replication etc. which is all very well documented. As long as your DB can fit in RAM on a single machine it makes MySQL much faster. Perhaps even faster and easier to use than even memcached.
To migrate, first create a backup:
mysqldump -uroot -p --all-databases > dump.sqlThen do the upgrade:
gpg --keyserver hkp://keys.gnupg.net --recv-keys 1C4CBDCDCD2EFD2A
gpg -a --export CD2EFD2A | sudo apt-key add -
sudo emacs /etc/apt/sources.list
Add:
## Percona repository
deb http://repo.percona.com/apt oneiric main
deb-src http://repo.percona.com/apt oneiric main
sudo apt-get update
sudo apt-get install percona-server-server-5.5
sudo apt-get autoremove
Leave A Comment