Category Archives: Uncategorized

MySQL Utilities — The Database Operations

Version 1.5 of the MySQL Utilities are divided into five groups — Database Operations, General Operations, High Availability Operations, Server Operations, and Specialized Operations.

The Database Operations are mysqldbcompare, mysqldbcopy, mysqldbexport, mysqldbimport, and mysqldiff.

mysqldbcopy is used to copy databases between servers (or clone them on the same server. The default is to copy (or clone) everything or the –exclude option to exclude an object, change the storage engine with –new-storage-engine, and locking of the source databse can be turned off with –locking. There is even a –rpl option to pass replication statements so the the new copy a clone of the source server or sync the new copy to the same master as that the slave used for the source database. There is a –drop-first to make a fresh copy, -all to copy all databases

mysqldbcopy --source=root:hidave@localhost --destination=root:hidave@localhost 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.

More details on the man page.

After copying (or cloning) a database it is easy to double check
your work with mysqldbcompare.
mysqldbcompare --server1=root:hidave@localhost world:world_copy --run-all-tests
# server1 on localhost: ... connected.
# Checking databases world and world_copy on server1
#
# Object definitions differ. (--changes-for=server1)
#

--- `world`
+++ `world_copy`
@@ -1 +1 @@
-CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `world_copy` /*!40100 DEFAULT CHARACTER SET latin1 */

# Defn Row Data
# Type Object Name Diff Count Check
# -------------------------------------------------------------------------
# TABLE City pass pass pass
# TABLE Country pass pass pass
# TABLE CountryLanguage pass pass pass

# Databases are consistent.
#
# ...done

More at the man page.

It is easy to use mysqldiff to check differences between two instances. For demonstration purposes, a dummy column was added to wold_copy.City.
mysqldiff --server1=root:hidave@localhost world:world_copy
# server1 on localhost: ... connected.
# Comparing `world` to `world_copy` [FAIL]
# Object definitions differ. (--changes-for=server1)
#

--- `world`
+++ `world_copy`
@@ -1 +1 @@
-CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET latin1 */
+CREATE DATABASE `world_copy` /*!40100 DEFAULT CHARACTER SET latin1 */
Compare failed. One or more differences found.

With version 1.5 id comes the –diff to display the differences.

The mysqldbexport command exports metadata, data, or both. There is a –bulk-insert can be used with –export=DATA to export data from a database for bulk import.
mysqldbexport --server=root:hidave@localhost world
# Source on localhost: ... connected.
SET FOREIGN_KEY_CHECKS=0;
# Exporting metadata from world
DROP DATABASE IF EXISTS `world`;
CREATE DATABASE `world`;
USE `world`;
# TABLE: world.City
CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
# TABLE: world.Country
CREATE TABLE `Country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
`LifeExpectancy` float(3,1) DEFAULT NULL,
`GNP` float(10,2) DEFAULT NULL,
`GNPOld` float(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int(11) DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
# TABLE: world.CountryLanguage
CREATE TABLE `CountryLanguage` (
`CountryCode` char(3) NOT NULL DEFAULT '',
`Language` char(30) NOT NULL DEFAULT '',
`IsOfficial` enum('T','F') NOT NULL DEFAULT 'F',
`Percentage` float(4,1) NOT NULL DEFAULT '0.0',
PRIMARY KEY (`CountryCode`,`Language`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `countryLanguage_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
#...done.
SET FOREIGN_KEY_CHECKS=1;

The man page.

And fianlly mysqldbimport is used to read metadata, data or both. Note that if an object exists on the destination server it will be dropped. If you need to copy only the data after a truncate table, use the –import=data option. See the man page for details.

Next time: The MySQL Utilities General Operations

Leave a comment

Filed under Uncategorized

Raleigh MySQL Tech Tour

MySQL Raleigh Tech Tour

Please join MySQL Sr. Sales Consultant Tony Darnell who will review latest and greatest updates in MySQL 5.6 and share his experience on how they improve performance, scalability and high availability. Topics include:

  • MySQL 5.6 — New Features Update
  • Security Improvements — Encryption
  • Fabric
  • MySQL Enterprise Tools Update
  • MySQL Utilities Update
  • New MySQL Plug-In for Oracle OEM
  • The Road Ahead — MySQL 5.7
  • Oracle Support for MySQL

To register you must have an Oracle account, if you don’t have an Oracle account, sign up is quick and easy.

We look forward to your participation at this exclusive seminar.

Oct 21, 2014
08:30 AM – 12:30 PM
Oracle Raleigh
8081 Arco Corporate Drive,
Suite 270
Raleigh, NC 27617

Featured Speaker:

Tony Darnell,
Sr Sales Consultant

David Stokes,
MySQL Community Manager

Leave a comment

Filed under Tech Tour, Uncategorized

Fabric Webinar with Andrew Morgan June 19th.

MySQL Fabric – High Availability & Automated Sharding for MySQL

MySQL Fabric is built around an extensible and open source framework for managing farms of MySQL Servers. Currently two features have been implemented – High Availability (built on top of MySQL Replication) and scaling out using data sharding. These features can be used in isolation or in combination. MySQL Fabric aware connectors allow transactions and queries to be routed to the correct servers without the need for a proxy node, so operations run as quickly as ever. MySQL FabricIn this webinar you will learn what MySQL Fabric is, what it can achieve and how it is used – by DBAs, Dev-Ops and developers. You’ll also be exposed to what is happening under the covers. In addition to the presentation, there will be live on-line Q&A with the engineering team. This is a great opportunity to learn about the latest developments directly from the people building them.

WHO:
Andrew Morgan, Principal MySQL Product Manager
Mats Kindhal, Senior Principal Software Developer
WHEN:
Thu, Jun 19: 09:00 Pacific time (America)
Thu, Jun 19: 10:00 Mountain time (America)
Thu, Jun 19: 11:00 Central time (America)
Thu, Jun 19: 12:00 Eastern time (America)
Thu, Jun 19: 13:00 São Paulo time
Thu, Jun 19: 16:00 UTC
Thu, Jun 19: 17:00 Western European time
Thu, Jun 19: 18:00 Central European time
Thu, Jun 19: 19:00 Eastern European time
Thu, Jun 19: 21:30 India, Sri Lanka
Fri, Jun 20: 00:00 Singapore/Malaysia/Philippines time
Fri, Jun 20: 00:00 China time
Fri, Jun 20: 01:00 日本
Fri, Jun 20: 02:00 NSW, ACT, Victoria, Tasmania (Australia)

The presentation will be approximately 60 minutes long followed by Q&A.
WHERE:
Simply access the web seminar from the comfort of your own office.

Register

Leave a comment

Filed under MySQL Fabric, Uncategorized, Webinar

MySQL 5.7 & Fabric in Sunnyvale May 22nd

MySQL Fabric and 5.7 will be the topics of presentations this Thursday (5/22) at the Plug and Play Tech Center in Sunnyvale, California. Lee Stigile is presenting: What’s new in MySQL 5.7, and Sastry Vendantam is presenting MySQL Fabric.

Agenda is as follows:

5:00-5:30 Networking/Socialize over food and drinks
5:30-6:00 Lee will present MySQL 5.7
6:00-6:30 Sastry will present Fabric
6:30-7:00 Q&A and Socialize over food and drinks

Here is the link to register/RSVP

Plug and Play Tech Center
Thursday, May 22, 2014 from 5:00 PM to 7:00 PM (PDT)
440 N Wolfe Rd
Sunnyvale, CA 94085

1 Comment

Filed under Uncategorized

Triggers — MySQL 5.6 and 5.7

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 price change and the old price.

The table for products.
CREATE TABLE products (id INT NOT NULL auto_increment,
price DECIMAL(5,2) NOT NULL,
PRIMARY KEY (id));

The table for price changes on the product table.
CREATE TABLE products_log (id INT NOT NULL,
price DECIMAL(5,2) NOT NULL,
change_date timestamp);

Now to define a trigger that will log price changes. We do this when a price is updated. Now the use od OLD.price to avoid confusion between the old price or the new price being saved in the log.
DELIMITER |
CREATE TRIGGER product_price_logger
BEFORE UPDATE ON products
FOR EACH row
BEGIN
INSERT INTO products_log (id, price)
VALUES (id, OLD.PRICE);
END
|
DELIMITER ;

Add in some data.
INSERT INTO products (price) VALUES (1.10),(2.24),(.99),(.01),(.34);

So UPDATE a record.
UPDATE products SET price='1.11' WHERE ID = 1;

So did it work? Yes, and no. Running SELECT * FROM products_log; Provides us with a time stamp of the change and the OLD.price. But I forgot to also record the id!! Challenge: Correct my mistake and compare it to an update I will make in a few days.

Now 5.7 introduces multiple triggers for the same event. Lets add yet another log this time recording who made the change;

The ‘who made the change table’.
CREATE table who_changed (
id INT NOT NULL,
who_did_it CHAR(30) NOT NULL,
when_did_it TIMESTAMP);

And the second trigger.
DELIMITER |
CREATE TRIGGER product_price_whom
BEFORE UPDATE ON products
FOR EACH ROW
FOLLOWS product_price_logger
BEGIN
INSERT INTO who_changed (id, who_did_it)
VALUES (OLD.id, user());
END
|
DELIMITER ;

So UPDATE products SET price='19.99' WHERE id=4; is run and we see that both triggers execute. Note that SHOW TRIGGERS from schema; does not provide any information on trigger order. But you can find all that as action_order in PERFORMANCE_SCHEMA.TRIGGERS

Being able to order triggers makes it easy to make logical steps when processing data. Can you get into trouble with this? I am certain someone will manage to make a mess with this. But I think most of us will enjoy being able to use this great new functionality.

4 Comments

Filed under Basics, DBA Tools, MySQL, MySQL Certification, Uncategorized

Colorado MySQL Meetup Group November 11th

The Colorado MySQL meetup is getting together on November 11th.

Location: Oracle 500 Eldorado Boulevard, Broomfield, CO

All MySQL users and interested parties are invited to participate and attend the following activities! 

Monday, November 11, 2013

From 6:00PM to 8:30PM

Leave a comment

Filed under Uncategorized, User Group

Studying for the New MySQL 5.6 DBA Exam

I have have been receiving a lot of questions about the new MySQL 5.6 DBA exam. Sadly I can not answer a lot of the questions until after my exam in early November. However I do know some good things about the new exams.

First — No True or False questions. Oracle has set the standards on that policy and I had to update the ol’ 5.0 exams to make certain there the T/F questions were removed. Why is this important? Don’t you have a fifty/fifty chance on T/F questions? The trouble is that some of these questions were a wee bit tricky and needed parsing plus a full understanding of the subject to get them right. For fairness, it is easy to do without the T/F questions.

Second — No more pick up to N answers. Once again, for fairness.

Third — One exam for the certification. In the past the costs, not just financial, of setting up the second exam and studying for it turned off many candidates.

So what am I studying? MySQL 5.6 Database Administrator is a list of the items on the exam.

Under Architecture, that page lists the following

    MySQL Architecture

  • Use MySQL client programs to interface with the MySQL Server interactively and in batch
  • Describe how MySQL uses disk and memory resources
  • List and describe key characteristics of standard MySQL storage engines including InnoDB, NDB, MyISAM, MEMORY, FEDERATED

Let’s take the first bullet point. How does one find the MySQL client programs? The are listed in the MySQL Manaual at http://dev.mysql.com/doc/refman/5.6/en/programs-client.html. So spend some time with mysql, mysqladmin, mysqlcheck, mysqldump, mysqlshow, mysqlimport, and mysqlslap. Read the man pages AND use the commands.

So one bullet point down, 21 to go!

2 Comments

Filed under Uncategorized

Zendcon

Zendcon is a difficult conference if you work in an exhibit hall booth. At other shows, attendees ask general questions about MySql or how Oracle is guiding products. Zendcon is much more pointed. When is 5.7 multisource relication going to be available? How does Visual Explain work? How can I make sure replication goes back to semi-sync after going async? It is a great show but the level of questions are a notch or two higher than most other shows.

20131009-115828.jpg

Leave a comment

Filed under MySQL, Uncategorized

Deep Dive Technical Buffet on last day of MySql Connect

Proving this it is the show for in-depth technical information, MySql Connect finished with a bang. Six different two hour plus deep dive tutorials were offered and all had very good attendence. Many of the attendee were seasoned Oracle DBAs eager to gorge at this buffet of technical knowledge.

The crowd for Ligaya Trumele’s Getting Started
with MySql
was packed with
Oracle DBAs who were amazed at the simplicity of the MySql architecture once they figured out the concept of multiple storage engines.

Luis Soares had a tutorial that was everything you ever wanted to know about 5.6 replication plus a look multi source that will be in 5.7. His discussion on how and when binlogs are written and the impact if that timing on replication was intense, detailed, and worth the price of the show itself.

Finishing a mentally overload day, I listened to Bernd Ocklin on MySql Cluster 7.3 and this product has really changed in the past few years. I highly recommend that you grab his slide deck on how Cluster partitions data.

Leave a comment

Filed under Uncategorized

MySQL Community Team at PHPTek, Drupalcon, Texas Linux Fest, SELF, Redhat Summit, and Lonestar PHP

This is the start of the heavy travel season for the MySQL Community Team. So if you are attending PHPTek, Drupalcon, Texas LinuxFest, SELF, Redhat Summit, or Lonestar PHP please make sure to say ‘hi’ while you are in Chicago, Portland, Austin, Charlotte, Boston, or Addison. This group of trips is starting with a swag bag full of MySQL stickers, thumb drives, and ‘boogie bots’

MySQL Boogie Bots

    Talks

  • PHPTek – Ten Things to Make Your MySQL Servers Faster and Happier — May 16th, 2:45p – 3:45p
  • Texas Linuxfest – The Proper Care and Feeding of a MySQL Database for Linux Administrators — May 31st, 11:25a – 12:20p
  • SELF – Two full days of MySQL talks but I am talking on Ten Things to Make Your MySQL Servers Faster and Happier. MySQL 101, MySQL User Administration — June 8th – 9th
  • Redhat Summit – Big & Traditional Databases — June 12th, 4:40p – 5:50p</li?

2 Comments

Filed under Uncategorized