tag:blogger.com,1999:blog-58562298169670353122026-05-28T12:45:26.540-07:00MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.comBlogger36125tag:blogger.com,1999:blog-5856229816967035312.post-6781433193485507272016-01-28T14:39:00.004-08:002016-01-28T14:49:42.488-08:00

Yes, it has been a while since my last post.  Still in the midst of MySQL ecosystem, and enjoying it tremendously.  I thought I would make this post as it relates to a topic that has some varied results in the online world, and I would like to kick start this blog again.  We'll see how that goes... :) MySQL has been able to harness the potential of more powerful (CPU) and larger (

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com2tag:blogger.com,1999:blog-5856229816967035312.post-27802144562384969122012-04-24T15:30:00.002-07:002012-04-24T15:34:17.597-07:00

Inspired by a post from Juice Analytics. We are a conflicted people. We love our TV and movie violence but worry that it ruins our children’s minds. We want to reduce healthcare costs, but don’t want to restrict the free market. Conflicts like these leave little room for a satisfactory answer. Basic principles are in conflict and deeply-rooted desires run up against painful consequences. We

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-81816695129440779962012-01-24T12:45:00.000-08:002012-01-24T12:48:42.300-08:00

Problem: Find out if an entire string is numeric.  (Just like the is_numeric function in php.) Problem, Part II: Most online resources show how you can find out if part of your string is numeric. Solution: SELECT str FROM tbl   WHERE str REGEXP('(^[0-9]+$)');Give it a whirl! For those who would like to view some test results, here goes: mysql> SELECT str FROM tbl; +---

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com5tag:blogger.com,1999:blog-5856229816967035312.post-28905491159337004942011-06-03T07:37:00.000-07:002011-06-03T07:37:16.961-07:00

Problem: Pin down redundant queries by finding out which tables are being selected from the most. I like general query log parsing tools, such as mk-query-digest or mysqlsla. However, there are times when I am on a server as a dba with limited access and can only read files in /tmp.  And other times I just need to get something done quickly. So what did I do to find out the most

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com3tag:blogger.com,1999:blog-5856229816967035312.post-57754721994166684262011-04-27T10:03:00.000-07:002011-04-27T10:03:15.069-07:00

My Ignite Talk (5 minute talk, 20 slides, 15 seconds per slide) at the MySQL Conference about Data Visualization was picked among three for "the Best of Ignite".  I would say I barely scraped into third spot, and it was inspiring giving a talk after Baron Schwartz gave his at the keynote on the last day of the conference. The other two talks were "Scale Fail" by Josh Berkus (hilarious,

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-33006068541115215092011-02-02T08:57:00.000-08:002011-02-02T08:57:59.787-08:00

This is the first time this has happened to me, so I got excited about it until I noticed that this command has been with us from nearly the very beginning (ie MySQL 4). The Situation Master Server contains db1 Slave Server contains db1 and db2 Slave Server needs db1 from the Master to replicate to db2 The Solution On my.cnf on the slave server: --replicate-rewrite-db=from_name->to_name Thus

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com3tag:blogger.com,1999:blog-5856229816967035312.post-29234622892171370392011-01-11T13:17:00.000-08:002014-02-19T09:54:52.763-08:00

There is nothing that MySQL 5.1 explicitly performs to log user connections, but there is a combination of MySQL commands that can log user connections without having to turn on the general query log. I repeat: you do not have to turn the general query log on to make this work! Would you like to find out what that is? With the help of Baron Schwartz's post on http://www.xaprb.com/blog/2006/07/

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com13tag:blogger.com,1999:blog-5856229816967035312.post-55842051611720771812010-02-19T14:40:00.001-08:002010-02-19T15:00:05.910-08:00

Are you tired of waiting an hour or so performing a LOAD DATA INFILE statement involving a file with millions of rows?If so, and you have enough money to pay for 3 monthly installments of $5.99... (Belgian chocolate will substitute nicely) -- you TOO can be a hero in your office and go home early for the weekend. :)How? Disable keys. Load data infile. Enable keys.ALTER TABLE

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-43141968929469747162010-01-08T13:33:00.000-08:002010-01-08T14:02:53.819-08:00

Yes, it has been a while since I last posted, so we'll see how this year goes...Just the other evening I came across something that I should have done much sooner. Run SHOW PROCESSLIST every second without hitting "ENTER" repetitively.The Setup1) Create a temporary user with a non-sensitive password on localhost with SUPER privs. Yes, this crazy, but I did say *temporary*, right? mysql> GRANT

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com5tag:blogger.com,1999:blog-5856229816967035312.post-38110894159503957812008-11-19T08:04:00.000-08:002008-11-19T08:34:11.692-08:00

An excellent question was posted in a MySQL group in LinkedIn the other day that I had to post it here.What is your "Top 10 List for Programmers Working with MySQL"?I started mine... Let me know your thoughts on my list, and feel free to add other items to the list. I interface with programmers on a daily basis, and our experience lends us to help them in certain ways, but I think a "Top 10"

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com2tag:blogger.com,1999:blog-5856229816967035312.post-25075496143231404332008-10-15T14:59:00.000-07:002008-10-15T15:46:44.245-07:00

After receiving an email about talking with Robert David, Director of Sales at KickFire, I checked out recent news on KickFire, as there had been little written about this company aside from the big splash they made at the MySQL Users Conference back in April 2008.And, lo and behold, there was a piece of news, posted on October 14 - Kickfire Enters Into MySQL Enterprise Agreement With Sun

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com2tag:blogger.com,1999:blog-5856229816967035312.post-7467324608236616832008-10-10T08:48:00.000-07:002008-10-10T14:46:45.509-07:00

SetupWe have one InnoDB file per table on our database, which was set with the following option: innodb_file_per_table.This allows me to portably transport Innodb files on a system level with minimal hassle, and is much faster than mysqldump, as these tables are several GB each.ProblemWhen transporting an .idb file from one server to another, I ran into the following error:ERROR 1030 (HY000): Got

MySQL DBA Guyhttp://www.blogger.com/profile/09642019350937418521noreply@blogger.com4tag:blogger.com,1999:blog-5856229816967035312.post-705500855070541682008-09-04T10:12:00.000-07:002008-10-11T14:08:05.688-07:00

Yes, it has been too long since I last posted on this blog (3 months) - that's my apology; let's move forward.There's too many interesting MySQL bits and pieces that I still come across my desk that I cannot help but post these fantastic learning opportunities to my blog. Obviously, you can read about all things interesting from PlanetMySql.org, but I hope that my blog will help sift people

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5856229816967035312.post-80546842658547483442008-06-27T14:21:00.000-07:002008-10-11T14:48:57.752-07:00

My last post on June 10 was about when MySQL received queries from php scripts that looked like this:mysql_select_db = ('database_I_DO_want_to_replicate', $link);$sql = "INSERT INTO db_I_DO_NOT_want_to_replicate.repl_table (a,b,c) VALUES (1,2,3);";-- and thus could not pass this over to the slave unless I set Replicate_Wild_Ignore_Table value IN ADDITION to Replicate_Ignore_DB as such in my

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-66319549472742413292008-06-10T07:21:00.000-07:002008-10-11T14:11:23.060-07:00

We recently moved some databases to different servers, and I altered our mySQL slave configuration files to take into account some of the databases we wanted to replicate (Replicate-do-db) and others that we wanted to ignore (Replicate-ignore-db) -- each of these server cases were mutually exclusive.All went well, until I found a peculiar error:Error 'Table 'db101.table101' doesn't exist' on

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-23745430485748910672008-05-07T14:48:00.000-07:002008-05-07T18:29:28.372-07:00

This is a great little sql statement that packs a nice punch for the number crunchers out there...A disclaimer first: make sure you don't do this on a live OLTP table with millions of rows unless you want your customers to wait. Thus, run it on your slave :).Onto business...The Request"I need to know the number of people who made one purchase, two purchases, three purchases, and so on. Right

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-8318816963504613832008-04-22T13:42:00.000-07:002008-10-11T14:12:09.830-07:00

Wow... I had a great time at the annual MySQL Users Conference in Santa Clara.Highlights:Received my CMDBA designation. Woohoo!Sun announced the possibility of an open source mobile phone;All day mysql proxy tutorial with Jan Kneschke and Giuseppe Maxia;Amazon's Power of Infrastructure as a Service - great premise; glitches remain;Great food and a nifty side trip to Santa Cruz;MySQL Workbench -

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-43255925000395419622008-03-14T07:43:00.000-07:002008-03-14T09:40:53.474-07:00

Sometimes technology gets in the way of getting things done, and us tekkies must do what we can to make our data warehouse work for the purpose it is intended. Ralph Kimball makes 10 good points about the common pitfalls to avoid when implementing a data warehouse, as listed in his book "The Data Warehouse Toolkit", published by Wiley.Become immersed with technology rather than the requirements

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-8333405359146773522008-03-07T10:59:00.000-08:002008-03-11T13:03:58.185-07:00

Yes, it has been a while since I added an entry in my blog. I have been working on creating a dynamic data warehouse system reliant on the traditional LAMP stack (and a very nifty graphical plug-in - please comment below if you wish to know what it is!).Firstly, I must make these qualifications before you read further:I consider Bill Inmon and Ralph Kimball the pioneers of data warehousing; Data

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-21767158635554822102008-02-13T07:55:00.000-08:002008-02-13T08:22:29.174-08:00

***All point news bulletin***Do not ever use NOW() - `datetime field`! Broadcast this from the mountaintops of open source to the valleys of companies who dabble in it.Instead, use the following:TIME_TO_SEC(TIMEDIFF(Now(),`datetime field`))There are other ways to do the above, just as long as you do not use NOW() - `datetime field`.Wanna see proof?mysql> SELECT RIGHT(NOW(),8) `Now`, RIGHT(

Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-5856229816967035312.post-18140953644527000582008-02-06T11:29:00.000-08:002008-02-06T12:03:23.169-08:00

When your company decides that "it is time to build a data warehouse", what thoughts come to mind?1) A magical fairy ice cream land where data is presented in chocolate shells for everyone to digest perfectly;2) A big literal warehouse in the industrial section of town with rusty old containers;3) Another place to put data, which means another place for you to track and monitor additional

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-27449295037368599232008-01-30T08:38:00.000-08:002008-10-11T14:12:45.922-07:00

I have seen too many date fields that have been named after reserved key words that I need to make an entry for this... It's like naming your kid "Kid". Wait a sec... that's happened many times before as well.I will make a "Proper Naming Conventions" page for mySQL after checking a few more sources first, but this date naming issue cannot wait another day.Thus..First Rule: Do not use a reserved

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-90427987875089922942008-01-23T07:22:00.000-08:002008-01-23T20:17:26.973-08:00

I really like the 21st century open source technology community. 20 years ago, if a company bought out another company, everyone would have their own island of opinions and response, and you would not know if you were on-track or even if the publicized reports were on-track either.Today, it is amazing to see the responses around the boards and blogs, which seems to keep the mainstream reports

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-40165429491530066872008-01-15T08:09:00.000-08:002008-01-15T09:19:43.130-08:00

There was only so much I could do for my 1976 Mustang. I did not have money to put an Edelbrock head in, so I spent what little I had in a new stereo, a tinted back window, a new muffler that increased the neighborhood noise level a notch, and a couple of other random parts. In the end, it really did not go any faster, and I was $650 poorer.I liken my experience with SELF JOIN to my 1976

Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-16215306936189572202008-01-07T07:48:00.000-08:002008-01-09T10:34:39.303-08:00

This is a continuance from my previous entry on reversing an email address to search for a domain. Minutes after I ran the following query,SELECT COUNT(1) FROM rv_emails WHERE rv_email LIKE REVERSE('%msn.com');I was asked to do a count of the top 50 domains in our table of email addresses.So, I simply changed my previous route by performing the following steps:1) Add a 'domain' column to my

Unknownnoreply@blogger.com0