I’m installing a 4 server replication system. I want advice on best practise on how to do this.
What I have in mind is:
1 Write v5.0.x Innodb server
I want to install Innodb on this machine to ensure that the data that is loaded by my applications is 100% there or not at all. Innodb will automatically roll back my transaction if it doesn’t complete.
2 Read v5.0.x MyISAM/Memory server
I want to use memory storage engine for my data that will be queried the most to make the queries as fast as posible. I know that if the server crash the data will be lost BUT this will ONLY be a read server and it will only get the data from the Write server.
1 Backup/Maint. v5.0.x MyISAM server
This server will be used to make hourly backups and act as a backup server for any of the other servers.
The Read and Read/Maint. server will receive all data and updates from the write server.
My question is:
1 Is this the best way of doing this(90% of DB actions at present is reads.)
2 Can Innodb be replicated to MyISAM.(if not, please supply a reason)