1

I am trying to export a data from a table, Here's an example tables,

# table
role_permission:

role_permissionID  roleID

      1               100
      2               101
      3               100


# table
role:

 roleID          organizationID 
  100                 3121
  200                 1212

Here, role permission table is linked with role table by roleID column.

I want to take a dump of role_permission table using mysqldump but only for organizationID=3121?

I know how to take dump of role table but not role permission table.

mysqldump --compact --no-create-info -u root -p RF role --where="organizationID=100" > role.mysql

Please do help?

user_12
  • 1,778
  • 7
  • 31
  • 72

1 Answers1

1

You can use. The folder has to have write permissions and if You use --secure-file-priv you will get an error 1250 and have to enable it

SELECT r1.*,r2.* 
FROM role_permission  r1 
     INNER JOIN role r2 ON r1.roleID = r2.roleID 
INTO DUMPFILE '/tmp/world';
nbk
  • 45,398
  • 8
  • 30
  • 47
  • I am getting this error that you mentioned, `OperationalError: (1290, 'The MySQL server is running with the --secure-file-priv option so it cannot execute this statement')`, and I don't have access to change settings in mysql hosted server. – user_12 Sep 05 '21 at 17:03
  • yes you can follow this https://stackoverflow.com/questions/31951468/error-code-1290-the-mysql-server-is-running-with-the-secure-file-priv-option – nbk Sep 05 '21 at 17:44
  • When I use this strategy, the json column data I am not able to import this csv file, I used INTO OUTFILE. Json data is not parsing when importing this exported data – user_12 Sep 06 '21 at 03:21
  • the dumpfile should be a sql file and not a csvmake the comanf as it is and open the sql file – nbk Sep 06 '21 at 10:37