Category Archives: Basics

Presents for DBAs

Dear Fellow DBAs–You are a pain to shop for at holiday time. Once again @stoker has compiled a list that you can print out, circle what you desire, and hand to a relative. If you do not want a Duck Dynasty Chia Pet or a blanket with arms, print this now NOW!

Leave a comment

Filed under Basics

Last Day to register for North American MySQL Virtual Developer Day!

There is one more day until the 2013 Virtual Developer Day for North America so register today! Europe, Middle East, Africa and Some Asian Countries have a week before their event!

Virtual Developer Day: MySQL is a one-stop shop for you to learn all the essential MySQL skills. With a combination of presentations and hands-on lab experience, you’ll learn the latest features in MySQL 5.6, have the opportunity to practice in your own environment and sharpen your skills to:

Develop your new applications cost-effectively using MySQL
Improve performance of your existing MySQL databases
Manage your MySQL environment more efficiently
Don’t miss this exclusive opportunity to learn tips and tricks from the MySQL experts at Oracle!

WHO:
Rob Young, Product Management Director, Oracle MySQL
Craig Sylvester, Principle Sales Consultant, Oracle MySQL
Ligaya Turmelle, Principle Technical Support Engineer, Oracle MySQL
Lee Stigile, Sales Consulting Senior Manager, Oracle MySQL
Ben Krug, Senior Technical Support Engineer, Oracle MySQL
Calvin Sun, Senior Engineering Manager, InnoDB, Oracle MySQL
Mark Leith, Software Development Senior Manager, Oracle MySQL
Jørgen Løland, Senior Software Engineer, Oracle MySQL
Lynn Ferrante, Principle Sales Consultant, Oracle MySQL
Americas and Canada – March 12th, 2013

9:00 a.m – 1:00 p.m PST / 12:00 p.m. – 4:00 p.m EST / 1:00 p.m – 5:00 p.m BRT

Europe, Middle East, Africa and Some Asian Countries (English) – March 19th, 2013

9:00 – 13:00 UTC/GMT / 10:00 – 14:00 CET / 12:00 – 16:00 AST / 13:00 – 17:00 MSK / 14:30 – 18:30 IST

March 12, 2013 Online – access from your computer directly Learn more and register for the virtual event »
March 19, 2013 Online – access from your computer directly Learn more and register for the virtual event »

2 Comments

Filed under Basics, MySQL

North Texas MySQL Users Group Meeting April 2nd

Come early to have pizza and network and then see a presentation and demo of the new features in MySQL 5.6.

RSVP to make sure we get a large enough room and sufficient pizza.

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

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

Leave a comment

Filed under Basics, community team, MySQL

MySQL Basics — mapping territory

This blog entry is on a step that many DBAs either skip, ignore, or do not know about. My last entry was on some install tricks and this time I want to cover where to put the various pieces of a MySQL server if you are looking to maximize performance. George Trujillo covered this for Oracle DBAs transitioning to MySQL but generally you do not see a lot of best practices information on this subject.

1. Pick a spot on a drive for the server software. It does not matter if it is /opt/mysql, /usr/local/mysql, or some variant.
2. Pick a spot on another drive that is on another disk controller for the log files.
3. Pick a spot on yet another drive that is on yet another disk for the data. Or get different spots for the various schemas you have to support — accounting, manufacturing, shipping all on different drives and controllers.

The more you separate the various pieces, the less bottle necks in the hardware will arise. With the data, programs, logs, and everything else on the system on one disk it is very easy get to the point where waiting for the disk to complete a prior read/write to complete. Given a preference, splitting the pieces to different controllers would be the best. But sometimes you get stuck with having to use one controller (and it will take away some potential performance).

The biggest points of contention will be log files, especially if you are using replication, and the data. So if you can only get two separated drives/controllers, please do split these two up.

But what if you are corporately mandated to use a SAN? Well, SANs are very useful. But if you are trying to eek that last 15% out of a server, then you need to get off that SAN. Virtual environments have a similar problem in that visualization itself if not going to be as fast as running the same code on the real hardware. If you are required to be on the corporate SAN check with the SAN administrators to see if there is some way to cut down on contention.

RAID? RAID 1, 5 or 10? Yes. Please use RAID and go with the level you are most comfortable with. Paranoia unfortunately pays off over time with disk drives. And a good RAID does not mean you do not have to make backups.

And if you are just starting out with a single instance on an old laptop or desktop in order to learn MySQL, then you are not concerned with performance – yet – and you can get by on a single disk. But please starting looking at the variable that point to where various items are stored. Hint SHOW VARIABLES LIKE ‘%DIR%’;

Leave a comment

Filed under Basics, MySQL

Install Tricks

Installing MySQL is a simple process. It is very common on a modern Windows machine to install in under three minutes. The Windows Installer Team have really increased the speed in the last few releases. But what if you are running Linux?

For the very fussy who want everything tailored ‘just so’, download the source code and configure exactly what you want. Don’t need Serbian character sets, different collations, or some storage engines you never use? Then read up on Installing MySQL from Source. With a bit of time and fuss, you can have a minimalist server that contains only the components you need. This is not recommended unless you know you can lock down the system and never, ever need to ad more components on the fly.

Next comes the MySQL Binaries. Untar/Zip the file from Dev.MySQl.Com, point your config files to the correct places, and run mysqld_safe. Read Installing MySQL from Generic Binaries on Unix/Linux for the details. This is my personal favorite as I can have several versions installed and change versions by starting the server for that version. The /usr/local of my test server has a version of 5.1, two versions of 5.5, and four versions of 5.6 available. Be sure to make your startup scripts point to /usr/local/mysql and that directory is linked to the version you want to use.

Finally comes the packages. Both let you leave much of the worry of software inconsistencies into package management software. This makes upgrades easier. However some distro are s-l-o-w about getting the latest and greatest MySQL packaged for their flavor. So please take a look at the packages from Dev.MyQL.Com rather than wait. Peruse Installing MySQL on Linux before you begin.

Next time we will cover initial configuration and start up.

2 Comments

Filed under Basics, MySQL

MySQL Marinate Updates

MySQL Marinate has started strong and it is not too late for you to join!

Join the MySQL Marinate self study group. We are only in week two and you can easily catch up with the rest of the class.

162 signed up for this FREE virtual self-study course from the Boston MySQL Users Group. Each week the group covers a chapter and does the homework. The homework from the first two weeks was pretty light and, if you get stuck, the participants are very helpful.

1 Comment

Filed under Basics, MySQL

EXPLAIN versus VISUAL EXPLAIN part I

MySQL VISUAL EXPLAIN

MySQL:’s new visual EXPLAIN provides a graphical representation of what the optimizer plans to run your code. Compare to CLI version below.

Optimizing MySQL Queries is often wrongly viewed by many DBAs as a ‘dark art’. For years we have been pre-pending EXPLAIN to SELECT statements in hopes of looking into the entrails of our offered code in hopes that the query optimizer deities looked whit favor on our efforts. But with MySQL 5.6 and the latest versions of MySQL Workbench, we have now entered into the age of an EXPLAIN that works with more than just SELECT statements and the ability to see graphically what is happening to our queries in the optimizer. Take a look at the two screen captures in this post and compare them. The query used is a very simple two table join from the good ol’ World database. Command Line EXPLAIN of a MySQL Query

I will dig into some other examples next post on what poor queries look like with VISUAL EXPLAIN as well as some well written queries. This is one graphic tool that an old command line dinosaur like me can enjoy!

2 Comments

Filed under Basics, MySQL

Two Dallas talks this week!

Two talks in the Dallas area this week. On Tuesday, the North Texas MySQL Users Group, a special interest group of the Dallas Oracle Users Group, is meeting and the subject will be MySQL 101. So please load MySQL and Workbench on a laptop (or try to) and we will go over the basics. RVP so we can get the right amount of pizza ordered!

Then on Thursday, I will present The Proper Care and Feeding of a MySQL database for Linux Administrators at the Dallas/Fort Worth Unix User Group meeting.

Leave a comment

Filed under Basics, MySQL

MySQL Utilities mysqldbcompare

Need to copy a database from another server to another and make certain that the two are identical? The previous blog entry was a quick into to mysqldbcopy from the MySQL Utilities. This time we use mysqldbcompare to double check on the database we just copied. This is a very quick way to copy a database from a master to a slave or from production to a test server.

$ mysqldbcopy --force --source=root@10.0.0.18 --destination=root@@localhost davestuff:davestuff
# Source on 10.0.0.18: ... connected.
# Destination on localhost: ... connected.
# Copying database davestuff renamed as davestuff
# Copying TABLE davestuff.a
# Copying GRANTS from davestuff
# Copying data for TABLE davestuff.a
#...done.
$mysqldbcompare -a --server1=root@10.0.0.18 --server2=root@localhost davestuff:davestuff
# server1 on 10.0.0.18: ... connected.
# server2 on localhost: ... connected.
# Checking databases davestuff on server1 and davestuff on server2
#
# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE a pass pass pass

Databases are consistent.
#
# ...done

Leave a comment

Filed under Basics, MySQL

MySQL Utilities mysqldbcopy

The MySQL Utilities that come with Workbench can make short work of some important tasks. Back before MySQL 4, I used to have to regualrly create a copy of a production database by using a script that used mysqldump to save the database, create the new database, and feed in the data from the dump. It was not pretty but it worked reasonable well. But I longed for a more universal, one step process to do this work. And now we have mysqldbcopy.


$mysqldbcopy \
--source=root:xxxx@localhost:3306:/var/run/mysqld/mysqld.sock \ 
--destination=root:xxxx@localhost:3306:/var/run/mysqld/mysqld.sock \
world:world_copy
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database world renamed as world_copy
# Copying TABLE world.City
# Copying TABLE world.Country
# Copying TABLE world.CountryLanguage
# Copying data for TABLE world.City
# Copying data for TABLE world.Country
# Copying data for TABLE world.CountryLanguage
#...done.

5 Comments

Filed under Basics, MySQL