ThetaJoin Blog

MySQL Admin Cookbook from Packt Publishing, authored by Daniel Schneller & Udo Schwedt, is a new addition to the MySQL literary genre with 99 great recipes for mastering MySQL configuration and administration. Announcing quick answers to common problems, these 99 recipes cover a broad spectrum of managing MySQL, and are appropriate for new MySQL administrators as well as more experienced administrators. MySQL Admin Cookbook is available in dead tree and PDF formats. The book's website is: https://www.packtpub.com/mysql-admin-cookbook/book with errata found at http://www/packtpub.com/support.

Subjects Covered

• Replication
  
• Indexing Tools
  
• Backing Up & Restoring MySQL Data
  
• Managing Data
  
• Monitoring & Analyzing MySQL Installation
  
• Configuring MySQL
  
• MySQL User Management
  
• Managing Schemas
  
• Good to Know

All in that order.

Recipe Format

All recipes follow a common format:

1. Introduction of what this recipe will accomplish and why you'd want to use it.
   
2. Getting Ready – This section outlines the requirements for the recipe, user privileges, programming language familiarity, additional software required, and where to find additional information.
   
3. How to do it – Step-by-step instructions and expected output from these steps. All the reader has to do is follow the steps.
   
4. How it works – The details on how the recipe works. If you're unsure what happens during the How to do it section, be sure to spend all the time needed to understand how the recipe works. Don't implement any recipe unless you fully understand what it's doing and how it works.
   
5. There's more – Following on from the How it works section, this part will give the reader additional information to expand the functionality of the recipe, or provide other tools that provide the same, or nearly the same functionality. If there are additional concerns to look out for, it'll be included here as well.

MySQL Admin Cookbook Review

MySQL Admin Cookbook really is not designed to be read cover-to-cover as I did for this review, but it left me with a clunkiness about chapter order. There were a few of the recipes I thought were too basic – creating a basic user, creating indexes and tables. Those DBA functions should be automatic before diving into a book with this level of technical content. After those little bits of personal opinion, overall, I really liked the book. I put a few of the recipes into action with the servers I'm responsible for – Using a custom prompt, I would rely on the tab within terminal to see which server I was on, but that tab is missing when I SSH into a server from my phone. I quickly used Display query results page by page and Default pager as well since it makes it much easier to use the MySQL command-line client on smaller screens. Sometimes, it's the small things that make a large impact.

I work mostly from the command-line, but I like the way MySQL Admin Cookbook used both command-line tools and MySQL GUI tools to demonstrate the recipes. The authors acknowledge that the MySQL GUI tools are end of life, but they are still useful tools, so don't discount them. One of the things I thought was left out was using phpMyAdmin as one of the “GUI” tools. If you've never heard of phpMyAdmin, it is a very popular web-based MySQL interface used by many web hosting companies. One of the reviewers is heavily involved with phpMyAdmin development as well, so I would think at least one recipe in Chapter 3: Tools should have used phpMyAdmin.

One of the most popular command-line tools outside of MySQL command-line is Maatkit, which is mentioned several times throughout the book. On this subject, Maatkit could really use its own book ( hint, hint Baron! ), but I digress – hopefully in a positive direction.

At the time of this review, the was no errata on the Pakt website, and I couldn't add anything to the errata anyway. I've not tried all the recipes for accuracy, but I couldn't find any tpyos or mispelings.

If you'd like to see another DBA's perspective on MySQL server management or gain additional MySQL technical expertise, pick up a copy of MySQL Admin Cookbook.

If you'd like your MySQL, Drupal, or other F/OSS book reviewed on this site, please contact reviews@thetajoin.com.

This is Part II of an unknown number of posts about using Amazon S3 & CloudFront to host your Drupal site's streaming media. Part I covered a very basic approach using an Ogg Vorbis Video formatted file delivered over HTTP. Although this will work over HTTP with limited support from embedded media players, this isn't the internet standard for delivering streaming videos. One standard protocol to use is the real time messaging protocol or rtmp, developed by Adobe Systems. From the Amazon CloudFront manual:

"CloudFront uses Adobe Flash Media Server 3.5 to stream on-demand content with Adobe's
Real-Time Messaging Protocol (RTMP). CloudFront accepts RTMP requests over port 1935 and
port 80."

This means flash videos can be streamed from Amazon CloudFront. This post will show how to convert that Ogg Vorbis Video file to flash video format on Ubunta 9.10 and then configure Amazon CloudFront to support streaming the flash video.

1. Converting Ogg Vorbis to Flash Video

In order to convert the ogv file to flv format, ffmpeg needs to be installed. On Ubuntu 9.10:

sudo apt-get install ffmpeg

2. Convert the ogv file to flv format:

ffmpeg -i iPodVideo5GRepair.ogv -s 800x600 iPodVideo5GRepair.flv

The -s ffmpeg option resizes the video to 800x600. The original video file was recorded at 1600x1000. This reduced the file size from 440MB to 124MB. Still 44 minutes in length though.

3. Create Amazon S3 Bucket

Amazon CloudFront needs an Amazon S3 bucket to store the flv file. CloudFront uses Amazon S3 as the source for the flv file to stream. Amazon CloudFront only supports the distribution of content, not the storage. Using your favorite Amazon S3 client, create a bucket that's named like your internet domain name. In the case of ThetaJoin, a bucket was created named streams.thetajoin.com.

4. Configure Bucket for Streaming Content

CloudFront supports content distribution using two different methods - download and streaming. The download method is used for static content such as PDFs, JPG, or PNG files. The other method is the streaming method that's used to stream either audio or video files. A CloudFront distribution can only support one of these methods at a time, hence the creation of the streams.thetajoin.com bucket to support the CloudFront distribution that will stream the flash video file. Log in to the Amazon Web Services (AWS) Manager to access CloudFront: https://console.aws.amazon.com/cloudfront/home . Once there, the streams.thetajoin.com bucket can be configured to support CloudFront streaming. Click on the Create Distribution button:

Choose Streaming for the Delivery Method, select the Amazon S3 bucket, give it a CNAME the same as the Amazon S3 bucket name, and make sure it's enabled. Creating the CloudFront streaming distribution can take some time. AWS Manager will show that the distribution was created:

The streams.thetajoin.com bucket has the following attributes:

• Delivery Method: Streaming
• Domain Name: s9vf9f1gnogon.cloudfront.net
• Origin Bucket: streams.thetajoin.com.s3.amazonaws.com
• State: Deployed
• Status: Enabled

The streams.thetajoin.com bucket is ready to support streaming of any streamable content stored there.

5. Configure DNS

The URL to the CloudFront distribution is s9vf9f1gnogon.cloudfront.net, which is on the ugly side and doesn't maintain your site domain name. It would be great for your site branding to use a portion of the origin bucket URL, streams.thetajoin.com only. To do this, you'll need to configure your DNS server with a CNAME value that's the name of your Amazon S3 bucket, that points to the Domain Name of your CloudFront distribution. In ThetaJoin's case streams.thetajoin.com points to s9vf9f1gnogon.cloudfront.net. Now, when a URL with the domain streams.thetajoin.com is requested from the media player, the DNS server will "redirect" the URL to s9vf9f1gnogon.cloudfront.net. From the end user, it'll look like your streaming content is being delivered from your own private content delivery network.

Amazon S3 and CloudFront are configured to support streaming the flash video that was converted in step 1. Part III of this series will show how to add streaming content to your site.

Lately, I've had a few people ask me "How much data is in our database?". Sure, you can look at the file sizes of MyISAM tables and indexes and get a ballpark figure, but what if you need exact results, or are running InnoDB storage engine? That proves to be more challenging! In playing around with the information_schema, I've put together some queries to help:

Calculate Index Sizes

mysql> SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024*1024), 2), ' GB') AS 'Total Index Size'
FROM information_schema.TABLES 
WHERE table_schema LIKE 'database'; 

+------------------+
|Total Index Size  |
+------------------+
|1.70 GB           |
+------------------+

1 row in set (1.60 sec) 

Calculate the Total Size of Stored Data

mysql> SELECT CONCAT(ROUND(SUM(data_length)/(1024*1024*1024), 2), ' GB') AS 'Total Data Size' 
FROM information_schema.TABLES 
WHERE table_schema LIKE 'database';

+-----------------+
| Total Data Size |
+-----------------+
|3.01 GB |
+-----------------+

1 row in set (1.35 sec) 

Per Table Sizes

SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,2),'M') AS 'Number of Rows', 
CONCAT(ROUND(data_length/(1024*1024*1024),2),'G') AS 'Data Size', 
CONCAT(ROUND(index_length/(1024*1024*1024),2),'G') AS 'Index Size' ,
CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),2),'G') AS'Total'
FROM information_schema.TABLES 
WHERE table_schema LIKE 'database'; 

Just replace database with the partial name of your database you need to analyze. Yes, I know, those wonderful Maatkit tools contains mk-find which can do the same thing, but then you won't learn about the information_schema database!! It's important to also know that running the above commands on a server that's very busy can result in slowing it down even more. The information schema tables are 'virtual tables' they don't exist like regular database tables. Just be aware, and if you need to collect this information on a busy database, make a backup copy of your Drupal database and move it to a development server.