MySql replication with Raw Data and InnoDB tables

Question

I want to start replication on DB that has many InnoDB tables and MyISAM tables. I did everything according to manual Creating a Data Snapshot Using Raw Data Files.

Firstly, I tried copied all tables files with relay log and etc. without ib_logfile and ibdata. Replication didn't work it couldn't find InnodDB tables, after that I tried to do the same with ib_logfile and ibdata, it failed with error message Error 'Unknown table engine 'InnoDB'' on opening tables.

When I tried to copy my.cnf from original server mysql failed to start at all (version 5.1.41).

I am not sure if it's possible to start replication with raw data and InnoDb files. Please, if you have experience, share it with me. How can I deal with it.

Thanks!

Answer 1

When you get 'Unknown table engine INNODB', it means something when wrong with startup. If you look in your error logs, you should be able to get more information.

My first instinct is that your ib_logfile is a different size on your master than what is configured in the slave. If you stop the slave server, delete the ib_logfile from the snapshot when setting up the slave, then restart mysql, it should correct itself.

NOTE: Do not delete the ibdata file! Only the ib_logfiles

Answer 2

Instead of using a tar method, try using rysnc.

I have written posts about how to use rsync a running server with minimum downtime.

• How can I move a database from one server to another?
• MySQL Community Edition: Can I replicate my entire server with another server in one go?
• https://serverfault.com/questions/257394/create-a-mysql-slave-from-another-slave-but-point-it-at-the-master/257426#257426
• Any better way out of MySQL InnoDB log "in the future"? (See Option 02)

Just make sure

• Binary Logging is enabled on the Master
• you copy /etc/my.cnf from master to new slave
• change the server-id in the slave's my.cnf