0

Suppose, we have 2 tables: t1,t2 in database db: Expected the below code to dump tables based on conditions specified in the where clauses:

mysqldump --login-path=login_path \
    --single-transaction db t1 \
    --where="user_id>200" , t2 --where="id>200" > /tmp/users_data.sql

However the following error is popped:

mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ * FROM t1 WHERE id>200 Unknown column 'id' in 'where clause' (1054)

How to dump multiple tables with where clauses using mysqldump correctly? If it's not possible, is there a workaround?

  • 1
    IMO it's a wrong job for mysqldump – akuzminsky Mar 4 at 22:15
  • the goal is to dump multiple related tables atomically – user1264304 Mar 8 at 12:48
  • Check mustaccio’s answer. His solution #1 will work for you – akuzminsky Mar 8 at 14:48
1

MySQL dump has only a single where clause which makes the use hard in two tables like you do.

One way would be in t1, create a generated column called id that is effectively user_id.

ALTER TABLE t1
ADD id UNSIGNED INT GENERATED ALWAYS AS (user_id),
ADD INDEX id_key ( id)

Adjust type to match the type of user_id.

ref: https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html

0

Solution 1:-

Dump Table data Using SELECT ... INTO OUTFILE statement which writes the selected rows to a file & Restore into table using LOAD DATA statement.

Eg.

  • Take backup of table from Backup Server using

    SELECT * INTO OUTFILE '/tmp/result.txt'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM DB.T1 WHERE user_id>200;
    
  • Create Same T1 table on Restore Server Using

    CREATE TABLE T1 (...) 
    
  • Copy files from Backup Server to Restore Server ( Using the scp command on Linux)

  • Restore Data in Table using

    LOAD DATA INFILE '/tmp/result.txt' INTO TABLE DB.T1;
    

Solution 2:-

Need to prepare shell script which will pass value to where option in mysqldump command for each table.

  • I like your SELECT INTO FILE solution. You can wrap two of them into a transaction and achieve what OP asks for. – akuzminsky Mar 8 at 14:46
  • Solution 2 will not work correctly because two mysqldumps would be two transactions – akuzminsky Mar 8 at 14:47
  • @akuzminsky the following error pops Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement when running the select * into file... and have no permission to /var/lib/mysql-files/ – user1264304 Mar 10 at 21:10
  • That’s a separate problem. You may want to create another question for that – akuzminsky Mar 10 at 21:31
  • @user1264304 Check parameter value of --secure-file-priv & assign that path into SELECT * into /PATH/filename & if you have linux based server check privileges of that path. – JYOTI RAJAI Mar 11 at 7:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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