In the age of jetsetting and space travel and ORMs and such, MySQL DBAs are the least sophisticated ones nowadays, usually fighting terabytes or petabytes of data with army of shell scripts – as there’re no nice frameworks to explain what you want to do in MySQL administration. The nice thing about proper object frameworks is that they allow to concentrate on the work and logic done, allowing to think on the process done, rather on languages/APIs/etc.

For example, moving a slave to another master down a replication topology could be expressed this way (this is a working code, actually):

slave = mysql(options.slave)
oldmaster = mysql(slave.get_master())
newmaster = mysql(options.newmaster)

oldmaster.lock()
oldpos = oldmaster.pos()
newmaster.wait(oldpos)
newmaster.lock()
oldmaster.unlock()
slave.wait(oldpos)
slave.change_master(newmaster)
newmaster.unlock()

I’m sure transaction group/global IDs would simplify the process …

Only the other day I was talking with someone who does a lot of work on the shell command line, but hadn’t used the GNU screen tool, so I’d better scribble a post about it as I regard it as an absolute must-have for any remote work, for multiple reasons.

First of all, what screen does. You start screen inside a terminal session (local or SSH remote), and then you can create additional sessions though Ctrl-A C. The initial screen is number 0, the next one 1, and so on. You can switch between screens with Ctrl-A # where # is the screen number. This way, you can have multiple things going within a single ssh connection, very handy. But that’s not all!

If you get disconnected (it happens and you reconnect, your screen sessions will still be there, and running too. You can reattach with screen -r. To do a nice disconnect, you can do Ctrl-A D (detach) before …

I’m observing the process of most awesome SHOW commands being abolished, destroyed and some weird information_schema tables are introduced instead.

Say, even though you can select configuration variables using @@syntax, you can’t do same for much more interesting to DBAs status variables in any more interesting logic.

Apparently instead of doing

SHOW STATUS LIKE "questions"

one has to do this now (I’m being dramatic here, above hasn’t been removed yet, but hasn’t been expanded for better usage either):

SELECT VARIABLE_NAME, VARIABLE_VALUE
FROM INFORMATION_SCHEMA.GLOBAL_STATUS
WHERE VARIABLE_NAME="QUESTIONS"

Do note, those SQL standard followers will get caps-lock button swapped with space bar soon.

Of course, we, DBAs, know that one can simplify stuff by creating stored routines:

CREATE FUNCTION `gstatus`(v varchar(64)) returns varchar(1024)
return
( SELECT …

One thing you learn when you start to manage several servers is that life is easier if things are done the same way. I manage a few MySQL database instances on a few different hosts and here are a list of some things which I think could do with improvement. Note these views are from a UNIX point of view and for Windows MySQL DBAs many of the comments may not be appropriate. Also I’m not talking here about the administration of a single database, but the problems when you administer multiple instances.

Issues

First lets talk about the issues I notice in my day to day usage of MySQL. Then I’ll try and come up with some suggestions as to how these issues might be resolved.

• Give up root earlier and expect to run as a non-root user. While it’s true that the mysqld process normally runs as the mysql user it’s also true that to be a MySQL DBA is almost impossible at the moment if you don’t have root …

I’ve been working on various different MySQL related issues and maintenance procedures some of which have not gone according to plan.  Here is a recipe that may help you avoid wasting a lot of time, especially if your database is large.

In order to do some of these tests make tests against a server configured identically to the one you plan to work on but instead which has no data. That is the mysql database needs to be complete but the other databases need to be dumped with the –no-data or -d options.  Don’t forget to also include any triggers or stored routines.

Now run the “procedure” on this “emtpy instance”. As it has no data most things run very quickly. So if you have issues you can repeat the procedure in no time. Restoring the instance too is easy as it’s tiny. This makes the whole procedure scriptable and you can be confident in the results.

Once you are …

The ibdata file is too big 10GB, and actually we've only about 2GB (data+index) in innodb storage engine.How we can defragment this file and reduce it?How is this happened?By default the ibdata file created initially by (innodb_data_file_path = ibdata1:10M:autoextend) and auto extended by (innodb_autoextend_increment = 8MB) when it’s needed, and this file (tablespace) contain all innodb tables (

I’m happy to note (this is internal Open Query happiness but I’m pleased to share) that so far we have a 100% renewal rate for our Proactive Services for MySQL subscriptions. Some of the early clients have grown in the initial period and are have now moved to a higher # of hours (this can also be changed upward during a term), which is of course excellent both for the clients and for us.

I was in eager anticipation of this time since the introduction of the concept late last year, as it is of course the essential proof of whether a subscription service actually works over time. Ideally, you’d want renewal to be a simple straightforward process, with the client having experienced the value of the service. This is relatively straightforward in this case, since it’s not an insurance, emergency or retainer type arrangement – the client actually gets benefits each and …

In large vBulletin forum we had strange problem in memory table "session", we've 25M post, 1.7M user, 20K online user.So we change engine of session table to InnoDB and set configuration of innoDB as follow (be careful this configuration is not proper for other tables because this is good in performance but bad in crash and recovery, and data reliability)innodb_data_home_dir = /dev/shm/mysql/

Here's something to be aware of. The table_rows column in the information_schema.tables table does not accurately reflect the number of rows in the actual table.This weekend I was moving a db from one host to another. As a good DBA, I took the row counts from each table I was moving so I could check it on the other side. When the copy was done, I queried information_schema.tables again and

Yesterday, The Pythian Group issued a press release about my book, Pythian’s partnership with Sun, and our new “MySQL Adoption Accelerator Package”. I am not a marketing guru, but I can tell you what we the package means in terms of new work that the MySQL teams have been doing.

Basically, the MySQL Adoption Accelerator Package combines customized training with a comprehensive audit of systems. The name “Adoption Accelerator” makes it sound like it’s only for new applications that are almost ready to go live. What the program actually does is have us evaluate your systems, and intensively train you in the areas you want and need. The program is designed to suit all your needs, whether it’s teaching you about one topic (say, query optimization) or an entire range of topics, from Architecture to ZFS (special issues with running MySQL on ZFS, that is, but that did not fit a cute …