How do I truncate all data in my MySQL database?

Question

I'm using MySql 5.5 on Mac 10.7.5. From the shell (I'm using bash), I'd like to be able to run a command o truncate data in all tables. Also, I'd like to enter a single command that won't prompt me for a password. I've tried this, that I found on another SO post, but no dice. What is a shell command I can use to truncate all database table data?

mysql -umyuser -p -e 'SET FOREIGN_KEY_CHECKS = 0; show tables' my_db | while read table; do mysql -e -umyuser -p "truncate table $table" my_db; done
Enter password: 

mysql  Ver 14.14 Distrib 5.5.25, for osx10.6 (i386) using readline 5.1
Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Usage: mysql [OPTIONS] [database]
  -?, --help          Display this help and exit.
  -I, --help          Synonym for -?
  --auto-rehash       Enable automatic rehashing. One doesn't need to use
                  'rehash' to get table and field completion, but startup
                  and reconnecting may take a longer time. Disable with
                  --disable-auto-rehash.
                  (Defaults to on; use --skip-auto-rehash to disable.)
  -A, --no-auto-rehash
                  No automatic rehashing. One has to use 'rehash' to get
                  table and field completion. This gives a quicker start of
                  mysql and disables rehashing on reconnect.
  --auto-vertical-output
                  Automatically switch to vertical output mode if the
                  result is wider than the terminal width.
  -B, --batch         Don't use history file. Disable interactive behavior.
                  (Enables --silent.)
  --character-sets-dir=name
                  Directory for character set files.
  --column-type-info  Display column type information.
  -c, --comments      Preserve comments. Send comments to the server. The
                  default is --skip-comments (discard comments), enable
                  with --comments.
  -C, --compress      Use compression in server/client protocol.
  -#, --debug[=#]     This is a non-debug version. Catch this and exit.
  --debug-check       Check memory and open file usage at exit.
  -T, --debug-info    Print some debug info at exit.
  -D, --database=name Database to use.
  --default-character-set=name
...

No dice, you'll probably have to iterate through all of the tables and `TRUNCATE` them one by one. — Julian H. Lam, Mar 18 '13 at 13:15
What do you mean "no dice"? I posted this question because I couldn't get the command to work in the first place and am hoping someone knows of a way to fix it. — Dave, Mar 18 '13 at 15:24
If you don't want to type in the password, you can put it into you my.cnf config file. You probably want to set the permissions so 600 so others can't see it. — ckim, Mar 18 '13 at 17:46

score 5 · Accepted Answer · answered Mar 18 '13 at 13:22

5

You can dump database without data:

mysqldump -u myuser -p --databases --add-drop-database --no-data my_db > my_db.sql

And restore it after that

mysql -u myuser -p < my_db.sql

answered Mar 18 '13 at 13:22

Rostyslav

428
3
13

Very smart! (+1) You can even do this in one go: `mysqldump -u[user] -p[password] --databases --add-drop-table --no-data my_db | mysql -u[user] -p[password]`. Please notice, in the canonical form of `-u` and `-p` there is no space between the option switch and its value. I also suppose you meant `--add-drop-table` instead of `--add-drop-databases`. Replace `--databases` with the name of one database to only purge one given database. – RandomSeed Mar 18 '13 at 13:34

How do I truncate all data in my MySQL database?

1 Answers1