Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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, because in our current setup it takes ages

We have a mixture of MyISAM and InnoDB.

Best regards Lars

share|improve this question

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer
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? – Lars Feb 4 '12 at 17:27
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. – atxdba Feb 4 '12 at 18:00
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 Feb 4 '12 at 18:13
And of course we put read lock on the master, show master status and then change master on the slave – Lars Feb 4 '12 at 18:15
Does the show processlist on the master show a thread under the slave's username running command 'binlog dump'? – atxdba Feb 4 '12 at 18:23
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.