In most cases you'll use a SSH-pipe to push a query stream in a database or use the mysql-client directly on the sending host. If you don't need any encryption ‐ for example in a private network ‐ you will lose time (which is expensive in a migration scenario).
A faster alternative is using netcat(1). To stream query's from one side to another (as I said without the mysql-client on the sending host). Simply use this command on the host which is running the MySQL-daemon to open a new socket:
nc -lp 2222 | mysql -u<user> -p<pass>
...and this command from the sender to push the query's to the new opened port 2222:
cat query.sql | nc -q1 example.com 2222
To take a snapshot of your database, don't export it and read the dump with the commands above. Peter describes a faster way Using netcat to copy MySQL Database, which uses the same approach but pipes the stream to tar(1) instead of mysql client utility.
Note: The option -q1 is necessary to close the connection right after EOF was sent.
You might also be interested in the following
- Is it possible to avoid query parsing inside of MySQL?
- Write data asynchronously to MySQL
- Sorted Array to MySQL
- Importing entire Wikipedia into MySQL
Sorry, comments are closed for this article. Contact me if you want to leave a note.