DB copy within MySQL that is faster than `mysqldump`?
I have a production db that I'd like to copy to dev. Unfortunately it takes about an hour to do this operation via mysqldump | mysql and I am curious if there is a faster way to do this via direct sql commands within mysql since this is going into the same dbms and not moving to another dbms elsewhere.
Any thoughts / ideas on a streamlined process to perform this inside of the dbms so as to eliminate the long wait time?
NOTE: The primary goal here is to avoid hour long copies as we need some data very quickly from production in the dev db. This is not a question about locking or replication. Wanted to clarify based on some comments from my including more info / ancillary remarks than I should have initially.
You could set up a slave to replicate the production db, then take dumps from the slave. This would allow your production database to continue operating normally.
After the slave is done performing a backup, it will catch back up with the master.