KrazyWorks | Category Archive

Convert XLSX to a MySQL Table

October 14, 2021 – 11:49 am

Some people find it hard to let go of Excel even when the situation is ripe for a database. And so dozens of different versions of the same spreadsheet edited by many people are floating …

Bash Scripts and MySQL

October 10, 2020 – 1:49 pm

I frequently use MySQL to store data collected or processed by various shell scripts. The database comes in handy when massaging data with good old awk and sed gets too cumbersome.

Useful MySQL One-Liners

November 8, 2017 – 12:16 pm

I am by no means an expert SQL programmer. Having said that, I’ve been using MySQL for a very long time. This is a collection of simple but useful SQL queries and MySQL configuration options. …

Unlike Oracle, MySQL has no support for parallel SQL queries. A bummer for sure, in light of all the awesome multi-coreness we enjoy these days. There’ve been some attempts (1, 2) to get around this …

A Better Mysqlcheck Script

April 2, 2015 – 10:44 am

Mysqlcheck is a stand-alone MySQL utility for checking, optimizing and repairing tables. It’s a useful tool that allows automating the usually time-consuming database maintenance tasks. One of the advantages of mysqlcheck it is ability to run …

Searching Large Database Tables

December 9, 2013 – 9:19 pm

Recently I ran into a little problem: I needed to make a simple Web UI allowing users to search a database table containing a copy of the Postfix maillog for quarantined mail addressed to them. …

MySQL mysqlhotcopy Script

December 2, 2013 – 1:30 pm

Here’s a simple script to automate mysqlhotcopy runs from cron to backup multiple MySQL databases. The script loops through the list of databases and kicks off an instance of mysqlhotcopy in the background for each one. Really, …

Remove Duplicate Posts in WordPress

December 5, 2009 – 10:28 pm

Below is the SQL script that will attempt to identify and remove duplicate posts in your WordPress database. This script can be useful for autoblogging. If you use plugins like WP-o-Matic to pull full-text RSS …

Duplicating WordPress Installation

June 25, 2009 – 2:51 pm

Whenever you upgrade your WordPress installation or do development work, it is always a good idea to be working on a copy of your main site and not on the real thing. Copying WordPress installation …

Restart Apache, MySQL When Low on Memory

June 25, 2009 – 4:27 am

Heavy traffic, hungry SQL queries, leaky applications will eventually leave your server low on memory. Apache will go on a hunt for swap space and MySQL will start freezing in thoughtful contemplation. Your hard drives …

MySQL Global Search and Replace Script

June 25, 2009 – 3:53 am

A day will eventually come when your need to find and replace a string of text in your database. You don’t know which row, or which column, or which table. Heck, you may not even …

Passing MySQL Commands from Shell Script

June 25, 2009 – 3:30 am

Running MySQL commands from a shell script is a relatively simple task that has a lot of people baffled. Some say its too complicated and suggest using PHP or Perl, others claim doing so is …

Convert MySQL table to CSV file

March 27, 2008 – 4:41 pm

CSV is a field-separated format that can be easily imported into a spreadsheet application. Usually the separator field is a comma. However, sometimes it is easier to select a more unusual character (like “@” for …

MySQL query cache

August 17, 2007 – 11:15 am

Query cache can significantly speed up database operations for identical search queries. This is particularly useful for Web server applications, where multiple requests for the same data are a regular occurrence.
To enable query caching on …

MySQL LOAD DATA Syntax

January 2, 2007 – 8:33 am

Bottlenose Dolphin (Tursiops truncatus) pod swimming over coral reef, Ogasawara Island, Japan

Using LOAD DATA LOCAL INFILE is a much faster way to insert lots of data into a table than using INSERT. The only preliminary step is to format the input file with unique field separators.
In …

Creating table indexes in MySQL

November 21, 2006 – 6:32 pm

Creating an index for specific columns in a database table can greatly speed up search operations. This is important for any database query that use WHERE in the SQL string. Below is a practical example …

The correct MySQL GRANT syntax

September 21, 2006 – 12:18 pm

It is surprising how many people run into problems trying to use the GRANT command: everything from not being able to access a database table to losing admin access to MySQL altogether. There’s a bunch …

Managing your MP3 collection

May 3, 2006 – 10:40 am

Remember all the MP3s you ripped or downloaded sitting in that big directory somewhere on your hard drive? Getting those files organized by genre, performer, or album can be daunting task if the MP3s are …

Generating complex SQL queries with shell scripts

February 21, 2006 – 9:08 am

It is sometimes necessary to use complex select and insert queries with dozens of variables. Writing such queries by hand is a tedious process and the chance of making a mistake is high. A few …

Find and Replace in MySQL

December 28, 2005 – 11:34 am

A simple one-line command to perform find and replace operations in MySQL:

Exporting spreadsheet data to MySQL

December 9, 2005 – 2:10 am

The following is a process for migrating spreadsheet data to MySQL database using a Shell script in a Unix environment. To follow this tutorial you must already know how to create MySQL schemas and tables. …

Install PHP, Mysql, Apache2 on Solaris 9

November 21, 2005 – 11:05 am

A sample procedure for installing PHP, MySQL and Apache2 on Solaris 9.

Articles in MySQL

Editor

Hi Igor

Latest Comments