mysql
Comparing MySQL table structures across hosts
Have you ever had to quickly scan table structures to see if they were the same on two separate hosts? Eyeballing this can be a total pain. I wrote a quickie Bash script to automate this. Please note, this is NOT a thorough test. Just a glimpse or cursory check. A deeper look will reveal […]
Scripting MySQL replicated slave restarts
The recent reboot requirements at Amazon AWS have brought up the topic of automated restarts of MySQL database servers. I’d like to offer a safe solution for scripting the procedure. Let’s pretend that we’ve already confirmed there’s no production traffic. Here are the basic commands: Stopping MySQL Server Stop replication mysqladmin -u root -p stop-slave […]
InnoDB lost file pointers to partitions
Recently I went through the exercise of setting up a new replicated slave host. I proceeded through my normal list of tasks to get the new host stood up. This time, something different happened. Replication crashed. Hard. Last_SQL_Error: Could not execute Update_rows event on table core_stats.plays_hourly; Can’t find record in ‘plays_hourly’, Error_code: 1032; handler error […]
So mysql.com got hacked. What does that mean for me?
http://techie-buzz.com/tech-news/mysql-com-database-compromised-sql-injection.html http://www.pcworld.com/businesscenter/article/240609/mysqlcom_hacked_to_serve_malware.html There’s been so much drama recently about MySQL’s public web site getting hacked. It’s causing mis-directed hyperbole about the MySQL database. I’m sure vendors of closed source technologies love the bad press for MySQL, but as a user of production MySQL databases and database administrator specializing in MySQL technology, I’m hearing people ask […]
Understanding binary and relay log disk space management for MySQL
D R A F T I’m writing this as an attempt to help non-database-administrators (nDBAs) understand how to triage disk space crises on MySQL hosts. Here’s the scenario: – One of your MySQL hosts is consuming disk space rapidly. You perform investigation at the operating system level and discover that the logs, not the data […]
Database noob making changes and something goes wrong…
Recently, I answered a question on a forum site from a user who stated they were “database noobs”. They made a change and had a problem. Here’s the advice I gave: Performing an ALTER to change storage engines won’t make rows disappear. However, let me offer some advice since you said you’re ‘database noobs’ in […]
Row Deletion MySQL Stored Procedure
Scenario: You’ve got a huge history table that’s getting too big to deal with. It’s grown out of your control and the application has an SLA of 100% uptime. What now? My solution: The answer is to archive rows off the system, then delete rows by primary key. DROP PROCEDURE IF EXISTS proc_delete_id_range_safe; DELIMITER $$ […]
Why I won’t use HandlerSocket in Production…
There is a ton of hype around HandlerSocket right now. It looks awesomely wonderfully fast, fun and useful… however, I suspect there’s another side to the story. These are my notions gleaned from my limited knowledge and experimentation: HandlerSocket has some great use cases, but from a DBA perspective, it is a dangerous toy. 1.) […]
Identifying InnoDB Foreign Key Constraints
When using InnoDB tables in MySQL, the option exists to apply foreign key constraints. Data modelers do this for various reasons, which I’m not going to explore here. What’s important is that these constraints are religion for DBAs (and client software) and have to be respected to maintain data integrity. Knowing the hierarchy of data […]
