Keeping your Drupal from Drooping — part 2

So let’s clone our current Drupal 7.22 site from MySQL 5.1 that came with CentOS 6.4 and get it running on MySQL 5.6.

Upgrading MySQL 5.1 -> 5.6 has an official recommendation of making sure the intermediate 5.5 update is performed too. Please review Section 2.11.1 while you make a backup of all your data. Yes, you do have to make a backup. When you run the upgrade, there are tables in the mysql scehma that will be tweaked and skipping a step can make for a very messy late night in an attempt to recover.

An other thing to look for is that on occasion new reserved words are introduced that may conflict with column names in use in your schemas. So it pays to read through the release notes for new reserved words and then run a quick grep on your tables to be safe. If you find that there is a new reserved word doing duty as one of your column name, you can either put the reference in single quotes or rename. This has not been a Drupal problem for quite a while but you need to be aware it can happen.

Software upgrades are like a good spaghetti meal — have lots of sauce and do not under cook the noodles.

CentOS uses RPM as their package manager and it usually wants to manage one version of a program. So we could run the MySQL 5.5 upgrade with rpm -u and then do the same for the 5.6 RPMs. But often RPMs are like the a game of Jenga where too many blocks are dependent on other blocks and you end up not being able to move with out knocking over the entire stack. I found a great How to upgrade MySQL 5.5 to MySQL 5.6 on CentOS 6.3/Red-hat/Fedora blog that covers the following in a little more detail but the gist of it follows.

The approach in the link above uses the Remi repositories which requires a little setup.
rpm -Uvh http://download.fedoraproject.org/pub/epel/6/i386/epel-release-6-8.noarch.rpm

rpm -Uhv http://rpms.famillecollet.com/enterprise/remi-release-6.rpm

yum --enablerepo=remi,remi-test install mysql mysql-server


Restart the mysqld, run mysql_update, and double check your Drupal site. Your should be running Drupal7.22 on top of MySQL 5.5.

MySQL 5.6 is not in the Remi repositories so the upgrade this time is a little more involved. Read through How to upgrade MySQL 5.5 to MySQL 5.6 on CentOS 6.3/Red-hat/Fedora and keep in mid this also works for Centos 6.4 too. First grab the RPMS from the MySQL site.

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-shared-5.6.11-2.el6.x86_64.rpm/from/http://cdn.mysql.com/

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-client-5.6.11-2.el6.x86_64.rpm/from/http://cdn.mysql.com/

wget http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-server-5.6.11-2.el6.x86_64.rpm/from/http://cdn.mysql.com/

Next, stop MySQL. Then remove MySQL 5.5.

yum remove mysql-server-5.5.31-1.el6.remi.x86_64 mysql-libs-5.5.31-1.el6.remi.x86_64 mysql-devel-5.5.31-1.el6-remi.x86_64


Install MySQL 5.6!

rpm -ivh MySQL-shared-5.6.11-2.el6.x86_64.rpm MySQL-client-5.6.11-2.el6.x86_64.rpm MySQL-server-5.6.10-1.el6.x86_64.rpm


Hey, great, we’re finished, right? Well, no. MySQL 5.6 is a much more secure at installation time and provides a random password for root. So restart mysqld again and use your new, super-secret password from /root/.mysql_secret to login as root. Use SET PASSWORD to reset the root password, test it a few times, and then remove the super-secret password file. Run mysql_upgrade to convert any remenents of older MySQL versions to the current version.

Finally, we have Drupal-7.22 running on a Centos 6.4 using MySQL 5.6! Whew!

Keeping your Drupal from Drooping — part 1

This is the first screen after a sucessful Drupal 7.22 install.

Drupal is a content management system that runs at least 2.1% of all websites(1). It is easy to use, extensable with over 20,000 add-ons, and runs beautifully with a LAMP stack.

At the heart of most Drupal sites is a MySQL database with, as of Version 7.22, 76 tables. Recently I was asked what needs to be done to a ‘generic’ Drupal to get it running on MySQL 5.6. It is a very easy update that provides better performance, security, and allows access to the newest MySQL 5.6 updates.

For this example, the generic box is a two CPU Dell x86_64 box running Centos 6.4. This is fairly typical of what a low-end hosted system from one of the many web hosting businesses a small busines might rent. Centos 6.4 was installed.

Now to use Yum to get the with the default versions of Apache, PHP, and MySQL. Sadly the MySQL provided is 5.1.69 not 5.6 (out for a month or so) or even 5.5 (two years since GA status). We will upgrade MySQL later.

yum -y install mysql-server httpd php php-mysql wget

chkconfig httpd on
chkconfig mysqld on

/etc/init.d/mysqld start
/etc/init.d/httpd start


Next we need to create a database for Drupal’s use. This is how it can be down from a Linux shell for those shy on using the MySQL client program.

echo 'CREATE DATABASE drupal;' | mysql
echo "GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER, LOCK TABLES, CREATE TEMPORARY TABLES ON drupal.* TO 'drupal'@'localhost' IDENTIFIED BY 'drupal';" | mysql
echo "FLUSH PRIVILEGES;" | mysql

Now to get Drupal installed. Make sure your Apache DocRoot is where you install the software.

cd /var/www/html/
wget http://ftp.drupal.org/files/projects/drupal-7.22.tar.gz
tar -xzf drupal-7.22.tar.gz --strip-components=1
rm drupal-7.22.tar.gz
chown -R apache.apache .

cp ./sites/default/default.settings.php ./sites/default/settings.php


Now Drupal can be configured using a web browser. Be sure to plug in your infromation from the GRANT ALL PRIVILEGES ON drupal.* TO ‘drupal’@'localhost’ IDENTIFIED BY ‘drupal’; step for you database name, user and password. You should see something similar to the graphic at the top of this post.

So now we have a fairly generic Drupal installation that is ready to upgraded to MySQL 5.6 and next time the upgrade steps will be detailed as well as some upgrade methodologies that need to be considered.

1. See http://trends.builtwith.com/cms/Drupal

Book Review – MySQL Workbench: Data Modeling & Development

MySQL Workbench: Data Modeling & Development
Michael McLaughlin
Oracle Press
ISBN 978-0-07-178188-5

MySQL Workbench is one of those tools that quickly becomes invaluable. It is a SQL query tool, a data modeler, and an admin tool all in one. As such a complicated tool, there is a bit of a learning curve before one can move beyond the basic functionality. Now there is a book that not only is a guided tour of MySQL Workbench but it contains little snippets full of DBA and system admin tricks that make this a must have for anyone using Workbench.

The book is divided into six sections that starts with configuration. This part of the book assumes now previous database or MySQL background as it gently guides you through the installation of the key components on mac OS X, Windows, and Linux. The author’s teaching background is very evident here as the material is ‘undergraduate proof’ and very clearly written. The illustrations are large, clear, and informative.

The second section covers data modeling including creating tables, reverse engineering schemas, and creating routines. This section covers it all from the theory of data modeling through building complex schemas with foreign keys. The discussion on the choice of keys in tables should be mandatory reading for any DBA.

The third section delves into using Workbench for query development and the management of SQL scripts. This section is short and to the point.

Many folks use Workbench as an admin tool and the fourth section covers setting up connections, user management (MySQL has no predefined user roles but Workbench allows the set of users with privileges for various task like DBA, backup administrator, and such). Importing, migrating and exporting databases is extensively covered.

The book finishes up with chapter questions (and you need to be careful; some of the questions are tougher than they look), how to extend Workbench, and setting MS SQL Server (to use the migration tools).

Does the book lack anything? Well, it does not cover the recent MySQL Utilities that allow the cloning of a server for replication and replication fail over. But those are very new and no doubt will picked up for a future revision.

But do you need this book? Yes, if you are new to Workbench and want to get up to speed quickly. Yes if you are getting into data modeling. And yes if you are not a full time DBA and want to use this free tool as your admin interface. Old, barnacle crusted DBAs my not need a copy but they should buy them for their juniors.

Thanks for attending MySQL Community Reception

Cryogenic Dolphin

Thanks to all who came to the MySQL Community Reception last night. Food, drink, and lots of great folks talking about MySQL.

We even had a Raspberry PI based MySQL Cluster on display

Raspberry PI MySQL Cluster

and one of the Pi was given away as a door prize (Congrats Jens).

What happened in Santa Clara does not stay in Santa Clara

But the hit if the evening was the Cryogenic Dolphin or a Sakila made of ice.

‘Cryogenic Dolphin’ at MySQL Community Reception

Come see out new cool ‘cryogenic dolphin’ at the MySQL Community Reception Monday. Come hear about the latest 5.6 news from MySQL Engineers, see Cluster on Raspberry Pis, and walk away with our giveaways. And did I mention it is FREE?!?!

MySQL User Group meeting June 4th in North Texas

Date: Tuesday, June 04, 2013
Time: 5:30 PM – 7:00 PM

Oracle Corporation
6031 Connection Drive
Suite 900
Las Colinas, TX 75039

Event is free to the public. But please RSVP so we order enough pizza.

Learn how to set up MySQL replication for simple master/slave relationship, including a live demo. Then we will cover how to scale using replication and advanced strategies. Giveaways include books from Oracle press!

Pizza at 5:30 p.m.
Presentation begins at 6:00 p.m.

Ten days to go! Do not forget to register for MySQL 5.6 Reception

Do not forget to register for the MySQL Community Reception by Oracle.

Lots of giveaways and one very cool dolphin but space is limited so RSVP Today!

Monday, April 22, 2013
6:30 P.M. – 8:30 P.M. PDT
TechMart Santa Clara
5201 Great America Parkway
Santa Clara, CA, USA

Join Oracle’s MySQL Team on April 22, 2013, as we celebrate the general availability of MySQL 5.6. With product demos and fun activities in a relaxing atmosphere, this is the party for the MySQL community to get together and have a toast on the work all of us did to make MySQL 5.6 the best release ever. Whether you are an attendee at Percona Live, a member of local MySQL user groups, a MySQL user in the Bay Area, or simply interested in MySQL technology, you’re all invited to Oracle’s MySQL Community Reception.
Mingle with your peers and learn from real-world experiences.
Meet MySQL engineers and get the first-hand information on the latest product development.
Have lots of fun!
RSVP today and get ready for an evening of casual but informative conversation, with complimentary food and refreshments from Oracle!

MySQL passes SQL Server

The April DB Engines rank show that MySQL has passed SQL Server to become the second highest ranked database management system.

MySQL overtakes SQL Server

Congratulations to my fellow MySQL-ers at Oracle — your hard work is being noticed.

“Let’s Celebrate MySQL 5.6 GA!” – MySQL Community Reception by Oracle

Space is limited so RSVP Today!

Join Oracle’s MySQL Team on April 22, 2013, as we celebrate the general availability of MySQL 5.6. With product demos and fun activities in a relaxing atmosphere, this is the party for the MySQL community to get together and have a toast on the work all of us did to make MySQL 5.6 the best release ever. Whether you are an attendee at Percona Live, a member of local MySQL user groups, a MySQL user in the Bay Area, or simply interested in MySQL technology, you’re all invited to Oracle’s MySQL Community Reception.

Mingle with your peers and learn from real-world experiences.

Meet MySQL engineers and get the first-hand information on the latest product development.
Have lots of fun!

RSVP today and get ready for an evening of casual but informative conversation, with complimentary food and refreshments from Oracle!

Monday, April 22, 2013

6:30 P.M. – 08:30 P.M. PDT

TechMart Santa Clara
5201 Great America Parkway, Santa Clara, CA 95054

Bulgarian & Latin America MySQL Events

Bulgaria

The Spring Conference of the Bulgarian Oracle Users Group Conference of the Bulgarian Oracle Users Group will be held from 17th to 19th of May 2013 in SPA hotel Hisar in Hissarya.

• Georgi Kodinov, Senior Software Development Manager, ORACLE, Quick Dive into MySQL
• Georgi Kodinov, Senior Software Development Manager, ORACLE, MySQL Security : Past and Present
• Joze Senegacnik, , DBProf, Is My SQL Statement Using Exadata Features?

Latin America

Oracle Day Costa Rica (Thursday April 4)

• http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=165842&src=7620972&src=7620972&Act=4

• http://oracledbacr.blogspot.com/2013/01/oracle-day-costa-rica-2013-19-de-marzo.html
• http://youtu.be/ZacenHJfNhY

And Oracle Day Bogota Colombia (Wednesday April 10)

• http://www.oracle.com/webapps/events/ns/EventsDetail.jsp?p_eventId=165858&src=7620930&src=7620930&Act=6

By the way, there are some great MySQL presentations from the 2012 LAD Tour:

https://docs.google.com/folder/d/0B2616xVwDe3cOFBLZEU4d3BQVDA/edit