tag:blogger.com,1999:blog-58562298169670353122024-02-06T21:53:55.888-08:00mySQL Hints: Tips, Suggestions, and FindingsMySQL 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:00MySQL Swap Insanity: SolvedYes, 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:00The Conflicted Data AnalystInspired 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:00How to Find Out if an Entire String Is NumericProblem: 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:00How to Parse the Most Selected Tables From MySQL General Query Log in One LineProblem: 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:00My Ignite Talk at MySQL ConferenceMy 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:00Need to Replicate to a Database with a Different Name?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
ThusMySQL 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:00How to Log User Connections in MySQLThere 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:00How to Save a Lot of Time with LOAD DATA INFILE Involving Large FilesAre 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:00How to SHOW PROCESSLIST Every .5 SecondsYes, 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:00My Top 10 List For DevelopersAn 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:00KickFire is BackAfter 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:00Fixing InnoDB IMPORT TABLESPACE Error: ERROR 1030 (HY000): Got error -1 from storage engineSetupWe 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): GotMySQL 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:00Case Sensitive FieldsYes, 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:00Things Not Replicating Correctly? Part 2My 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:00An Interesting Replication TipWe 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:00How to Group Customers by Number of Purchases Quickly!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:00MySQL Conference: The Week AfterWow... 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:00Data Warehousing 101: Pitfalls to AvoidSometimes 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:00Data Warehousing 101: Requirements for a Data WarehouseYes, 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; DataUnknownnoreply@blogger.com0tag:blogger.com,1999:blog-5856229816967035312.post-21767158635554822102008-02-13T07:55:00.000-08:002008-02-13T08:22:29.174-08:00Don't Ever Use NOW() - `datetime field`***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:00Data Warehousing 101: The purpose of a data warehouseWhen 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:00What Do You Name Date Fields?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:00Sun + MySQL: One Week LaterI 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:00An exercise in SELF JOINingThere 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:00How to Send a Report on Most Common Domain Names without Grinding Your DBThis 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