Copy result of select statement into a local database table

I would like to copy the contents of a remote DB query into a local database table. What would be the best way to do this? Currently I’m trying something like:

mysql --host=example.com --user=syncdb --password=syncdb_pass db_name 
      -e "SELECT user, MD5(user) FROM auth_user" 
> mysql -u root --host=127.0.0.1 --password="" localdb

The first part of the command works fine to grab the data — the second part I’ve just added as a sort of pseudo-code to show what I’m trying to do. How could I accomplish this? Another way to view it would be as:

INSERT INTO mylocaldb.table (SELECT user, MD5(user) FROM myremotedb.auth_user)

Answer

An alternative tool to use for this purpose is execsql.py (https://pypi.org/project/execsql/), which will read a set of commands from a script file, where the script commands can summarize data on the remote server and copy those data to the local server. If there are more operations that you’re going to be doing with the data after it’s copied to the local server, commands to carry out those steps can be included in the same script following the copy operation.

Assuming that the initial connection is made to the local database, and that the script to be run is named “get_remote_users.sql”, then you can start the process with a command like:

execsql.py -u root -w get_remote_users.sql 127.0.0.1 localdb

The meaning of the command line options and arguments is described at http://execsql.osdn.io/syntax.html

The following script can then be used to connect to the remote database, create a view, copy that view to the local database, then delete that view.

-- !x! connect to mysql(server=example.com, db=db_name, user=syncdb, need_pwd=True, password=syncdb_pass) as remote
-- !x! use remote
create or replace view userlist as
select user, MD5(user) as user_md5 from auth_user;
-- !x! copy userlist from remote to replacement remote_users in initial
drop view userlist;
-- !x! use initial

The lines starting with -- !x! are metacommands to execsql.

This creates (or replaces) a table named “remote_users” in your local database.

If this is a frequently occurring operation and you can leave the userlist view in place on the remote server, then this script can be reduced to just the two lines containing the connect and copy metacommands.

Disclaimer: I wrote execsql.py.