Category Archives: Uncategorized

Toad for MySQL

Long before MySQL, DBAs have been using Toad. Toad is short for Tool for Oracle Application Developers and I remember the Oracle DBAs raving about how TOAD made so many things simpler. Now it is many years later and Dell has released TOAD for MySQL.

On the product page we are promised Toad™ for MySQL is a freeware development tool that enables you to quickly and efficiently create and execute queries, automate database object management, and develop SQL code. Toad MySQL provides utilities to compare, extract, and search for objects. The MySQL tool also enables you to record and play back keyboard commands, and transfer data across MySQL databases. The MySQL developer tool increases efficiency by helping you manage projects, import/export data, and administer the database. Sounds pretty good!

But how does it stack up against MySQL Workbench on my Windows Laptop?

Microsoft .NET Framework 4.5 is required before installing Toad.

Toad For MySQL? Yup, Toad is now available for MySQL

Toad For MySQL? Yup, Toad is now available for MySQL

Starting ‘Toad for MySQL Freeware’ starts off with links for video tutorials, other freeware, blogs, and a link to visit the Toad for MySQL Community.

Connections
Connecting to Toad is similar to Workbench but then there is just so much information to build a DSN.

Entity Relationship Mapping
Tables are dragged manually from the Object Explorer to the diagram window and foreign key mappings is automatically drawn. This is in contrast to Workbench that maps all tables.

Queries

Queries and results from that query in a separate pane.

Queries and results from that query in a separate pane.

Toad will provide hints for columns or keywords as you type queries. You can not get an Explain Plan as as their is an odd error about violation of a constraint and their is no Visual Explain. You do have an option for a drag-n-drop pivot table from query results. You can created tables, edit existing tables, drop tables and all the functions you would expect.

Build Queries
You can drag-n-drop to build queries. I grabbed the world.City table, dropped it on the canvas, and clicked on the Name Column. Next I dragged the Country table and clicked on that tables’ Name column. Toad automatically generated the SQL to to perform an inner join. And that is cool and should help infrequent query writers.

Not Workbench
Workbench has more functionality for things like system administration, backups, import, user admin, system statistics, and DBA oriented work.

So What IS Toad?
Toad is a query tool and fantastic for those who do not need all the admin functionality of Workbench. If you write SQL this may be an option for you. There is no Explain, Visual Explain, or any info on a query plan. And the ability to drag-n-drop tables may be what you need.

I will tray incorporating Toad in my normal work and see what else pops up.

Leave a comment

Filed under Uncategorized

MySQL Fabric — Three Node High Availability Server Farm

So how do you use MySQL Fabric to set up a Highly Available Server Farm? The last two postings in this series were on installing Fabric on a master and then setting up slaves. Now it is time to get get the Fabric Farm started.

The Fabric controller is node number 1 and the slaves are 10, 20, and 30 at IP 10.10.13.1, 10, 20, and 30 respectively. I am keeping with the last octet of the IP addresses for clarity. I am trying to recreate the Fabric Farm from this image. Highly Available Fabric Farm

On the 10, 20, and 30 systems we need to add the following to the mysld section of the my.cnf
[mysqld]
binlog-format=ROW
log-slave-updates=true
gtid-mode=on
enforce-gtid-consistency=true
master-info-repository=TABLE
relay-log-info-repository=TABLE
sync-master-info=1
report-host=192.168.0.201
report-port=3306
server-id=1
log-bin=mysql1-bin.log

Next set up replication grants on the data nodes and the controller. We want the ability to use any of the slaves and the READ-WRITE master plus be able to switch them at will.
mysql> CHANGE MASTER TO MASTER_HOST='10.10.13.10', MASTER_USER='replication', MASTER_PASSWORD='*****', MASTER_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> START SLAVE;
Query OK, 0 rows affected (0.00 sec)
I started out with 10 as the replication master and used the above on 20 and 30.

A 5.7 Problem
The data farm machines are running MySQL 5.6 but my Fabric controller is running 5.7. If you are also running 5.7 note that by default your SQL MODE is strict and you will get
mysqlfabric manage setup
[INFO] 1423080020.902196 – MainThread – Initializing persister: user (Fabric), server (localhost:3306), database (fabric).
Error: Command (CREATE TABLE log (subject VARCHAR(40) NOT NULL, reported TIMESTAMP /*!50604 (6) */ NOT NULL, reporter VARCHAR(64) NOT NULL, message TEXT, category int NOT NULL, type int NOT NULL, INDEX key_subject_reported (subject, reported), INDEX key_reporter (reporter), INDEX key_reported (reported), INDEX key_category (category), INDEX key_type (type)), ()) failed accessing (localhost:3306). 1067 (42000): Invalid default value for ‘reported'

Opps! 5.7 has STRICT for SQL MODE as the default and I assumed reported was being fed no data. So it was time to set the default SQL MODE to ANSI and restart my Fabric controller’s instance.

So Create the Fabric Database already!
mysqlfabric manager setup creates the necessary tables in the Fabric state store. These tables will be used to store the topology information. The admin user created here can be used to create other users that can connect to fabric as can be seen below. And this command needs to be run only once.

mysqlfabric manage setup
[INFO] 1423080461.323271 - MainThread - Initializing persister: user (Fabric), server (localhost:3306), database (fabric).
Finishing initial setup
=======================
Password for admin user is not yet set.
Password for admin/xmlrpc:
Repeat Password:
Password set.
Password set.
No result returned

Side note 1: For over over a decade I have been typing the five letter m-y-s-q-l group alone for a good part of my professional workday and my fingers have a hard time adding f-a-b-r-i-c. Fellow old timers take care.
Side note 2: It can get confusion which password for which function is being asked for at any one time. I am sure more familiarity on my part would help but I can see folks getting confused easily.
Side note 3: In the early days of MySQL cluster I had to dig into the various pieces as it was not intuitive how things fit together and Fabric for me is the same way. I hope more time will on Fabric will build familiarity.

Orville and Wilbur Wright Time
mysqlfabric manage start &
[1] 12690
dstokes@dstokes-E7240:~/vagrant$ [INFO] 1423080590.563764 - MainThread - Initializing persister: user (Fabric), server (localhost:3306), database (fabric).
[INFO] 1423080590.571004 - MainThread - Loading Services.
[INFO] 1423080590.585766 - MainThread - MySQL-RPC protocol server started, listening on localhost:32275
[INFO] 1423080590.592161 - MainThread - Fabric node starting.
[INFO] 1423080590.596949 - MainThread - Starting Executor.
[INFO] 1423080590.597056 - MainThread - Setting 5 executor(s).
[INFO] 1423080590.597318 - Executor-0 - Started.
[INFO] 1423080590.597674 - Executor-1 - Started.
[INFO] 1423080590.597998 - Executor-2 - Started.
[INFO] 1423080590.598294 - Executor-3 - Started.
[INFO] 1423080590.599090 - Executor-4 - Started.
[INFO] 1423080590.599224 - MainThread - Executor started.
[INFO] 1423080590.607582 - MainThread - Starting failure detector.
[INFO] 1423080590.609208 - XML-RPC-Server - XML-RPC protocol server ('127.0.0.1', 32274) started.
[INFO] 1423080590.609764 - XML-RPC-Server - Setting 1 XML-RPC session(s).
[INFO] 1423080590.610071 - XML-RPC-Session-0 - Started XML-RPC-Session.

Now the three data nodes can be turned into a server farm.
mysqlfabric group create davesfarm
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
cc54e1b2-4e54-4d02-99b4-3692d35fcce1 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423080661.24 Triggered by .
4 2 1423080661.25 Executing action (_create_group).
5 2 1423080661.27 Executed action (_create_group).

This farm is called davesfarm by me but just 5ca1ab1e-a007-feed-f00d-cab3fe13249e by Fabric.

So what does the farm look like?
mysqlfabric group lookup_servers davesfarm
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
----------- ------- ------ ---- ------

After the farm is created, it needs to be made active.

mysqlfabric group activate davesfarm
Password for admin:
[INFO] 1423081400.519033 - Executor-0 - Monitoring group (davesfarm).
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
5ac9a044-c650-428e-ba50-50a6718f2342 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423081400.49 Triggered by .
4 2 1423081400.5 Executing action (_activate_group).
5 2 1423081400.53 Executed action (_activate_group).

Cool!

mysqlfabric group lookup_groups
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

group_id description failure_detector master_uuid
--------- ----------- ---------------- -----------
davesfarm None 1 None

Now Add an admin
An admin is needed to populate the farm.
mysqlfabric user add client --user=admin
Password for admin:
Add a new Fabric user
=====================
Username: client
Protocol (default xmlrpc):
Password:
Repeat Password:

Select role(s) for new user
ID Role Name Description and Permissions
-- ---------- ---------------------------
1 superadmin Role for Administrative users
+ Full access to all core Fabric functionality
2 useradmin Role for users dealing with user administration
+ User administration
+ Role administration
3 connector Role for MySQL Connectors
+ Access to dump commands
+ Reporting to Fabric

Enter comma separated list of role IDs or names: 1
Fabric user added.
No result returned

And On This Farm He Had a …
group add davesfarm 10.10.13.10
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
550b1ba6-082d-4fef-9808-8fdfd6397de9 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423083005.22 Triggered by .
4 2 1423083005.23 Executing action (_add_server).
5 2 1423083005.27 Executed action (_add_server).

mysqlfabric group add davesfarm 10.10.13.20
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
4fd59253-bd10-42b9-82cd-0d61959a695c 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423083297.97 Triggered by .
4 2 1423083297.98 Executing action (_add_server).
5 2 1423083298.01 Executed action (_add_server).
mysqlfabric group add davesfarm 10.10.13.30
...

Welcome to Farmville!!!
mysqlfabric group lookup_servers davesfarm
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- --------- --------- ------
0977d639-abbb-11e4-a804-0800273b9c80 10.10.13.30 SECONDARY READ_ONLY 1.0
1f96a235-abb9-11e4-a7f8-0800273b9c80 10.10.13.10 SECONDARY READ_ONLY 1.0
304e20cd-abba-11e4-a7ff-0800273b9c80 10.10.13.20 SECONDARY READ_ONLY 1.0

So now the farm is populated but we need to create a pecking order by making one of them the primary.

mysqlfabric group promote davesfarm --slave_id=1f96a235-abb9-11e4-a7f8-0800273b9c80
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

uuid finished success result
------------------------------------ -------- ------- ------
af79f3e0-fda2-4531-8aab-c8a48ad036a6 1 1 1

state success when description
----- ------- ------------- ------------------------------------------------------------------
3 2 1423084103.73 Triggered by .
4 2 1423084103.73 Executing action (_define_ha_operation).
5 2 1423084103.75 Executed action (_define_ha_operation).
3 2 1423084103.74 Triggered by .
4 2 1423084103.75 Executing action (_check_candidate_fail).
5 2 1423084103.77 Executed action (_check_candidate_fail).
3 2 1423084103.77 Triggered by .
4 2 1423084103.77 Executing action (_wait_slave_fail).
5 2 1423084103.81 Executed action (_wait_slave_fail).
3 2 1423084103.8 Triggered by .
4 2 1423084103.81 Executing action (_change_to_candidate).
5 2 1423084103.93 Executed action (_change_to_candidate).

mysqlfabric group lookup_servers davesfarm
Password for admin:
Fabric UUID: 5ca1ab1e-a007-feed-f00d-cab3fe13249e
Time-To-Live: 1

server_uuid address status mode weight
------------------------------------ ----------- --------- ---------- ------
0977d639-abbb-11e4-a804-0800273b9c80 10.10.13.30 SECONDARY READ_ONLY 1.0
1f96a235-abb9-11e4-a7f8-0800273b9c80 10.10.13.10 PRIMARY READ_WRITE 1.0
304e20cd-abba-11e4-a7ff-0800273b9c80 10.10.13.20 SECONDARY READ_ONLY 1.0

So Fabric has been started, a farm has been established, and it is ready for testing. But the testing will have to wait for another post.

And if you want to stop Fabric, the command is:
shell> mysqlfabric manage stop

Some blogs to help:

2 Comments

Filed under MySQL Fabric, Uncategorized

MySQL Fabric — Second Steps, er, Threads

MySQL Fabric will be used set up a three node High Availability server farm and this is the second part of a series. If you missed the last post, I will be doing a live demo (gulp!) on Fabric for the Triangle MySQL User Group in Raleigh. But part of the pain of live demos is how do you get multiple servers set up to make a server farm. E-I-E-I-oh?

Of course getting the slaves up on one laptop and running correctly is the biggest expenditure of time for this demo. For this I will use Vagrant. I am using Vagrant to set up three identical servers using VirtualBox to provide the servers I need for the demo. I am using Ubuntu.
sudo apt-get install vagrant VirtualBox
mkdir vagerant
cd vagrant
vagrant box add db1 https://github.com/kraksoft/vagrant-box-ubuntu/releases/download/14.04/ubuntu-14.04-amd64.box
vagrant init db1

Then I created db2 and db3 using the last two steps. Be sure to add the vbguest plugin so you can have a shared folder and some otehr features.
vagrant plugin install vagrant-vbguest
There is a problem with the current Vagrant for getting a shared folder to work (hint — shared folder for your files you want to install on the farm server like the MySQL 5.6 tarball).
To get the shared folder to work, boot the servers and then sudo ln -s /opt/VBoxGuestAdditions-4.3.10/lib/VBoxGuestAdditions /usr/lib/VBoxGuestAdditions .

The Vagrantfile is where the three boxes are defined and the interesting parts follows:

config.vm.define "db1" do |db1|
db1.vm.box = "db1"
db1.vm.network :private_network, ip: "10.10.13.10"
end

config.vm.define "db2" do |db2|
db2.vm.box = "db2"
db2.vm.network :private_network, ip: "10.10.13.20"
end

config.vm.define "db3" do |db3|
db3.vm.box = "db3"
db3.vm.network :private_network, ip: "10.10.13.30"
end
This provides three boxes creatively named db1, db2, and db3 on a private ’10’ network.

The nest step it to run vagrant up, ssh to each of the boxes, and make things ready for installing MySQL. This includes apt-getting libaio1, adding the address to /etc/host and setting the hostname. Then install MySQL 5.6 and setup a user named fabric on each instance.

The next post will cover setting up the HA Fabric farm.

Leave a comment

Filed under Uncategorized

SYS Schema First Steps

Oracle DBAs have has the luxury of their V$ variables for a long time while we MySQL DBAs pretended we were not envious. With MySQL 5.6 and 5.7 we were gifted with the PERFORMANCE_SCHEMA tables. But there is such a wealth of information in those tables that it is intimidating to plunge in to them.  Thankfully Mark Leith has given us the SYS Schema. The SYS Schema is a collection of views, functions and procedures to help MySQL administrators get insight in to MySQL Database usage.

The first step is to get a copy of the SYS SCHEMA files.
git clone https://github.com/MarkLeith/mysql-sys

Next install the SYS Schema (here for MySQL 5.7)
mysql -u root -p < ./sys_57.sql

Now run MySQL and look at this new schema.
mysql> use sys;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+———————————————–+
| Tables_in_sys |
+———————————————–+
| host_summary |
| host_summary_by_file_io |
| host_summary_by_file_io_type |
| host_summary_by_stages |
| host_summary_by_statement_latency |
| host_summary_by_statement_type |
| innodb_buffer_stats_by_schema |
| innodb_buffer_stats_by_table |
| innodb_lock_waits |
| io_by_thread_by_latency |
| io_global_by_file_by_bytes |
| io_global_by_file_by_latency |
| io_global_by_wait_by_bytes |
| io_global_by_wait_by_latency |
| latest_file_io |
| memory_by_host_by_current_bytes |
| memory_by_thread_by_current_bytes |
| memory_by_user_by_current_bytes |
| memory_global_by_current_allocated |
| memory_global_total |
| processlist |
| ps_check_lost_instrumentation |
| schema_index_statistics |
| schema_object_overview |
| schema_table_statistics |
| schema_table_statistics_with_buffer |
| schema_tables_with_full_table_scans |
| schema_unused_indexes |
| statement_analysis |
| statements_with_errors_or_warnings |
| statements_with_full_table_scans |
| statements_with_runtimes_in_95th_percentile |
| statements_with_sorting |
| statements_with_temp_tables |
| sys_config |
| user_summary |
| user_summary_by_file_io |
| user_summary_by_file_io_type |
| user_summary_by_stages |
| user_summary_by_statement_latency |
| user_summary_by_statement_type |
| version |
| wait_classes_global_by_avg_latency |
| wait_classes_global_by_latency |
| waits_by_host_by_latency |
| waits_by_user_by_latency |
| waits_global_by_latency |
| x$host_summary |
| x$host_summary_by_file_io |
| x$host_summary_by_file_io_type |
| x$host_summary_by_stages |
| x$host_summary_by_statement_latency |
| x$host_summary_by_statement_type |
| x$innodb_buffer_stats_by_schema |
| x$innodb_buffer_stats_by_table |
| x$innodb_lock_waits |
| x$io_by_thread_by_latency |
| x$io_global_by_file_by_bytes |
| x$io_global_by_file_by_latency |
| x$io_global_by_wait_by_bytes |
| x$io_global_by_wait_by_latency |
| x$latest_file_io |
| x$memory_by_host_by_current_bytes |
| x$memory_by_thread_by_current_bytes |
| x$memory_by_user_by_current_bytes |
| x$memory_global_by_current_allocated |
| x$memory_global_total |
| x$processlist |
| x$ps_digest_95th_percentile_by_avg_us |
| x$ps_digest_avg_latency_distribution |
| x$ps_schema_table_statistics_io |
| x$schema_index_statistics |
| x$schema_table_statistics |
| x$schema_table_statistics_with_buffer |
| x$schema_tables_with_full_table_scans |
| x$statement_analysis |
| x$statements_with_errors_or_warnings |
| x$statements_with_full_table_scans |
| x$statements_with_runtimes_in_95th_percentile |
| x$statements_with_sorting |
| x$statements_with_temp_tables |
| x$user_summary |
| x$user_summary_by_file_io |
| x$user_summary_by_file_io_type |
| x$user_summary_by_stages |
| x$user_summary_by_statement_latency |
| x$user_summary_by_statement_type |
| x$wait_classes_global_by_avg_latency |
| x$wait_classes_global_by_latency |
| x$waits_by_host_by_latency |
| x$waits_by_user_by_latency |
| x$waits_global_by_latency |
+———————————————–+
92 rows in set (0.00 sec)

Note that the views preceded by x$ are designed to be used within tools.

So the first table in the list above is is host_summaryand a quick query reveals the following.
mysql> select * from host_summary\G
*************************** 1. row ***************************
host: localhost
statements: 1328
statement_latency: 10.73 s
statement_avg_latency: 8.08 ms
table_scans: 88
file_ios: 17268
file_io_latency: 6.34 h
current_connections: 3
total_connections: 10
unique_hosts: 2
current_memory: 0 bytes
total_memory_allocated: 0 bytes
1 row in set (0.03 sec)

Now that I have pried open the lid, the next post will show how to dig deeper into the SYS Schema.

1 Comment

Filed under MySQL, Uncategorized

2014 MySQL DBA/Developers Holiday Gifts

MySQL DBAs and Developers are hard to shop for but here are some ideas that will appeal to them.

1. Hamster Wheel Standing Desk http://www.dudeiwantthat.com/gear/office/hamster-wheel-standing-desk.asp

Hamster Wheel Standing Desk
Get your favorite MySQL-er a new desk, some exercise, and you will be giving a gift that provides more than just a place to stash stuff.

For the DBA/Dev on the go, there is also the Refold Portable Cardboard Desk http://www.dudeiwantthat.com/gear/office/refold-portable-cardboard-desk.asp that does not provide as much exercise but could be just the thing for the trip to that oh so crowded Starbucks.

2. “DB Evil Genius” Mug http://www.cafepress.com/mf/10655743/db-evil-genius_mugs?productId=46810134
What goes better on a new desk than a new mug?

3. Sometimes I Self Join postcards http://www.cafepress.com/+query_self_joins_postcards_package_of_8,295044156

4. T-shirts are always popular and the cornerstone of most developers ensembles.

http://www.zazzle.com/funny_database_administrator_t_shirts_shirt-235829736802064906

5. The Degree or normality in a database in inversely proportional to that of its DBA t-Shirt

http://www.redbubble.com/people/iuchiatesoro/works/8774766-dba-self-deprecating-humour?p=t-shirt

6. Dolphin Casting Kit (MySQL mascot)

Found at a local TJ Max but popular in toy sections of most retailers.

Found at a local TJ Max but popular in toy sections of most retailers.


7. Another fun shirt for those who enjoy facial hair humor

http://www.occupationtshirts.com/view/3489237/if-you-really-moustache-i-m-a-database-administrator-funny-t-shirt

8. A shirt proclaiming the truth from http://www.dictionarytshirts.com!

9. A Keep Clam shirt

10. And finally for the MySQL-ers in your life that is a little edgy go to http://ep.yimg.com/ay/yhst-54334793715728/golden-scorpion-clear-computer-mouse-6.png

Any other ideas? Please add your comments below.

Leave a comment

Filed under 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