MySQL
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 [...]
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 $$ [...]
