Rain City Consulting

I maintain a large (~20G) MySQL InnoDB database. This database is currently replicated to a single MySQL instance. I need to replicate this database to another MySQL instance. To do this, I must

1) Get a snapshot of the data
2) Get the binary log position of the data snapshot
3) Load the data snapshot in the MySQL instance
4) Configure and run the slave MySQL instance

Get a Snapshot of the Data

This is fairly simple, but gets a little difficult with a large InnoDB database (especially if you're too cheap to buy InnoDB Hot Backup, which I am). I could do a MySQL Dump (mysqldump), but this is not practical with a large database (though, in some cases it may be your only option). The other option is to do a binary copy of the database. This binary copy will work if you don't mind copying all InnoDB tables that live in the source database i.e. if you have two databases running in your master MySQL instance that both use InnoDB, the binary InnoDB files will have both database's data. If that didn't make sense, the gist is that one cannot separate InnoDB data by database like one can with MyISAM tables.

My current set up is a read/write MySQL instance replicating to a read-only instance. I'm going to shutdown the read-only instance (it will failover to the read/write instance) and get a snapshot from the read-only instance.

MySQL documentation note: The MySQL documentation often talks about a "default database". This stumped me for a while. The default database is "the one selected by USE" (http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html).

On the slave:

$ # Stop the MySQL server
$ /etc/init.d/mysql stop
$ cd $MYSQL_DATA_DIR
$ # Copy the binary files
$ scp -r innodb-data master.info db1 relay-log.info hostname-relay-bin.* new-slave-server:/var/tmp/dbtmp
$ # My InnoDB data files are stored in the $MYSQL_DATA_DIR/innodb-data directory.


On the new slave:
* Edit the my.cnf file to have the same InnoDB config as the slave you just copied from
* Add a new, unique server-id into the my.cnf file
* Add replicate-do-db values to the my.cnf file if you want to replicate only certain DBs

$ mv /var/lib/mysql/ib* /var/tmp
$ mv /var/tmp/dbtmp/innodb-data /var/lib/mysql
$ mv /var/tmp/dbtmp/*relay* /var/lib/mysql
$ mv /var/tmp/dbtmp/master.info /var/lib/mysql
$ chown -R mysql:mysql /var/lib/mysql


I had to do some editing of the relay files (contents and file names) to make it work.