Use Replication for backups? Are you schemas consistent?

Many people have a master/slave MySQL environment of various different topologies, and many use the slave as a backup.
Is your slave schema identical to your production schema? As long as an SQL statements completes without an error, your slave schema can differ. Common examples are different indexes or storage engines for example. However if you use the slave as backup, you want to ensure when you recover, you are recovering a production environment, not a slave environment.

While the changes may be small, the can lead to different results. For a client I found that the default value of a price field was 10.00 in one schema and 0.00 in another. Not withstanding using defaults for important fields and not defining in an INSERT is a different issue, it highlighted the different schemas can easily exist.

My tool of choice is SchemaSync. The reason why I like this command is that it provides migration scripts to upgrade or downgrade your schemas.

I vote for Planet MySQL moderation

How this happens or who does it is obviously a larger and more complex conversation however it is better then involving innocent animals.

How is it that trivial $#*! gets voted and has a string of comments I can not explain, however Planet MySQL should have practical MySQL related content. I would vote for the front page and RSS feed to show only moderated content, and content that did not pass the cut (and there is a lot of this) can be available on a less important page if necessary.

Rather then complaining like many open source communities, let us propose a way to improve the system we use.

Checked that MySQL backup log lately?

Running a MySQL backup and ensuring it completed successfully and backup files exist is not enough. In my B&R Quiz from Checked your MySQL recovery process recently? one important step is “Do you review your backup logs EVERY SINGLE day or have tested backup monitoring in place?”

This is what I found when reviewing a backup log for a client today.

mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES
mysqldump: Got error: 1142: SELECT,LOCK TABL command denied to user 'root'@'localhost' for table 'cond_instances' when using LOCK TABLES

The backup script was completing, backup files were in place (and are listed in the log file) however these errors were occurring.

Further investigation was less then one minute of actual work. This server runs multiple MySQL instances and recently one instance was upgraded from MySQL 5.1 to MySQL 5.5 however the call to mysqldump was not. This error was the result of running 5.1 mysqldump against a 5.5 database. Change the hard coded path and the error message went away.

The greater problem is teaching people to understand the importance of backups.

Why are we standing still?

I wrote an email a week ago to several close friends titled, “[w]hy are we standing still?” I opened with “[y]ou are all good friends and you are all smart people. We need to work together more … I deal with startups all the time and I rarely find a team of smart articulated people, so why can’t we just do this?”

What was the motivation? I had just read online that startup A has 100 million users and startup B had just raised $42 million. I came to the realization that I am wasting my time trying to develop significantly better tools in my chosen profession because I would never achieve these types of numbers. Those tools would never need the performance and scalability expertise for which I am widely recognized. I am, as CNN Money recently wrote in “Tech companies desperate for ‘rockstarninja’ engineers,” a “Rockstar Ninja” in the tech field.

I work with startups daily and most abuse the technology being used. Technology is not even the problem. In fact, for me and my group of close tech friends, it is almost trivial at times. What is complex, however, is the people and the process combined with the one thing that nobody can escape, time. Ideas are also not the problem, I have plenty of those. I even have several ideas at various stages of actual implementation including VisMarks – Visual Bookmarking and Mooify – The social barometer for moods, thoughts and emotions. These are each at different levels of initial completion. Are they world changers? Probably not, however, they are iterations of the process of what Eric Ries calls The Lean Startup Machine. (Side Note: The next Lean startup machine weekend event is in New York starting on April 1st.)

So back to my band of smart friends, why are we still standing still? Not being content with just talking, I took action and have organized a 24 hour weekend collaboration at my own home for next weekend, code named JFDI/Bliss (there is an interesting story behind the name). I set aside not one project to tackle but three. And by tackle, I mean create, deploy, iterate and even complete a MVP, keeping in mind the technology is not the hard part. The first project is from my good friend Graham, the founder of Ultra Light Startups. We have had many conversations about our respective ideas in the past years, discussing different potential projects. This project is actually referenced by our second project from another friend, John (uBlanket his current startup), that we discussed just last week over dinner. The third, my own VisMarks project, needs only one technical hurdle solved and some user interface design to complete initial functionality. We will also be reviewing and integrating technology from the Lean Startup Bundle for SXSW for the The Lean Startup Challenge.

I can easily provide the food and drink, internet, power, a large finished basement area, a back yard if the weather is good, bedding (it’s a 24 hr event) and even some of my world class beer collection so there are no physical hurdles or expenses to start working together. That is our lean ultralight startup. We have two goals: first, to be able to collaborate in person because thought can be multiplied exponentially when discussing any idea or problem and second, to have fun.

So it all sounds easy, right? Wrong. What’s missing? Just as the capability of being able to code is only a small portion of being a qualified and competent developer, creating the technology is only one portion of a successful startup. Managing Director of TechStars New York, David Tisch (@davetisch) stated at a recent Ultra Light Startups panel discussion some key points that highlighted the resource components of a successful startup. The ideals of this NYC accelerator are mentorship, network and exposure. We need all three. When asked what is the ideal skill set for a team, his response was three people: one business, one technical and one product person. We need those as well. However, when David was asked what is most important when choosing startups for their program, the answer was the people on the team. Important considerations are how you work together, what have you done together as a team, where do you most need help. For the last question that answer is easy. We need help and mentoring in areas including marketing, business development, legal, accounting, PR, promotion, funding, sales, leadership, management, vision, etc. All of these cost money we do not presently have and are necessary to get the traction of millions of users.

The goal should not just to become rich but be happy, have a lot of fun and make a difference in the world in small way we know best, technology.

There are many innovators in our industry, however, the following are a few I follow very closely. These include Dave McClure (@davemcclure), who I followed for a long time before we first met at the inaugural Rethink Hawaii event in 2009. Dave’s AARRR startup metrics for pirates approach is something I share with many clients that do not see the critical need of tracking what their leads do from initial acquisition. This is necessary to help answer questions including what is the your total cost of acquisition of a paying client and what is the best return on investment.

Eric Ries (@ericries) is a person I have followed now for over a year reading, many great posts including What is a startup?, Four (not five) myths about the Lean Startup and Revisiting the Software Design Manifesto to name a few.

Finally, the Kauffman Foundation (@kauffmanfdn), provides good resources and opportunities including the recent 2011 State of Entrepreneurship Address in Washington DC. Entrepreneurs create new businesses and new businesses are the greatest source of jobs and this creates a better economy. Entrepreneurship is also not just about creating something new, it is also about finding better ways of doing things we presently do. Their work with immigration reform, and involvement with the Startup Visa and Statup America are issues close to my own heart.

I have easily been distracted from my day job and upcoming speaking presentations researching work for our initial kickoff. Excitement is a great motivator to achieving something great.

MySQL conference schedule

I am one of the crazy individuals(*) that will be speaking at both the regular O’Reilly MySQL Conference and the IOUG Collaborate conference both being held in the second week of April. My 4 presentations are:

Upcoming NY Presentation – How Better Indexes Save You Money

For all those in New York this is an upcoming MySQL presentation held in conjunction with our colleagues at General Assembly on March 22nd 2011.

This presentation “How Better Indexes Save You Money” will be discussing how one simple technique can result in huge MySQL performance improvements and with zero code changes necessary. Many people think they know indexes, however MySQL and MySQL Storage Engines have some specifics that differ from more traditional RDBMS products. Learn some of the key analysis and verification techniques and be able to see immediate potential results in performance.

You can find more details at Meetup.com EffectiveMySQL. This new group is all about highly technical MySQL related content “no fluff, just stuff”.

Your PHP installation appears to be missing the MySQL extension which is required by WordPress.

I recently deployed a new WordPress installation to my existing production webserver running Apache, MySQL and PHP for other websites, yet I was presented with the following message.

“Your PHP installation appears to be missing the MySQL extension which is required by WordPress.”

This thread at wordpress.org did not help me, however I was able to solve the problem, but this thread is now marked as closed. That’s poor form because I can’t share the solution I found.

My PHP configuration file did not have the following.

#php.ini
[PHP]
extension=mysql.so

Adding this and restarting Apache did not fix the problem.

The problem was more fundamental and required PHP to be recompiled. Orginally PHP was configured with the ‘–with-mysqli’ option. PHP requires the ‘–with-mysql’ which is rather stupid they have this dependency.

Recompiling PHP and adding the necessary extension were both necessary to get my new WordPress installation operational.