Welcome to Tip #1 in MySQL Replication Series. In this tip we will go over what to do when you only want to replicate certain data to slaves. Most general way to tell what is replicated to which slave is to include following configuration directive in my.cnf file depending on your environment and your goals. We will start with slave server side options since you have more flexibility on slave on what to replicate and what not to.
You are currently browsing the archive for the MySQL category.
For a given value of “natively”… | Carsten’s Random Ramblings
A quick (but somewhat simplistic) way to test performance of different methodologies is to turn off the query cache and use the BENCHMARK function. BENCHMARK loops over an expression a given number of times, and always returns 0, which is in itself quite uninteresting. But we can then look at the execution time to see how long things took.
I love it when the verbose expressions turn out wicked fast.
Colin Charles Agenda » Blog Archive » Compiling MySQL UDFs on Mac OS X
Compiling and installing a User Defined Function for MySQL on Mac OS X seems tricky. There are installation notes, but they seem to be sparse on OS X (the comments are clues, though).
[The Unexpected SQL Injection] Web Security Articles – Web Application Security Consortium
Abstract:
We will look at several scenarios under which SQL injection may occur, even though mysql_real_escape_string() has been used. There are two major steps at writing SQL injection resistant code: correct validation and escaping of input and proper use of the SQL syntax. Failure to comply with any of them may lead to compromise. Many of the specific issues are already known, but no single document mentions them all.
Although the examples are built on PHP/MySQL, the same principles apply to ASP/MSSQL and other combinations of languages and databases.
How to notify event listeners in MySQL at Xaprb
A high-performance application that has producers and consumers of some resource, such as a queue of messages, needs an efficient way to notify the consumers when the producer has inserted into the queue. Polling the queue for changes is not a good option. MySQL’s GET_LOCK() and RELEASE_LOCK() functions can provide both mutual exclusivity and notifications.
The Data Charmer: MySQL 5 general purpose routine library – II: Using arrays
Implementation
Arrays, as part of the MySQL General Purpose Stored Routines Library are implemented as datasets, all belonging to one table, and identified by a unique name. The details of the implementation are hiddenby the interface, and you should not worry a lot about it.
General concepts about the array library
Whenever possible, each routine is implemented both as a procedure and as a function. Only when a function returns an array value or an array property, then there is no corresponding procedure. This is a design choice, so that you are free of calling the routine in the most convenient way. When using a procedure that has a function synonym, a user variable with the same name of the procedure is set with the function return value. For instance, calling function array_create() you get the array ID.
How to implement a queue in SQL at Xaprb
This article explains how to create a fixed-size FIFO (first-in, first-out) queue in SQL, where rows added after a threshold will cause the oldest row to be deleted. There are several ways to do this, but MERGE on Oracle and DB2, and MySQL’s non-standard extensions to SQL, make an elegant solution easy.
Update a PostgreSQL blogger pointed out the obvious method I missed: triggers! There’s also a really neat PG-specific feature that allows it to work even more elegantly on that platform. Well worth a read. I sometimes wish I worked at a PostgreSQL shop so I could have time to learn as much about it as I’ve learned about MySQL.
Technical Notes and Articles of Interest » MySQL Backup & Recovery – Part 1
In order to copy your database, there are two different ways. The first is to shutdown your database first then copy your data files. Be sure to also copy your my.cnf file, this is important in a recovery process. The second is to use FLUSH command. For example.
Optim MySQL: OLAP Paradise – WITH ROLLUP
Though this is an age old concept, but after seeing the ignorance factor of many regarding this, I thought of writing a bit about it. ROLLUP is a GROUP BY modifier that adds extra rows to the output which contain summary data. At each level of aggregation the aggregated column is filled with a NULL value.
MySQL AB :: Follow Up on Rolling Sums – Self Joins vs User Variables
What about using a user variable to make the rolling sum from the beginning? Something simple enough like this: