// archives


This category contains 28 posts

Finding duplicate rows in MySQL

I saw this question online reading “How do I identify duplicate rows in MySQL” and it’s been gnawing at me so here’s my approach: Make a duplicate of your subject matter CREATE TABLE a2 LIKE a1; INSERT INTO a2 SELECT * FROM a1; Add a ‘checksum’ column to the new table ALTER TABLE a2 ADD […]

Getting the mysqldump you always hoped for.

Dumping data from MySQL might be seem like a covered topic, but just so it’s clear, you aren’t stuck outputting SQL. There’s at least 3 options for outputting data. Option 1: The SQL way Good’ole fashioned structured query language output of your whole database in a single file. mysqldump -u root -p -f your_schema_name_here > […]

MySQL EXPLAIN Deception and Fraud

Okay, let’s get this straight. I wrote the headline to get some attention. Now that you’re hooked, I have to admit for full disclosure, I’m a MySQL fanboy. This post is simply to draw some attention to the fact that EXPLAIN is just one (very important) tool in a developer’s arsenal for diagnosing performance problems. […]

Backing up with mysqldump part deux

MYSQLDUMP is a utility for outputting database contents into text files. This is nice, but has many configuration options. One, often missed detail is the specific permissions required to accomplish the job. I made a note on the mysql manual (bottom of page) to illustrate what I consider to be a best practice for automated […]

How to restore root to MySQL server on MAMP

See my dba.stackexchange.com answer: How to restore root to MySQL server on MAMP Assuming you went with a generic install of MySQL, my procedure for MAMP is: 1. Edit your my.cnf, probably located at: /usr/local/my.cnf, adding the lines: skip-grant-tables skip-networking 2. Restart mysqld sudo su – /usr/local/mysql/bin/mysqladmin shutdown nohup /usr/local/mysql/bin/mysqld_safe & exit 3. Change root […]

How I setup MySQL Multi Master Replication

There are tons of articles online about setting up MySQL multi-master replication. Recently, a friend asked me to send him a link so he could try it on his own. I must have read 20 articles and could not find one that did it my way. So here it is. My BASIC step-by-step guide to […]

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 […]