Question :
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?
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.