Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 28

Displaying posts with tag: basics (reset)

Changes in MySQL 5.6.20
Employee +1 Vote Up -0Vote Down

The MySQL Release Notes should be part of any DBA’s regular reading list. The Changes in MySQL 5.6.20 came out last week and there are some interesting goodies.

  • MySQL now includes DTrace support on Oracle Linux 6 or higher with UEK kernel.
  • A new system variable binlog_impossible_mode controls what happens if the server cannot write to the binary log, for example, due to a file error.
  • The mysqlhotcopy utility is now deprecated and will be removed in a future version of MySQL

5.6.20 has a slew of bug fixes, functionality changes, and notes.

So why should you be reading the changes on a regular basis? There isa goldmine of information in them. For instance, if you use

  [Read more...]
MySQL APT Repository
Employee +1 Vote Up -0Vote Down

THe MySQL APT Repository provides an easy and convenient way to get the latest MySQL software. My test server was need of a refresh so I put on a fresh install of Ubuntu 14.04 and downloaded mysql-apt-config_0.2.1-1ubuntu14.04_all.deb.

sudo dpkg -i mysql-apt-config_0.2.1-1ubuntu14.04_all.deb
[sudo] password for dstokes:
Selecting previously unselected package mysql-apt-config.

You will get a choice to install MySQL 5.6 or the latest 5.7 DMR.

sudo apt-get update Pulls the latest information from the repository for the various packages.

sudo apt-get install mysql-server Installs the server and will start it running. And then a quick sudo apt-get install mysql-workbench to get me where i needed to

  [Read more...]
Triggers — MySQL 5.6 and 5.7
Employee +4 Vote Up -0Vote Down

MySQL Triggers are changing in 5.7 in a big way. Triggers have been around since 5.0 and have not changed much up to 5.6 but will gain the ability to have multiple triggers on the same event. Previously you had ONE trigger maximum on a BEFORE UPDATE, for example, and now you can have multiple triggers and set their order.

So what is a trigger? Triggers run either BEFORE or AFTER an UPDATE, DELETE, or INSERT is performed. You also get access to the OLD.col_name and NEW.col_name variables for the previous value and the newer value of the column.

So how do you use a trigger? Let say you are updating the price of an inventory item in a product database with a simple UPDATE statement. But you also want to track when the

  [Read more...]
Foreign Keys and MySQL
Employee +1 Vote Up -0Vote Down

Foreign Keys are often a mystery to new DBAs in the MySQL world. Hopefully this blog will clear some of this up.

In this example, we will have a table for employee data and a table for the data on offices. First we need the two tables.
CREATE TABLE employee (
-> e_id INT NOT NULL,
-> name CHAR(20),
-> PRIMARY KEY (e_id)
-> );

CREATE TABLE building (
-> office_nbr INT NOT NULL,
-> description CHAR(20),
-> e_id INT NOT NULL,
-> PRIMARY KEY (office_nbr),
-> FOREIGN KEY (e_id)
-> REFERENCES employee (e_id)

Those who do not use Foreign Keys will not be familiar with the last four lines of the building table. The trick is

  [Read more...]
MySQL Slashes and Backslashes
Employee +1 Vote Up -0Vote Down

There was a on Twitter to @MySQL How can I insert “/” into a varchar field please. Since they were polite and used please, I wanted to be helpful.

At first I thought this was the ol’ MySQL string literal problem where you need to have two backslashes to get one backslash in a character field. See String Literals in the MySQL Manual. A lot of people have trouble with the backslash when they first get started with MySQL. From the quote below, you will see that some characters need to be escaped with a backslash. But what about the forward slash?


  [Read more...]
Learning PHP, MySQL, GIT, CSS, HTML, OOP, etc.
Employee +1 Vote Up -0Vote Down

“How do I learn PHP, MySQL, GII, CSS, HTML, OOP, and all that quickly, without becoming confused or frustrated but still get to do the cools stuff?” I get this question on a very frequent basis and until last Friday, I had to suggest a number of ways of pulling together all the pieces together. But it still lacked a comprehensive theme and flow. Then last Friday at LonestarPHP, the LonestarPHP organizers teamed with PHPWomen,to provide a foundations track that covered all the above and more. Davey Shafik, Elizabeth Smith, Matt Frost, and Michelle Sanver put together an amazing day of learning with PHPBridge for a crowd of about

  [Read more...]
Newbie password mistake
Employee +2 Vote Up -0Vote Down

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';

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

  [Read more...]
Presents for DBAs
Employee +0 Vote Up -0Vote Down

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.
  [Read more...]
Last Day to register for North American MySQL Virtual Developer Day!
Employee +3 Vote Up -0Vote Down

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!

Rob Young, Product Management Director, Oracle

  [Read more...]
North Texas MySQL Users Group Meeting April 2nd
Employee +2 Vote Up -0Vote Down

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

MySQL Basics — mapping territory
Employee +2 Vote Up -0Vote Down

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

  [Read more...]
Install Tricks
Employee +1 Vote Up -1Vote Down

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

  [Read more...]
MySQL Marinate Updates
Employee +2 Vote Up -0Vote Down

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.

  [Read more...]
Employee +2 Vote Up -0Vote Down

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

  [Read more...]
Two Dallas talks this week!
Employee +1 Vote Up -0Vote Down

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.

  [Read more...]
MySQL Utilities mysqldbcompare
Employee +4 Vote Up -0Vote Down

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@ --destination=root@@localhost davestuff:davestuff
# Source on ... 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

  [Read more...]
MySQL Utilities mysqldbcopy
Employee +4 Vote Up -0Vote Down

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 \
# Source on localhost: ... connected.
# Destination on localhost: ... connected.
# Copying database world renamed as world_copy
# Copying TABLE world.City
# Copying TABLE

  [Read more...]
Dilbert — How long to train a DBA? Dallas MySQL Users Group Meeting
Employee +4 Vote Up -0Vote Down

Today’s Dilbert asks how long to train a technical writer to become a Database Analyst. The answer? 45 minutes!

Right now there is a big shortage of MySQL DBAs. Recently at OSCON, I asked a group how do we best develop new DBAs. The consensus was to train Linux administrators. For the past year I have been working on The Proper Care and Feeding of a MySQL Database for Linux Administrators and will work to expand it in hopes it will become a good starting place for those wanting to add MySQL DBAs skills to their professional arsenal. Please let me know what you have found that works in this area for your situation so we can get more MySQL DBAs.

And there will be a Dallas MySQL Users Group

  [Read more...]
Newbie: User and Host question
Employee +1 Vote Up -1Vote Down

Today on MySql Forums, there was a question in the newbie section about two users — I have a doubt on db host and db user relationship . What does this mean ?

name host tom % joe

New DBAs are often confused by the quirky methods of authentication that MySQL uses. Heck, extremely experienced MySQL DBAs can get confused.

From the manual, 6.2.4. Access Control, Stage 1: Connection Verification

When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct

  [Read more...]
Waffle House — smothered and covered business metrics
Employee +3 Vote Up -0Vote Down

MySQL Stickers

How to measure a Storm’s Fury One Breakfast at a Time is a non MySQL-Centric story I need to share with my fellow DBAs. The US Federal Emergency Management Agency has a metric to judge the severity of a storm. They monitor the status of Waffle House Restaurants. Waffle House goes to extreme measures to open up storm damaged or impacted restaurants that need to be studied by anyone who had any disaster recovery duties. Their measures are so extensive that FEMA now tracks which Waffle Houses are

  [Read more...]
MySQL logins, passwords, and hosts
Employee +1 Vote Up -0Vote Down

“I needed a password eight characters long so I picked Snow White and the Seven Dwarfes.” — Nick Helm1

MySQL Account Management is often nebulous mess to new DBAs. Frank2 is a new MySQL with many years of Linux administration duties in his background and now he has been taking on more duties after his companies previous DBA departed. One of his co-workers was in the middle of a name change and could no longer access the need instance. Frank had run

mysql -u root mysql
mysql>UPDATE user SET Name='jjones' where Name='jsmith';

But Mrs. Jones was not able to access the database. Frank tried for a while to find the answer. To greatly simplify the MySQL authorization process, the sever checks the Host of a new connection, then the User and Password. Frank

  [Read more...]
Does Oracle offer support for Community MySQL Server?
Employee +6 Vote Up -0Vote Down

Yes. Any customer that is comfortable with the Community Edition of the server but
desiring Oracle Support can get it. This gives you 7/24 support for Oracle for the world’s most popular open source database.

For details, see page ten of http://www.oracle.com/us/support/library/057419.pdf

  [Read more...]
Things to make you smarter
Employee +4 Vote Up -0Vote Down

It is hard to keep track of what is evolving in the MySQL environment. But there are three quick things you can do to help keep yourself up to speed.

1. Podcasts

Sheeri K. Cabral and Sarah Novotny put together a weekly podcast that you need to be hearing. The February 11th edition was an in depth look at using logical volume snapshots with my colleague Lenz Gimmer. If you use LVM or are looking for a way to snapshot your instance, this is a master class on the subject.

2. Webinars

Upcoming free seminars on MySQL Administration Tools, the new Enterprise backup product, and the latest on cluster are going to be presented in the next few weeks.


  [Read more...]
MySQL Basics Part 3 – Your my.cnf or my.ini file
Employee +2 Vote Up -0Vote Down

Many novice MySQL DBAs peek at their option files with some trepidation. Many experienced members of their brethren view the files as incantations from some Harry Potter-ish dark art. These files are fairly simple to read and are there to help DBAs. The option or configuration files exist to keep you from having to type all your variations on the command line each time you restart your server.

At the bottom of this entry is the complete my.ini file from the MySQL instance on my laptop. It is nothing special, MySQL 5.5 on a Windows XP Oracle-standard Dell desktop, and I asked the Windows Installation Wizard that I wanted tables to support transactions.

This results in an option file that has fewer than two dozen lines to run the server. There are a good deal of comment lines that make the file larger. But the

  [Read more...]
MySQl Basics — Configuration Files
Employee +1 Vote Up -1Vote Down

This is a continuation of the previous blog entry but now I am going to assume that you have your MySQL 5.5.8 software installed but not started – yet.

Why option files?

The MySQL server can be run with options provided on the command line and any DBA who has been around the job for a while will know –skip-grant-tables. Most instances run with too many options to input the desired options by hand over and over again. So most people use a option file to keep the configuration information.

What is the option file named?

Windows instances will have a my.ini file.

*NIX instances will have a my.cnf,

But that configuration file name can be changed on the command line.

Where are these files found?

Windows folks will should start to look in

  [Read more...]
MySQL Basics, Part 1
Employee +4 Vote Up -0Vote Down

The new calendar year is a good time to review what coaches call fundamentals or basics and martial arts senseis call ki-hon.

What version of MySQL should I be using?

Hopefully you are using MySQL 5.5.8 as it is the latest generally available release. If you are running the version 5.1 or older, you need to start reading the release notes and upgrade procedures. 5.5.8 has better performance and has many new features.

What platforms are supported by MySQL 5.5.8 ?

  • Windows 32-bit and 64-bit in either MSI Installer format or as a Zip archive.
  • SuSE SLES Version 11 in 32-bit and 64-bit RPMs for x86 SLES Version 10 in 32-bit and 64-bit RPMs for x86 or IA-64 RPMs.
  • Red Hat & Oracle Enterprise Linux 5 in 32-bit or 64-bit RPMs and IA-64 RPMs. Red Hat & Oracle Enterprise Linux 4 in 32-bit or 64-bit
  [Read more...]
MySQL Back to Basics: Analyze, Check, Optimize, and Repair
+0 Vote Up -0Vote Down

It felt like the right time for us to look back at some useful commands for table maintenance that some of us may not have mastered as much as we might like to think.

In my post about gathering index statistics, I referred to OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE — but I never explained in depth what the different commands do, and what the differences between them are. That is what I thought I would do with this post, focusing on InnoDB and MyISAM, and the differences in how they treat those commands. I will also look at different cases and see which one is right for in each case.


Good Database Design is Mightier than Hardware
+0 Vote Up -0Vote Down
Have you ever heard the one about throwing hardware at a software problem? In one of my previous blog posts, I mentioned something along the lines of?well I’ll just cut and paste . . . In my experience, the solution to most problems (the ones the caller refers to as “it’s running slow”) are not rooted in [...]
Showing entries 1 to 28

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.