Upgrading from MySQL 5.1 to MariaDB 5.5

In my last post, a tale of two MySQL upgrades, a few folks asked if I would outline the process we used to upgrade, and what kind of downtime we had.

Well, the processes were different for each upgrade, so I will tackle them in separate blog posts. The first step was to upgrade all our MySQL 5.1 machines to MariaDB 5.5. As mentioned in the previous post, MariaDB’s superior performance for subqueries is why we switched – and we switched back to MySQL for 5.6 to take full advantage of the performance_schema.

It is not difficult to blog about our procedure, as we have documentation on each process. My first tip would be to do that in your own environment. This also enables other folks to help, even if they are sysadmins and not normally DBAs. You may notice the steps contain items that might be “obvious” to someone who has done maintenance before – we try to write them detailed enough that if you were doing it at 3 am and a bit sleep-deprived, you could follow the checklist and not miss anything. This also helps junior and aspiring DBAs not miss any steps as well.

The major difference between MySQL 5.1 and MySQL 5.5 (and its forks, like MariaDB) is that FLOAT columns are handled differently. On MySQL 5.1, a float value could be in scientific notation (e.g. 9.58084e-05) and in 5.5, it’s not (e.g. 0.0000958084). This makes checksumming difficult, as all FLOAT values will show differences even when they are the same number. There is a workaround for this, devised by Shlomi Noach.

We have an n+1 architecture for databases at Mozilla – this means that we have an extra server. If we need 1 master and 3 slaves, then n+1 is 1 master and 4 slaves. Because of this, there are 2 different ways we upgrade – the first slave we upgrade, and subsequent slaves/masters.

These steps are copied and pasted from our notes, with minor changes (for example, item #2 is “send out maintenance notices” but in our document we have the e-mail addresses to send to).

Assumptions: Throughout these notes we use ‘/var/lib/mysql’, as that is our standard place for MySQL. You may need to change this to suit your environment. We are also using Red Hat Enterprise Linux for our operating system, so this procedure is tailored to it (e.g. “yum install/yum remove”). We control packages using the freely available puppet mysql module we created.

For the first slave
The overall procedure is to perform a logical backup the database, create a new empty installation of the new server version, and import the backup. Replication does work from MySQL 5.1 to MariaDB 5.5 and back (at least, on the 25 or so clusters we have, replication worked in both directions. Your mileage may vary).

  1. Make sure the slave has the same data as the master with checksums (the previous checksum is fine, they should be running every 12 hours).
  2. Send out maintenance notices.

  3. Take the machine out of any load balanced services, if appropriate

  4. Set appropriate downtimes in Nagios

  5. Start a screen session on the server

  6. Do a SHOW PROCESSLIST to see if there are any slaves of the machine. If so, move them to another master if they are needed. [we have a different checklist for this]

  7. Do a SHOW SLAVE STATUS to see if this machine is a slave.

    1. If this machine is a slave, ensure that its master will not delete its binlogs while the upgrade is occurring.

    2. If this machine is a slave, do a SLAVE STOP; and copy the master.info file somewhere safe [or the slave_master_info table if using that]

  8. Stop access to the machine from anyone other than root (assuming you are connecting from root):

  9. UPDATE mysql.user SET password=REVERSE(password) WHERE user!='root'; FLUSH PRIVILEGES;
  10. See what the default character set is for the server and databases:
    SHOW VARIABLES LIKE 'character_set_server'; SHOW VARIABLES LIKE 'character_set_database';
    SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE DEFAULT_CHARACTER_SET_NAME!='utf8' AND SCHEMA_NAME NOT IN ('mysql');

    If applicable, change the server defaults to UTF8 and change databases to utf8 with ALTER DATABASE dbname DEFAULT CHARACTER SET utf8;

  11. Stop access to the machine from anyone other than root (assuming you are connecting from root): UPDATE mysql.user SET password=REVERSE(password) WHERE user!='root'; FLUSH PRIVILEGES;

  12. Check to see how big the data is:
    mysql> SELECT SUM(DATA_LENGTH)/1024/1024/1024 AS sizeGb FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA!='information_schema';

  13. Determine how you can export the data, given the size. You may be able to export without compression, or you may need to do a mysqldump | gzip -c > file.sql, then compress the old data files instead of just moving them aside.

  14. Do a du -sh * of the datadir and save for later, if you want to compare the size of the database to see how much space is returned after defragmenting

  15. Export the data from all databases, preserving character set, routines and triggers. Record the time for documentation’s sake. I’m assuming the character set from step 9 is utf8 (if it’s something like latin1, you’ll need to put in –default-character-set=latin1 in the command). If the machine has slaves, make sure to use –master-data=1. If you need to compress, change the shell command accordingly:
    time mysqldump --all-databases --routines --triggers --events > `date +%Y-%m-%d`_backup.sql
  16. Stop MySQL

  17. Copy the config file (usually /etc/my.cnf) to a safe place (like /etc/my.cnf.51)

  18. Do a rpm -qa | egrep -i "percona|mysql". Do a yum remove for the mysql/percona packages. It’s OK if it also removes related packages, like perl-DBD, but make a note of them, because you will want to reinstall them later. Sample:
    yum remove Percona-Server-client Percona-Server-shared-compat Percona-XtraDB-Cluster-devel Percona-Server-server
  19. Move the /var/lib/mysql directory to /var/lib/mysql-old. Compress any files that need compression (if you need to compress, to decompress the sql file). If you absolutely cannot keep the files, see if you can copy them somewhere. We really want to preserve the old data directory just in case we need to revert.

  20. Decompress the sql file, if applicable.

  21. Install the proper packages by changing puppet to use “maridb55″ instead of “mysql51″ or “percona51″. Verify with rpm -qa | egrep -i “percona|mysql|maria”
  22. [this may be different in your environment; we use the freely available puppet mysql module we created.
  23. Run mysql_install_db

  24. Make any changes to /etc/my.cnf (e.g. run puppet). When going from MySQL 5.1 to 5.5, there are no particular global changes Mozilla made.
  25. - when we went from MySQL 5.0 to MySQL 5.1, we did a global change to reflect the new slow query log options.
  26. chown -R mysql:mysql /var/lib/mysql/

  27. chmod 775 /var/lib/mysql

  28. Start MySQL and check the error logs for any warnings. Get rid of any warnings/errors, and make sure MySQL is running.

  29. Turn off binary logging. Import the export, timing how long it takes, for reference:

  30. time mysql < YYYY_MM_DD_backup.sql
  31. Restart MySQL and look for errors, you may need to run mysql_upgrade.

  32. Turn on binary logging, if applicable.

  33. Test.

  34. If this machine was a slave, re-slave it. Let it catch up, making sure there are no data integrity errors, and no replication errors.

  35. Reinstate permissions on the users:
    UPDATE mysql.user SET password=REVERSE(password) WHERE user!='root'; FLUSH PRIVILEGES;
  36. Re-slave any slaves of this machine, if needed.

  37. Turn back on Nagios, making sure all the checks are green first.

  38. Run a checksum on the master to propagate to this slave, and double-check data integrity on the slave. Note that you will want to use --ignore-columns with the output of this command in the checksum, to avoid false positives from scientific notation change (see https://blog.mozilla.org/it/2013/01/17/mysql-5-1-vs-mysql-5-5-floats-doubles-and-scientific-notation/)

  39. Find FLOAT/DOUBLE fields to ignore in checksum: SELECT GROUP_CONCAT(DISTINCT COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('float','double') AND TABLE_SCHEMA NOT IN ('mysql','information_schema','performance_schema');
  40. Put the machine back into the load balancer, if applicable.

  41. Inform folks the upgrade is over

On the first upgrade, we did what is usually recommended - do a logical export with mysqldump, and then an import. With other upgrades in the same replication hierarchy, we can take advantage of Xtrabackup to stream the new version directly to the machine to be upgraded.

The general procedure here is similar to the above, except that a logical export is not taken. After preparation steps are taken, a new empty MariaDB 5.5 server is installed. Then we use xtrabackup to backup and restore the existing MariaDB 5.5 server to the machine we are upgrading.

For subsequent slaves, and the master

  1. Coordinate with affected parties ahead of time

  2. Send out any notices for downtime

  3. Take the machine out of any load balanced services, if appropriate. If the machine is a master, this means failing over the master first, so that this machine becomes a regular slave. [we have a different checklist for how to failover]

  4. Set appropriate downtimes in Nagios, including for any slaves

  5. Start a screen session on the server

  6. Do a SHOW PROCESSLIST to see if there are any slaves of the machine. If so, move them to another master if they are needed.

  7. Do a SHOW SLAVE STATUS to see if this machine is a slave.
    1. If this machine is a slave, ensure that the master will not delete its binlogs while the upgrade is occurring.

    2. If this machine is a slave, do a SLAVE STOP; and copy the master.info file somewhere safe

  8. Save a list of grants from pt-show-grants, just in case there are users/permissions that need to be preserved.
  9. [this is done because sometimes masters and slaves have different users, though we try to keep everything consistent]
  10. Figure out how big the backup will be by doing a du -sh on the datadir of the already-upgraded machine to be backed up, and make sure the new machine has enough space to keep the old version and have the new version as well.

  11. Stop MySQL on the machine to be upgraded.

  12. Copy the config file (usually /etc/my.cnf) to a safe place (like /etc/my.cnf.51)

  13. Do a rpm -qa | egrep -i "mysql|percona". Do a yum remove for the mysql packages (at least mysql-server, mysql). It’s OK if it also removes related packages, like perl-DBD, but make a note of them, because you will want to reinstall them later.

  14. Move the /var/lib/mysql directory to /var/lib/mysql-old. Compress any files that need compression. If you absolutely cannot keep the files, see if you can copy them somewhere. We really want to preserve the old data directory just in case we need to revert.

  15. Install the proper packages by changing puppet to use “maridb55″ instead of “mysql51″ or “percona51″, running puppet manually. Verify with rpm -qa | egrep -i "percona|mysql|maria"

  16. Run mysql_install_db

  17. Make any changes to /etc/my.cnf (or run puppet). When going from MySQL 5.1 to 5.5, there are no particular changes.

  18. chown -R mysql:mysql /var/lib/mysql/

  19. chmod 775 /var/lib/mysql

  20. Start MySQL and check the error logs for any warnings. Get rid of any warnings/errors, and make sure MySQL is started.

  21. Stop MySQL, and move or delete the datadir that was created on upgrade.

  22. If you are directly streaming the backup to the machine to be upgraded, do this on the machine to be upgraded:
    cd $DATADIR
    nc -l 9999 | tar xfi -

  23. On the machine to be backed up (that is already upgraded), in a screen session, making sure you get any slave info:
    time innobackupex --slave-info --stream=tar $DATADIR | nc (IP/hostname) 9999

  24. Once xtrabackup is complete, fix permissions on the datadir:
    chown -R mysql:mysql /var/lib/mysql/
    chmod 775 /var/lib/mysql

  25. Prepare the backup:
    time innobackupex --apply-logs --target-dir=/var/lib/mysql

  26. Fix permissions on the datadir again:
    chown -R mysql:mysql /var/lib/mysql/
    chmod 775 /var/lib/mysql

  27. Restart MySQL and look for errors

  28. Test.

  29. If this machine was a slave, re-slave it. Let it catch up, making sure there are no data integrity errors, and no replication errors.

  30. Re-slave any slaves of this machine, if needed.

  31. Turn back on Nagios, making sure all checks are green first.

  32. Put the machine back into the load balancer, if applicable.

  33. Inform folks the upgrade is over

It’s long and detailed, but not particularly difficult.

A Tale of Two MySQL Upgrades

At the beginning of 2013, Mozilla’s MySQL databases were a mix of MySQL 5.0, Percona’s patched MySQL 5.1, Percona’s patched MySQL 5.5 and MariaDB 5.5. MySQL 5.1 was released in November 2008 – so at the beginning of the year, we still had databases with no new major features in 4 years. Currently we have almost all our databases at Oracle’s MySQL 5.6 – the only stragglers are our cluster running TokuDB and a few machines that are no longer in use. Here’s a graph showing the state of our machines – you can see that in the first half of the year we concentrated on upgrading our 5.0 and 5.1 servers to 5.5, and then in the second half of the year we upgraded everything to MySQL 5.6 (click on the image to get a larger version):

MySQL Versions in 2013

After running some tests, we determined that MariaDB 5.5 was the best option for us and our particular workload. For most of our servers, it did not matter whether we use Percona, MariaDB or Oracle’s MySQL, but our Bugzilla servers really benefited from MariaDB’s better subquery optimization, so we went with that. We had set up some Percona 5.5 servers over the spring/summer of 2012, when we moved some of our infrastructure to a new data center.

We upgraded to MySQL 5.5 to be on a recent version of MySQL. In the middle of the year, we had a choice – should we stay where we were, or should we upgrade? We had no particular directive from developers to upgrade for the new MySQL 5.6 features. However, we have been doing more and more digging into our systems, and we really wanted the performance_schema features so we could dig even more. We want to be able to parse queries in real-time, perhaps with Anemometer without having to take an offline log file and run pt-query-digest on it.

So, we chose to upgrade to MySQL 5.6. Unfortunately, there were no other GA products to test against – by mid-2013, neither MariaDB nor Percona had a GA 5.6 product, so our bake-off was functional only, not performance-related. Oracle’s MySQL 5.6 passed with flying colors, and so we proceeded to upgrade.

Now, we have a recent and consistent version of MySQL installed, that we can work with to gain insights into our systems. A pretty great goal to have been met for 2013!

MySQL Workbench “Clean up SQL” Feature

I was playing around with MySQL Workbench earlier in the week, and ran across the “clean up SQL” feature, which I thought was neat. Here’s a picture-based demonstration – you can click on the pictures to make them bigger, so they are more readable.

Here is a typical complex query that looks pretty good formatted in the results from a performance schema query:
query from performance schema

Simply click the “broom” icon and watch as your SQL is cleaned up, with one field in the SELECT per line and the JOINs indented and formatted prettily:
nicer, cleaned up SQL

Pretty cool, for just the click of a button!

Learn MySQL for Free with MySQL Marinate, Season 3!

The 3rd season of MySQL Marinate begins October 1st, 2013*. Join the meetup group and RSVP at season 3 to join! You can do the work on MySQL, or if you prefer, MariaDB or Percona.

If you do not have the book yet, you can still do the first week by using the online material from “Browse Contents” on the O’Reilly book page for Learning MySQL. There is homework for week 1, see the master list for all the information.

If you would like to learn MySQL from the ground up, consider joining us. This is for beginners – If you have no experience with MySQL, or if you are a developer that wants to learn how to administer MySQL, or an administrator that wants to learn how to query MySQL, this course is what you want.

If you are not a beginner, you are welcome to join too – maybe you need a refresher, or maybe you just want to test your knowledge or earn badges. That’s OK too!

The format of a virtual self-study group is as follows:

Each participant acquires the same textbook (Learning MySQL, the “butterfly O’Reilly book”, published 2007). You can acquire the textbook however you want (e.g. from the libary or from a friend) but if you buy the book, we ask that you buy it from our Amazon Store, to help pay for meetup fees.

Each participant commits to read one chapter per week, complete the exercises and post a link to the completed work. Tweet using the hashtag #mysqlmarinate.

Each participant obtains assistance by posting questions to a discussion area set up on the Virtual Tech Self Study Message Board for each chapter.

Each participant receives a badge upon finishing each chapter and all assignments.

Note: There is no classroom or video instruction.

How do I get started?

Become a member of the Virtual Tech Self Study Meetup Group.

Register for MySQL Marinate. RSVP to this event: Yes

Acquire the book (the only item that may cost money). Get your hands Learning MySQL – see if your local library has it, if someone is selling their copy, or buy it from our Amazon Store (this helps pay for meetup fees).

When your book arrives, start your virtual learning by reading one chapter per week. Complete the exercises; if you have any questions, comments or want to learn more in-depth, that’s what the forums are for!

Learning MySQL

FAQs:

Q: How long will the course last?

A: We will cover 12 lessons (chapters) in the book, so 12 (twelve) weeks starting October 1st, though we will have one week that is a break so that you can catch up if you need to or you have a week off if you need it. Refer to the MySQL Marinate Season 3 Master Discussion List for specific dates.

By January 1st, 2014, you will know MySQL!!

Q: Can I get ahead?

A: Sure! This is go-at-your-own-pace. To prevent spoilers, please put comments in the appropriate chapter threads.

Q: Does this cover the Percona patch set or MariaDB forks?

A: This covers the basics of MySQL, which are immediately transferable to Percona’s patched MySQL or MariaDB builds.

Q: What do I need in order to start the course?

A: All you need is the book and access to a computer, preferably one that you have control over. Installing MySQL is chapter 2, so really, all you need is the book and a computer to start, you don’t have to worry about any prerequisites. If you do not have the book yet, you can still do the first week by using the online material from “Browse Contents” at the O’Reilly Learning MySQL page.

Q: Where can I put completed assignments?

A: You will be able to put completed assignments on github. Instructions are in week 1.

Q: The book was published in 2007. Isn’t that a bit old?

A: Yes! O’Reilly is working on new material, but it is not ready yet. The basics are still accurate, and we will let you know what in the book is outdated. We will also have optional supplemental/”extra credit” material for those who want to learn more right away. We are confident that this self-study course will make you ready to dive into other, more advanced material.

Soak it in!

*either tomorrow or today, depending on when and where you read this. Or it was in the past, if you have to catch up on your blog posts. It’s OK, you can join us late, too. You can go at your own pace.

Code of Conduct: It’s a Misnomer

For me, a Code of Conduct is not actually guidelines for how to act. For me, a Code of Conduct is what to do if there is a problem with someone’s conduct. When I get on a plane, I am told what to do in case of emergency – here are the exits, here’s how to use your life jacket and oxygen mask. I am not told every little thing that could be an emergency – I believe that would be a waste of time and invariably something would be left out.

Similarly, for conferences, listing out all the behaviors that might be problematic is a waste of time, and invariably, behaviors are left out. In my opinion, that is a waste of time. What is NOT a waste of time is giving out the information of what to do in case of emergency.

At the MySQL Connect website, under the “Plan” tab, is a link to the Oracle Events Code of Conduct. It says, simply, to act professionally and respectfully, and if there are any problems, contact Oracle Security and gives a phone numbers. I like that. Simple, and effective. And it was put under the “Plan” tab – exactly where it should be. It’s something to note as you plan to attend.

I put that number in my phone and, thankfully, did not have to use it. But knowing that I had a plan in case something happened made all the difference to me.

*BIG DISCLAIMER – I do not speak for all people everywhere on this matter, just myself.

MySQL Puppet Module and Slides

During yesterday’s MySQL Connect conference, Brandon Johnson and I gave a joint talk about how Mozilla uses puppet to manage hundreds of MySQL servers efficiently. We also released our mysql puppet module on github, so that all can benefit. The slides for the talk are at http://bit.ly/puppet-mysql-slides.

Upgrading to MySQL 5.6 Slides

I am about to give a talk at MySQL Connect about what you need to know before upgrading to MySQL 5.6.

The PDF slides are online at http://bit.ly/upgrade56. I will be posting a video in the next few weeks!

Video: Getting Started with Performance Schema

Recently I gave a new talk to both the Tokyo and Boston MySQL User Groups about how to get started using performance schema. I have put some resources online for those interested:

Performance Schema talk video

PDF slides

The feedback has been excellent, so I hope that you find this video useful when trying to learn how to use performance schema and dive into the depth of the information it provides you.

Indexing Talk Online

I am doing a quick blog post to announce that I have put an indexing talk online*. Most recently, I delivered this indexing talk at Confoo and Scale 11x.

The talk is on YouTube at Are You Getting the Best Out of Your MySQL Indexes? There are also PDF slides.
From the official conference description, if you want to know more:
MySQL indexes are often used to make performance better. However, they can make performance suffer if you are not using them properly. Oracle ACE Director Sheeri Cabral explains the pitfalls to avoid with indexes and how to utilize compound indexes to maximize index availability with the least amount of write overhead.

*I know I have not been posting blogs for a long time. This was a very busy year, and I took March through July off from conferences in order to buy a house and move.

Having etherpad problems?

Some folks are reporting that some etherpads are not working after a routine database switchover. We have figured out a way to recover the last known working revision, and have already done so for a handful of etherpads.

We are working to proactively find these etherpads and fix them, but if you have an etherpad that is broken that you want to call attention to, please put it in bug 894913 – https://bugzilla.mozilla.org/show_bug.cgi?id=894913.