MySQL
Video – Using the MySQL client Tee command
MySQL Minute video explaining how to capture session output to a text file. This can be super helpful when firefighting or just experimenting.
Use MySQL to generate a tiny url style string
We all appreciate getting small urls and the most famous link generator is http://tinyurl.com/. They do a perfect job of creating shortened URLs using random strings. The idea is pretty simple. Create a string of N char length and use it as key to reference a value. Perhaps it’s a URL or a document or […]
XtraDB Cluster on CentOS 7
Setting up XtraDB Cluster (aka Galera) is straight forward…or so I was told. To do this correctly, there are some tricks. Some published. Some disparate. I’m going to save you HOURS of Googling for answers and break down the process here. Just know that from version to version, dependency changes have a HUGE impact. I […]
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 > […]
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 […]
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 […]
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 […]
