Mysql – What’s the fastest way to replicate a MySQL database from one machine to another

backupMySQLmysqldump

What's the fastest way to replicate a MySQL database from one machine to another? The two machines are connected via LAN. How to do it without mysqldump?

If using mysqldump, is the fastest way

$ mysqldump -h localhost -u foo -pbar mydb > mydb.sql

and then copy mydb.sql to desination machine and do 

$ mysql -h localhost -u foo -pbar < mydb.sql

? When importing, do I need to specify the database?

Best Answer

I would personally do it in one step with a pipe using mysqldump like so

mysqldump -u user_source -p --opt database_name table_name | mysql -u user_target -p --host=target_db_ip -C target_database

but if mysqldump is not allowed just save the dump somewhere you can access it then use wget to move it to the other server and unpack there using mysql. I normally use this command because it doesn't require me to stop the database and is a single command with no files rather than a process where I have to manage where the data files are.

Related Question