CODEWITHSUNDEEP
mysql
RailsEnthusiast
RailsEnthusiast

Reputation: 1291

Selectively dumping data with mysqldump

I am trying to export my database using mysqldump and the sql file should satisfy the following conditions

  1. The file should not contain data from table_x ( Keep the structure )
  2. Delete/skip data that is older than 10 days from table_y ( Keep the structure )

The conditions may increase in future for different tables.

This dump file will be used on local environment and will work as replacement for production database.

And Is there a way to write all these conditions inside a file?

Upvotes: 1

Views: 689

Answers (1)

Finlay Beaton
Finlay Beaton

Reputation: 620

mysqldump is what you want: http://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump --single-transaction --host=localhost --user=MyUser --password=MyPassword MyDatabase MyTable --where="mydatefield > 'insert-10 day old date here'"
mysqldump --single-transaction --host=localhost --user=MyUser --password=MyPassword --ignore-table=MyDatabase.MyTable --ignore-tableMyDatabase.MySecondIgnoredTable

This answer could be improved by changing the 'insert-10 day old date here' placeholder with a backtick bash command that is curdate - 10 days.

Specifying the password on the command line can be a security risk and is discouraged, especially in scripts or shared hosting environment. Check the help file above to see the more complex process of creating a credentials file to use in the command.

If you have MyISAM tables instead of InnoDB you'll want to switch --single-transaction with --skip-add-locks

I realize this question is from a year ago, but someone might still find useful.

Upvotes: 1

Related Questions