Skip to content

Tag Archives: mysqldump

MySQL – A simple script to truncate all MySQL tables in a Database

Wednesday, December 22, 2010
The official logo of the database query MySQL

Want a quick and dirty way to Truncate all the tables in a MySQL DB?  You can use the following one line script.

mysqldump -ppassword YourDBName –no-data dumpfile | mysql dumpfile

What this does is uses mysqldump to output all the tables in the database “YourDBName” into a dump file.  This dump file contains drop statements, and create table statements for each table in the database.  What this effectively does is drops every table, and recreates it WITHOUT any data inside.  Be very careful, since this is a very powerful script, it has the ability to completely wipe out your database with no hope of recovery unless you have a current backup.

For additional ways to execute specific commands against all tables in a DB, look into using mk-find in Maatkit, it is an excellent tool for advanced MySQL scripting.

Related articles
Filed in MySQL, Scripting | Also tagged , , , , , , , , , , , , , , , , , | Comments (0)

MySQL – Best way to remove all data from a table

Monday, November 22, 2010
MySQL's logo. Converted from EPS with Scribus ...

There are multiple ways in which to remove all data from a MySQL Table.

The first is to use the DROP TABLE command, which will remove the entire table with data.  This basically removes the entire table, you will not be able to recover the structure or data.

The second is DELETE * FROM Table which will remove the data,  leaving the auto-increment values alone, as well as the rest of the table structure.  This method is very slow on large tables using Innodb.  This is because each record has to be written to the log.  In general, you should never use this method to remove all data from a table.

The third method is to issue a TRUNCATE TABLE command which will quickly remove all the data from the table, reset the auto-increment values (but leave them as auto-increment columns, so they will just start at 1 and go up from there again).

Lastly, you can use the nuclear option, which is as follows :

mysqldump -ppassword YourDBName YourTblName –no-data dumpfile | mysql dumpfile

This will drop the table, and then recreate it all from scratch.

Related articles
Filed in MySQL, Scripting | Also tagged , , , , , , , , , , , , , , | Comments (0)

MySQL – Restoring from a Dump Syntax

Friday, March 26, 2010

Here’s how to pass the arguments to MySQL to restore from a Dump:

mysql [database name] < [backup file name]

The “[database name]” specifies the name of the database to restore, and “[backup file name]” specifies the full path and filename to the file generated by mysqldump, such as backup.sql. You should also specify your MySQL username and password as usual using -uroot -ppassword.

If I wanted to restore the database named maindatabase from the file named backup.sql, I would run:

mysql maindatabase< backup.sql

Filed in MySQL, Restore Backup | Also tagged , , , , , , , , , , | Comments (0)