how to drop data of only one table in mysqldump?

user_1829920 0
Viewed 139

My situation: a webshop running Shopware6, database quite big (34GB total) but most of it is the logs (table log_entry = 28GB) and the saved shopping carts (table cart = 3GB).

I would like to do a mysqldump but for 2 tables log_entry and cart, I would like to save only the schema.

I know how to do only the schema for all tables with the --no-data flag or the data only with the --no-create-info flag and to ignore a table with the --ignore-table=[tablename].

Is my best option to do 2 dumps, one with the schema only and a second one with data only where I ignore the 2 tables?

that would then give

mysqldump -u user -p $dbname --no-data > backup_schema.sql
mysqldump -u user -p $dbname --no-create-info --ignore-table=$dbname.cart --ignore-table=$dbname.log_entry > backup_data.sql
shopware backup mysql
2 Answers

If you want to use native mysqldump, you cannot avoid to make two calls as already mentioned by yourself.

We use the GDPRdump tool by SmileSA for such jobs, where you can leave out (truncate) and even anonymize data during the dump.

There is already a Shopware 6 template for this on GitHub https://github.com/portaltech-reply/gdpr-dump-shopware

A less sophisticated solution which basically does what you already tried in a bit more flexible way and into one dump-file, is https://github.com/amenk/SelfScripts/blob/master/mysql-stripped-dump (self-link)

user_2885680

If it works, it might be your best bet. Although, is it possible to send it SQL statements directly in your environment? Another way might be to export the data into CSV format using an SQL statement that gets the exact data you want. This code would get just the data (username, email and state):

SELECT username, email, state
FROM TABLENAME
INTO OUTFILE '/temp/yourdata.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

This one will get the Columns & Data together:

SELECT 'username', 'email', 'state'
UNION ALL
SELECT username, email, state
FROM TABLENAME
INTO OUTFILE '/temp/yourdatafull.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

I know this might not be the exact answer you were looking for, but it might give you an alternative idea for a secondary or alternative backup method. Or at the very least it is handy to drop a file that will load easy into excel that you can play around with to do some manual calculations or data mining. Although a drawback is I believe if you do this, it will use the first datatype when you do a join so if you have dates or numbers after it might confuse it a bit. Also if you have the --secure-file-priv set to ON you will only be able to output to the specific directly specified in the MySQL settings.

Of course if you have an environment where you are saving integers and dates as strings, I think you should be fine. Will need some testing on that for sure, just stumbled across that over here if you want more information on this method:

https://www.databasestar.com/mysql-output-file/

user_159207860
Related