MySQL Tips From Percona Live 2012

Last week I summarized the Percona Live 2012 Conference for you. I went over some of the different topics that were covered during the talks. Today I’m going to briefly discuss the 2 tutorials that I personally felt were most beneficial to me and hopefully they will be to you too! Both talks were designed for Database Administrators (DBA) but could be easily appreciated by application developers and Systems Administrators as well. Ronald Bradford (Effective MySQL) was the speaker at both talks made them feel like a 2 part series. I found the information in “Idiosyncrasies of MYSQL That Bite” to build on the first talk, “Explaining the EXPLAIN”.

Explaining the EXPLAIN was the first talk of the two. Ronald Bradford used this presentation to discuss how the MySQL Query Optimizer develops a Query Execution Plan (QEP). He pointed out that EXPLAIN will only work on SELECT statements. This means you may need to re-write a query into a SELECT using WHERE. It’s interesting to note that the QEP is generated every time MySQL actually processes a query. There is no way to guarantee that a query is run in the same fashion as the QEP provided using EXPLAIN but chances are good. I learned that based on how your query is formed and how your tables are built, there are times where MySQL might just completely re-write your query to get the results you are looking for, and the resulting queries could be very expensive.

The second talk was called Idiosyncrasies of MySQL That Bite, also by Ronald Bradford. A lot of what he talked about here was actually familiar to me, but there were some extremely valuable insights that came from his time working with databases in general. For instance, I knew that the default date for a DATETIME column was all zeros, but I never considered how badly this could affect critical data where MySQL has replaced malformed dates with the default. I also never thought about how a misspelled “`ENGINE=InoDB`” or charset mismatch could completely change the way perfectly normal queries could run.  Luckily, MySQL has the SQL_MODE variable which can be used to modify most of this kind of behavior.

Both talks were valuable to me, partly because in each one, the speaker showed us some very interesting quirks to look out for in MySQL, and what to do when you see them, or how to even prevent them all together. He did it in a way that was fun, and easy to understand. I hope you were able to take away some tips as well.

6 Steps to Take After Your Server Has Been Compromised

When maintaining a secure server, make sure to take the following precautions: Keep your software up to date, use strong passwords, follow the principal of least privilege and employ defense in depth.

Sometimes the precautions don’t always work. No one ever wants their server to be hacked or compromised, but unfortunately it can and does happen. With a little preparation, you can quickly respond to a hacked server so you can smoothly recover your server – ultimately limiting the amount of data exposed or lost and minimizing your downtime. Below are 6 steps that you should immediately do after your server has been breached; they don’t have to be completed in order but will definitely help in mitigating future risk.

Step 1: Know Who is Involved – Of everyone responding to the security threat, make sure everyone knows what their own role is and what the roles of others are. Understanding how to communicate amongst each other will help all of the subsequent steps.

Step 2: Assess the Scope – Understand what has already been observed, what has already been done and what is currently happening. This quick assessment will help determine which steps should be performed next and in what priority to prevent future network security issues.

Step 3: Contain the security breach – These immediate steps will help prevent further damage or information loss. If the attacker is actively on your system, you may need to kill their processes immediately to limit further damage. This may involve blocking the network to a specific block of IP addresses, especially if data is actively being streamed off of the server to the attacker’s machine.

Step 4: Maintain State – Your classification and impact analysis of the compromised system will help determine if the security breach should be contained or the state maintained first. For further forensic work, it is best to leave the system unaltered (or as unaltered as possible). You can copy log files or take an image of the disk for further analysis. This also prevents the attacker for altering the logs.

Here is a simple example of how to clone a block device or partition: On the secure system, run nc -l -p 9999 | dd of=/dev/incident-xyz.dd. Then on the compromised system, run dd if=/dev/sda | nc <ip of secure system> 9999. You can also use dcfldd, which provides hashing on-the-fly among other useful features. To limit the amount of empty space being transferred, you can also use gzip between dd and netcat on both servers.

Step 5: Notifications – Follow the appropriate process for notifying the impacted, may include PCI or HIPAA regulations or federal and state laws depending upon the nature of the data exposed (or potentially exposed).

Step 6: Remediate – Putting the same vulnerable hacked server back online will likely only lead to another incident, so any workarounds or patches should be applied before bringing services back online. This may include data integrity checking or a complete system re-install.

Throughout this process, make sure you communicate with the rest of the team, keep a cool head, make sure you document what you are observing, and what actions are being taken with your network security issue. If you believe your server has been compromised and still don’t know what to do let us know, we can help.

Discover htop

Here’s a cool program that can be pretty helpful: htop (http://htop.sourceforge.net/).

Monitoring the vital stats on a Linux server can be difficult; you’ve got a ton of utilities like top, free, /proc, vmstat, sar, along with logs to sort through. Imagine if on Linux you could watch each process, view its threads and sub processes, track memory usage and sort your entire systems performance by whatever variables you desired? This is possible and much more with htop.

Htop calls itself “an interactive process viewer for Linux” that runs from the console as an ncurses-built application. Once you load it, right away you’ll notice some considerable improvements over the standard top. The use of ncurses gives htop visually pleasing and functional text-based graphical interface. Along the top of the screen you’ve got color coded bars:

  • CPU usage, color coded to show cpu time by normal, kernel, irq, and io-wait usage
  • Memory usage, color coded to show actual usage vs. cached and buffer usage–a huge timesaver instead of trying to interpret the output of vmstat or free!
  • Uptime and load averages.

The process list in htop is also vastly improved. You can scroll through it (arrow keys), view it in tree mode (F5) to show links between threads spawned by a process, sort by columns (F6) any of the defined metrics, search for keywords (F3), change nice level (F7,F8) or even interactively kill a specific process (F9).

Enter setup (F2) and you’ll find that everything about the main window is customizable. You can display as little or as much information as you want it to. You can also change the style of or add additional meters to the top, as well as change the default color coding. The columns in the main part of the display are also entirely customizable allowing you to choose from a wide variety of available system monitors and resource counters.

The best part about htop is that is easy to use and understand, even a novice can get a look at what’s going on under the hood of their machine, but it’s still powerful enough to use as a serious timesaving device for the advanced user. Once you get in the habit of using htop, it’s something you’ll want to have at your fingertips at all times.

Tips for Designing a Solid Backup Plan

Not having to think about backups is awesome and what would be even more awesome is not having to design a solid backup plan for your environment. But that’s not the name of the game. In complex IT environments it’s difficult to come up with a backup solution, however it’s necessary if you want to be able to sleep peacefully at night. Here’s some tips on how to design a solid backup plan:

Keep Reading »

Troubleshooting Your Windows DFSR (Distributed File System Replication)

So you have DFSR setup and you run into issues with files not being copied, missing files or even performance issues. Here are some troubleshooting tips to investigate and possibly correct common DFSR issues.

First a quick introduction on DFSR. DFSR (Distributed File System Replication) formerly DFS, is a replication service that keeps folders/files synchronized across multiple servers. This allows for higher data availability using RDC (Remote Differential Compression). RDC allows for efficient updates of files across the network.

Keep Reading »

©1996-2011 INetU Inc, All Rights Reserved.