MySQL GTID Replication – Making DDL Changes to a Slave and Catching Up to Master

MySQLreplication

Here's what I would like to do:
1. hang a slave off my production master.
2. stop replication
3. adjust some data types and create some indexes on the slave (no data mods)
4. restart replication
5. once the slave has caught up, lock the db & swap slave & master

-the goal, obv. is to have no significant downtime while making some changes that will take time (200G table getting modified)
-it should be possible, because the DDL changes I'm making have to do with shrinking data types & adding constraints – nothing that changes the actual values of data.
-since GTID replication is row-based, I don't know if replication will choke trying to insert, for example, 11 digit ids into a 5 digit id column, even if the values are in range.

Best Answer

You have described a common way to make DDL changes. Yes, it should work. But... please describe the datatype change further.

If you are ALTERing a COLUMN that is current INT SIGNED to be a SMALLINT SIGNED, and if none of the values are bigger than 32767, there should be no problem. If you are changing from SIGNED to UNSIGNED, and none of the numbers are negative, then no problem. Etc. In general, if the old value will fit in the new datatype, then it is OK. If it won't fit, then something will be lost. 1 million into a SMALLINT UNSIGNED will probably become 65535.

As for the application, how does mysql know it is getting an 11-digit id? It sees a number of some number of digits and shoves it into the target size.

Do SHOW WARNINGS; after the DDL.

Related Solutions

MySQL Replication – Setup Master-Master-Slave Replication

First of all, I dislike your setup. When Server B fails, the Server C will not be in replication anymore. If you want to be safe, that the data is on every server in sync, you should try to use a cluster. A cluster is minimum 3 servers and would fit your environment.

Next question for me is, why don't you use just one master (Server A) and two slaves (B+C)?

To answer your question, you need to activate the relay log, so that all changes to the slave are stored for the other slaves:

# REPLICATION slave to other slaves #
log-slave-updates              = 1
relay-log                      = /var/lib/mysql/relay-bin
slave-net-timeout              = 60

MySQL – Copying Raw Data Files for Master-Slave Replication and GTIDs

I assume GTID has been already enabled on the master:

Stop the master server
Copy your data directory to the slave host
Start the master. Technically it is not needed, but if you do not feel safe with what you do and want to be 100% sure everything is correct before continuing with replication- you can start it in read-only mode. Once you see that it is not needed, you can skip that step. This is only to avoid doing anything wrong on the master because you do not trust yourself.
Delete or move away specifically the auto.cnf file so it can be recreated with a different number on restart (otherwise it will be considered the same server and replication will fail)
Start the slave host
Configure replication, the difference would be that instead of using a file and a log position, you will execute CHANGE MASTER TO ... MASTER_AUTO_POSITION = 1;
Start replication - slave will automatically keep up with the changes. If for some reason the auto_position does not work, you will need to SET gtid_purged to the value on SHOW MASTER STATUS of the read_only master.
Check that replication is running and without errors with SHOW SLAVE STATUS- gtid_executed should show the same range than the master. Remember to unset read_only on the master if you set it before.

Everything is fully documented on the manual (coming from standard replication). You have a Percona post talking about it here.

Best Answer

Related Solutions

MySQL Replication – Setup Master-Master-Slave Replication

MySQL – Copying Raw Data Files for Master-Slave Replication and GTIDs

Related Question