// archives

mysql

This tag is associated with 27 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 > […]

The valuable value of MySQL NULL

In many programming languages, coders often use NULL or null as an equivalent to the numeric ‘0’ (zero) in comparison tests. This normally works out, but it’s worth mentioning that in the database world, ‘NULL’ means something else. To illustrate this, in MySQL we can see that querying NULL returns a string representing the keyword/value […]

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

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