I'm trying to dump all tables of a specific MySQL database into each file(s) per table from a remote server.
I tried to use mysqldump with option --tab=dir_name as the output directory, but it seems only work locally. When I do it with option --host=remote_db_ip to connect to remote DB server, it only produces .sql files (with only table structure inside) on my server, and throws the following errors because it is using SELECT INTO OUTFILE which is trying to find that output path on the remote server machine.
mysqldump: Got error: 1: Can't create/write to file '/output/path/table_name.txt' (Errcode: 2 - No such file or directory) when executing 'SELECT INTO OUTFILE'
P.S. I know one workaround that is to dump to the remote DB server first then transfer back to my server, but the target database is huge and there is not enough extra disk space for storing dump output on the remote DB server.
MySQL version: 5.6
Best Answer
You could temporarily set up a
sshfsmount of the target directory and use--tab=dir_nameon that mounted directory.