mysql
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 [...]
Use BASH to extract MySQL ACL list
Need to quickly extract all the GRANT info for your database users? Here’s a one-liner: for i in $( mysql -u readuser -pReadPassword -h your.host -e “SELECT CONCAT(user,’@\”,host,’\”) AS ACL FROM mysql.user ORDER BY user” -s ); do mysql -u readuser -pReadPassword -h your.host -e “SHOW GRANTS FOR $i” -s ; done ; Here’s a [...]
Using MySQL Stored Procedures for User Authentication
Scenario: You want to authenticate users into a private web site by accepting an e-mail address and a password. You don’t want to do a bunch of security on the front end however you do want to use a token on the client for various purposes. Also, you want the tokens to expire automatically and [...]
Creating a MySQL Function to convert miles and kilometers
MySQL has the ability to create user defined functions that can be called just like the built-ins. There was a recent project that involved some distance calculations. As part of the performance testing, we experimented with moving the logic around to different parts of the application stack. Here’s a short example of two functions created [...]
Testing MySQL data migrations Part 1
On occasion as database administrators we have to migrate data for various reasons. No matter the reason or the origin, we have to test the migration to see if the result is what we expect. The question is: How? In Part 1 of this series, I’m going to explore a simple “smoke test” of a [...]
MySQL Insert On Duplicate Update Madness
you may need to perform an INSERT INTO SELECT FROM statement. Often when you do this, you’ll run into duplicate key errors. To overcome this, you’ll need to add ON DUPLICATE KEY UPDATE syntax to the end of your insert.
MySQL Best Practice: Forget ALTER, use RENAME instead
MySQL is notorious for a few bummer scenarios. One of them is when you have a table with a couple hundred thousand rows or more and an indexed column or two, and you’ve been asked to add a column to meet a data model requirement. You know this will cause an outage or at least [...]
Creating a Date Dimension Stored Procedure
Our data warehouse was getting pretty big. In fact, it was it was getting into the hundreds of gigabytes of fact data. We started looking for ways to save hard drive space. It became apparent that storing redundant varchars, datetimes, enums and other large column types was unnecessary and causing the scale constraints. Part of [...]
Sysbench for MySQL Burn-In Testing
The reason for my last post about getting Sysbench installed on OS X, was that I had a chore to burn-in four MySQL hosts. They are currently replicating which is normal behavior for this group. I wanted to add some stress testing. Here is the script I created for his purpose. You can modify the [...]
