Setting up MySql Master-Slave Replication without locking?

I'm trying to set up replication for a database roughly 80gbs in size. From all the documentation I read it seems like when y开发者_如何学编程ou do the inital mysqldump to get the data to the slave you have to do a global

FLUSH TABLES WITH READ LOCK

Then record the binlog position after the dump. Is there anyway to set up replication without locking the database? Or at least do table-level locking?

You can use Percona XtraBackup if you are using only InnoDB tables. If you have MyISAM tables, you will require at least a brief lock. If you have only MyISAM tables, and you must avoid any downtime, then dirty tricks are required. The details vary greatly from situation to situation, and generally have way too many subtleties and forks in the decision tree to discuss in a forum like this.

Without locking all tables you will not be able to create a consistent dump; locking single tables makes no sense.

If your database only contains MyISAM tables you should be able to simply copy the datadir to the slave. There are ways to achieve this for InnoDB, but iirc these tools are not free; try searching e.g. for innodb hotcopy.

For MyISAM tables, you can use mysqldump

mysqldump --single-transaction --master-data DATABASE_NAME

This will require a short lock on the database for a consistent dump, but will give you a SQL file you can run on the slave to set the correct replication position. See the manual for more information.

If you're using InnoDB, XtraBackup is a free utility which should do the job. This blog post seems to provide a good overview of the complete process.

In either case, you need a lock to ensure a consistent dump. But the duration of the lock is quite short and shouldn't impact performance.