I received a panic call from a newbie MySQL DBA. Or should I say the ‘Linux Admin’/’MySQl DBA’/’CSS guru’/’PHP Programmer’/’Network Admin’/’Backup Operator’/’CIO’ of a small business. He had reset his password was was now locked out. Luckily, he had only changed his password and still had root access.

What he did:
mysql>use mysql;
SET PASSWORD for 'mrdoesall'@'10.%' to 'bigsecret';
mysql>


Long time MySQL DBAs should be groaning at this with a wince remembering when it happened to them. For those of you who did not catch the problem, what happened is that he value in the user.password table is set to the string ‘bigsecret’. When our friend tries to login, the password is encrypted and compared to the value in user.Password. The comparison of the encrypted value does not equate to the unencrypted value and the login fails.

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!

Presents for your MySQL DBA Cafe Press yet again has a large line of DBA themed t-shirts. This shirt is available in Women’s, Maternity, and seven other styles at Zazzle has its own page of DBA shirts …

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 …

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

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 …

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 …

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.

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. …

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.

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 …