2

After having an awful experience with a dedicated server and spending 3 whole days repairing corrupted databases (Yes I know.... backup!).... I have narrowed the issues down to a few websites. The trouble is, these websites are Magento websites.

I am having to import the .ibd files into the /var/lib/mysql folder and DISCARD/IMPORT the tablespace.

Is there a way to alter ALL tables in the database at once, i.e ALTER TABLE * DISCARD TABLESPACE and then move the files across... Then ALTER TABLE * IMPORT TABLESPACE ?

1

Not as part of MySQL, but tools like Percona Xtrabackup makes the process a bit faster for exporting/backing up the tables, allowing things like using regular expressions or lists for filtering:

For importing a list of tables, you can use some automation oneliners like this one found on Bill Karwin tools:

mysqldump --no-data $schema > schema-ddl.sql
mysql -N -B <<'EOF' > discard-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` DISCARD TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF
mysql -N -B <<'EOF' > import-ddl.sql
SELECT CONCAT('ALTER TABLE `', table_name, '` IMPORT TABLESPACE;') AS _ddl
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='$schema' AND ENGINE='InnoDB';
EOF

Basically, you can use the information_schema.tables table for listing the tables you want using "dynamic sql". For example, change $schema above for the desired database name.

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.