Everything MySQL

A great place to be!

Chris's Blog (30)

No Password – No Problem

In the past I have needed to gain access to MySQL servers when the password had been strangely forgotten. Below there are thee different processes to gain access to MySQL if you just don’t have the right kind of access you need or want.



Option 1: --skip-grant-tables



This will allow you to login to the server and change what you need to in the mysql.user table. Of course you will need to restart the server again without --skip-grant-tables if you want the… Continue

Added by Chris on October 20, 2009 at 6:49pm — 3 Comments

A MySQL problem or something else?

Sometimes problems manifest inside of MySQL but the MySQL server is really not the problem. A good example is how MySQL uses reverse DNS lookups for authentication.



The Problem



You are alerted to a problem with a MySQL server either by Nagios or whatever… You log into the server and everything seems to be working correctly until you run “show full processlist”. The majority of your connections are in an “Unauthenticated” state and the rest are in some other state,… Continue

Added by Chris on October 5, 2009 at 3:53pm — 2 Comments

Pager - but not on call!

Over the past few years I have found that "pager" inside of MySQL is a really useful tool. I have come up with a few simple, but extremely effective, ways to use it.



-- Example 1


Lets say you have a MySQL server that is really busy with extremely long queries. You run a “show full processlist” and everything going on streams before your eyes, new lines and all. A more readable way to see what is going on is…



Mysql> pager less –S



By running the… Continue

Added by Chris on October 2, 2009 at 5:25pm — 2 Comments

Fun with mysqlslap benchmarking

Mysqlslap is a good benchmarking tool but can be much more versatile in my opinion. So, recently, I was benchmarking a problem that was, not hard to solve, but somewhat tricky to benchmark. Using mysqlslap was the fastest way to get the answers I wanted with the least amount of BS, or so I thought!



Lets take a look:



-- The problem

1. A MySQL instance with 5… Continue

Added by Chris on September 22, 2009 at 7:06pm — No Comments

The Project Formally Known as Golden Gate

So I was watching the discovery channel about the maintenance of the Golden Gate bridge. I was surprised to find out that it takes two years to paint the whole thing, then, when they are "finished" the job starts all over again. Basically, this is a never ending project! I started to think about the larger, several thousand server, MySQL installations I've worked on and how to improved performance. Oddly enough The Project Formally Known as Golden Gate was born! Thought I would change the name… Continue

Added by Chris on September 14, 2009 at 10:00pm — 1 Comment

Using TMPFS for MySQL's tmpdir

There have been a lot of systems I have looked at where the Created_tmp_disk_tables was very high. MySQL has good documentation on how it uses memory, specifically temp tables, here.



I would like to talk about not "why" MySQL does this but how to speed up the performance when MySQL internally or users create temporary tables to disk. A great solution is TMPFS, a quick how to is… Continue

Added by Chris on September 12, 2009 at 9:27pm — 3 Comments

What's faster than ALTER?

Well, the answer I always get is "DROP". Then I would say... "what if you wanted to preserve your data?" It is very common for DBAs to think only in terms of what is obviously available in MySQL, ALTER, than all of the tools that are really there. I have been placed in situations in many companies where ALTER was just not the way to go.



Given the amount of MySQL usage throughout the WEB2.0/IT industry and the fact that data sets seem to keep growing... I have come up with a great… Continue

Added by Chris on September 10, 2009 at 2:30pm — 8 Comments

Testing Replication Over the Pond – Part 2 Secure

Testing Secure Replication



For the first test I used encrypted (SSL) replication and inserted 200,000 records using three 10 minute disconnection intervals per hour.



After several hundred thousand of inserts, deletes and updates on the SOA and RR tables simultaneously over a normally connected SSL replication channel I have the following results:



RR Update Test

Test 1:

RR Records Updated (Changes Set) = 129145

Elapsed… Continue

Added by Chris on September 6, 2009 at 8:41pm — No Comments

Testing Replication Over the Pond - Part 1 Non-Secure

Testing Non-Secure Replication



A series of experiments were conducted to determine whether MySQL replication would prove to be reliable with SSL enabled. Please note that all tests were conducted using the MyDNS schema with includes the SOA and RR tables on MySQL 5.1.



The first experiment sets focused on replication operations and not on a predetermined set of Insert, Update or Delete patterns. So Inserts were used since they are the easiest to tag and verify.… Continue

Added by Chris on September 6, 2009 at 8:40pm — No Comments

Index for god sake!

This is an old post I put on the old mysqlhow2.com that ended up on modphp.org. Both sites are run by people I know so it's coo. Anyway, I thought it should be here as well.



Lets look at the results of the query without returning all the results:



NOTE: Use EXPLAIN

- Explain will show the possible rows to be examined in your query. (The more rows the slower the return)

- Example of EXPLAIN: (we will be using the world database by… Continue

Added by Chris on September 6, 2009 at 8:39pm — No Comments

RSS

© 2020   Created by Chris.   Powered by

Badges  |  Report an Issue  |  Terms of Service