MySQL Bugs: #11321: LOAD DATA FROM MASTER which doesn't skip the mysql DB

Bug #11321	LOAD DATA FROM MASTER which doesn't skip the mysql DB
Submitted:	14 Jun 2005 15:19	Modified:	31 Aug 2006 19:02
Reporter:	Andreas Ley	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
LOAD DATA FROM MASTER would have been a perfect way to start replication from a master where the update lock is bearable if it wouldn't skip the mysql database.

How to repeat:
See documentation (BTW, one shouldn't be forced to fill out this field for a Feature Request)    

Suggested fix:
I'd wish there was a LOAD DATA FROM MASTER WITH GRANT TABLES or LOAD ALL DATA FROM MASTER or any other option to just replicate all and everything from the master.

Hi Andreas,

There are reasons why the mysql database is skipped using this statement, but an extra option is something that can be discussed I guess.

Regards,
Geert

The reasons why the mysql database is skipped are clearly documented, and I didn't intend to discuss them. However, there are situations where one wants to replicate the whole server, including the mysql database, and there is no possibility to do this while LOAD DATA FROM MASTER holds the lock. Thus I'd like to propose an option to LOAD DATA FROM MASTER to tell it to also replicate the mysql database (or at least the authentication information from the mysql database).

Since the current implementation of LOAD DATA FROM MASTER
and LOAD TABLE FROM MASTER is very limited, these statements
are deprecated in versions 4.1, 5.0 and 5.1 of MySQL. We will
introduce a more advanced technique (online backup) in a 
future (>5.1) version, that will have the added advantage of
working with more storage engines.

For 5.1 and earlier, the recommended alternative solution to
using LOAD DATA|TABLE FROM MASTER is mysqldump on the master 
piped (or output copied) to the mysql client on the slave. 
This also has the advantage of working for all storage engines.
Another alternative is mysqlhotcopy, which is faster because
it does a binary copy, but works only for MyISAM tables.

A deprecation warning has been added for these statements in
4.1, 5.0 and 5.1.