Category Archives: Backup

MySQL Marinate – So you want to learn MySQL! – START HERE

Want to learn or refresh yourself on MySQL? MySQL Marinate is the FREE virtual self-study group is for you!

MySQL Marinate quick links if you know what it is all about.

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 will likely still learn some nuances, and it will be easy and fast to do. If you have absolutely zero experience with MySQL, this is perfect for you. The first few chapters walk you through getting and installing MySQL, so all you need is a computer and the book.

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, hard copy or online). Yes, the book is old, but SQL dates back to at least the 1970’s and the basics haven’t changed! There are notes and errata for each chapter so you will have updated information. The book looks like this:

O'Reilly Butterfly book picture

O’Reilly Butterfly book picture

Each participant commits to reading each chapter (we suggest one chapter per week as a good deadline), complete the exercises and post a link to the completed work.

Each participant obtains assistance by posting questions to the comments on a particular chapter.

Note: There is no classroom instruction.

How do I get started?

– Watch sheeri.com each week for the chapters to be posted.

– Get Learning MySQL
Acquire a book (the only item that may cost money). Simply acquire Learning MySQL – see if your local library has it, if someone is selling their copy, or buy it new.

– Start!
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 comments for!

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

A: This covers the basics of MySQL, which are applicable to Percona’s patched MySQL or MariaDB builds, as well as newer versions of MySQL.

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

A: All you need is the book and access to a computer, preferably one that you have control over. Windows, Mac OS X or Unix/Linux will work. A Chromebook or tablet is not recommended for this course.

Q: Where can I put completed assignments?

A: Completed assignments get uploaded to github. See How to Submit Homework

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

A: Yes! The basics are still accurate, and we will let you know what in the book is outdated. I have contacted O’Reilly, offering to produce a new edition, and they are not interested in updating the book. We will also have optional supplemental material (blog posts, videos, slides) 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!

Reference/Quick Links for MySQL Marinate

Teaching Thinking Patterns

How do you teach a thinking pattern? In my previous job I worked with a data analyst, who was really good at thinking about how our data correlates among itself. He was good at the data analysis, good at thinking up relationships, and good at coming up with complex comparisons.

However, he wrote some pretty funky SQL. We had him take a course in basics of MySQL, and from time to time I’d take a gander in the slow query logs and pick a few queries and point out the good and bad things. Most of these are optimization tips, such as “Indexes don’t apply to columns when you’re applying a function to that column”.

But it’s hard to try to think outside of your own box, and even running EXPLAIN on every query won’t necessarily tell you how you can fix a query. The problem is that there’s no way to know if you have optimized the query as much as possible. Even experienced DBAs

I was often frustrated when repeating concepts — though I always took a different approach, since I find that if I have to repeat myself it means that it didn’t sink in with the way I said it before. But I know many people, myself included, have managed to learn how to think differently.

The question, I guess, is how do you help someone else think differently?

The Real Reason Why Oracle Costs So Much

http://www.oracle.com/openworld/2007/appreciation.html

Now, Billy Joel is one of my all-time favorite pop musicians. I saw him in concert and nosebleed seats at the Boston Garden cost me USD $100 per ticket, and I bought 4 tickets (my twin brother is a die-hard Billy Joel fan, they were a holiday surprise 2 years ago!)

Billy Joel regularly sells out sports arenas. I can only imagine how much Oracle paid to have a concert with him.

And don’t get me wrong, the rest of the list is also stellar. Which only adds to my disbelief.

MySQL shows customer appreciation by not grossly overcharging.

MySQL: Because you’re smart enough to buy your own damn concert tickets.

Log Buffer #72 — a Carnival of the Vanities for DBAs

Welcome to the 72nd edition of Log Buffer, the weekly review of database blogs.

Oracle OpenWorld (OOW) is over, and Lucas Jellema of the AMIS Technology blog notes the OOW Content Catalog has been updated with most of the presentations available for download.

On his way home from OOW, Chris Muir of the appropriately titled One Size Doesn’t Fit All blog notes how OOW and the Australian Oracle User Group Conference and OOW compare with regards to 99% fewer attendees in AUSOUG Perth conference – from 45k down to 350.

Mark Rittman of Rittman Mead Consulting summarizes OOW’s impact on business intelligence and data warehousing in Reflections on Oracle’s BI Strategy. On his way home, Mark found time for A First Look at Oracle OLAP 11g, noting the pros, cons, gotchas and suggestions for improvement for many useful new features.

Microsoft SQL Server also has a new release in the works. Ted Malone in Agile Methods for the DB Dev is excited about SQL Server 2008 “Katmai” CTP 5 New Features and descries almost 20 of them.

Ian Barwick of PostgreSQL Notes talks about Converting tsearch2 to 8.3 now that the tsearch2 full text search engine has been integrated as a core PostgreSQL feature.

Patrick Barel of the Bar Solutions Weblog explains a new feature of Oracle 11g called Virtual Columns. While virtual data may be a new topic, using databases on virtual machines is an ongoing issue. Marco Russo of SQL BI gives his opinion on when to use virtual machines in SQL Server Virtualization.

Database professionals can be real characters, and set in their ways. Bad puns make good transitions, and Corrado Pandiani sheds light on MySQL’s rules for Charsets and Collations on Multicolumn Fulltext Indexes. Adam Douglas of Binary Expressions fixed some trouble with MySQL and French Characters not rendering properly.

Greg Sabino Mullane shows reasons for his Problems with pl/perl and UTF-8. In Tending the Garden, Selena Deckelmann goes through the very easy process of Automatic Character Set Conversion in PostgreSQL. Selena has also been busy organizing the development of ptop, an interactive, command-line tool for monitoring the current status of a PostgreSQL database. If you read this in time and are in the Portland, Oregon area you can join the ptop hackathon at noon (local time) tomorrow, Saturday November 24th, or you can read the ptop meeting summary from pdxpug.

While some of us are database tools, some of us prefer to contribute database tools. Baron Schwartz honors MySQL’s trademark by announcing that MySQL Toolkit is now Ma’atkit. Ma’at, pronounced “mott”, is the ancient Egyption patron saint of truth, harmony and order. In addition, Baron proclaims “Ma’atkit Version 1297 Released!”

Hubert Lubaczewski notes the changes to the analyze.pgsql.logs.pl script of pgsql-tools in update 3 and update 4.

Hubert also notes how to find overlapping time ranges and how to find the number of ranges a time belongs to in time ranges in postgresql – part 2. Though written for PostgreSQL, both posts can easily be applied to another DBMS. In the same vein, Yves Trudeau shares the DBMS-independent graphical images of Unix memory usage in Generating graphs from vmstat output.

Jeromy McMahon posts sample SQL code for viewing Oracle extent segments for tablespaces, temporary spaces and sort segment space. The Cheap DBA gets Oracle specific with a Slick Shell Script for Reporting on Oracle Workload. Krister Axel of codeboxer.com has A really clean dynamic insert proc for PL/SQL ETL packages, including validation checking and exception handling. zillablog‘s Robert Treat treats us to a function for tracking plperl shared variables.

Jen M is Keeping IT simple by coding capacity measurements to show How Not to Outgrow Your DB Infra: A Simple Step. She follows up with more code to monitor a specific cache to resolve unexplainable slowness/resource leak in SQL Server.

This post began with a conference, and so it shall conclude. The Call For Proposals for PgCon 2008 is underway, and David Fetter lets us know that PgCon 2008 will be held May 22-23 at the University of Ottawa. This is different from Joshua Drake‘s call for volunteers for Command Prompt’s Postgresql Conference East 08, on March 28-29 at the University of Maryland. Neil Conway informs us of a Jim Gray Tribute, consisting of a general session and 9 half-hour technical sessions reviewing some of the 1998 Turing Award winner’s work.

In case this edition did not give you enough to read, Beth Breidenbach of Confessions of a Database Geek created an aggregate blog feed for posts relating to information quality.

Top 10 MySQL Best Practices

So, O’Reilly’s ONLamp.com has published the “Top 10 MySQL Best Practices” at http://www.onlamp.com/pub/a/onlamp/2002/07/11/MySQLtips.html. Sadly, I find most “best practice” list do not thoroughly explain the “why” enough so that people can make their own decisions.

For instance, #3 is “Protect the MySQL installation directory from access by other users.” I was intrigued at what they would consider the “installation” directory. By reading the tip, they actually mean the data directory. They say nothing of the log directory, nor that innodb data files may be in different places than the standard myisam data directories.

They perpetuate a myth in #4, “Don’t store binary data in MySQL.” What they really mean is “don’t store large data in MySQL”, which they go into in the tip. While it’s true that there is very little benefit to having binary data in a database, they don’t go into what those benefits are. This means that people can’t make informed decisions, just “the best practice is this so I’m doing it.”

The benefit of putting binary data in MySQL is to be able to associate metadata and other data. For instance, “user 200 owns file 483”. If user 200 is gone from the system, how can you make sure file 483 is as well? There’s no referential integrity unless it’s in the database. While it’s true that in most cases people would rather sacrifice the referential integrity for things like faster database backups and easier partitioning of large data objects, I believe in giving people full disclosure so they can make their own informed decision.

#5 is my biggest pet peeve. “Stick to ANSI SQL,” with the goal being to be able to migrate to a different platform without having to rewrite the code. Does anyone tell Oracle folks not to use pl/sql like collections? Nobody says “SQL is a declarative language, pl/sql is procedural therefore you should never use it”. How about SQL Server folks not to use transact-sql statements like WAITFOR? MATCH… AGAINST is not standard SQL, so I should never use it?

Now, of course, if you’re selling a product to be run on different database platforms, then sure, you want to be platform agnostic. But you’d know that from the start. And if you have to migrate platforms you’re going to have to do lots of work anyway, because there are third-party additions to all the software any way.

And why would *anyone* choose a specific database, and then *not* use those features? I think that it’s a good tip to stick to ANSI SQL if you *know* you want to, or if you have no idea about the DBMS you’re using.

If you want to see how this cripples MySQL, check out Visibone’s SQL chart at: http://www.visibone.com/sql/chart_1200.jpg — you can buy it here: http://sheeri.com/archives/104. I may post later on about my own personal MySQL Best Practices….

“kill” Oddness

So, at midnight I got a call from customer service saying our site was slow. I narrowed it down to one of our auxiliary databases, that seems to have gotten wedged just about midnight. Normal queries that took less than 4 seconds started taking longer and longer, moving up to 5 seconds and past 30 seconds in the span of a minute or so.

In the moment, I thought killing off all the queries would be a good move. My kill script, which consists of:


for i in `/usr/bin/mysql -u user -pPass -e 'show full processlist' | grep appuser | cut -f1`
do
mysql -u user -pPass -e "kill $i"
done

This will attempt to kill any mysql connection owned by the appuser. I used it a few times, and it didn’t work. So I used a trick I learned when we bring our site down — sometimes there are straggling connections to mysql, so what I do is change the app user’s password by direct manipulation of the mysql.user table and flush privileges.

Within 10 seconds, all the connections from the appuser were gone, and when I put the correct password back and flushed privileges, everything came back normal. Queries started taking their usual amount of time.

Why is it that queries that refused to be killed by “kill”, and yet changing the password for the user they were running as killed them off? Some were running more than 45 seconds, in various states of “Sending data” and “closing tables”. Nothing was running for much longer than 60 seconds, so it doesn’t seem like there was a big query that was wedging things.

Oh, and what happened at midnight to cause this? No clue, the only thing we run at that time is a PURGE LOGS FROM MASTER, which we do every hour, as we fill up a 1.1G binary log file every 20 minutes or so. This database holds a particularly heavy write application and also runs reports, so we optimize the tables every week (wednesday at 2 am). I’ve put the optimization to daily, as when I ran it manually this morning it took about 20 seconds.

Anyone have an idea about why changing the password worked so quickly when kill did not?

OurSQL Episode 19: MySQL Proxy

Direct play the episode at:
http://technocation.org/content/oursql-episode-19%3A-mysql-proxy-0

Feedback:
http://odeo.com/sendmeamessage/Sheeri

Call the comment line at +1 617-674-2369.
E-mail podcast@technocation.org

News:
MySQL Focuses on Japan
http://www.mysql.com/news-and-events/news/article_1368.html

MySQL Associate Certification Now Available
http://www.mysql.com/certification/

Learning resource:
Pythian Group’s Carnival of the Vanities for the DBA community, published weekly on Fridays.
http://www.pythian.com/blogs/category/log-buffer/

Feature:
MySQL Proxy
Giuseppe Maxia’s Blog:
http://datacharmer.blogspot.com

Getting Started with MySQL Proxy article plus tutorials:
http://www.oreillynet.com/pub/a/databases/2007/07/12/getting-started-with-mysql-proxy.html

public Subversion tree:

http://forge.mysql.com/wiki/mysql_proxy

Tutorials:
Intercept and dump queries (part 1): http://forge.mysql.com/snippets/view.php?id=75

Make macros to map “cd” to “use” and “ls” to “show tables” (part 2): http://forge.mysql.com/snippets/view.php?id=76

Injection Queries (part 3): http://forge.mysql.com/snippets/view.php?id=77

Lua interpreted language:
http://www.lua.org/

Ruby on Rails Presentation Video

aka, “Better late than never”…..

Back in March 2007, the Boston MySQL User Group (http://mysql.meetup.com/137) watched and heard Brian DeLacey give a tutorial of Ruby on Rails, including its interaction with MySQL using ActiveRecord.

I knew absolutely nothing about Ruby on Rails before attending the presentation, other than Ruby was a language and people were saying that Rails made for easy development. After the presentation, I knew enough to start coding!

Brian is an excellent speaker, and this presentation is long overdue. (I’d tried creating the video before, during and after the MySQL Users Conference back in April, and my application kept crashing. I guess it just needed a break, because I fired it up today and it seemed to save the movie OK. Please let me know if you watch the presentation and something seems wrong).

Enjoy!

Direct download link:
http://www.technocation.org/movies/mysql/2007_03_Rails_BostonUG.wmv

Ruby on Rails by Brian DeLacey

When is a DBA not a DBA?

A sysadmin friend of mine was describing some DBA work he was doing, and wrote this:

I’m not much of a DBA, really – if it can’t be done through phpMyAdmin I’m not likely to be doing it.

This is in stark contrast to so many so-called DBAs who say “I’m a DBA….If it can’t be done through phpMyAdmin it must be Senior DBA work.”

I’ve used phpMyAdmin for MySQL administration, and there’s just something so nice about working on commandline.

A Note About the 12 Days of Scaleout

Some have pointed out that the 12 Days of Scaleout campaign is a “cheap marketing tactic.”

Why, yes. It’s inexpensive as far as campaigns go. It’s definitely marketing. The grumbling seemed to be that there was no content on how the scaleout happened and worked for these companies.

We have to remember that not everyone is a geek. While we already know and love MySQL, there are people out there who only vaguely understand what a “database” is, much less have even heard of MySQL. Many laypeople I talk to haven’t heard of Oracle!

MySQL needs this kind of marketing. Perhaps it better belongs as an advertisement in a glossy magazine, but I see no problem with MySQL using what they own — lists, forums, PlanetMySQL, its own web page — to do cheap marketing. In fact, “cheap marketing” is one of the main reasons for having a website! The Log Buffers are a great way to give back to the community, but they’re also marketing for Pythian.

Heck, the people who post on Planet MySQL are marketing themselves — which may seem like a silly statement until you realize that there are a few folks who are independent consultants.

So, yes. I’m all for Planet MySQL being cheap marketing. I market my podcasts on the ‘planet, and I market my own skills. The next time I look for a job, I can point an employer to the fact that I’m at or near the top of the Planet MySQL top posters list.