MySQL
Dependency error installing mylvmbackup on Ubuntu 8.04
I’ve started an investigation of MySQL Backups using LVM . I’m working with Lenz’s mylvmbackup but I found it both used Perl and needed a number of dependencies installed. Installing dependencies failed on my test system, yet I found it actually worked when I went back to my dev system (but it is not configured with LVM for full testing).
The size of memory tables
I was doing some database sizing in MySQL 5.1.30 GA for memory tables. Generally I have used INFORMATION_SCHEMA.TABLES data_length,index_length as a reasonable guide. However working with a MEMORY table, after deleting rows, the size did not decrease as expected.
Using Flipper to manage MySQL Pairs
As discussed previously in Options using MySQL pairs I have started evaluating the strengths and weaknesses of various open source options. This is an evaluation of Flipper , a product from Proven Scaling a MySQL consulting organization.
Most valuable MySQL slides
My presentation from the 2008 MySQL Conference on Top 20 Design Tips for Data Architects has been receiving a lot of traffic lately. Most recently this presentation was featured on the front page of Slideshare , as well as last month I made the top 10 of Hacker News .
Some MySQL pairs terminology
In response to a number of comments, I thought I would clarify the scope of my discussion regarding Options using MySQL pairs before I begin. As mentioned their is no one way or type of configuration for MySQL in a HA solution, however the simplest progression from a single Master/Slave environment is the concept of a pair of servers, configured to support a fail over and fail back via MySQL Replication.
Options using MySQL Pairs
Configuring a production environment using a pair of MySQL servers in a Master/Fail Over Master situation is a common process to provide many benefits including supporting failover, backup/recovery, higher availability for software & database upgrades.
Where is the innovation?
The 2009 MySQL Conference has closed it’s submissions for papers. This year the motto is “Innovation Everywhere”. Last weekend’s Open SQL Camp in Charlottesville, Virginia, we had the chance to talk about the movements in the MySQL ecosystem.
When mysqldump –no-set-names matters
I had this perplexing problem yesterday where a mysql dump and restore was producing different results when using MaatKit mk-table-checksum. mk-table-checksum --algorithm=BIT_XOR h=192.168.X.XX,u=user,p=password --databases=db1 --tables=c DATABASE TABLE CHUNK HOST ENGINE COUNT CHECKSUM TIME WAIT STAT LAG db1 c 0 192.
ORDER BY (the lesser known way)
We all know with MySQL you can use ORDER BY with a list of columns to return an ordered set, e.g. ORDER BY name, type, state; I often use the syntax ORDER BY 1,2; which I’m surprised that some people do not know about.
It begins, the downfall of current Web 2.0 sites
The current US financial situation has claimed a victim in the Web 2.0 world — Uber . I’m not sure if this is the first significant name, but it will not be the last site running MySQL where investors will be quick to cut losses and move on.
Selecting wise indexes
Indexes are a great way to improve performed in a MySQL database, when used appropriately. When used in-appropriately the impact can be a degradation of performance. The following example from Movable Type shows how when reviewing the slow query log I found numerous occurrences of Inserts take 3 or more seconds, with no reported lock contention time for this insert.
Why you do not use GRANT ALL ON .?
Why you do not use GRANT ALL ON *.*? I was with a client today, and after rebooting a MySQL 5.0.22 instance cleanly with /etc/init.d/mysqld service, I observed the following error, because you always check the log file after starting MySQL.
A neat trick for a row number in a MySQL recordset
While working for a client, I had need to produce canned results of certain different criteria, recording the result in a table for later usage, and keep the position within each result.
Securing your OS for MySQL with JeOS
Do you have a full time System Administrator? Do you have only a part-time SA, or none at all? Packet General’s Data Security and PCI Compliance solutions run on a dedicated appliance, based on a “Just Enough Operating System” (JeOS) to minimize exposure.