MySQL Replace function

I don’t recommend over using the REPLACE() function in MySQL.  It completely destroys performance.  However, sometimes, either by poor design, or just oversight, you have to replace something in order to make a join work, or the application code work.  REPLACE() is case-sensitive. The syntax is pretty simple: REPLACE(`field`, ‘find’, ‘replacewith’) Example: Fruit Values: Apple, Orange, Pear,...

Read More

Replicating Warnings

Warning Statement may not be safe to log in statement format You may get this error if you are replicating using statement based replication and not row based replication. The fix for the most part is simply to avoid “LIMIT” clauses. Do not use limits on Updates or Deletes because the records may be stored physically different on the replicated server and if you aren’t careful, you may delete undesired data.

Read More

Inserting from the results of a MySQL SELECT

If you need a quick way to insert into another table from the results of a query, the best way would be to use the INSERT-SELECT Syntax. It’s quite simple. INSERT INTO tableA (FieldA, FieldB) (SELECT FieldC, FieldD FROM tableB) ; Note that field A & C and B & D should have the same data type. Using this method you can avoid any secondary programming language or loops to achieve your insert.

Read More

Testing a replicated backup system

Testing a replicated backup system

If you have a high need for availability for a production application, but don’t have the money to invest in full fault tolerance networking, machinery, and access, you’ll need to have some sort of realtime backup system in place should your primary environment go down. This poses the issue of how do you keep one system on standbye, but be sure that it’s ready to go at all times. The image below shows one possible setup. The...

Read More

Resync a double replicated MySQL Database

Resync a double replicated MySQL Database

Certain scenarios need a master database replicated to multiple slaves instead of just one. If you have a very heavy read application you might benefit from a number of slaves. It also provides a sort of double backup if one slave drifts away from the data it’s supposed to have. Double replication also allows you to test a backup system. I have a separate article dedicated to that. With a double replication you’ll have a setup like...

Read More

MySQL Replication Re-Syncing

MySQL Replication Re-Syncing

I may be in the minority here, but I’ve spent alot of time resyncing my MySQL replication slaves. They just seem to drift away from an exact duplicate of the master. Here’s a quick list of tips to remember when performing this task. Make sure you STOP the MySQL service on the master before you make your snapshot.  Otherwise you’ll have no idea where to start your slaves.  When you restart the service, you’ll see what...

Read More