SQL Server – Replicating Wide Table in MS SQL

I think that, if your purpose is to avoid locking, you should use pt-online-schema-change (a tool from Percona Toolkit) or Gh-ost (from GitHub).

Here is how pt-osc works:

• Creates a ghost table identical to the original table, but empty
• Runs ALTER TABLE on the ghost table
• Creates triggers on the original table, which insert new rows in the new table, and updates and deletes them if they are already there.
• It copies the rows in chunks, monitoring the server performance (it can slow down or pause its work if necessary)
• Switch the table names

gh-ost is very similar, the main difference is that it creates no triggers, and uses the binary log instead to detect the changes to the original table.

To answer your question more directly - yes, it is possible to add a column on the slave. And most probably it will simply work, as long as it has a default value. But it is very likely that in the future you will regret this choice, because you'll need to do something that will break replication, like adding a column in the master. I recommend to sure to check the documentation and fully understand the dangers, before doing such a thing.

The first obvious question is why would you want to do this? It's probably a bad idea and completely unnecessary, since you can join across databases:

SELECT * FROM db1.table1 JOIN db2.table2 ON db1.table1_id = db2.table2_id WHERE ...

Next, in order to understand why this doesn't work, you first need to understand the basics of MySQL replication. In summary:

1. On the master, a statement is executed
2. On the master, that statement is written to the binary log
3. On the slave, the SLAVE IO_THREAD connects to the master, reads the binary log from a known position, and writes the queries to the local relay log
4. On the slave, the SLAVE SQL_THREAD reads the relay log and executes the statements.

You seem to be expecting that by having multiple rewrite statement in place for the same database, that the SQL thread will execute the relay log statements twice. This is not the case. From the documentation, replicate-rewrite-db simply changes the value of USE db as it is being written to the relay log, so one or the other of your statements is being evaluated, but not both.

To accomplish what you are trying to do, there are a couple options that come to mind

1. Run multiple instances of MySQL on the same host on different ports. This can be done with mysqld_multi. This is very easy and can fit in with your rewrite rules.
2. Use the BLACKHOLE storage engine with AFTER INSERT, AFTER UPDATE, and AFTER DELETE triggers on the master. This is very ugly, but could work for certain use cases

The way #2 would look is like this:

On the master server, create the second database and a copy of the table that uses the BLACKHOLE storage engine:

mysql> create database db1;
Query OK, 1 row affected (0.03 sec)

mysql> create database db2;
Query OK, 1 row affected (0.00 sec)

master> use db1;
Database changed
mysql> create table foo ( id int auto_increment primary key, name varchar(32) not null ) engine=innodb; 
Query OK, 0 rows affected (0.16 sec)

mysql> use db2;
Database changed
master> create table foo ( id int auto_increment primary key, name varchar(32) not null ) engine=BLACKHOLE;
Query OK, 0 rows affected (0.05 sec)

master> use db1;
Database changed
master> CREATE TRIGGER insert_into_blackhole AFTER INSERT ON foo FOR EACH ROW INSERT INTO db2.foo SELECT * FROM db1.foo WHERE id = NEW.id;

master> CREATE TRIGGER update_into_blackhole AFTER UPDATE ON foo FOR EACH ROW UPDATE db2.foo2 SET id = NEW.id, name = NEW.name WHERE id = OLD.id;

master> CREATE TRIGGER delete_from_blackhole AFTER DELETE ON foo FOR EACH ROW DELETE FROM db2.foo2 WHERE id = OLD.id;

On the slave, create the tables as their regular storage engine.

INSERT/UPDATE/DELETE operations into master://db1.foo will get written to the binary log for db2.foo and then get replayed on the slave, where it will exist in both places.

There are a lot of caveats with this, of which here are only a few. I recommend extensive testing.

• ONLY INSERT/UPDATE/DELETE operations will get reproduced in both places.
• ALTER TABLE operations will not get reproduced, so changes will have to be made to both the BLACKHOLE and the main table manually.
• TRUNCATE TABLE operations will not get reproduced.
• It will be very difficult to detect out-of-sync data.
• Bulk operations will block until the trigger has completed. BLACKHOLE is quite fast because it's essentially /dev/null, but in large enough batches simply generating the statements may take a long time. If you perform a bulk INSERT/UPDATE/DELETE of 1000 rows, that will generate 1000 individual statements to execute against the BLACKHOLE table. You can improve on this by setting up a relay slave.
• Because of the conversion from bulk updates to individual row updates, the slave may have a tendency to lag behind.

Using a relay slave: Your replication topology would look like this:

master -> relay -> slave

In the relay slave topology, you'd end up with an unaltered master and instead, the BLACKHOLE configuration would be on the relay slave. Your regular slave that contained the copies would be a slave of the relay. This would prevent any slowdown of the writes on the master. Note, due to the way triggers are applied on slaves, the relay slave topology will only work with STATEMENT based replication.

CAVEAT: I'm certain I've missed some critical detail, so test this extensively.

My advice: Don't do it. I can't think of any good reasons why you would need to have the same exact data replicated in two places on the same server.