Moving the binary location of MySQL for replication

Question

Is it possible to move the contents of /var/lib/mysql when setting up replication instead of doing mysqldump> for export and mysql < for insert on the slave? I want this because in our current setup it takes ages.

We have a mixture of MyISAM and InnoDB.

Answer 1

Yes, you have two options. Both require none of the data changes in any of the files during copying.

1. Completely shutdown the server you are going to clone. Copy it over, make relevant .cnf changes (especially server-id), start up the new instance.
2. If you're using LVMs you can take an LVM snapshot while the server is running and then copy that snapshot over.

If you're doing this to get a slave running, in both cases you'll want to have run show master status before shutting down/taking the snapshot so you'll know what to CHANGE MASTER TO when spinning up the new slave. I would recommend setting read_only=1, then check the position. If going the snapshot route you can set read_only=0 so the master can continue to take writes.

• Lars: That's brilliant, I'll give it a try, thank you very much. Is it the whole /var/lib/mysql folder or just the files corresponding to the DB names?
• atxdba: Everything in /var/lib/mysql, not just the underlying db folders. Something else to keep in mind, the /var/lib/mysql/mysql folder is a db too, one that is version dependent. Make sure the server you copy it to has a mysql version >= where you're copying it from. If it's newer, that's fine but be sure to run the mysql_upgrade tool.
• Lars: We just tried this with both including and excluding the mysql DB, as well as run mysql_upgrade and not. Both Slave_IO_Running=Yes and Slave_SQL_Running=Yes but no data is beeing transferred while updating on the master. Nothing in error logs either on both tries.
• Lars: And of course we put read lock on the master, show master status and then change master on the slave
• atxdba: Does the show processlist on the master show a thread under the slave's username running command 'binlog dump'?