sqlhjalp

Oracle at DrupalCon Denver Conference 2012

2012-02-21T13:05:00.001-07:00

Oracle is pleased to be a Bronze Sponsor of DrupalCon Denver.

DrupalCon will be held the Colorado Convention Center in Denver on March 19-23, 2012. There are community events scheduled throughout the entire week, some officially by DrupalCon, others by sponsors and other community members.

Come visit Oracle Booth #400 and find out the latest information about MySQL and Linux! I will be at the booth so please stop by and say hello.

For event information and to register, click here.

Oracle Events page with additional information can be found here.

InnoDB Tablespace Automatically Expanding Issues

2012-02-20T10:04:00.000-07:00

I recently received a question asking about InnoDB and the ability to autoextend the ibdata files.

The question was basically this:
If the database has 1 ibdata file that is using autoextend but also has a max value set, will it start a new file once the max value is reached.

The answer is no it will not create a new file.You are able to use autoextend and have more than 1 ibdate file though.

For example I made a simple example via a virtual box.

Started using:
innodb_data_file_path            = ibdata1:20M:autoextend:max:100M
innodb_autoextend_increment        = 50M

I downloaded the full employees dataset from launchpad.

Started the server and I have the 20M file.
-rw-rw---- 1 mysql mysql 20M Feb 20 10:53 ibdata1

mysql -t < employees.sql
ERROR 1114 (HY000) at line 18 in file: 'load_salaries.dump': The table 'salaries' is full

I see the file grew to 100M and stopped.
-rw-rw---- 1 mysql mysql 100M Feb 20 10:48 ibdata1

I can start over to prove this does work as well.
innodb_data_file_path            = ibdata1:20M:autoextend:max:2G
innodb_autoextend_increment        = 50M

mysql -t < employees.sql
...
+------------------+
| INFO             |
+------------------+
| LOADING salaries |
+------------------+
-rw-rw---- 1 mysql mysql 1020M Feb 20 10:54 ibdata1

You are able to use autoextend when using more than 1 ibdata file as well.

innodb_data_file_path = ibdata1:20M;ibdata2:20M:autoextend:max:2G
innodb_autoextend_increment             = 50M

These are just simple examples but I hope this answers the question.

MySQL User Group Day on FreeNode !

2012-02-17T11:55:00.000-07:00

As MySQL Community Managers, Dave an myself are always looking for ways to gather feedback on MySQL.

We want to make ourselves available to you. The current plan is the last Monday of every month Dave and Myself will be on #freenode and join a chat room called MySQL_user_groups.

We will also try to be available as much as possible in a chat room called MySQL_Community.

You can join us and ask questions or just let us know your opinions.

We want to hear about everything from your best presentation topics, to the worst. What is the biggest struggle you find with user groups ?

How could Oracle help?

MySQL for Database Administrators Training on Demand

2012-02-16T11:46:00.000-07:00

I ran across the "Training on Demand" option for a MySQL for Database Administrators class. I thought I would share in case you missed it.

https://blogs.oracle.com/MySQL/entry/mysql_for_dbas_training_on

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getCourseDesc?dc=D61762GC20

They also are offering a new course : MySQL Performance Tuning
http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getCourseDesc?dc=D61820GC20

More information can be found on the MySQL Database Administration and Development - Learning Path site.

http://education.oracle.com/pls/web_prod-plq-dad/db_pages.getlppage?page_id=212&path=SDAD

Summary Tables with MySQL

2012-02-14T16:43:00.000-07:00

I was recently talking with a few people and the concept of summary tables came up as solutions for them. The issue is, they never thought of it. Hopefully this post will help them as well as others.

Summary Tables are not a new concept. They are exactly what they sound like, basically summary of existing data. Aggregated tables, materialized views and summary tables are very dominate in data warehouses. You can also take advantage of these concepts as part of your reporting systems as well.

So summary tables are real tables. So you can take advantage of indexes and etc with them. In the examples I am using, I consider them more of a summary table than aggregated tables . Depending on your application and needs it could grow into more of an aggregated tables and or materialized views situation.

How you separate your data and tables is dependent on your reporting and application needs.

The following is a high level example of how you can use summary tables.

Let us assume that you have a report that is populated with different website traffic aspects.

This could be a report that includes impressions, hits and leads gathered via your site from some other sources. Other in the company, of course, want to spend money where it best makes money. So they need updated information reliably and do not want to wait for it.

Currently you have table for your reporting tools and you calculate ROI per report request via SQL.

For example:

CREATE TABLE `some_report` (

`traffic_source_id` int(5) unsigned NOT NULL DEFAULT '0',

`ad_id` int(10) unsigned NOT NULL DEFAULT '0',

`affiliate_id` int(10) unsigned NOT NULL DEFAULT '0',

`affiliates_key` varchar(25) DEFAULT '',

`date_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`impressions` int(5) unsigned DEFAULT '0',

`hits` int(5) unsigned DEFAULT '0',

`leads` int(5) unsigned DEFAULT '0',

UNIQUE KEY `traffic` (`traffic_source_id`,`affiliate_id`,`ad_id`,`date_time`,`affiliates_key`),

KEY `date_time` (`date_time`)

) ENGINE=InnoDB ;

This table is populated hourly, so an insert could be similar to the query below. Your system executes queries like this thousands+ of time per day because it is based on different traffic sources as well as affiliate and their affiliate keys. So this table is going to grow dynamically, hours per day * traffic_source_id * affiliate_id * ad_id * affiliates_key.

For example:

INSERT INTO some_report VALUES (100,4343,9839,'SomeID',DATE_FORMAT(NOW() ,'%Y-%m-%d %H:00:00'),89832,44916,22458);

INSERT INTO some_report VALUES (100,4343,9839,'SomeID2',DATE_FORMAT(NOW() ,'%Y-%m-%d %H:00:00'),93332,34716,23438);

INSERT INTO some_report VALUES (100,2343,9839,'SomeID',DATE_FORMAT(NOW() ,'%Y-%m-%d %H:00:00'),543232,44316,458);

INSERT INTO some_report VALUES (100,2343,9839,'SomeID2',DATE_FORMAT(NOW() ,'%Y-%m-%d %H:00:00'),89832,24916,2458);

INSERT INTO some_report VALUES (100,5343,9839,'SomeID',DATE_FORMAT(NOW() ,'%Y-%m-%d %H:00:00'),1239832,2344916,2538);

Just for reference lets assume :

100 = Google.com

4343 = An ad words ad for MySQL

9839 = Some Marketing Company

SomeID = affiliates_key of some marketing company

You have reviewed your explains per query and your SQL query is the fastest you can get it. It just takes time to calculate all the ROIs when reports are spanning a lot of different entities. The web pages are taking to long and people are getting upset.

So you decide to change your tables and create a process to roll up your data across different summary tables using an ETL or Events or cron job based scripts. So your system populates the RETURN_VALUED per ad_id & affiliate_id. You do not know what the affiliate is paying out to other affiliates_keys but you are aware of what you spent and what the lead returns to you.

First start with our lowest common denominator. Here you have data per hour.

CREATE TABLE `some_report_hour` (

`traffic_source_id` int(5) unsigned NOT NULL DEFAULT '0',

`ad_id` int(10) unsigned NOT NULL DEFAULT '0',

`affiliate_id` int(10) unsigned NOT NULL DEFAULT '0',

`affiliates_key` varchar(25) DEFAULT '',

`date_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

`impressions` int(5) unsigned DEFAULT '0',

`hits` int(5) unsigned DEFAULT '0',

`leads` int(5) unsigned DEFAULT '0',

`cost` decimal(9,2) DEFAULT '00.00',

`roi` decimal(7,2) DEFAULT '00.00',

UNIQUE KEY `traffic` (`traffic_source_id`,`affiliate_id`,`ad_id`,`date_time`,`affiliates_key`),

KEY `date_time` (`date_time`)

) ENGINE=InnoDB ;

Then every hour it populates more data into the new table via your ETL / Event / Stored Procedure / Cron job Script. It depends on how you want to handle and decide your values for

For example:

DELIMITER //

CREATE PROCEDURE some_report_hour_procedure(IN affiliate_id int(6) , RETURN_VALUED int(6) , COST decimal(5,3) )

BEGIN

INSERT INTO some_report_hour

SELECT traffic_source_id, ad_id, affiliate_id, affiliates_key, date_time, impressions, hits, leads, COST, ROUND((SUM(leads * RETURN_VALUED ) / COST),2)

FROM some_report r

WHERE r.date_time = DATE_FORMAT(NOW() ,'%Y-%m-%d %H:00:00')

GROUP BY traffic_source_id, ad_id, affiliate_id, affiliates_key;

END //

DELIMITER ;

CREATE EVENT some_report_hour_event

ON SCHEDULE

EVERY 1 HOUR

COMMENT ' just an example. Depends on you how you want to populate these values. This is for just 1 affiliate_id '

CALL some_report_hour_procedure(9839,5,100000); # (affiliate_id , RETURN_VALUED , COST)

So the data is much like the following:

+-------------------+-------+--------------+----------------+---------------------+-------------+---------+-------+-----------+---------+

+-------------------+-------+--------------+----------------+---------------------+-------------+---------+-------+-----------+---------+

| 100 | 2343 | 9839 | SomeID | 2012-02-14 16:00:00 | 543232 | 44316 | 458 | 100000.00 | 22.90 |

| 100 | 2343 | 9839 | SomeID2 | 2012-02-14 16:00:00 | 89832 | 24916 | 2458 | 100000.00 | 122.90 |

| 100 | 4343 | 9839 | SomeID | 2012-02-14 16:00:00 | 89832 | 44916 | 22458 | 100000.00 | 1122.91 |

| 100 | 4343 | 9839 | SomeID2 | 2012-02-14 16:00:00 | 93332 | 34716 | 23438 | 100000.00 | 1171.91 |

| 100 | 5343 | 9839 | SomeID | 2012-02-14 16:00:00 | 1239832 | 2344916 | 2538 | 100000.00 | 126.90 |

+-------------------+-------+--------------+----------------+---------------------+-------------+---------+-------+-----------+---------+

Nothing is dynamically different but you have removed the ROI from your query. It will be a simple select now. But now you can use your ETL/Event/Cron Job Script to keep the day table updated as well.

CREATE TABLE `some_report_day` (

`traffic_source_id` int(5) unsigned NOT NULL DEFAULT '0',

`ad_id` int(10) unsigned NOT NULL DEFAULT '0',

`affiliate_id` int(10) unsigned NOT NULL DEFAULT '0',

`affiliates_key` varchar(25) DEFAULT '',

`date_time` date NOT NULL DEFAULT '0000-00-00',

`impressions` int(5) unsigned DEFAULT '0',

`hits` int(5) unsigned DEFAULT '0',

`leads` int(5) unsigned DEFAULT '0',

`cost` decimal(9,2) DEFAULT '00.00',

`roi` decimal(7,2) DEFAULT '00.00',

UNIQUE KEY `traffic` (`traffic_source_id`,`affiliate_id`,`ad_id`,`date_time`,`affiliates_key`),

KEY `date_time` (`date_time`)

) ENGINE=InnoDB ;

Notice I left the field date_time labeled as date_time. This is assuming you might have your report presented via column names and you just adjust the FROM table on selects based on what type of report needed. This is of course dependent on your application.

Notice that your inserts are going to be “group by” to match your key.

For example:

CREATE EVENT some_report_day_event

ON SCHEDULE

EVERY 1 HOUR

COMMENT 'updates some_report_day hourly'

REPLACE INTO some_report_day SELECT traffic_source_id, ad_id, affiliate_id, affiliates_key , DATE_FORMAT(NOW() ,'%Y-%m-%d') as date_time, SUM(impressions), SUM(hits), SUM(leads), SUM(cost), ROUND( SUM(leads) * 5 / SUM(cost) ,2) as ROI

FROM some_report_hour r

WHERE r.date_time BETWEEN DATE_FORMAT(NOW() ,'%Y-%m-%d 00:00:00') AND DATE_FORMAT(NOW() ,'%Y-%m-%d 23:59:59')

GROUP BY traffic_source_id, ad_id, affiliate_id, affiliates_key;

Now the reports that gather data per day and not per hour are going to be faster as they are going to be simple selects as well.

So the data is much like the following:

+-------------------+-------+--------------+----------------+------------+-------------+---------+-------+-----------+------+

+-------------------+-------+--------------+----------------+------------+-------------+---------+-------+-----------+------+

| 100 | 2343 | 9839 | SomeID | 2012-02-14 | 543232 | 44316 | 458 | 100000.00 | 0.02 |

| 100 | 2343 | 9839 | SomeID2 | 2012-02-14 | 89832 | 24916 | 2458 | 100000.00 | 0.12 |

| 100 | 4343 | 9839 | SomeID | 2012-02-14 | 89832 | 44916 | 22458 | 100000.00 | 1.12 |

| 100 | 4343 | 9839 | SomeID2 | 2012-02-14 | 93332 | 34716 | 23438 | 100000.00 | 1.17 |

| 100 | 5343 | 9839 | SomeID | 2012-02-14 | 1239832 | 2344916 | 2538 | 100000.00 | 0.13 |

+-------------------+-------+--------------+----------------+------------+-------------+---------+-------+-----------+------+

You can continue this concept for monthly and yearly data.

You could also build summary tables broken down by affiliate_id and day for example.

It really depends on what your after. Just remember data is never deleted but just copied into summary table for ease of use and speed.

This is a very simple example but hopefully it gets you thinking and started on how you can summarize your data easily.

The community helping customers restore faster with mysqldump

2012-02-07T16:06:00.004-07:00

A big thanks to Xiaobin Lin for taking the time to submit and the related patch for bug #64248. The patch is based on 5.5.20
This should help users to restore their database faster thanks to fast index creation. More information is available via the bugs page. I have heard that this is just one of several patches he has contributed.

Contributions such as this, help MySQL to continue to deliver an always improving product.

So a big “Thank You” from the MySQL team.

RMOUG Training Days 2012

2012-02-03T10:45:00.000-07:00

The Rocky Mountain Oracle User Group ( RMOUG ) Training days are almost here. This year they are going to have a MySQL Oracle ACE Director, Ronald Bradford talk on MySQL Security Essentials. I will also be having a MySQL Crash Course . I was going to talk about replication but after attending the IOUG User summit and the seeing the vast interest in MySQL from Oracle users, I decided to take a step back and do this talk 1st. I have had a similar talk at a RMOUG meetup hands on labs in the past, but it has been updated.

Alex Gorbachev of Pythian also has a session, Monitoring MySQL with Oracle Cloud Control 12c.

Look forward to seeing everyone and I think great things are on the horizon with RMOUG and MySQL.

MySQL Knowledge Zone

2012-01-30T12:32:00.000-07:00

A recent trip to the SCALE Conference raised a few common questions. One of them was: does Oracle does offer a MySQL 5.5 exam ? I hope the links below help all of those that asked and others who might have the same question.

MySQL for Beginners (SQL-4401) This exam has been validated for MySQL 5.0, 5.1 and 5.5. They also offer a MySQL 5 Certified Associate Exam

Oracle also now offers, MySQL Specialization Criteria.

Related Press Release :New Oracle PartnerNetwork (OPN) Specialization for MySQL Enables Partners to Showcase Expertise

Oracle MySQL Developer Days - Germany & France

2012-01-27T10:50:00.000-07:00

MySQL is coming to Frankfurt am Main in Germany for a MySQL Developer Day, Febuary 9th.

This developer day has a wide range of sessions and two great resources: Johannes Schlüter, MySQL Software Developer (great PHP resource as well) and Giuseppe Maxia, Oracle ACE Director for MySQL.

Take advantage of this chance to learn more about MySQL ! More information on the event in Germany can be found here.

Paris France will also host a Developer day in March 21st. More information on this will becoming soon.

Well deserved !

2012-01-27T09:38:00.000-07:00

Giuseppe Maxia and Sarah Novotny both have now been awarded Oracle ACE Directors for MySQL !

They both are well respected in the MySQL community and have achieved great things so far. We look forward to seeing what they will do next.

Oracle has MySQL Ace Members up against the wall!

2012-01-25T15:26:00.000-07:00

The Oracle User Group Leaders Summit is over and it appears that Oracle has some of the MySQL ACE Members (George, Sheeri, Ronald) backed up against the wall!

It was great to get the user groups together and we need more MySQL User Groups to attend next year! You can follow IOUC on twitter @IOUC_Conference & http://www.iouc.org/

User Group Leadership Summit

2012-01-25T10:44:00.000-07:00

Day three of the User Group Leadership Summit is here and MySQL has continued to have a valid presence from the community as well as the Oracle Team.

George Trujillo introduced MySQL for the Oracle DBA to a very attentive audience.
Ronald Bradford gave a great presentation on why people should use MySQL.
Sheeri Cabral talked on the MySQL Community and user groups. Members of Oracle user groups showed great interest in the MySQL User groups.

So how can I summarize the summit in one line:
Oracle DBA user group members are interested in MySQL and the community!

Events

2012-01-24T14:17:00.000-07:00

Take advantage of the events.oracle.com site and get a RSS feed of local events on your area.

Currently :

January 31, 2012

Webcast

Oracle Webcast: MySQL Embedded Online Forum
Register

February 02, 2012

Raleigh

MySQL TechTours: Raleigh, NC
Register

February 08, 2012

Irving

Oracle's MySQL Tech Tour - Dallas
Register

February 08, 2012

El Segundo

You're Invited - Oracle's MySQL Tech Tour Event – El Segundo
Register

February 09, 2012

Santa Clara

You're Invited - Oracle's MySQL Tech Tour Event – Santa Clara
Register

February 09, 2012

Frankfurt am Main

Oracle Technology Network Developer Day: MySQL
Register

February 23, 2012

Kista

Oracle High Availability Solutions for MySQL
Register

More User Groups

2012-01-24T09:01:00.000-07:00

Day one of the User Group Leaders Conference is complete and day two is about to begin. MySQL has nine sessions including three provided by MySQL ACEs.

The take away from day 1 --- Need more user groups ! Oracle user group leaders from around the world are asking and it is up to the community to provide MySQL user groups in their area.

A list of user groups is available here. This list includes Facebook and Meetup.com groups. If you are aware if a group in your area please support it. If you feel a user group is missing please let us know.

SCALE

2012-01-21T16:44:00.000-07:00

Great turn out at SCALE !
MySQL Day on Friday was a great success. MySQL sessions packed all day.

So far Saturday has had a great turn out at the booth with numerous questions about MySQL.

Tux even gave us a visit.

MySQL Day at SCALE

2012-01-15T08:27:00.000-07:00

MySQL Day at SCALE is just a few days away. I will be giving a replication session that covers the concept, different configurations and then goes into the MySQL 5.6 Features for Replication as well. To name a few: Slave tables for replication, Replication Checksums, Time Delayed Replication (my favorite) and Optimized Row Based Replication. The 5.6 Features will include examples for setup, as all features tested with the latest DMR.

I look forward to seeing everyone.

“Stay hungry, stay foolish” ...... but have a backup :)

2012-01-09T10:21:00.000-07:00

As we all settle into 2012, I remember the great saying “Stay hungry, stay foolish.”

The famous“Stay hungry, stay foolish” reference is from the last Whole Earth Catalog and also made popular via the Steve Jobs 2005 Commencement Address at Stanfor d, for those that do not know. Computers today would be a very different place without the numerous people that did stay hungry and foolish as it all came together. How does that apply to the DBA of today?

Hopefully we are all still hungry but not foolish with our data. MySQL is running most of the databases that power the web these days and we cannot afford to be foolish with that data. When it comes to data integrity and security we should “wag more bark less.” By that I mean, be excited about the options you have, anticipate issues before they arise, “wag more , be hungry.” Do not be stuck in a “bark or foolish” situation of lost data and tables, downtime, or lost backups.

How many of you have had to recover a critical table that someone, you thought never would, decided to truncate. Granted accidents happens and it is hard to anticipate all of the possible situations you might find yourself in. Which is why you need a stable backup solution. Now, how long did it take you to recover that truncated data? How long it took for you to be notified? How far behind was your replicated slave database? Could you stop replication and dump the table for a recovery? This is why I, and many others, are excited about the Delayed Replication options with MySQL 5.6.

I was at Oracle Open World 2011 and I sat in on a few of the replication sessions. During one I heard someone mention, “Why would you want to delay replication?” Live Rolling backups I immediately thought and this person soon realized the benefits. It depends on the database size and activity and disks are becoming cheaper and cheaper for you to handle the logs. Would you rather pull your database off a tape, load it, dump the table then fix what was lost? I touched on this some before in a Database Chain blog post. With the Delayed Replication option you can now address some of your backups concerns with a live replicated database chain. Consider this, Server A is your slave, Server B gets time delayed set to a Day ( CHANGE MASTER TO MASTER_DELAY=86400 ), Server C gets 6 Days (So a week back from master , CHANGE MASTER TO MASTER_DELAY=518400 ), Server D gets set to 24 Days back (~month from master , CHANGE MASTER TO MASTER_DELAY=2073600 ). Now you have full access to historical data if needed. Live rolling backups !

Don't be foolish with data. Of course, you can still dump to tape the pending logs and database backups for a serious disaster. But the option to have live historical data native to MySQL replication is fantastic.

Lastly, To quote from Eric Raymond's the The Cathedral and the Bazaar, “The next best thing to having good ideas is recognizing good ideas from your users.” Delayed Replication is a community driven idea and was entered as a bugs in 2006 and 2007 http://bugs.mysql.com/bug.php?id=21639 , http://bugs.mysql.com/bug.php?id=22072, http://bugs.mysql.com/bug.php?id=28760 then entered into the worklogs in 2010 WL#344: Time-delayed Replication.

More data on this topic:
http://www.clusterdb.com/mysql-replication/delayed-replication-in-mysql-5-6-development-release/
http://datacharmer.blogspot.com/2011/01/first-look-at-delayed-replication-in.html

See ya soon at SCALE ! ( https://www.socallinuxexpo.org/scale10x/schedule )

In Case you missed these

2011-12-16T13:18:00.000-07:00

Just in case you missed these posts....

Facebook has some interesting blogs on benchmarks, InnoDB and etc...

W hat is in the InnoDB buffer pool? 12/15/2011 02:14 PM

Dynamic padding for tpcc-mysql 11/21/2011 11:51 AM

The shutdown benchmark 11/15/2011 05:29 PM

The effect of page size on InnoDB compression 11/07/2011 11:40 AM

Bugs and Spam don't mix

2011-12-16T09:26:00.000-07:00

A big thank you to the MySQL web team and engineers for helping combat spam and keeping the bugs.mysql.com site spam free.

reCaptcha is now installed and is a requirement when adding a comment to a current bug. We want the community to be able to review and comment on bugs with little spam as possible getting in the way.

MySQL Cluster to MySQL Server ... Part 2

2011-12-11T13:12:00.001-07:00

I previously posted blog about the topic of a MySQL Cluster set up and replication to a MySQL Server. The first blog was just the installation, now lets test the cluster and get replication started. Both are extremely easy.

Some people might ask, “Why set up replication from a cluster? It is a HA system why replication?”

The ability to move data out of the cluster and allow others to do reporting, data exports, simple backups all can be done easily with it replicated into another MySQL server. Yes a Cluster can do all of those queries, but taking advantage of the replication to keep some users and queries out of your production database is rarely a bad thing.

Testing the Cluster Install. Of course more MySQL Documentation:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-quick.html


mysql> SHOW ENGINES\G 
*************************** 1. row ***************************
Engine: ndbcluster
Support: YES
Comment: Clustered, fault-tolerant tables
Transactions: YES
XA: NO
Savepoints: NO

*************************** 3. row ***************************
Engine: ndbinfo
Support: YES
Comment: MySQL Cluster system information storage engine
Transactions: NO
XA: NO
Savepoints: NO

mysql>  USE test;
Database changed
mysql>;  CREATE TABLE ctest (i INT) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.28 sec)

mysql>  SHOW CREATE TABLE ctest \G
*************************** 1. row ***************************
Table: ctest
Create Table: CREATE TABLE `ctest` (
`i` int(11) DEFAULT NULL
) ENGINE=ndbcluster DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

That was easy enough...Table is built and using the ndbcluster engine.

So lets set up replication from a cluster to a MySQL Innodb Server.

1^st create a user for the replication.


GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'10.%' IDENTIFIED BY '

<password>

';
flush privileges;

For this example I am jut using a Windows Virtual Box instance. The MySQL windows installer worked great. For a guy who ever runs windows, it was nice that this was so easy. (http://dev.mysql.com/downloads/installer/)

On the Cluster server, I made sure to have my server_id and big logs set.


vi /etc/my.cnf 
[mysqld]
server-id=1
log-bin                                 = /var/lib/mysql/mysql_demo-bin

restarted the sql server and confirmed it was running:


mysql> show master status\G< 
*************************** 1. row *************************** 
File: mysql_demo-bin.000001 
Position: 112 Binlog_Do_DB:  
Binlog_Ignore_DB:   1 
row in set (0.00 sec)

So lets create a simple table that we can use for test later.

PLEASE take note... This is just a table to show it replicated. If you create tables for real use in a Cluster and replicated ... ALWAYS have a primary key!


mysql>  CREATE TABLE slave_test (i INT) ENGINE=NDBCLUSTER;
Query OK, 0 rows affected (0.28 sec)

Now back on my windows mysql server. Since it came with workbench I used this to set the server_id (server_id =2) and restart the server. I then opened the sql editor and added the master settings.

via_mysql_workbench>

 CHANGE MASTER TO
-> MASTER_HOST='10.132.241.18',
-> MASTER_PORT=3306,
-> MASTER_USER='slave_user',
-> MASTER_PASSWORD='<password>';

via_mysql_workbench> start slave;

via_mysql_workbench>use test;

via_mysql_workbench> SHOW CREATE TABLE slave_test ;

'slave_test', 'CREATE TABLE `slave_test` (

`i` int(11) DEFAULT NULL

) ENGINE=InnoDB DEFAULT CHARSET=latin1'

mysql> show slave status;

...

Slave_IO_Running, Yes

Slave_SQL_Running Yes

...

So now via these blog posts.. I have a cluster with a geolocated node both on Oracle Linux 6 being replicated to another remote location MySQL server running Windows via a Virtual box instance. I can also now use Workbench to connect to the local windows server as well as the cluster if needed. This is not a production system but it does show that Cluster can be installed easily, remotely, and takes advantage of commodity systems.

Part 1 -- The Install
Part 2 -- Testing Cluster more and Replication Setup

Congrats to three more MySQL Oracle ACE members...

2011-12-09T13:25:00.001-07:00

It is truly amazing the amount of knowledge and talent that work with and support MySQL and the MySQL community. Thanks to all of you.

Over the last month the MySQL ACE list has grown even more:

George J. Trujillo is now an Oracle ACE and a MySQL Oracle ACE.

Wagner Bianchi became a MySQL Oracle ACE not long ago.

Roland Bouman is the latest addition as a MySQL Oracle ACE.

Congrats to all of them !

MySQL Cluster to MySQL Server Part 1

2011-12-09T10:52:00.003-07:00

Recently I was working on a MySQL Cluster that is replicated to a MySQL server. I will create a few blog posts to show how all of this can be done. This is of course not the only way to do this.. Any feedback or other options are welcomed in comments for the community to use.

Part one: the install

First the MySQL Documentation about MySQL Cluster is very good I will make references to it for your convenience.
You can go about this a few different ways but this is a simple RPM install:

Download all the related rpms from http://dev.mysql.com/downloads/cluster/ and install.

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-linux-rpm.html) is here to help you with the installs as well.

rpm -ihv MySQL-Cluster-gpl-*.rpm

Ok that was easy enough ... Make sure we have what we expected...


rpm -qa | grep MySQL

MySQL-Cluster-gpl-debuginfo-7.1.17-1.el6.i686
MySQL-Cluster-gpl-client-7.1.17-1.el6.i686
MySQL-Cluster-gpl-clusterj-7.1.17-1.el6.i686
MySQL-Cluster-gpl-test-7.1.17-1.el6.i686
MySQL-Cluster-gpl-server-7.1.17-1.el6.i686
MySQL-Cluster-gpl-devel-7.1.17-1.el6.i686
MySQL-Cluster-gpl-management-7.1.17-1.el6.i686
MySQL-Cluster-gpl-extra-7.1.17-1.el6.i686
MySQL-Cluster-gpl-storage-7.1.17-1.el6.i686

Just so this can be a tiny bit of a more real world example, I also installed an external node in a remote (out of the subnet/datacenter) location.


[root@remote_cluster src]# rpm -ihv MySQL-Cluster-gpl-storage-*.rpm

OK software install is straight forward and easy, but what do you do now ?

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-configuration.html) on how to configure the cluster are straight forward and very easy to follow.

Based on that documentation, I have set all the ips in all the related configuration files ...

My Config file looks like this after removal of comments. I have a local node and a remote node for this simple example.


#/var/lib/mysql-cluster/config.ini 
[ndb_mgmd]
# Management process options:
hostname=10.132.241.18                  # Hostname or IP address of MGM node
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files
NodeId=1

[ndbd default]
# Options affecting ndbd processes on all data nodes:
NoOfReplicas=4                            # Number of replicas
datadir=/var/lib/mysql-cluster  # Directory for MGM node log files
DataMemory=80M                # How much memory to allocate for data storage
IndexMemory=18M               # How much memory to allocate for index storage

[ndbd]
hostname=mysql_demo.localdomain
NodeId=3

[ndbd]
hostname=10.159.37.130
NodeId=4

[mysqld]
Nodeid=10

[mysqld]
Nodeid=30

[mysqld]
Nodeid=40

Nodes... Start your engines..... ok so really, lets get it started...

MySQL Documentation (http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-install-first-start.html) on how to start it up for the 1st time..

On Localhost


/usr/sbin/ndb_mgmd -f /var/lib/mysql-cluster/config.ini
MySQL Cluster Management Server mysql-5.1.56 ndb-7.1.17

On Localhost / Data Node


/usr/sbin/ndbd
2011-12-08 12:21:16 [ndbd] INFO     -- Angel connected to 'localhost:1186'
2011-12-08 12:21:16 [ndbd] INFO     -- Angel allocated nodeid: 3

On remote node :


./ndbd
2011-12-08 12:25:39 [ndbd] INFO     -- Angel connected to '10.132.241.18:1186'
2011-12-08 12:25:40 [ndbd] INFO     -- Angel allocated nodeid: 6

On Localhost Start the sql server and check the nodes.


# /etc/init.d/mysql start

# /usr/local/bin/ndb_mgm -e show
Connected to Management Server at: localhost:1186
Cluster Configuration

---------------------
[ndbd(NDB)]     4 node(s)
id=3    @127.0.0.1  (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=4 (not connected, accepting connect from mysql_demo.localdomain)
id=5    @10.159.37.130  (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)
id=6    @10.159.37.130  (mysql-5.1.56 ndb-7.1.17, starting, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @10.132.241.18  (mysql-5.1.56 ndb-7.1.17)

[mysqld(API)]   3 node(s)
id=10 (not connected, accepting connect from any host)
id=30 (not connected, accepting connect from any host)
id=40 (not connected, accepting connect from any host)

OK lets log in and test it ?


# mysql

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.56-ndb-7.1.17-cluster-gpl MySQL Cluster Server (GPL)
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select VERSION();
+-------------------------------+
| VERSION() |
+-------------------------------+
| 5.1.56-ndb-7.1.17-cluster-gpl |
+-------------------------------+
1 row in set (0.00 sec)

Of course if you need to stop and restart (MySQL Documentation: http://dev.mysql.com/doc/refman/5.0/en/mysql-cluster-install-shutdown-restart.html) a cluster, it is a simple process.


/usr/local/bin/ndb_mgm -e shutdown
/etc/init.d/mysql stop

Others posts on cluster installs:

http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-installation.html

http://alexyu.se/comment/37

http://dbperf.wordpress.com/2011/03/04/mysql-cluster-setup-and-replication-between-ndb-and-non-ndb-engines-2/

Mine:
Part 1 -- The Install

Part 2 -- Testing Cluster more and Replication Setup

XFCE TO LXDE

2011-11-30T09:05:00.000-07:00

Wow how things change quickly sometimes. As per a previous post I have recently moved to XFCE. It was great and things worked very well. Then, it just went nuts...

Active windows failed to work and I was stuck on a single desktop (I live with multi-desktop), mouse failures and etc. To quote the great Dr. Peter Venkman, "Human sacrifice, dogs and cats living together... mass hysteria! "

Anyway, I needed to get something done at the moment and not fight with it so I did a quick yum install of LXDE. I had to go to the gnome session to do this, that is how crazy the XFCE went. But I am currently happy with the LXDE.

I will get back to XFCE and see if I can find out what in the world made it go nuts but everything takes time.

Now I am not going to say I am at home yet, after all I did that with XFCE and things went crazy. But it is working very well so far.

OTN MySQL User Forum

2011-11-29T15:42:00.000-07:00

One thing I enjoy about this job is being able to meet face to face the with MySQL Community. We have a big community and it is hard to keep up ! Sadly, I will not be able to make the OTN MySQL User Forum in Brazil but I want to encourage the community to attend the event.

It is a free forum, full of technical sessions, taught by engineers and leaders of the MySQL open source community will also explore Oracle's strategy with regard to MySQL, as well as the new MySQL Enterprise innovation and what to expect from MySQL 5.6.

Best of luck to the MySQL Brazil Community.

MySQL and Debian Linux

2011-11-28T10:47:00.000-07:00

It is available now ! MySQL 5.5.18 now offers Debian Linux (.deb) packages.

They are available via the http://dev.mysql.com/downloads/mysql site !

Finally the Debian community can upgrade easily to MySQL 5.5 !

Database Chain

2011-11-22T14:07:00.000-07:00

I was at an event recently and the topic of replication stirred the curiosity of the audience. A few audience members had Master to Master but they wanted to move away from that. Others had multiple slaves but wanted little downtime and backend work if a master failed. Relayed replication or a database chain is an option to solve some of their issues. Another option would be Cluster but it depends on your infrastructure, budget and application, some of them are looking into this as well. Is a chain the best solution for everyone, of course not. While I cannot do consultant work to help them, I can blog about it…

A relayed replication environment allows you to, of course, have replicated databases but also have a replication environment that is still available if or when the master fails. The image to the left shows a simple example of how the databases work together.

One of the first things to keep in mind is that your application/environment needs to be able to handle a master switch. That means do not reference the master by IP or hostname. Instead use NIS, LDAP, alias in host files, or just DNS. Something simple as Server A == Master, then your MySQL clients and applications reference master when doing connections. This allows you to adjust the reference point quickly when a master fails. So if Server A crashed, adjust reference to Server B == Master. You still have 2 slaves running as well. You can then stop slave on Server B, examine Server A and gather any data you feel might have been lost. Sometimes starting the Slave on Server B to pull anything left over from Server A can work but auto increment ids could cause a conflict easily, so use caution when doing this. Once I am done with Server A, I move that to become a new slave of Server D, I now have four servers again.

I like to have at least four servers when doing a chain. If I only had three servers then I am very dependant on Server B, the 1^st slave. If that box was to fail I am left building a slave from backups or off the master. If I have four and Server B crashes I can move the Master to Server C and still have a replicated system. Now, this of course is entirely dependant on your slaves staying up to date. Building your application to process data that will not slow down a slave can help support this type of environment.

Another nice feature coming soon with MySQL is timed delayed replication. This option can be used with a chain as well. The time delayed option pulls all data and only applies it to the db based on the threshold you set. So you could build out a live backup solution using a database chain. Server A is your slave, Server B can get time delayed set to a Day, Server C gets 6 Days (So a week back from master), Server D gets set to 24 Days back (~month from master). Now you have full access to historical data if needed. If a user drops a table that shouldn’t have, it is a lot easier to dump that table and import it rather than pulling it off a tape or disk 1^st.

Do not let the slave servers go to waste either. Allow your database handles to use the all of the slaves accordingly. Keep all the reads you can off the master. Create a script/class that uses a handle for your master that uses an account with appropriate access. Then create another script/class to handle connections to the slaves. This slave connection can be created to accomplish a few things. First allow it to have a default of seconds back from master per query passed to it. Second create it to use the aliases of all the slaves and connect to check what is the seconds back, then use the valid server per threshold. This is a very fast check but the process can be enhanced to have times set on a schedule with another script, depends on your application. Now this allows your slave connection to take a query that has a threshold of 1 day (running a report for all of yesterday for example) and push that down to execute on Slave D as long as it is within the threshold.

Gnome 3 to XFCE

2011-11-21T18:08:00.000-07:00

So I recently moved my laptop onto Fedora 16. I had some issues with the previous Linux distro and figured I would go with this for my laptop.

Issues with Gnome 3 are not new but I figured I would collect a few links for everyone so hey can get past the issues with this.

1st - Get icons back on your desktop -
http://json8.wordpress.com/2011/08/05/enabling-desktop-icons-in-gnome-3/

2nd - Get workspaces to work on more than 1 monitor. ( THIS DROVE ME CRAZY)
http://gregcor.com/2011/05/07/fix-dual-monitors-in-gnome-3-aka-my-workspaces-are-broken/

3rd. The wireless worked great so that was helpful.

4th Set your shortcuts to switch to workspace because going to activities everytime to get anyplace drives me crazy. Shows some outside users testing was needed more.

So if this is enough for you then great! But remember gnome 3 is not the only option :
http://digitizor.com/2011/08/04/linus-torvalds-ditches-gnome-for-xfce/
http://linux.slashdot.org/story/11/08/04/0115232/linus-torvalds-ditches-gnome-3-for-xfce

A quick yum install of xfce and my life was so much better.

If your a long time Gnome user, like myself, KDE was not an option. No reason I am just not a KDE guy. However, I did come to find that I am a XFCE guy. Within seconds of entering the XFCE environment I was home.

OTN Developer Day: MySQL - Minneapolis, MN

2011-11-17T15:32:00.000-07:00

Thanks to the community for a great turn out at the OTN Developer Day: MySQL in Minneapolis, MN.

The room was packed and we had to bring more tables in to handle the crowd. Sessions covered enterprise, replication, overviews and perfomance.

I hope the local MySQL user group can support the new members in their area.

http://www.meetup.com/mn-mysql/

Best of luck to everyone and thanks again.

Easy Phone Number Checks

2011-11-08T15:50:00.000-07:00

So this is a simple free example of how to check a phone number via a mysql database. Yes more complete validation options are available, some even have to pay for, but this is better than no check at all.

The data is available here : wget -q http://www.telcodata.us/custom/telcodata.dum p

It loads a table like this:

CREATE TABLE `telcodata` (

`npa` int(3) NOT NULL,

`exchange` int(3) NOT NULL,

`thousands` int(3) NOT NULL DEFAULT '-1',

`company` text,

`ratecenter` text,

`clli` text,

`type` text,

`emaildomain` text,

`parentco` text,

`state` text,

`zip` text,

`lat` float(9,5) DEFAULT NULL,

`lon` float(9,5) DEFAULT NULL,

`latanum` int(11) DEFAULT NULL,

`ocn` varchar(4) NOT NULL,

`ilec` varchar(4) DEFAULT NULL,

`ilecname` text,

`tandem` text,

KEY `rcstate` (`ratecenter`(11),`state`(2)),

KEY `npaexch` (`npa`,`exchange`,`thousands`)

)

Once the data is loaded you can use this with data you have gathered. This could be from websites or data warehouse data who knows...

Then you can check to see what type of a phone number it is ?

SELECT npa,exchange,thousands, company , ratecenter , type, parentco, CONCAT(npa,exchange,'0000','@',emaildomain) as send_a_txt

FROM telcodata where npa=303 and exchange=335 and ( thousands=8 or thousands < 0 ) ORDER BY thousands desc LIMIT 1 \G

npa: 303

exchange: 335

thousands: 8

company: CELLCO PARTNERSHIP DBA VERIZON WIRELESS - CO

ratecenter: DENVER

type: WIRELESS

parentco: Verizon

send_a_txt: 3033350000@vtext.com

1 row in set (0.00 sec)

I have placed 0000 in place of the thousands field, if you wanted a valid thousands option you would replace it with your data.

A none cell looks like this

SELECT npa,exchange,thousands, company , ratecenter , type, parentco , CONCAT(npa,exchange,'0000','@',emaildomain) as send_a_txt FROM telcodata where npa=303 and exchange=623 and ( thousands=9 or thousands < 0 ) \G
*************************** 1. row ***************************
       npa: 303
exchange: 623
thousands: -1
   company: QWEST CORPORATION
ratecenter: DENVER
      type: RBOC
parentco: NULL
send_a_txt: NULL
1 row in set (0.05 sec)

In this case you can check to see if the data given is a cell phone or not by checking the type field. Your tests are only as good as your data but this is a start.

MySQL Utilities

2011-11-04T14:52:00.000-06:00

I recently explored the mysql-utilities scripts available on launchpad ( https://launchpad.net/mysql-utilities ) The examples below are from my local test db.

The code is available via a quick bzr download after you sign in with your launchpad id of course.

bzr launchpad-login <user_id_ from_launchpad>

bzr branch lp:~mysql/mysql-utilities/trunk

MySQL Utilities code does have some requirements, Python between 2.6 and 3.x . So check your python version , some of the distributions have older versions. ( # python –version ) If you want the manuals, then Sphinx (which also requires Jinja) is also required. Of course a connector to the database would be helpful. You can get the latest python connector, via launchpad as well, at https://launchpad.net/myconnpy.

If your curious about more of the options for setup the help commands will give you a full list. ( python setup.py –help-commands ). Otherwise a quick set up is as simple as python setup.py install. I happen to not have the /etc/profile.d/mysql-utilities.sh on my system and the set up tossed an error. A simple touch of this file and all worked fine.

So you have it all installed, now what? You will find inside the scripts directory (../trunk/scripts) approximately 15 different python scripts.

mysqldbcompare.py , mysqldbcopy.py, mysqldbexport.py ,mysqldbimport.py , mysqldiff.py , mysqldiskusage.py , mysqlindexcheck.py, mysqlmetagrep.py , mysqlprocgrep.py , mysqlreplicate.py, mysqlrplcheck.py , mmysqlrplshow.py , mmysqlserverclone.py, mmysqlserverinfo.py , mysqluserclone.py

Some quick examples via the employee database.

Index Checks:

./mysqlindexcheck –server=<username>:<password>@<HOST>:<PORT> employees

# Source on 192.168.0.2: ... connected.

# The following indexes are duplicates or redundant for table employees.dept_emp:

CREATE INDEX emp_no ON employees.dept_emp (emp_no) USING BTREE

# may be redundant or duplicate of:

ALTER TABLE employees.dept_emp ADD PRIMARY KEY (emp_no, dept_no)

# The following indexes are duplicates or redundant for table employees.dept_manager:

CREATE INDEX emp_no ON employees.dept_manager (emp_no) USING BTREE

# may be redundant or duplicate of:

ALTER TABLE employees.dept_manager ADD PRIMARY KEY (emp_no, dept_no)

# The following indexes are duplicates or redundant for table employees.salaries:

CREATE INDEX emp_no ON employees.salaries (emp_no) USING BTREE

# may be redundant or duplicate of:

ALTER TABLE employees.salaries ADD PRIMARY KEY (emp_no, from_date)

# The following indexes are duplicates or redundant for table employees.titles:

CREATE INDEX emp_no ON employees.titles (emp_no) USING BTREE

# may be redundant or duplicate of:

ALTER TABLE employees.titles ADD PRIMARY KEY (emp_no, title, from_date)

Quick Server information:

./mysqlserverinfo --server=<username>:<password>@<HOST>:<PORT> employees

# Source on 192.168.0.2: ... connected.

+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+

+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+

+----------------------+---------------+------------------+----------+--------------------------+--------------+----------------------+-----------------+------------+----------------+

If you need grep the tables to find something?

./mysqlmetagrep --server=<username>:<password>@<HOST>:<PORT> --pattern=emp_no

+----------------------------+--------------+---------------+------------+-------------+----------+

+----------------------------+--------------+---------------+------------+-------------+----------+

+----------------------------+--------------+---------------+------------+-------------+----------+

Disk Usage:

./mysqldiskusage --server=<username>:<password>@<HOST>:<PORT>

# Source on 192.168.0.2: ... connected.

# Database totals:

+---------------------+-----------------+

| db_name | total |

+---------------------+-----------------+

….

Total database disk usage = 24,721,809,136 bytes or 23.00 GB

They are tools that are quick and easy to get installed and use to help and enhance your MySQL experience.

MySQL Utilities are part of the MySQL Workbench but they can be used as a stand alone option.

Also available is the testing suite “mut” which is available under the mysql-test directory.

# ./mut.py --server=<username>:<password>@<HOST>:<PORT>

MySQL Utilities Testing - MUT

Parameters used:

Display Width = 75

Sorted = True

Force = False

Test directory = './t'

Utilities directory = '../scripts'

Starting port = 3310

Servers:

Connecting to 192.168.0.2 as user kdl on port 3306: CONNECTED

---------------------------------------------------------------------------

TEST NAME STATUS TIME

===========================================================================

experimental.t1 [pass] 48

experimental.t2 [pass] 14

experimental.t3 [pass] 3

experimental.t4 [pass] 10

main.check_index [FAIL]

ERROR: Result file mismatch:

- # Source on localhost: ... connected.