Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 4 down vote favorite

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?

share|improve this question
1  
Are you trying to simply copy data over, or are you trying to set up replication? – Chris Henry Jun 26 '12 at 1:17
The fastest way would be to stop mysql, copy over the data directory, then start mysql on the new computer. That would require downtime on the source, though. – Keith Randall Jun 26 '12 at 1:25
I just want to copy data. – Problemania Jun 26 '12 at 3:04
How to determine what files to copy to the target? – Problemania Jun 26 '12 at 3:10
@user001 Usually just the whole /var/lib/mysql directory. It is configurable though, check your my.cnf file (usually /etc/mysql/my.cnf) for the datadir variable. – Keith Randall Jun 26 '12 at 3:53

migrated from stackoverflow.com Jun 27 '12 at 11:58

1 Answer

up vote 1 down vote

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.

share|improve this answer
What does --opt do? After the pipe, can I use mysql -h target_db_ip -u user_target -p -C new_table? Also, which database will the table be created in on the target? – Problemania Jun 26 '12 at 3:10
1  
--opt is a combination of several things like remove a table of this name if it exists already in the target, etc. the -C means compress the table in transit for faster transfer and the database in the target will be the same as the database in the source by default unless you specify otherwise – hackartist Jun 26 '12 at 3:53
What if the database under same name does not exist on the target machine? Will it be automatically created? – Problemania Jun 26 '12 at 20:33
Not 100% sure, I have always just gone into phpmyadmin on the target and made the database by hand then used this command to get the tables I want copied over but I am sure it will mention somewhere in the documentation – hackartist Jun 27 '12 at 2:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.