I recently wrote about non-deterministic queries in the replication stream. That’s resolved by using either MIXED or ROW based replication rather than STATEMENT based.

Another thing that’s not fully handled by STATEMENT based replication is temporary tables. Imagine the following:

1. Master: CREATE TEMPORARY TABLE rpltmpbreak (i INT);
2. Wait for slave to replicate this statement, then stop and start mysqld (not just STOP/START SLAVE)
3. Master: INSERT INTO rpltmpbreak VALUES (1);
4. Slave: SHOW SLAVE STATUS \G

If for any reason a slave server shuts down and restarts after the temp table creation, replication will break because the temporary table will no longer exist on the restarted slave server. It’s obvious when you think about it, but nevertheless it’s quite …

As you probably know, Tungsten Replicator can replicate data from MySQL to MongoDB. The installation is relatively simple and, once done, replication works very well. There was a bug in the installation procedure recently, and as I was testing that the breakage has been fixed, I wanted to share the experience of getting started with this replication.

Step 1: install a MySQL server

For this exercise, we will use a MySQL sandbox running MySQL 5.5.31.

We download the binaries from dev.mysql.com and install a sandbox, making sure that it is configured as master, and that it is used row-based-replication.

$ mkdir -p $HOME/opt/mysql
$ cd ~/downloads
$ wget …

You might find a warning like the below in your error log:

130522 17:54:18 [Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statements writing to a table with an auto-increment column after selecting from another table are unsafe because the order in which rows are retrieved determines what (if any) rows will be written. This order cannot be predicted and may differ on master and the slave.
Statement: INSERT INTO tbl2 SELECT * FROM tbl1 WHERE col IN (417,523)

What do MariaDB and MySQL mean with this warning? The server can’t guarantee that this exact query, with STATEMENT based replication, will always yield identical results on the slave.

Does that mean that you have to use ROW based (or MIXED) replication? Possibly, but not necessarily.

For this type of query, it primarily refers to the fact that without …

DrupalCamp Conference is hold in Göteborg, Sweden on May 25, 2013.

MySQL is part of this show and I would like to invite you to our session on "Scalability and Availability with MySQL Replication" given by Sven Sandberg on Saturday-May 25, 2013 @ 13:45-14:25!

So, if you are close to Göteborg or attending this event, do not forget to come to listen Sven's talk!

Global Transactions Identifiers are one of the new features regarding replication in MySQL 5.6. They open up a lot of opportunities to make the life of DBAs much easier when having to maintain servers under a specific replication topology. However you should keep in mind some limitations of the current implementation. This post is the first one of a series of articles focused on the implications of enabling GTIDs on a production setup.

The manual describes very nicely how to switch to GTID-based replication, I won’t repeat it.

Basically the steps are:

• Make the master read-only so that the slaves can execute all events and be in sync with the master
• Change configuration for all servers and …

Now that I'm back doing just database stuff, I've come to realize I've gotten a little sloppy about doing my homework.  Homework's never been my favorite thing in the world, but it often reduces stress when your under the gun during an outage or upgrade...

We had a MySQL database server that's been slow on DML changes, and based on the slowest statements being 'COMMIT', we had a good mind

2013 is on it's initial months and we already have 5.6 GA and the first release of 5.7, 5.7.1 DMR with lots of exciting new features, future is promising!

A new feature added in replication was the NONBLOCKING option to SHOW SLAVE STATUS command.

In the past if we stop slave during a big transaction, until the transaction was applied we cannot execute SHOW SLAVE STATUS to see the slave progress. The latter operation would block until the former finish, this would disable all external monitoring or third-party applications that need a immediate response from server.

To solve this problem, NONBLOCKING option was added to SHOW SLAVE STATUS, when it is used we can obtain immediate response from server about slave progress. As a …

I configure all our master databases to use row-based binary logging where I work. In my opinion it is a much safer option than statement-based replication. The advantages and disadvantages of both types of MySQL replication are detailed in the online documentation here. You can't view the events a slave is applying directly with 'show processlist' but by issuing 'show open tables where in use' you can detect what table is receiving the attention of the SQL thread. If you need more information the mysqlbinlog command must be used to decode the slaves relay logs or masters binary logs.

Our developers often change a lot of rows with a single update statement. This usually results in some reasonable replication lag on downstream slaves. Occasionally the lag continues to grow and eventually nagios …

Send to Kindle

These days I’ve answer some questions about replication lag, and I realized that most of people does not correctly understand how this process works internally on MySQL, and why does delays happen:

• MySQL replication: most important config parameters for performance on slave server?
• mysql replication delay very serious

See the bellow image, it represents asynchronous replication on MySQL, I highly recommend you to read my other post:

…

Send to Kindle

See the bellow image, it represents how asynchronous replication works on MySQL

Bearing in mind the number on the image, let’s see how the process works:

• Item 1 in the image represents the clients executing queries on master, note that the master is capable to handle multiple simultaneous connections (it can be configurable by max_connections variable). 
• Master process these queries, and save it to his binary log(item number 2 in the image), then it can later on be replied on the slave.
• The slave has 2 threads reubuntu mount ext4 partitionsponsible to deal with replication :
  • 
    …