Alter all table's - discard tablespace 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 ?
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:


Partial backups
Restoring individual tables


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

mysqldump --no-data $schema &gt; schema-ddl.sql
mysql -N -B &lt;&lt;'EOF' &gt; 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 &lt;&lt;'EOF' &gt; 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.