38

I'm writing some db utility scripts, and one of the tasks I need to do is rebuild the data only, but leave the schema intact. What is the easiest way to automate this from the command-line using bash and the mysql tools (no php, etc)?

Update: I'd like the solution to handle all tables in one command, and if possible, not need to be updated if tables are added or removed.

Dana the Sane
  • 14,762
  • 8
  • 58
  • 80

4 Answers4

64
TRUNCATE tableName;

This will empty the contents of the table.

Edit in response to the Q edit: It seems from my quick test that you will have to do at least 2 queries as it seems that "show tables" cannot be used as a sub query, I don't know how to do this in bash so here is a PHP example, hopefully it will help.

<?php      
mysql_connect('localhost', 'user', 'password');
$dbName = "database";
mysql_select_db($dbName); /*added semi-colon*/
$result_t = mysql_query("SHOW TABLES");
while($row = mysql_fetch_assoc($result_t))
{
   mysql_query("TRUNCATE " . $row['Tables_in_' . $dbName]);
}
?>

At a minimum this needs some error handling.

Nicholas Shanks
  • 10,623
  • 4
  • 56
  • 80
UnkwnTech
  • 88,102
  • 65
  • 184
  • 229
  • Is there a way I can select the names of all the tables in the schema to feed into this? Otherwise I'll have to update the script when new tables are added. – Dana the Sane Jan 17 '09 at 22:24
  • I tried TRUNCATE * for you but that didn't work. Looks like you'll have to run a SHOW TABLES query and act off that. – Ross Jan 17 '09 at 22:31
  • I found this link http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/truncate-all-tables-part-i.aspx which has a stored procedure that may be easily adapted to what you need, or may work out of the box. – UnkwnTech Jan 17 '09 at 22:43
4

If you are on unix/linux you can use the shell to run:

mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]

Or Rational Relational has a blog post on how to write stored procedure to do this.

Brian Fisher
  • 23,519
  • 15
  • 78
  • 82
  • This was the kind of solution I was looking for, unfortunately it fails on the fk constraints. Since I don't really want to drop all of the constraints and re-add them, writing my own truncates ends up working better. – Dana the Sane Jan 17 '09 at 22:50
  • You could try SET FOREIGN_KEY_CHECKS = 0; – Brian Fisher Jan 18 '09 at 01:38
  • 1
    This is a nice way of doing it but why the grep for DROP? The OP asks for a way to leave the schema intact and this way will just drop tables. Skip the grep-part and the script works perfectly. – Andreas Wederbrand Jan 12 '12 at 12:37
1

Here's a BASH one-liner to truncate all tables from a list of databases:

for j in database_name1 database_name2; \
do for i in `echo 'show tables ' |mysql $j \
|grep -v 'Tables_in'`; do mysql $j -e "truncate $i"; done; done

Please note, truncating will remove all the data from the target tables without any prompting. Perhaps change "truncate $i" to "describe $i" first to make sure the tables in the result set are the ones intended to be emptied.

One more thing: if you want to iterate over every table in all MySQL databases (except information_schema and mysql, I would hope!), substitute the following for the above "database_name1 database_name2":

`echo 'show databases' | mysql | awk '$1 != "information_schema" && \
$1 != "mysql" {if (NR > 1) {print}}'`

So, here's a sample that's less destructive; it performs OPTIMIZE for all tables in every MySQL database (exceptions as noted above):

for j in `echo 'show databases' | mysql | \
awk '$1 != "information_schema" && $1 != \
"mysql" {if (NR > 1) {print}}'`; do for i in \
`echo 'show tables ' |mysql $j |grep -v \
'Tables_in'`; do mysql -e "optimize table $j.$i"; \
done; done

Modify the "action" performed as needed and with much trepidation!

rjamestaylor
  • 3,052
  • 1
  • 19
  • 10
  • This doesn't work with foreign key references. You'll get foreign key constraint errors. – Ray Booysen Jan 18 '09 at 20:41
  • Nowadays I think you'll find most MySQL users use FKs, I certainly do. – Dónal Jan 20 '09 at 15:43
  • Don: while I agree that FK constraint usage is proper and recommended I doubt hat "most MySQL users" use them. I've seen more than a thousand instances of MySQL in the past year and very few used the full relational and ACID features of modern MySQL. That said, dumping/reloading the schema is best. – rjamestaylor Jan 21 '09 at 12:52
  • This was actually a really neat idea - to get around the FK constraint, just pipe the output of the entire for loop to mysql in a single command like this: `for i in $(echo 'SHOW TABLES' | mysql -uroot -p | grep -v "Tables_in"); do echo "DELETE FROM $i;"; done | mysql -uroot -p ` – John Calcote Jul 14 '16 at 16:41
0

For people who want to do this via phpMyAdmin, have a look at this question:

How Can I Delete The Contents Of All Tables In My Database In phpMyAdmin Without Dropping The Database?

Community
  • 1
  • 1
mathijsuitmegen
  • 2,270
  • 1
  • 34
  • 36