Mysql – Replication with only update and insert

If you are sure the Slave is sync'd with the Master, you could do the following on the Slave:

• Activate Binary Logging
• Perform a full mysqldump every three months
• Run FLUSH LOGS; every midnight
• After the FLUSH LOGS;, make a physical copy of new binary logs, except the last one
• OPTIONAL Set expire_logs_days=180 in my.cnf and run SET GLOBAL expire_logs_days=180; (I used 180 because this give you 90 days to have access to changes from the previous two quarters. If you produce more than one binary log per day, make sure you have enough diskspace to hold 180G of binary logs or just reduce the number from 180. The idea is to have the changes to the Master. They are stored already in the Master's Binary Logs. Those same changes can be on the Slave as well if you activate Binary Logging on the Slave. The Master never needs to feel anything throughout this process.

You could use pt-table-checksum and pt-table-sync to sync up the Slave from the Master. (I wrote a shell script to automate them using mk-table-checksum and mk-table-sync)

Doing these things will allow you to reload a snapshot of the beginning of a Quarter onto another server. Replay all logs on the Slave (using mysqlbinlog) to any datetime from those logs to view different days within the next Quarter.

If all your data is InnoDB, here is an Alternative from the Master

• Binary Logging on the Master should already be enabled due to Replication
• Perform a full mysqldump (using --single-transaction) every three months
• Run FLUSH LOGS; every midnight
• After the FLUSH LOGS;, make a physical copy of new binary logs, except the last one
• OPTIONAL Set expire_logs_days=180 in my.cnf and run SET GLOBAL expire_logs_days=180; (I used 180 because this give you 90 days to have access to changes from the previous two quarters. If you produce more than one binary log per day, make sure you have enough diskspace to hold 180G of binary logs or just reduce the number from 180.

These are just some ideas you can try to organize using the Slave or the Master.

Ok,

Here a sample of config to setup your replication :

On Master in your my.cnf add :

server-id                       = 1
log-bin                        = /database/bin-log/mysql-bin
binlog_cache_size              = 16M
slave_compressed_protocol       = 1
binlog_format                  = MIXED
max_binlog_size                 = 100M

On your slave in my.cnf add :

server-id                       = 2
relay-log-space-limit           = 50G
relay-log                       = /database/relay-log/relay-bin
relay-log-index                 = /database/relay-log/relay-bin.index
skip-slave-start
read_only                       = 1

Now you have two solutions, dump or binary copy.

In both case you should made your transformation (myisam to InnoDB) on your slave slave and switch your slave to master. Also you should active binlogs to catch the master position.

First solution, you can make a dump of your master ( with the master-data option to catch master position) On your .sql dump file, search and replace ENGINE=myisam to InnoDB and load thé dump to thé slave server. You have all tables on InnoDB engine :) after started réplication and catching up your the late : "second behind master= 0 " it´s time to switch slave to master ( with a new dump (now from slave to master) and take care to switch the conf also).

The second solution is the same but with a cold backup, stop mysql, copy datadir (4gb Will probably done in ~ 10mins dépends on your solution : scp, usb keys...) start your slave and alter each table with engine=innodb. Like thé first one, when it´s done, you should switch master/slave and alter ex-master tables.

Caution with "alter table engine=InnoDB" Mysql Will create a InnoDB table and copy data in it so your table size is tablesize x 2 (when innodb table is dobe, mysql drop myisam table)

I'm on my phone so sorry if there are many strange word... :)

(I didn't detailed replication set up cause it's not the subject but we let me know if you want some help.)